1、感受索引美好的一面
1、索引的各种开销
-
访问集中导致热块竞争
drop table t purge;
create table t (id number,deal_date date,area_code number,nbr number,contents varchar2(4000));
insert into t(id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,700)),'J'),
ceil(dbms_random.value(590,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
create index idx_t_id on t(id);
--由于一般来说，最新的值都是最新产生的，所以访问容易产生热快竞争。
因为
select * from t where id=100000;
select * from t where id=99999;
select * from t where id=99998;
select * from t where id=99997;
---数据是相邻的，很可能在同一个索引块上，所以很容易产生热点索引块竞争。
--如果方向键索引，刚才的语句等于变成如此查询：
select * from t where id=000001;
select * from t where id=99999;
select * from t where id=89999;
select * from t where id=79999;
--他们都被分到很远的地方，不会在同一个索引块上，有效的避免了索引块竞争。
drop index idx_t_id ;
create index idx_t_id on t(id) reverse;
结论：一般来说，由于用户都是访问最新产生的数据的，所以容易产生索引的热点块竞争，
这也算是索引的开销的弊端了，不过这时可以考虑用方向键索引来将索引的位置转移到不同的地方，只是反向键索引要担心范围查询无法使用，这在案例 的部分会有描述。
-
回表性能取决于聚合因子
select a.table_name,
a.index_name,
a.blevel,
a.leaf_blocks,
b.num_rows,
b.blocks,
a.clustering_factor,
trunc(a.clustering_factor / b.num_rows,2) cluster_rate
from user_indexes a, user_tables b
where a.table_name = b.table_name
and index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' )
and a.clustering_factor is not null
order by cluster_rate desc;
TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS BLOCKS CLUSTERING_FACTOR CLUSTER_RATE
------------------ -------------------------- ----------- ---------- ---------- ----------------- ------------
DISORGANIZED DISORGANIZED_PK 1 208 100000 1219 99927 .99
COLOCATED COLOCATED_PK 1 208 100000 1252 1190 .01
结论：索引查询要尽可能的避免回表，如果不可避免，需要关注聚合因子是否过大。
-
索引更新方面开销比较大
drop table test1 purge;
drop table test2 purge;
drop table test3 purge;
drop table t purge;
create table t as select * from dba_objects;
create table test1 as select * from t;
create table test2 as select * from t;
create table test3 as select * from t;
create index idx_owner on test1(owner);
create index idx_object_name on test1(object_name);
create index idx_data_obj_id on test1(data_object_id);
create index idx_created on test1(created);
create index idx_last_ddl_time on test1(last_ddl_time);
create index idx_status on test1(status);
create index idx_t2_sta on test2(status);
create index idx_t2_objid on test2(object_id);
set timing on
--语句1(test1表有6个索引）
insert into test1 select * from t;
commit;
--语句2(test2表有2个索引）
insert into test2 select * from t;
commit;
--语句3(test3表有无索引）
insert into test3 select * from t;
commit;
-------------------------------------------------------------------------------------------------------------------------------
一次与出账相关的小故事
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
commit;
--请从这里开始注意累加的时间（从建索引到插入记录完毕）
set timing on
create index idx_t_owner on t(owner);
create index idx_t_obj_name on t(object_name);
create index idx_t_data_obj_id on t(data_object_id);
create index idx_t_created on t(created);
create index idx_t_last_ddl on t(last_ddl_time);
--语句1(t表有6个索引）
insert into t select * from t;
commit;
--以下进行试验2
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
commit;
---也从这里开始这里开始注意累加的时间（从插入记录完毕到建索引完毕）
set timing on
--语句1(t表有6个索引,此时先不建）
insert into t select * from t;
create index idx_t_owner on t(owner);
create index idx_t_obj_name on t(object_name);
create index idx_t_data_obj_id on t(data_object_id);
create index idx_t_created on t(created);
create index idx_t_last_ddl on t(last_ddl_time);
结论：
1. 在无索引的情况下，表的记录越大，插入的速度只会受到很小的影响，基本不会越慢。
2. 在无索引的情况下，分区表的插入要比普通表更慢，因为插入的数据需要做判断落在哪个分区。有这方面的开销。
3. 在有索引的情况下，表记录越大，索引越多，插入速度越慢。
4. 在有索引的情况下，如果表非常大，索引也很多，在条件允许下，可以将索引先失效再生效，速度可以更快。
5. 在有索引的情况下，分区表如果只是有局部索引，一般来说，分区表的插入速度比普通表更快，因为每个分区的分区索引都比较小，更新的开销自然也小。
-
建立索引的过程开销也较大
结论：普通的对表建索引将会导致针对该表的更新操作无法进行，需要等待索引建完。更新操作将会被建索引动作阻塞。
另注：在案例的章节中，大家将会见到ONLINE建索引的方式，
这种建索引的方式不会阻止针对该表的更新操作，与建普通索引相反的是，ONLINE建索引的动作是反过来被更新操作阻塞
结论：建索引的过程会产生排序，排序的开销一般比较大，所以要尽量避免在生产缓慢的时候建索引。
set linesize 266
drop table t purge;
create table t as select * from dba_objects;
建立索引前
scott@ORCL>select t1.name,t1.statistic#,t2.value from v$statname t1, v$mystat t2 where t1.statistic#=t2.statistic# and t1.name like '%sort%';
NAME STATISTIC# VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
sorts (memory) 640 361
sorts (disk) 641 0
sorts (rows) 642 11177686
create index idx_object_id on t(object_id);
建立索引后
scott@ORCL>select t1.name,t1.statistic#,t2.value from v$statname t1, v$mystat t2 where t1.statistic#=t2.statistic# and t1.name like '%sort%';
NAME STATISTIC# VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
sorts (memory) 640 362
sorts (disk) 641 0
sorts (rows) 642 11264220
2、逻辑索引失效
- 用索引代价反而更高
- 发生了索引列烈性的转换
案例：
create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));
Table created.
Elapsed: 00:00:00.03
scott@ORCL>insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;
commit;
create index idx_id on t_col_type(id);
set linesize 1000
set autotrace traceonly
select * from t_col_type where id=6;（走了全表扫描而没有走索引的执行计划）
Execution Plan
----------------------------------------------------------
Plan hash value: 3191204463
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_COL_TYPE | 1 | 36 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--实际上只有如下写法才可以用到索引，这个很不应该，是什么类型的取值就设置什么样的字段。
select * from t_col_type where id='6';（走索引了没有走全表扫描，走了强制类型转换）
Execution Plan
----------------------------------------------------------
Plan hash value: 3191204463
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_COL_TYPE | 1 | 36 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=6)
结论：什么类型就放什么值，否则会发生类型转换，导致系能问题！
（是存放字符的字段就设varchar2类型,是存放数值的字段就设置number类型,是存放日期的字段就设置date类型）
另外不止是类型转换，如果对列进行了运算，比如substr(列），trunc(列)等等，也会导致用不上索引，具体见案例分析中
- 对索引进行了各种运算
3、物理索引失效
- long列调整导致索引失效
drop table t purge;
create table t (object_id number,object_name long);
create index idx_object_id on t(object_id);
insert into t values (1,'ab');
commit;
select t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID';
STATUS INDEX_NAME
-------- ---------------
VALID IDX_OBJECT_ID
alter table T modify object_name clob;
set autotrace off
select t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID';
STATUS INDEX_NAME
-------- --------------
UNUSABLE IDX_OBJECT_ID
alter index idx_object_id rebuild;
set autotrace off
select t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID';
STATUS INDEX_NAME
-------- ---------------
VALID IDX_OBJECT_ID
- move操作导致了索引失效
结论：很多人想用ALTER TABLE MOVE的方式来降低高水平，结果经常忽略了这个操作会导致索引失效，
请大家通过下列的试验的回顾，以后多留意这点。
另外alter table t shrink space; 是否能十全十美呢，请看后面的案例分析
*/
- 分区表导致索引失效操作
在前面已经描述过了，这里就不再重试验了，请同学们自行回到前面的章节中进行复习总结，只是做一个总结归纳。
1.truncate分区会导致全局索引失效，不会导致局部索引失效。如果truncate 增加update global indexes，全局索引不会失效。
2.drop分区会导致全局索引失效，局部索引因为drop分区，所以也不存在该分区的局部索引了。如果drop分区增加update global indexes，全局索引不会失效。
3.split分区会导致全局索引失效，也会导致局部索引失效。如果split分区增加update global indexes，全局索引不会失效。
4.add 分区不会导致全局索引失效，也不会导致局部索引失效。
5.exchange会导致全局索引失效，不会导致局部索引失效。如果exchange分区增加update global indexes，全局索引不会失效。
重要结论：
1. 所有的全局索引，只要用到update global indexes ,都不会失效，其中add分区甚至不需要增加update global indexes都可以生效。
2. 局部索引的操作都不会失效，除了split分区。切记split分区的时候，要将局部索引进行rebuild;
4、索引取舍的控制
- 避免交叉重复建立索引
- 删除系统中从没用过的索引
结论：我们可以通过alter index xxx monitoring usage的命令，对具体的索引进行监控，通过查询v$object_usage视图的USED
字段的取值是YES还是NO，就可以知道该索引是否被使用过，具体如下：
- 组合索引过多的索引很可疑
189
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
