索引失效的一点测试
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/11/index-disable-test.html
链接：http://www.dbaroad.me/archives/2008/11/index-disable-test.html
今天在数据库上看到一个因左侧使用函数，导致索引失效的SQL，简单做了个这方面的测试：
前期数据准备：
sys@TESTDBA>create table t1(a varchar2(10)); Table created. sys@TESTDBA>begin 2 for i in 1..1000000 loop 3 insert into t1 values(i); 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. sys@TESTDBA>create unique index u_ind_t1 on t1(a) nologging; Index created. sys@TESTDBA>exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYS',tabname=>'T1', method_opt=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE); PL/SQL procedure successfully completed. sys@TESTDBA>select owner,table_name,num_rows,last_analyzed from dba_tables where table_name='T1' and owner='SYS'; OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED -------------------- -------------------- ---------- ---------------- SYS T1 1000000 2008-11-06 14:02 sys@TESTDBA>select owner,table_name,num_rows,last_analyzed from dba_tables where table_name='T1' and owner='SYS'; OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED -------------------- -------------------- ---------- ---------------- SYS T1 1000000 2008-11-06 14:02 sys@TESTDBA>select owner,index_name,distinct_keys,num_rows,last_analyzed from dba_indexes where table_name='T1' and owner='SYS'; OWNER INDEX_NAME DISTINCT_KEYS NUM_ROWS LAST_ANALYZED ----------- ------------------ ------------- ---------- ---------------- SYS U_IND_T1 1000000 1000000 2008-11-06 14:02 sys@TESTDBA>select count(*) from t1; COUNT(*) ---------- 1000000 sys@TESTDBA> |
以下是测试过程：
sys@TESTDBA>select count(*) from t1 where a=1; COUNT(*) ---------- 1 执行计划： SQL_TEXT ---------------------------------- select count(*) from t1 where a=1 [Execution Plan Information] -------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | -------------------------------------------------------------------------------- |00[00]SELECT STATEMENT |---- 2717640755.0 ---| | | 159 | |01[01]SORT AGGREGATE | | 1 | 7 | | |02[02] TABLE ACCESS FULL |T1 | 1 | 7 | 159 | -------------------------------------------------- [Predicate Information] -------------------------------------------------- 2 filter:TO_NUMBER("A")=1 |
这里我们看到，因为A是varchar2类型，发生了隐式的转换，导致索引失效。
在右侧加上引号后，索引正常使用：
sys@TESTDBA>select count(*) from t1 where a='1'; COUNT(*) ---------- 1 SQL_TEXT ----------------------------------------------------- select count(*) from t1 where a='1' [Execution Plan Information] ---------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | ---------------------------------------------------------------------------------- |00[00]SELECT STATEMENT |---- 221378319.0 ----| | | 2 | |01[01]SORT AGGREGATE | | 1 | 7 | | |02[02] INDEX UNIQUE SCAN |U_IND_T1 | 1 | 7 | 2 | ---------------------------------------------------- [Predicate Information] ---------------------------------------------------- 2 access:"A"='1' |
左侧显示使用函数，同样会使索引失效：
sys@TESTDBA>select count(*) from t1 where to_number(a)=1; COUNT(*) ---------- 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=159 Card=1 Bytes=7) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=159 Card=1 Bytes=7) sys@TESTDBA>select count(*) from t1 where to_char(a)='1'; COUNT(*) ---------- 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=159 Card=1 Bytes=7) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=159 Card=10000 Bytes=70000) sys@TESTDBA> sys@TESTDBA>select count(*) from t1 where mod(a,100000)=1; COUNT(*) ---------- 10 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=159 Card=1 Bytes=7) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=159 Card=10000 Bytes=70000) |
建立函数索引：
sys@TESTDBA>create index f_mod_ind on t1(mod(a,100000)); Index created. sys@TESTDBA>select count(*) from t1 where mod(a,100000)=1; COUNT(*) ---------- 10 sys@TESTDBA>exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYS',tabname=>'T1', method_opt=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE); PL/SQL procedure successfully completed. sys@TESTDBA>select owner,index_name,distinct_keys,num_rows,last_analyzed from dba_indexes where table_name='T1' and owner='SYS'; OWNER INDEX_NAME DISTINCT_KEYS NUM_ROWS LAST_ANALYZED --------------------- ---------------------- ------------- ---------- ---------------- SYS F_MOD_IND 100000 1000000 2008-11-06 14:25 SYS U_IND_T1 1000000 1000000 2008-11-06 14:25 sys@TESTDBA>select count(*) from t1 where mod(a,100000)=1; COUNT(*) ---------- 10 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=7) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'F_MOD_IND' (NON-UNIQUE) (Cost=3 Card=10 Bytes=70) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 518 bytes sent via SQL*Net to client 655 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed sys@TESTDBA> |
— The End —
关键字: SQL优化 | 性能优化
站内搜索