ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120612]函数索引中使用substr函数.txt

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

原创 Linux操作系统 作者:lfree 时间:2012-06-11 15:18:16 0 删除 编辑
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函数建立这样的索引来访问表.好处就是这样索引可以建立的很小.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-732456/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6291918