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);