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.物理失效

  索引坏了，分区导致全局索引失效，误删除