1、索引特性一高度较低
create table t1 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1;
create table t2 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10;
create table t3 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100;
create table t4 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000;
create table t5 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10000;
create table t6 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100000;
create table t7 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000000;
create index idx_id_t1 on t1(id);
create index idx_id_t2 on t2(id);
create index idx_id_t3 on t3(id);
create index idx_id_t4 on t4(id);
create index idx_id_t5 on t5(id);
create index idx_id_t6 on t6(id);
create index idx_id_t7 on t7(id);
set linesize 1000
set autotrace off
select index_name,
blevel,
leaf_blocks,
num_rows,
distinct_keys,
clustering_factor
from user_ind_statistics
where table_name in( 'T1','T2','T3','T4','T5','T6','T7');
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
------------------ ----------- ---------- ------------- -----------------
IDX_ID_T1 0 1 1 1 1
IDX_ID_T2 0 1 10 10 2
IDX_ID_T3 0 1 100 100 15
IDX_ID_T4 1 3 1000 1000 143
IDX_ID_T5 1 21 10000 10000 1429
IDX_ID_T6 1 222 100000 100000 14286
IDX_ID_T7 2 2226 1000000 1000000 142858
已选择7行。
规律:
从t1到t7(表记录依次增大10倍，从1到1000000),索引读的逻辑读是 2,3,3,4,4,4,5
从t1到t7(表记录依次增大10倍，从1到1000000)全表扫描的逻辑读是 3,5,19,148,1435,14298,142866
结论：索引特性高度较低是优化利器（）
2、特性二索引存储键值（可以优化sum、avg等，只要这个列有索引，那么索引中就存储了此键值，可以建立索引）
scott@ORCL>--要领：只要索引能回答问题，索引就可以当成一个"瘦表"，访问路径就会减少。另外切记不存储空值
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create index idx1_object_id on t(object_id);
set autotrace on
select count(*) from t;scott@ORCL>
Table dropped.
scott@ORCL>
Table created.
scott@ORCL>
86480 rows updated.
Statistics
----------------------------------------------------------
32 recursive calls
93260 db block gets
2594 consistent gets
1235 physical reads
27285480 redo size
1140 bytes sent via SQL*Net to client
1279 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
86480 rows processed
scott@ORCL>
Commit complete.
scott@ORCL>
Index created.
scott@ORCL>scott@ORCL>
COUNT(*)
----------
86480
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 345 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 83117 | 345 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
1306 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@ORCL>/
COUNT(*)
----------
86480
--为啥用不到索引，因为索引不能存储空值，所以加上一个is not null,再试验看看
scott@ORCL>--为啥用不到索引，因为索引不能存储空值，所以加上一个is not null,再试验看看
select count(*) from t where object_id is not null;scott@ORCL>
COUNT(*)
----------
86480
Execution Plan
----------------------------------------------------------
Plan hash value: 1296839119
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 57 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 83117 | 1055K| 57 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
265 consistent gets
192 physical reads
0 redo size
528 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@ORCL>/
COUNT(*)
----------
86480
3、索引特性三：索引本身有序（优化order by语句）
set autotrace traceonly
set linesize 1000
drop table t purge;
create table t as select * from dba_objects;
scott@ORCL>--以下语句没有索引又有order by ，必然产生排序
select * from t where object_id>2 order by object_id;scott@ORCL>
86478 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 961378228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83097 | 16M（使用了16M的空间）| | 4086 (1)| 00:00:50 |
| 1 | SORT ORDER BY | | 83097 | 16M| 19M| 4086 (1)| 00:00:50 |
|* 2 | TABLE ACCESS FULL| T | 83097 | 16M| | 345 (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
21 recursive calls
0 db block gets
1337 consistent gets
1235 physical reads
0 redo size
3589739 bytes sent via SQL*Net to client
10029 bytes received via SQL*Net from client
866 SQL*Net roundtrips to/from client
1 sorts (memory)（产生了排序操作）
0 sorts (disk)
86478 rows processed
那么如果在此列建立索引呢？---新增索引后，Oracle就有可能利用索引本身就有序的特点，利用索引来避免排序，如下：
create index idx_t_object_id on t(object_id);
set autotrace traceonly
select * from t where object_id>2 order by object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 4285561625
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83097 | 16M| 1535 (1)| 00:00:19 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 83097 | 16M| 1535 (1)| 00:00:19 |（回表后取了其他字段的值）
|* 2 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 83097 | | 207 (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3232 consistent gets
0 physical reads
0 redo size
9033473 bytes sent via SQL*Net to client
10029 bytes received via SQL*Net from client
866 SQL*Net roundtrips to/from client
0 sorts (memory)（没有磁盘排序）
0 sorts (disk)
86478 rows processed
走了索引，且没有产生排序操作。
那么如果我们如果值取object_id这一列，执行计划会发生什么呢？
--如下情况Oracle肯定毫不犹豫的选择用索引,因为回表取消了 !
select object_id from t where object_id>2 order by object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2498590897
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83097 | 1054K| 207 (1)| 00:00:03 |
|* 1 | INDEX RANGE SCAN| IDX_T_OBJECT_ID | 83097 | 1054K| 207 (1)| 00:00:03 |（不需要回表了，可以直接在索引中取数据。）
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1056 consistent gets
0 physical reads
0 redo size
669758 bytes sent via SQL*Net to client
10029 bytes received via SQL*Net from client
866 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86478 rows processed
4、索引有序与存储列值优化max。（可以建立max列的索引，然后只取最大或者最小就可以了）
5、回表
通过构造联合索引，观察一个消除TABLE ACCESS BY INDEX ROWID的例子
create table t as select * from dba_objects;
create index idx1_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select object_id,object_name from t where object_id<=5;
Execution Plan
----------------------------------------------------------
Plan hash value: 2486998213
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 316 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 316 | 3 (0)| 00:00:01 |（因为在object_name上没有索引，所以要取此值就必须回表取得表上面的数据）
|* 2 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<=5)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
694 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
对t表建联合索引
create index idx_un_objid_objname on t(object_id,object_name);
--该联合索引建完后，产生功效了！消除了TABLE ACCESS BY INDEX ROWID
scott@ORCL>select object_id,object_name from t where object_id<=5;
Execution Plan
----------------------------------------------------------
Plan hash value: 2827629532
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1106 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_UN_OBJID_OBJNAME | 14 | 1106 | 2 (0)| 00:00:01 |（没有回表）
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"<=5)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
70 consistent gets
1 physical reads
0 redo size
694 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6、聚合因子是索引优化要点
适用于在单独查询返回很多，组合索引查询返回很少。
组合查询的组合顺序，要全面考虑单列查询情况；
仅等值无范围查询时，组合索引不影响性能；
组合索引最佳的顺序一般是将列等值查询列置前。
注意组合索引和组合索引中关于in的优化。
91
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
