Oracle学习笔记组合索引（十二）
1.适用在单独查询返回很多，组合查询返回很少。
2.组合查询的组合顺序，要全面考虑单列查询情况
3.仅等值无范围查询时，组合索引顺序不影响性能
4.组合索引最佳顺序一般是将列等值查询的列置前。
5.注意组合索引与组合条件中关于IN 的优化
组合索引经要素! /* 1.适用在单独查询返回记录很多，组合查询后忽然返回记录很少的情况: 比如where 学历=硕士以上 返回不少的记录 比如where 职业=收银员 同样返回不少的记录 于是无论哪个条件查询做索引，都不合适。 可是，如果学历为硕士以上，同时职业又是收银员的，返回的就少之又少了。 于是联合索引就可以这么开始建了。 */ /* 2.组合查询的组合顺序，要考虑单独的前缀查询情况（否则单独前缀查询的索引不能生效或者只能用到跳跃索引） 比如你在建id,object_type的联合索引时，要看考虑是单独where id=xxx查询的多，还是单独where object_type查询的多。 */ --3.仅等值无范围查询时，组合索引顺序不影响性能（比如where col1=xxx and col2=xxx，无论COL1+COL2组合还是COL2+COL1组合） drop table t purge; create table t as select * from dba_objects; insert into t select * from t; insert into t select * from t; insert into t select * from t; update t set object_id=rownum ; commit; create index idx_id_type on t(object_id,object_type); create index idx_type_id on t(object_type,object_id); set autotrace off alter session set statistics_level=all ; set linesize 366 --性能和哪列在前没有什么差别 select /*+index(t,idx_id_type)*/ * from t where object_id=20 and object_type='TABLE'; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 5 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 57 | 1 |00:00:00.01 | 5 | |* 2 | INDEX RANGE SCAN | IDX_ID_TYPE | 1 | 9 | 1 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------------------- select /*+index(t,idx_type_id)*/ * from t where object_id=20 and object_type='TABLE'; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); Plan hash value: 3420768628 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 5 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 57 | 1 |00:00:00.01 | 5 | |* 2 | INDEX RANGE SCAN | IDX_TYPE_ID | 1 | 9 | 1 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------------------- --4.组合索引最佳顺序一般是将列等值查询的列置前。（测试组合索引在条件是不等的情况下的情况，条件经常是不等的，要放在后面，让等值的在前面） select /*+index(t,idx_id_type)*/ * from t where object_id>=20 and object_id<2000 and object_type='TABLE'; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 469 |00:00:00.01 | 86 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 | 469 |00:00:00.01 | 86 | |* 2 | INDEX RANGE SCAN | IDX_ID_TYPE | 1 | 1 | 469 |00:00:00.01 | 40 | ----------------------------------------------------------------------------------------------------- select /*+index(t,idx_type_id)*/ * from t where object_id>=20 and object_id<2000 and object_type='TABLE'; ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 469 |00:00:00.01 | 81 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 469 | 469 |00:00:00.01 | 81 | |* 2 | INDEX RANGE SCAN | IDX_TYPE_ID | 1 | 469 | 469 |00:00:00.01 | 35 | ----------------------------------------------------------------------------------------------------- --5.注意组合索引与组合条件中关于IN 的优化 --案例1 UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000; UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20; COMMIT; set linesize 1000 set pagesize 1 alter session set statistics_level=all ; select /*+index(t,idx1_object_id)*/ * from t where object_TYPE='TABLE' AND OBJECT_ID >= 20 AND OBJECT_ID<= 21; --6.案例2 --依然是关于IN的优化 (col1,col2,col3的索引情况，如果没有为COL2赋予查询条件时，COL3只能起到检验作用) drop table t purge; create table t as select * from dba_objects; UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000; UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20; Update t set object_id=22 where rownum<=10000; COMMIT; create index idx_union on t(object_type,object_id,owner); set autotrace off alter session set statistics_level=all ; set linesize 1000 select * from t where object_type='VIEW' and OWNER='LJB'; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+INDEX(T,idx_union)*/ * from t T where object_type='VIEW' and OBJECT_ID IN (20,21,22) AND OWNER='LJB'; select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
分区表各类聚合优化：
-- 范围分区示例
drop table range_part_tab purge;
--注意，此分区为范围分区
--例子1
create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (deal_date)
(
partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;
alter table RANGE_PART_TAB modify nbr not null;
--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--以下是插入2014年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
create index idx_part_id on range_part_tab (id) ;
create index idx_part_nbr on range_part_tab (nbr) local;
--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
set autotrace on
set linesize 1000
select max(nbr) max_nbr from range_part_tab partition(p_201305);
执行计划
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FULL SCAN (MIN/MAX)| IDX_PART_NBR | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 |
------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
select max(nbr) max_nbr
from range_part_tab
where deal_date >= TO_DATE('2013-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2013-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
select count(*) max_nbr from range_part_tab partition(p_201305);
执行计划
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE SINGLE| | 8716 | 8 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 8 (0)| 00:00:01 | 5 | 5 |
------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
select count(*) max_nbr
from range_part_tab
where deal_date >= TO_DATE('2013-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2013-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
select sum(nbr) max_nbr from range_part_tab partition(p_201305);
执行计划
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 8 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
select sum(nbr) max_nbr
from range_part_tab
where deal_date >= TO_DATE('2013-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2013-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
select distinct(nbr) from range_part_tab partition(p_201305);
执行计划
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8660 | 69280 | 9 (12)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 8660 | 69280 | 9 (12)| 00:00:01 | | |
| 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
152890 bytes sent via SQL*Net to client
6741 bytes received via SQL*Net from client
577 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8635 rows processed
select distinct(nbr)
from range_part_tab
where deal_date >= TO_DATE('2013-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2013-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 374 | 171 (1)| 00:00:03 | | |
| 1 | HASH UNIQUE | | 22 | 374 | 171 (1)| 00:00:03 | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
0 physical reads
0 redo size
152886 bytes sent via SQL*Net to client
6741 bytes received via SQL*Net from client
577 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8635 rows processed
---<=和<,<=扫描了两个分区
select count(*)
from range_part_tab
where deal_date >= TO_DATE('2013-05-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss')
and deal_date <= TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss');
COUNT(*)
----------
8635
执行计划
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 340 (1)| 00:00:05 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |
|* 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |
------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1136 consistent gets
select count(*)
from range_part_tab
where deal_date >= TO_DATE('2013-05-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss')
and deal_date < TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss');
COUNT(*)
----------
8635
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
啥时候分区索引性能反而低：
drop table part_tab purge;
create table part_tab (id int,col2 int,col3 int)
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p6 values less than (60000),
partition p7 values less than (70000),
partition p8 values less than (80000),
partition p9 values less than (90000),
partition p10 values less than (100000),
partition p11 values less than (maxvalue)
)
;
insert into part_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create index idx_par_tab_col2 on part_tab(col2) local;
create index idx_par_tab_col3 on part_tab(col3) ;
drop table norm_tab purge;
create table norm_tab (id int,col2 int,col3 int);
insert into norm_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create index idx_nor_tab_col2 on norm_tab(col2) ;
create index idx_nor_tab_col3 on norm_tab(col3) ;
set autotrace traceonly
set linesize 1000
set timing on
select * from part_tab where col2=8 ;
执行计划
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 13 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 39 | 13 (0)| 00:00:01 | 1 | 11 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PART_TAB | 1 | 39 | 13 (0)| 00:00:01 | 1 | 11 |
|* 3 | INDEX RANGE SCAN | IDX_PAR_TAB_COL2 | 1 | | 12 (0)| 00:00:01 | 1 | 11 |
-----------------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
539 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select * from norm_tab where col2=8 ;
执行计划
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| NORM_TAB | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_NOR_TAB_COL2 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
543 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select * from part_tab where col2=8 and id=2;
select * from norm_tab where col2=8 and id=2;
--查看索引高度等信息
select index_name,
blevel,
leaf_blocks,
num_rows,
distinct_keys,
clustering_factor
from user_ind_statistics
where table_name in( 'NORM_TAB');
select index_name,
blevel,
leaf_blocks,
num_rows,
distinct_keys,
clustering_factor FROM USER_IND_PARTITIONS where index_name like 'IDX_PAR_TAB%';
---索引的高度会影响性能
同时取最大最小值的案例：
MAX/MIN 的索引优化
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
alter table t add constraint pk_object_id primary key (OBJECT_ID);
set autotrace on
set linesize 1000
select max(object_id) from t;
select min(object_id) from t;
--等价改写，为数不多的SQL改写复杂了性能更优的情况
set linesize 1000
set autotrace on
select max(object_id),min(object_id) from t;
执行计划
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 46 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| PK_OBJECT_ID | 74796 | 949K| 46 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
160 consistent gets
0 physical reads
0 redo size
502 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select max, min
from (select max(object_id) max from t ) a,
(select min(object_id) min from t ) b;
执行计划
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 4 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 13 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
480 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
组合索引考虑单例索引：
组合索引的前缀与单列索引一致
drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id,object_type);
set autotrace traceonly
set linesize 1000
--以下就能用到索引，因为object_id列是前缀
select * from t where object_id=19;
执行计划
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1392 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
组合索引的前缀与单列索引不一致
drop index idx_object_id;
create index idx_object_id on t(object_type, object_id);
--以下就不能用到索引，因为object_id列是后缀
select * from t where object_id=19;
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 2484 | 292 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
1389 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
组合查询和in有关的优化：
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum ;
create index idx_id_type on t(object_id,object_type);
UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;
UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;
COMMIT;
set linesize 1000
set pagesize 1
alter session set statistics_level=all ;
select /*+index(t,idx1_object_id)*/ * from t where object_TYPE='TABLE' AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2939 |00:00:00.02 | 1117 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 3411 | 2939 |00:00:00.02 | 1117 |
|* 2 | INDEX RANGE SCAN | IDX_ID_TYPE | 1 | 299 | 2939 |00:00:00.02 | 736 |
--------------------------------------------------------------------------------------------------------
2 - access("OBJECT_ID">=20 AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<=21)
filter("OBJECT_TYPE"='TABLE')
已选择25行。
select /*+index(t,idx_id_type)*/ * from t t where object_TYPE='TABLE' AND OBJECT_ID IN (20,21);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2939 |00:00:00.01 | 598 |
| 1 | INLIST ITERATOR | | 1 | | 2939 |00:00:00.01 | 598 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 2 | 3411 | 2939 |00:00:00.01 | 598 |
|* 3 | INDEX RANGE SCAN | IDX_ID_TYPE | 2 | 1 | 2939 |00:00:00.01 | 217 |
---------------------------------------------------------------------------------------------------------
3 - access((("OBJECT_ID"=20 OR "OBJECT_ID"=21)) AND "OBJECT_TYPE"='TABLE')
已选择25行。
---col1,col2,col3的索引情况，如果没有为COL2赋予查询条件时，COL3只能起到检验作用(依然是in的优化）
drop table t purge;
create table t as select * from dba_objects;
UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;
UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;
Update t set object_id=22 where rownum<=10000;
COMMIT;
create index idx_union on t(object_type,object_id,owner);
set autotrace off
alter session set statistics_level=all ;
set linesize 1000
select * from t where object_type='VIEW' and OWNER='LJB';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 24 | 19 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 9 | 4 |00:00:00.01 | 24 | 19 |
|* 2 | INDEX RANGE SCAN | IDX_UNION | 1 | 22 | 4 |00:00:00.01 | 21 | 19 |
------------------------------------------------------------------------------------------------------------
select /*+INDEX(T,idx_union)*/ * from t T where object_type='VIEW' and OBJECT_ID IN (20,21,22) AND OWNER='LJB';
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 13 |
| 1 | INLIST ITERATOR | | 1 | | 4 |00:00:00.01 | 13 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 3 | 1 | 4 |00:00:00.01 | 13 |
|* 3 | INDEX RANGE SCAN | IDX_UNION | 3 | 1 | 4 |00:00:00.01 | 10 |
----------------------------------------------------------------------------------------------------
类似
select /*+INDEX(T,idx_union)*/ * from t T where (object_type='VIEW' and OBJECT_ID =20 AND OWNER='LJB')
or (object_type='VIEW' and OBJECT_ID =21 AND OWNER='LJB')
or (object_type='VIEW' and OBJECT_ID =22 AND OWNER='LJB')
