oracle正确使用索引,Oracle优化全攻略三（Oracle 怎么使用索引）

当前位置:我的异常网» 数据库 » Oracle优化全攻略三(Oracle 怎么使用索引)

Oracle优化全攻略三(Oracle 怎么使用索引)

www.myexceptions.net  网友分享于：2013-09-20  浏览：39次

Oracle优化全攻略三(Oracle 如何使用索引)

index索引的8种使用模式

索引的使用对数据库的性能有巨大的影响。

共有五类不同的使用模式。

1。INDEX UNIQUE SCAN    效率最高，主键或唯一索引

2。INDEX FULL SCAN      有顺序的输出，不能并行读索引

问题：如果表中建立了多个索引，Oracle是把所有的索引都扫描一遍么？

3。INDEX FAST FULL SCAN  读的最块，可以并行访问索引，但输出不按顺序

4。INDEX RANGE SCAN      给定的区间查询

5。INDEX SKIP SCAN       联合索引，不同值越少的列，越要放在前面

6. SCAN DESCENDING

7. index join

8. bitmap join

--实验后的总论。

能用唯一索引，一定用唯一索引

能加非空，就加非空约束

一定要统计表的信息，索引的信息，柱状图的信息。

联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面

只有做到以上四点，数据库才会正确的选择执行计划。

conn system/manager

grant select any dictionary to scott;

conn scott/tiger

drop table t1 purge;

create table t1 as select * from dba_objects;

analyze table t1 compute statistics;

create index it1 on t1(object_type);

set autot traceonly

select distinct object_type from t1;

将是全表扫描，为什么不使用索引呢？因为索引中不能含有null值，

如果使用索引就可能产生不正确的结果。

--增加非空约束

alter table t1 modify (object_type not null);

select distinct object_type from t1  ;

使用INDEX FAST FULL SCAN方式查找数据

--

select  object_type from t1;

使用INDEX FAST FULL SCAN，因为不需要排序

select  object_type from t1 order by 1;

使用INDEX FULL SCAN,因为要按照顺序输出

select  object_type from t1 where object_type='TABLE';

使用INDEX RANGE SCAN

--使用非唯一索引

create index i2t1 on t1(object_id);

select * from t1 where object_id=3762;

使用INDEX RANGE SCAN,因为数据库不知道是否唯一

--使用唯一索引

drop index i2t1;

create unique index i2t1 on t1(object_id);

使用INDEX UNIQUE SCAN,因为数据库知道是唯一的

--跳跃的扫描索引

create index i3t1 on t1(object_type,object_name);

select * from t1 where object_name='EMP';

select object_name from t1 where object_name='EMP';

使用INDEX SKIP SCAN，因为数据库知道可以跳过object_type,虽然object_name在第二个列。

--联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面

drop index i3t1;

drop index it1;

create index i3t1 on t1(object_name,object_type);

select * from t1 where object_type='TABLE';

计划为全表扫描。

----------------------------------

6. SCAN DESCENDING

降序

SQL> SELECT * FROM EMP ORDER BY 1 DESC;

Execution Plan

----------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=12 Bytes=384)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=12 Bytes=384)

2    1     INDEX (FULL SCAN DESCENDING) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=12)

正序

SQL> SELECT * FROM EMP ORDER BY 1;

Execution Plan

----------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=12 Bytes=384)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=12 Bytes=384)

2    1     INDEX (FULL SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=12)

----------------------------------

7. index join

SELECT OBJECT_NAME,OBJECT_TYPE FROM T1

WHERE OBJECT_NAME LIKE 'E%'  AND OBJECT_TYPE='TABLE';

Execution Plan

----------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=12 Bytes=372)

1    0   VIEW OF 'index$_join$_001' (Cost=9 Card=12 Bytes=372)

2    1     HASH JOIN

3    2       INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE) (Cost=10 Card=12 Bytes=372)

4    2       INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=10 Card=12 Bytes=372)

----------------------------------

8. bitmap join

SQL> SELECT COUNT(*) FROM T1 WHERE OBJECT_NAME LIKE 'E%'  OR OBJECT_TYPE='TABLE';

Execution Plan

----------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=31)

1    0   SORT (AGGREGATE)

2    1     BITMAP CONVERSION (COUNT)

3    2       BITMAP OR

4    3         BITMAP CONVERSION (FROM ROWIDS)

5    4           SORT (ORDER BY)

6    5             INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=3)

7    3         BITMAP CONVERSION (FROM ROWIDS)

8    7           INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE) (Cost=3)

--实验后的总论。

能用唯一索引，一定用唯一索引

能加非空，就加非空约束

一定要统计表的信息，索引的信息，柱状图的信息。

联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面

只有做到以上四点，数据库才会正确的选择执行计划。

conn system/manager

grant select any dictionary to scott;

conn scott/tiger

drop table t1 purge;

create table t1 as select * from dba_objects;

analyze table t1 compute statistics;

create index it1 on t1(object_type);

set autot traceonly

select distinct object_type from t1;

将是全表扫描，为什么不使用索引呢？因为索引中不能含有null值，

如果使用索引就可能产生不正确的结果。

--增加非空约束

alter table t1 modify (object_type not null);

select distinct object_type from t1  ;

使用INDEX FAST FULL SCAN方式查找数据

--

select  object_type from t1;

使用INDEX FAST FULL SCAN，因为不需要排序

select  object_type from t1 order by 1;

使用INDEX FULL SCAN,因为要按照顺序输出

select  object_type from t1 where object_type='TABLE';

使用INDEX RANGE SCAN

--使用非唯一索引

create index i2t1 on t1(object_id);

select * from t1 where object_id=3762;

使用INDEX RANGE SCAN,因为数据库不知道是否唯一

--使用唯一索引

drop index i2t1;

create unique index i2t1 on t1(object_id);

使用INDEX UNIQUE SCAN,因为数据库知道是唯一的

--跳跃的扫描索引

create index i3t1 on t1(object_type,object_name);

select * from t1 where object_name='EMP';

select object_name from t1 where object_name='EMP';

使用INDEX SKIP SCAN，因为数据库知道可以跳过object_type,虽然object_name在第二个列。

--联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面

drop index i3t1;

drop index it1;

create index i3t1 on t1(object_name,object_type);

select * from t1 where object_type='TABLE';

计划为全表扫描。

文章评论