[20120612]函式索引中使用substr函式.txt
1.建立測試環境
--建立函式索引
--如果按照執行計劃的內容,實際上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語句至少說明這樣的計劃是可行的.
--不使用環境變數看看.
3.分析表看看:
--這個對於我是顛覆性的,查詢name=:x條件,而可以使用IF_T_NAME的索引.
4.使用提示看看:
--可以發現使用提示在查詢name=:x 這樣的條件時,可以IF_T_NAME這樣的函式索引.
--刪除索引i_t_name,再看看情況如何呢?
--這樣可以得出結論,查詢name=:x這樣的條件,可以透過建立substr(name,1,5)這樣的函式索引來訪問.
--這樣可以想象一種情況,比如name的長度很長,如果前面的幾個字元選擇性很好,可以透過使用substr函式建立這樣的索引來訪問表.好處就是這樣索引可以建立的很小.
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- substr函式函式
- cursor_sharing和substr函式索引函式索引
- ascii函式和substr函式的用法ASCII函式
- insert()與substr()函式函式
- instr、substr函式用法函式
- 索引中使用函式索引函式
- 函式索引使用細節——自定義函式的索引化函式索引
- substr擷取函式 筆記函式筆記
- 【轉】oracle的substr函式的用法Oracle函式
- 函式索引陷阱函式索引
- oracle函式索引Oracle函式索引
- Oracle中REGEXP_SUBSTR函式(字串轉多行)Oracle函式字串
- [20170402]函式索引standard_hash.txt函式索引
- 如何使用函式指標呼叫類中的函式和普通函式函式指標
- 【函式】Oracle中聚合函式rank()使用方法函式Oracle
- python中id()函式、zip()函式、map()函式、lamda函式Python函式
- Oracle之函式索引Oracle函式索引
- 說說函式索引函式索引
- 函式索引的使用細節——常數表示式函式索引
- [20180509]函式索引問題.txt函式索引
- Mysql建表、索引、函式、查詢使用中的坑！！！MySql索引函式
- js字串擷取函式slice()、substring()、substr()JS字串函式
- 自定義函式索引使用及其注意點函式索引
- [20150803]使用函式索引注意的問題.txt函式索引
- 字元函式之Substr(chr,0,m)與Substr(chr,m)的區別字元函式
- 基於函式的索引函式索引
- 函式索引的問題函式索引
- deterministic function 函式索引Function函式索引
- 函式索引的儲存函式索引
- SQL優化--函式索引SQL優化函式索引
- sequence 和索引函式呼叫索引函式
- 【轉】linux下awk內建函式的使用(split/substr/length)Linux函式
- [20180212]函式索引問題.txt函式索引
- SQL優化中索引列使用函式之靈異事件SQL優化索引函式事件
- Rust中的into函式和from函式Rust函式
- Javascript之字串擷取函式slice()、substring()、substr()JavaScript字串函式
- 何時使用函式表示式與函式宣告函式
- 函式: 函式是怎麼使用的?函式