導(dǎo)讀：有的時候，使用錯誤的索引會導(dǎo)致Oracle數(shù)據(jù)庫的效率明顯下降，通過一些方法或者是技巧可以有效的避免這個問題，下文中就為大家?guī)肀苊馐褂缅e誤的數(shù)據(jù)庫索引的，以提高Oracle數(shù)據(jù)庫的工作效率。

　　這個例子中,如果我想使用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上單獨建立了一個索引idx_b(b)。

　　在正常情況下，where a=? and b=? and c=?會用到索引idx_a，where b=?會用到索引idx_b

　　比如：

　　SQL> analyze table mytest compute statistics;
　　Table analyzed.
　　SQL> select num_Rows from user_tables where table_name='MYTEST';
　　NUM_ROWS
　　----------
　　50000
　　SQL> select distinct_keys from user_indexes where index_name='IDX_A';
　　DISTINCT_KEYS
　　-------------
　　50000
　　SQL> set autotrace traceonly
　　SQL> select d from mytest
　　2 where a=10 and b=10 and c=10;
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 1542625214
　　--------------------------------------------------------------------------------
　　------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
　　|
　　--------------------------------------------------------------------------------
　　------

　　| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:0
　　0:01 |
　　| 1 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 16 | 2 (0)| 00:0
　　0:01 |
　　|* 2 | INDEX RANGE SCAN | IDX_A | 1 | | 1 (0)| 00:0
　　0:01 |
　　--------------------------------------------------------------------------------
　　------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　2 - access("A"=10 AND "B"=10 AND "C"=10)
　　Statistics
　　----------------------------------------------------------
　　1 recursive calls
　　0 db block gets
　　4 consistent gets
　　0 physical reads
　　0 redo size
　　508 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
　　SQL> select d from mytest
　　2 where b=500;
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 530004086
　　--------------------------------------------------------------------------------
　　------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
　　|
　　--------------------------------------------------------------------------------
　　------
　　| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:0
　　0:01 |
　　| 1 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 8 | 2 (0)| 00:0
　　0:01 |
　　|* 2 | INDEX RANGE SCAN | IDX_B | 1 | | 1 (0)| 00:0
　　0:01 |
　　--------------------------------------------------------------------------------
　　------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　2 - access("B"=500)
　　Statistics
　　----------------------------------------------------------
　　1 recursive calls
　　0 db block gets
　　4 consistent gets
　　0 physical reads
　　0 redo size
　　508 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
 

#p#

　　但是在這樣一個條件下：where a=? and b=? and c=? group by b會用到哪個索引呢?在索引的分析數(shù)據(jù)不正確(很長時間沒有分析)或根本沒有分析數(shù)據(jù)的情況下，oracle往往會使用索引idx_b。通過執(zhí)行計劃的分析，這個索引的使用，將大大耗費查詢時間。

　　比如在索引有統(tǒng)計信息，分析數(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

#p#

　　我們可以通過如下的技巧避免使用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ù)字類型

　　通過這樣簡單的改變，往往可以是查詢時間提交很多倍

　　當(dāng)然，我們也可以使用no_index提示，相信很多人沒有用過，也是一個不錯的方法：

　　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

上文中主要是以代碼的形式為大家講解的，看起來可能是不太容易理解，大家要深入其中去學(xué)習(xí)，這個技巧是非常實用的，希望大家能夠從中收獲。

【編輯推薦】

1. 詳解Oracle數(shù)據(jù)庫備份不同的恢復(fù)特性
2. 由淺入深講解MySQL數(shù)據(jù)庫索引的選擇性
3. Oracle數(shù)據(jù)庫11g創(chuàng)非集群基準測試世界紀錄
4. Oracle數(shù)據(jù)庫開發(fā)技術(shù)經(jīng)驗淺談