分类分类
2015-06-28 00:00作者：网管联盟
有的时候，使用错误的索引会导致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 |
#p#副标题#e#
但是在这样一个条件下：where a=? and b=? and c=? group by b会用到哪个索引呢?在索引的分析数
相关文章