Oracle性能优化5-索引的不足

索引的不足
1.索引开销
a.访问开销
反问集中导致热块的竞争(对最新数据的查询)
回表性能取决聚合因子
索引的访问开销，返回几条数据快，但是返回大量的数据很慢
全表扫描与全扫描
建索引会产生全表锁

b.更新开销
索引本身是有序的,更新索引需要重新排序
c.建立开销
建索引会产生大量的排序，索引会产生锁

2.索引使用
a.逻辑失效
类型转换，列运算 upper(列)
/*
结论：又是一次move table 引发的血案。
这次案例，是涉及有主外键的两表关联查询的性能，索引失效导致NL连接性能下降。
关于用NL连接的时候一般什么最快，具体的知识将在后续的表连接课程中描述。

drop table t_p cascade constraints purge;
drop table t_c cascade constraints purge;

CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);
CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));

ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);

INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;
INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;
COMMIT;

CREATE INDEX IND_T_C_FID ON T_C (FID);

SELECT TABLE_NAME,INDEX_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND_T_C_FID';
TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ -------
T_C IND_T_C_FID VALID

--不小心失效了，比如操作了
ALTER TABLE T_C MOVE;

SELECT TABLE_NAME,INDEX_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND_T_C_FID';
TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
T_C IND_T_C_FID UNUSABLE

--结果查询性能是这样的：
SET LINESIZE 1000
SET AUTOTRACE TRACEONLY
SELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;
执行计划
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 1500 | 111 (1)| 00:00:02 |
| 1 | NESTED LOOPS | | 25 | 1500 | 111 (1)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T_C | 25 | 750 | 111 (1)| 00:00:02 |
------------------------------------------------------------------------------------------
3 - access("A"."ID"=880)
4 - filter("B"."FID"=880)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
394 consistent gets
0 physical reads
0 redo size
3602 bytes sent via SQL*Net to client
459 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72 rows processed

---将失效索引重建后
ALTER INDEX IND_T_C_FID REBUILD;
查询性能是这样的：
SELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;
执行计划
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72 | 4320 | 87 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 72 | 4320 | 87 (0)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_C | 72 | 2160 | 87 (0)| 00:00:02 |
|* 5 | INDEX RANGE SCAN | IND_T_C_FID | 72 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
3 - access("A"."ID"=880)
5 - access("B"."FID"=880)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
3602 bytes sent via SQL*Net to client
459 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72 rows processed
b.物理失效
索引坏了，分区导致全局索引失效，误删除