您現(xiàn)在的位置：Oracle中如何避免使用特定錯(cuò)誤索引
這個(gè)例子中,如果我想使用idx_a而不是idx_b.
| SQL> create table test 2 (a int,b int,c int,d int); Table created. SQL> begin 2 for i in 1..50000 3 loop 4 insert into mytest values(i,i,i,i); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL> create index idx_a on mytest(a,b,c); Index created. SQL> create index idx_b on mytest(b); Index created. |
如表mytest,有字段a,b,c,d，在a,b,c上建立聯(lián)合索引idx_a(a,b,c)，在b上單獨(dú)建立了一個(gè)索引idx_b(b)。
在正常情況下，where a=? and b=? and c=?會(huì)用到索引idx_a，where b=?會(huì)用到索引idx_b
比如：
SQL> analyze table mytest compute statistics; |
比如在索引有統(tǒng)計(jì)信息，分析數(shù)據(jù)正確的情況下：
| SQL> select max(d) from mytest 2 where a=50 and b=50 and c=50 3 group by b; Execution Plan ---------------------------------------------------------- Plan hash value: 422688974 -------------------------------------------------------------------------------- ------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim e | -------------------------------------------------------------------------------- ------- | 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00: 00:01 | | 1 | SORT GROUP BY NOSORT | | 1 | 16 | 2 (0)| 00: 00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 16 | 2 (0)| 00: 00:01 | |* 3 | INDEX RANGE SCAN | IDX_A | 1 | | 1 (0)| 00: 00:01 | -------------------------------------------------------------------------------- ------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"=50 AND "B"=50 AND "C"=50) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 513 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 但如果索引分析數(shù)據(jù)不正確: SQL> select num_rows from user_tables 2 where table_name='MYTEST'; NUM_ROWS ---------- 50000 SQL> analyze index idx_a delete statistics; Index analyzed. SQL> analyze index idx_b delete statistics; Index analyzed. SQL> select distinct_keys from user_indexes 2 where index_name in ('IDX_A','IDX_B'); DISTINCT_KEYS ------------- SQL> select max(d) from mytest where a=50 and b=50 and c=50 group by b; Execution Plan ---------------------------------------------------------- Plan hash value: 3925507835 -------------------------------------------------------------------------------- ------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim e | -------------------------------------------------------------------------------- ------- | 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00: 00:01 | | 1 | SORT GROUP BY NOSORT | | 1 | 16 | 2 (0)| 00: 00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 16 | 2 (0)| 00: 00:01 | |* 3 | INDEX RANGE SCAN | IDX_B | 1 | | 1 (0)| 00: 00:01 | -------------------------------------------------------------------------------- ------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("A"=50 AND "C"=50) 3 - access("B"=50) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 513 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
我們可以通過(guò)如下的技巧避免使用idx_b，而使用idx_a。
| where a=? and b=? and c=? group by b||'' --如果b是字符類型 where a=? and b=? and c=? group by b+0 --如果b是數(shù)字類型 |
通過(guò)這樣簡(jiǎn)單的改變，往往可以是查詢時(shí)間提交很多倍
當(dāng)然，我們也可以使用no_index提示，相信很多人沒(méi)有用過(guò)，也是一個(gè)不錯(cuò)的方法：
| SQL> select /*+ no_index(mytest,idx_b) */ max(d) from mytest where a=50 and b=50 and c=50 group by b; Execution Plan ---------------------------------------------------------- Plan hash value: 422688974 -------------------------------------------------------------------------------- ------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim e | -------------------------------------------------------------------------------- ------- | 0 | SELECT STATEMENT | | 1 | 16 | 9 (0)| 00: 00:01 | | 1 | SORT GROUP BY NOSORT | | 1 | 16 | 9 (0)| 00: 00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 16 | 9 (0)| 00: 00:01 | |* 3 | INDEX RANGE SCAN | IDX_A | 500 | | 1 (0)| 00: 00:01 | -------------------------------------------------------------------------------- ------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"=50 AND "B"=50 AND "C"=50) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 513 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
