Oracle建立索引前后性能比较

1.SQL语句

create table C##HR.t_noindex as select * from all_objects;
create table C##HR.t_indexed as select * from all_objects
create index owner_idx on C##HR.t_indexed(owner);
select * from C##HR.t_noindex where owner='SYSTEM';
select * from C##HR.t_indexed where owner='SYSTEM';

2.查询时间

通过比较用时可以由较明显的感触

1.不建立索引

2.建立索引

3.通过执行计划(GUI方式)查询代价(推荐)

点击如图所示按钮/按F10(选中相应select语句再使用)

1.不建立索引

2.建立索引

4.通过执行计划(SQL方式,使用explain plan for ...(你的查询SQL语句))查询代价

1.不建立索引

EXPLAIN PLAN FOR select * from C##HR.t_noindex where owner='SYSTEM';
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

或者

EXPLAIN PLAN FOR select * from C##HR.t_noindex where owner='SYSTEM';
select * from table(dbms_xplan.display);

2.建立索引

EXPLAIN PLAN FOR select * from C##HR.t_indexed where owner='SYSTEM';
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

或者

EXPLAIN PLAN FOR select * from C##HR.t_indexed where owner='SYSTEM';
select * from table(dbms_xplan.display);

posted @ 2023-10-09 22:23  DawnTraveler  阅读(5)  评论(0编辑  收藏  举报