索引优化十二--组合索引的优化技巧与总结

发布时间:2020-05-24 14:30:34 作者:1415699306
来源:网络 阅读:434

组合索引经要素!


/* 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'));



                                                

                                                



推荐阅读:
  1. mongodb查询与索引优化
  2. MySQL--索引优化原则

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

oracle 组合索引 --

上一篇:Android studio 中layout不能匹配到相应的布局文件的值

下一篇:git 初始化

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》