Oracle数据库效率技巧：避免错误的索引(1)

导读：有的时候，使用错误的索引会导致Oracle数据库的效率明显下降，通过一些方法或者是技巧可以有效的避免这个问题，下文中就为大家带来避免使用错误的数据库索引的，以提高Oracle数据库的工作效率。

这个例子中,如果我想使用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上建立联合索引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