[ORALCE]SQL 优化案例之 组合索引的前缀和单列索引一致
- 组合索引的前缀和单列索引一致,走INDEX RANGE SCAN
drop table TX1 purge; create table TX1 as select * from dba_objects; SQL> select count(*) from (select distinct object_id from TX1); COUNT(*) ---------- 73396 SQL> select count(*) from (select distinct object_type from TX1); COUNT(*) ---------- 47 create index idx_object_id on TX1(object_id,object_type); set autotrace on set linesize 150 select * from TX1 where object_id=19; Execution Plan ---------------------------------------------------------- Plan hash value: 1750502627 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 481 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TX1 | 1 | 481 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=19) Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 34 recursive calls 0 db block gets 164 consistent gets 1 physical reads 0 redo size 2686 bytes sent via SQL*Net to client 398 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
- 组合索引的前缀和单列索引不一致,走FULL TABLE SCAN
drop index idx_object_id; create index idx_object_id on TX1(object_type,object_id); select * from TX1 where object_id=19; Execution Plan ---------------------------------------------------------- Plan hash value: 2923622636 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 7696 | 459 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TX1 | 16 | 7696 | 459 (1)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=19) Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 1514 consistent gets 0 physical reads 0 redo size 2686 bytes sent via SQL*Net to client 398 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
每天进步一点点,多思考,多总结
版权声明:本文为CNblog博主「zaituzhong」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。