[20120612]函式索引中使用substr函式.txt

1.建立測試環境

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

create table t as select rownum id ,cast(dbms_random.string('x',16) as varchar2(16)) name,lpad('x',100) text from dual connect by level<=1e4; 

SQL> create index i_t_name on t(name);

Index created.

SQL> explain plan for select * from t where name=:x;

Explained.

SQL> select * from table(dbms_xplan.display(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

PLAN_TABLE_OUTPUT

---------------------------

Plan hash value: 1766912905

----------------------------------------------------------------------

| Id  | Operation                   | Name     | E-Rows | Cost (%CPU)|

----------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |    115 |    41   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |    115 |    41   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T_NAME |     46 |     1   (0)|

----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("NAME"=:X)

Note

-----

   - dynamic sampling used for this statement (level=2)

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

21 rows selected.

--建立函式索引

SQL> create index if_t_name on t(substr(name,1,5));

Index created.

SQL> explain plan for select * from t where name=:x;

Explained.

SQL> select * from table(dbms_xplan.display(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

PLAN_TABLE_OUTPUT

---------------------------

Plan hash value: 1766912905

----------------------------------------------------------------------

| Id  | Operation                   | Name     | E-Rows | Cost (%CPU)|

----------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |      1 |     2   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T_NAME |      1 |     1   (0)|

----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("NAME"=:X)

       filter(SUBSTR("NAME",1,5)=SUBSTR(:X,1,5))

Note

-----

   - dynamic sampling used for this statement (level=2)

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

22 rows selected.

--如果按照執行計劃的內容,實際上sql語句被轉化為如下:
select * from t where name=:x and SUBSTR("NAME",1,5)=SUBSTR(:X,1,5);
--實際的執行計劃好像沒用filter(SUBSTR("NAME",1,5)=SUBSTR(:X,1,5)).explain 看帶繫結的sql語句至少說明這樣的計劃是可行的.
--不使用環境變數看看.

SQL> select id,name from t where rownum=1;

        ID NAME

---------- ----------------

         1 23C1W6VWH59ORH12

SQL> select * from t where name='23C1W6VWH59ORH12';

        ID NAME             TEXT

---------- ---------------- ---------

         1 23C1W6VWH59ORH12        x

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  cybmv2qjraur2, child number 0

-------------------------------------

select * from t where name='23C1W6VWH59ORH12'

Plan hash value: 1766912905

----------------------------------------------------------------------

| Id  | Operation                   | Name     | E-Rows | Cost (%CPU)|

----------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |        |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |      3 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T_NAME |      1 |     1   (0)|

----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("NAME"='23C1W6VWH59ORH12')

Note

-----

   - dynamic sampling used for this statement (level=2)

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

26 rows selected.

3.分析表看看:

SQL> exec dbms_stats.gather_table_stats(user,'t');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from t where name=:x;

Explained.

SQL> select * from table(dbms_xplan.display(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

PLAN_TABLE_OUTPUT

---------------------------

Plan hash value: 3367455390

-----------------------------------------------------------------------

| Id  | Operation                   | Name      | E-Rows | Cost (%CPU)|

-----------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |      1 |     2   (0)|

|*  1 |  TABLE ACCESS BY INDEX ROWID| T         |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | IF_T_NAME |      1 |     1   (0)|

-----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("NAME"=:X)

   2 - access(SUBSTR("NAME",1,5)=SUBSTR(:X,1,5))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

21 rows selected.

--這個對於我是顛覆性的,查詢name=:x條件,而可以使用IF_T_NAME的索引.

4.使用提示看看:

SQL> variable x varchar2(30);

SQL> exec :x := '23C1W6VWH59ORH12';

PL/SQL procedure successfully completed.

SQL> select /*+ index (t if_t_name ) */ * from t where name=:x;

        ID NAME             TEXT

---------- ---------------- -------

         1 23C1W6VWH59ORH12       x  

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  337p0r8k0cmkb, child number 0

-------------------------------------

select /*+ index (t if_t_name ) */ * from t where name=:x

Plan hash value: 3367455390

-----------------------------------------------------------------------

| Id  | Operation                   | Name      | E-Rows | Cost (%CPU)|

-----------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |        |     2 (100)|

|*  1 |  TABLE ACCESS BY INDEX ROWID| T         |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | IF_T_NAME |      1 |     1   (0)|

-----------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (VARCHAR2(30), CSID=852): '23C1W6VWH59ORH12'

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("NAME"=:X)

   2 - access("T"."SYS_NC00004$"=SUBSTR(:X,1,5))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

31 rows selected.

--可以發現使用提示在查詢name=:x 這樣的條件時,可以IF_T_NAME這樣的函式索引.

--刪除索引i_t_name,再看看情況如何呢?

SQL> drop index i_t_name;

Index dropped.

SQL> select  * from t where name=:x;

        ID NAME             TEXT

---------- ---------------- ------------------------------------------------------

         1 23C1W6VWH59ORH12                                                       

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  69yvh72cyvwsx, child number 0

-------------------------------------

select  * from t where name=:x

Plan hash value: 3367455390

-----------------------------------------------------------------------

| Id  | Operation                   | Name      | E-Rows | Cost (%CPU)|

-----------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |        |     2 (100)|

|*  1 |  TABLE ACCESS BY INDEX ROWID| T         |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | IF_T_NAME |      1 |     1   (0)|

-----------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (VARCHAR2(30), CSID=852): '23C1W6VWH59ORH12'

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("NAME"=:X)

   2 - access("T"."SYS_NC00004$"=SUBSTR(:X,1,5))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

31 rows selected.

--這樣可以得出結論,查詢name=:x這樣的條件,可以透過建立substr(name,1,5)這樣的函式索引來訪問.
--這樣可以想象一種情況,比如name的長度很長,如果前面的幾個字元選擇性很好,可以透過使用substr函式建立這樣的索引來訪問表.好處就是這樣索引可以建立的很小.