1、索引的开销
- 设置索引并行引起的风波
drop table t purge;
create table t as select * from dba_objects where object_id is not null;
alter table T modify object_id not null;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
commit;
set timing on
create index idx_object_id on t(object_id) parallel 8;
索引已创建。
已用时间: 00: 00: 09.85
select index_name,degree from user_indexes where table_name='T';
INDEX_NAME DEGREE
------------------------------ -------
IDX_OBJECT_ID 8
set linesize 1000
set autotrace traceonly
select count(*) from t;
执行计划
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5797 (2)| 00:01:10 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 8100K| 5797 (2)| 00:01:10 | Q1,00 | PCWC | |
| 6 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 8100K| 5797 (2)| 00:01:10 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
25365 consistent gets
20769 physical reads
0 redo size
426 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace off
alter index IDX_OBJECT_ID noparallel;
select index_name,degree from user_indexes where table_name='T';
INDEX_NAME DEGREE
------------------------------ -------
IDX_OBJECT_ID 1
SQL> select count(*) from t;
执行计划
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5797 (2)| 00:01:10 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 8100K| 5797 (2)| 00:01:10 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
20828 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这也是一个真实的故事，为了提高建索引的效率，采用了并行的方式，并且设到了索引的属性中去了，引发了性能故障。
一般来说，如果我们要做并行的操作，建议用HINT的方式给查询语句加索引，比如/*+parallel n*/
- 分区表与插入性能的提升
结论：如果表没有索引，插入的速度一般都不会慢，只有在有索引的情况下，才要考虑插入速度的优化。如果表有大量索引，一般来说，分区表的局部索引由于只需要更新局部分区的索引，所以索引的开销会比较小，所以插入性能比有着相同的记录数，列及索引的普通表更快。
- 未使用online建立索引量成了大错
结论：普通的对表建索引将会导致针对该表的更新操作无法进行，需要等待索引建完。更新操作将会被建索引动作阻塞。而ONLINE建索引的方式却是不会阻止针对该表的更新操作，与建普通索引相反的是，ONLINE建索引的动作是反过来被更新操作阻塞。
*/
2、索引去哪儿了
- like与%一波三折的故事
结论：索引遇到like '%LJB' 或者是'%LJB%'的查询，是用不到索引的（除非是全索引访问，这是索引能回答问题的一个例外）。
不过like 'LJB%'是可以用到索引的。原理其实很简单，从索引有序性就可以推理到原因了。
不过本次案例中还说了一个很有趣的，让'%LJB'用的索引的另类方法，值得大家推敲和学习，这里涉及到了函数索引的知识，在下一讲中会描述。
- move 致索引失效引锁等待
结论：又是一次move table 引发的血案。
这次案例，是涉及有主外键的两表关联查询的性能，索引失效导致NL连接性能下降。
关于用NL连接的时候一般什么最快，具体的知识将在后续的表连接课程中描述。
结论：move表会导致索引失效的又一个故事，由于move 外键所在的表，导致外键的表的索引失效，导致主外键的表更新起来举 步维艰，频频被锁
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
- shrink索引不失效也被弃用
结论：alter table t shrink的方式降低表的高水平位，也不会导致索引失效，却无法消除索引的大量空块。
最终导致虽然索引不失效，查询依然不用索引。
- 范围查询为何就用不到索引.
- 回收站还原表后的苦难经历
- 回收站恢复与约束的案例
/*
结论：关于误drop表，然后从回收站中取回表后，除了索引会丢，约束一样也会丢失。
以下的故事还真是来自一个由于操作人员失误引发的悲催故事。
*/
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);
set autotrace off
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);
--以下删除数据会失败
delete from t_p where id=8;
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (LJB.FK_T_C) - 已找到子记录
---换一个顺序可以（先删除t_c的记录，再删除t_p)
delete from t_c where fid=8;
delete from t_p where id=8;
commit;
--当然，也可以采用约束下失效再生效的方法
--以下删除操作会失败
drop table t_p;
ORA-02449: unique/primary keys in table referenced by foreign keys
---不过强制可以删除成功（drop table t_p cascade constraint;）
--换一个顺序（先删t_c,再删t_p就可以了)
SQL> drop table t_c;
表已删除。
SQL> drop table t_p;
表已删除。
--当然，也可以采用约束下失效再生效的方法
----注意，现实中的一个案例，外键所在的表被drop了，从回收站取回来的时候，记得，不仅是索引没了，约束也丢了。
DROP TABLE T_C ;
FLASHBACK TABLE T_C TO BEFORE DROP;
---发现不止是外键的索引丢失了，约束也丢失了。
SELECT TABLE_NAME,
CONSTRAINT_NAME,
STATUS,
CONSTRAINT_TYPE,
R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'T_C';
未选定行
prompt <p>失效对象
select t.object_type,
t.object_name,
'alter ' ||decode(object_type, 'PACKAGE BODY', 'PACKAGE', 'TYPE BODY','TYPE',object_type) || ' ' ||owner || '.' || object_name || ' ' ||decode(object_type, 'PACKAGE BODY', 'compile body', 'compile') || ';'
from user_objects t
where STATUS='INVALID'
order by 1, 2;
- 看看最典型的时间查询通病
/*
结论：避免对列进行运算，否则将用不到索引，除非使用函数索引。
请看一个开发人员中非常常见的写法：
where trunc(created)>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and trunc(created)<=TO_DATE('2013-12-15', 'YYYY-MM-DD')
这个写法会有什么问题呢？具体见试验过程如下：
*/
drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(created);
set autotrace traceonly
set linesize 1000
--以下写法大量的出现在开发人员的代码中，是一个非常常见的通病,由于对列进行了运算，所以用不到索引，如下：
select * from t where trunc(created)>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and trunc(created)<=TO_DATE('2013-12-15', 'YYYY-MM-DD');
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 296 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 2484 | 296 (2)| 00:00:04 |
--------------------------------------------------------------------------
1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))>=TO_DATE(' 2013-12-14
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
TRUNC(INTERNAL_FUNCTION("CREATED"))<=TO_DATE(' 2013-12-15 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
1390 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
---调整为如下等价语句后，就可以用到索引了。
select * from t where created>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and created<TO_DATE('2013-12-15', 'YYYY-MM-DD')+1;
执行计划
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
2 - access("CREATED">=TO_DATE(' 2013-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "CREATED"<TO_DATE(' 2013-12-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
- 请注意这写法是案例非笑话
- 组合升降序排序索引有玄机
/*
结论：索引能够消除排序，这是之前学过的内容，但是如果排序是部分升序部分降序，就必须建对应部分升降序的索引，否则无法用这个来消除排序。
比如order by col1 desc col2 asc，我们可以建(col1 desc,col2 asc)的索引。
值得一提的是，如果你的语句变成 order by col1 asc col2 desc，之前的(col1 desc,col2 asc)的索引依然可以起到避免排序的作用DESCING。
这在之前的课程中描述过，请同学们自行复习。不要为此多建无意义的索引。
*/
drop table t purge;
create table t as select * from dba_objects where object_id is not null ;
set autotrace off
insert into t select * from t;
insert into t select * from t;
commit;
create index idx_t on t (owner,object_id);
alter table t modify owner not null;
alter table t modify object_id not null;
set linesize 1000
set autotrace traceonly
--听说order by 列有索引可以消除排序，测试发现，Oracle选择不用索引，排序依然存在，索引去哪儿？
select * from t a order by owner desc ,object_type asc;
执行计划
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 398K| 78M| | 19133 (1)| 00:03:50 |
| 1 | SORT ORDER BY | | 398K| 78M| 94M| 19133 (1)| 00:03:50 |
| 2 | TABLE ACCESS FULL| T | 398K| 78M| | 1177 (1)| 00:00:15 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4209 consistent gets
0 physical reads
0 redo size
13981752 bytes sent via SQL*Net to client
215080 bytes received via SQL*Net from client
19517 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
292740 rows processed
--换个思路，建如下索引
drop index idx_t;
create index idx_t on t(owner desc,object_type asc);
--哦，索引再这，效率果然提高了,COST比未用索引导致排序的代价19133低，是14687。
select * from t a order by owner desc ,object_type asc;
执行计划
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 398K| 78M| 14687 (1)| 00:02:57 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 398K| 78M| 14687 (1)| 00:02:57 |
| 2 | INDEX FULL SCAN | IDX_T | 398K| | 1085 (1)| 00:00:14 |
-------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
52710 consistent gets
0 physical reads
0 redo size
13821025 bytes sent via SQL*Net to client
215080 bytes received via SQL*Net from client
19517 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
292740 rows processed
261
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
