索引访问开销_设置索引并行属性引风波
/*
为了提高建索引的效率，采用了并行的方式，并且设到了索引的属性中去了，引发了性能故障。
一般来说，如果我们要做并行的操作，建议用HINT的方式给查询语句加索引，比如/*+parallel n*/
*/
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
索引更新开销_分区表与插入性能的提升
/*
结论：如果表没有索引，插入的速度一般都不会慢，只有在有索引的情况下，才要考虑插入速度的优化。
如果表有大量索引，一般来说，分区表的局部索引由于只需要更新局部分区的索引，所以索引的开销会比较小，所以插入性能比
有着相同的记录数，列及索引的普通表更快。
*/
--构造分区表，插入数据。
drop table range_part_tab purge;
create table range_part_tab (id number,deal_date date,area_code number,nbr1 number,nbr2 number,nbr3 number,contents varchar2(4000))
partition by range (deal_date)
(
partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')),
partition p_201403 values less than (TO_DATE('2014-04-01', 'YYYY-MM-DD')),
partition p_201404 values less than (TO_DATE('2014-05-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;
--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有100万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr1,nbr2,nbr3,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
ceil(dbms_random.value(18900000001,18999999999)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 2000000;
commit;
--以下是插入2014年部分日期随机数和表示福建地区号含义（591到599）的随机数记录，共有20万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr1,nbr2,nbr3,contents)
select rownum,
to_date( to_char(sysdate,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
ceil(dbms_random.value(18900000001,18999999999)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 400000;
commit;
create index idx_parttab_id on range_part_tab(id) local;
create index idx_parttab_nbr1 on range_part_tab(nbr1) local;
create index idx_parttab_nbr2 on range_part_tab(nbr2) local;
create index idx_parttab_nbr3 on range_part_tab(nbr3) local;
create index idx_parttab_area on range_part_tab(area_code) local;
drop table normal_tab purge;
create table normal_tab (id number,deal_date date,area_code number,nbr1 number,nbr2 number,nbr3 number,contents varchar2(4000));
insert into normal_tab select * from range_part_tab;
commit;
create index idx_tab_id on normal_tab(id) ;
create index idx_tab_nbr1 on normal_tab(nbr1) ;
create index idx_tab_nbr2 on normal_tab(nbr2) ;
create index idx_tab_nbr3 on normal_tab(nbr3) ;
create index idx_tab_area on normal_tab(area_code) ;
select count(*) from normal_tab where deal_date>=TO_DATE('2014-02-01', 'YYYY-MM-DD') and deal_date<TO_DATE('2014-03-01', 'YYYY-MM-DD');
select count(*) from range_part_tab where deal_date>=TO_DATE('2014-02-01', 'YYYY-MM-DD') and deal_date<TO_DATE('2014-03-01', 'YYYY-MM-DD');
set timing on
insert into range_part_tab
select rownum,
to_date( to_char(sysdate+60,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
ceil(dbms_random.value(18900000001,18999999999)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 400000;
commit;
insert into normal_tab
select rownum,
to_date( to_char(sysdate+60,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
ceil(dbms_random.value(18900000001,18999999999)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 400000;
commit;
SQL> set timing on
SQL> insert into range_part_tab
2 select rownum,
3 to_date( to_char(sysdate+60,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'),
4 ceil(dbms_random.value(591,599)),
5 ceil(dbms_random.value(18900000001,18999999999)),
6 ceil(dbms_random.value(18900000001,18999999999)),
7 ceil(dbms_random.value(18900000001,18999999999)),
8 rpad('*',400,'*')
9 from dual
10 connect by rownum <= 400000;
已创建400000行。
已用时间: 00: 00: 51.20
SQL> insert into normal_tab
2 select rownum,
3 to_date( to_char(sysdate+60,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'),
4 ceil(dbms_random.value(591,599)),
5 ceil(dbms_random.value(18900000001,18999999999)),
6 ceil(dbms_random.value(18900000001,18999999999)),
7 ceil(dbms_random.value(18900000001,18999999999)),
8 rpad('*',400,'*')
9 from dual
10 connect by rownum <= 400000;
已创建400000行。
已用时间: 00: 01: 20.04
索引建立开销_未用online建索引酿大错
/*
结论：普通的对表建索引将会导致针对该表的更新操作无法进行，需要等待索引建完。更新操作将会被建索引动作阻塞。
而ONLINE建索引的方式却是不会阻止针对该表的更新操作，与建普通索引相反的是，ONLINE建索引的动作是反过来被更新操作阻塞。
*/
drop table t purge;
create table t as select * from dba_objects;
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;
select sid from v$mystat where rownum=1;
--12
set timing on
create index idx_object_id on t(object_id) online;
索引已创建。
session 2
sqlplus ljb/ljb
set linesize 1000
select sid from v$mystat where rownum=1;
--134
--以下执行居然不会被阻塞
update t set object_id=99999 where object_id=8;
session 3
set linesize 1000
select * from v$lock where sid in (12,134);
SQL> select * from v$lock where sid in (134,12);
SQL> select * from v$lock where sid in (134,12);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EB79320 2EB7934C 12 AE 100 0 4 0 278 0
2EB79394 2EB793C0 134 AE 100 0 4 0 303 0
2EB79408 2EB79434 12 DL 106831 0 3 0 25 0
2EB79574 2EB795A0 12 DL 106831 0 3 0 25 0
2EB795E8 2EB79614 12 OD 106831 0 4 0 25 0
2EB7965C 2EB79688 12 TX 131079 31688 0 4 11 0
0EDD7A9C 0EDD7ACC 134 TM 106831 0 3 0 23 0
0EDD7A9C 0EDD7ACC 12 TM 106831 0 2 0 25 0
0EDD7A9C 0EDD7ACC 12 TM 106834 0 4 0 25 0
2C17C3B8 2C17C3F8 134 TX 131079 31688 6 0 23 1
2C1A2448 2C1A2488 12 TX 589853 31754 6 0 25 0
已选择11行。
select /*+no_merge(a) no_merge(b) */
(select username from v$session where sid=a.sid) blocker,
a.sid, 'is blocking',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a,v$lock b
where a.block=1 and b.request>0
and a.id1=b.id1
and a.id2=b.id2;
BLOCKER SID 'ISBLOCKING BLOCKEE SID
------------------------------ ---------- ----------- ------------------------------ ----------
LJB 134 is blocking LJB 12
索引去哪儿_like与 %之间
/*
结论：索引遇到like '%LJB' 或者是'%LJB%'的查询，是用不到索引的（除非是全索引访问，这是索引能回答问题的一个例外）。
不过like 'LJB%'是可以用到索引的。原理其实很简单，从索引有序性就可以推理到原因了。
让'%LJB'用的索引的另类方法，这里涉及到了函数索引的知识，
另外即便是'%LJB%'，也不见的就一定用不到索引，可以考虑全文检索，
*/
思路：
1. 全文检索
2. 寻找函数构造的机会，并建函数索引
drop table t purge;
create table t as select * from dba_objects where object_id is not null;
set autotrace off
update t set object_id=rownum;
update t set object_name='AAALJB' where object_id=8;
update t set object_name='LJBAAA' where object_id=10;
commit;
create index idx_object_name on t(object_name);
SET AUTOTRACE ON
SET LINESIZE 1000
select object_name,object_id from t where object_name like 'LJB%';
OBJECT_NAME OBJECT_ID
------------------------------ ---
LJBAAA 10
LJB_TMP_SESSION 72521
LJB_TMP_SESSION 72910
LJB_TMP_TRANSACTION 72522
LJB_TMP_TRANSACTION 72911
已选择5行。
执行计划
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 395 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5 | 395 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_NAME | 5 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
602 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)
5 rows processed
SQL> select object_name,object_id from t where object_name like '%LJB%';
OBJECT_NAME OBJECT_ID
---------------------------------- ---
AAALJB 8
LJBAAA 10
LJB_TMP_SESSION 72521
LJB_TMP_TRANSACTION 72522
LJB_TMP_SESSION 72910
LJB_TMP_TRANSACTION 72911
已选择6行。
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 948 | 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 948 | 292 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
653 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)
6 rows processed
select object_name,object_id from t where object_name like '%LJB';
OBJECT_NAME OBJECT_ID
---------------------------- --
AAALJB 8
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 948 | 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 948 | 292 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
496 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 reverse('%LJB') from dual;
REVER
-----
BJL%
create index idx_reverse_objname on t(reverse(object_name));
set autotrace on
select object_name,object_id from t where reverse(object_name) like reverse('%LJB');
OBJECT_NAME OBJECT_ID
---------------------------- --
AAALJB 8
执行计划
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3596 | 509K| 290 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 3596 | 509K| 290 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IDX_REVERSE_OBJNAME | 647 | | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
496 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
move 致索引失效:
/*
结论：
这次案例，是涉及有主外键的两表关联查询的性能，索引失效导致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
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 idx_IND_T_C_FID on T_C(FID); --以下操作导致外键索引失效 ALTER TABLE T_C MOVE; 外键索引删除后，立即有锁相关问题 --首先开启会话1 select sid from v$mystat where rownum=1; DELETE T_C WHERE ID = 2; --接下来开启会话2，也就是开启一个新的连接 select sid from v$mystat where rownum=1; --然后执行如下进行观察 DELETE T_P WHERE ID = 2000; --居然发现卡住半天不动了！ --假如外键有索引，就不会产生死锁情况， --首先开启会话1 ALTER INDEX idx_IND_T_C_FID REBUIDL; select sid from v$mystat where rownum=1; DELETE T_C WHERE FID = 2; --接下来开启会话2，也就是开启一个新的连接 select sid from v$mystat where rownum=1; DELETE T_P WHERE ID = 2000;
shrink索引不失效也被弃用
/*
结论：alter table t shrink的方式降低表的高水平位，也不会导致索引失效，却无法消除索引的大量空块。
最终导致虽然索引不失效，查询依然不用索引，具体见案例如下：
*/
--这里用alter table t shrink的方式降低高水平位，结果避免了索引的失效，不过索引不失效了，是否索引就一定会被用到吗，
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;
set autotrace off
insert into t select * from t;
insert into t select * from t;
commit;
create index idx_object_id on t(object_id);
set linesize 1000
set autotrace on
select count(*) from t;
set autotrace off
delete from t where rownum<=292000;
commit;
set autotrace on
select count(*) from t;
alter table t enable row movement;
alter table t shrink space;
select count(*) from t;
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 740 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
424 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
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME STATUS
------------------------------ -------
IDX_OBJECT_ID VALID
set autotrace on
--原来发现走了，还更慢。
select /*+index(t)*/ count(*) from t;
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 675 (1)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_OBJECT_ID | 740 | 675 (1)| 00:00:09 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
649 consistent gets
0 physical reads
0 redo size
424 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
--结论，alter table t shrink 不会导致索引失效，但是索引块的高水平无法释放。还是会产生大量的逻辑读。
范围查询为何就用不到索引：
/*
结论：方向键索引可以消除索引热块访问竞争，是一个很不错的技术，该技术只能用在等值查询，而不能用在范围查询。
以下是生产中的一个案例，有人讲索引建成了反向键索引，却忘记了该系统有大量范围查询，还在纠结为何范围查询用不到索引。
*/
--这里说的是反向键索引的故事
drop table t purge;
create table t (id number,deal_date date,area_code number,nbr number,contents varchar2(4000));
set autotrace off
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) reverse;
set linesize 1000
set autotrace off
select index_name,index_type from user_indexes where table_name='T';
INDEX_NAME INDEX_TYPE
------------------------------ -----------
IDX_T_ID NORMAL/REV
set autotrace traceonly
--以下语句缘何用不到索引。
select * from t where id=28;
执行计划
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69 | 138K| 401 (0)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 69 | 138K| 401 (0)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 486 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
--不过奇怪的是，缘何下列语句却用不到索引，索引去哪儿？
select * from t where id>=28 and id<=50;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 304 | 608K| 1709 (1)| 00:00:21 |
|* 1 | TABLE ACCESS FULL| T | 304 | 608K| 1709 (1)| 00:00:21 |
--------------------------------------------------------------------------
统计信息
------------------------------------------------------
0 recursive calls
0 db block gets
6303 consistent gets
0 physical reads
0 redo size
2263 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23 rows processed
--原因在于这个索引不是普通索引，是为了避免热块竞争而建立的反向键索引，根本部支持范围查询，只支持等值查询。
回收站还原表后的苦难经历
/* 结论：关于误drop表后从回收站中取回表，需要记住一些细节，比如，这时其实该表的索引已经丢了。 一定要在恢复该表的同时记住将索引及约束等属性补完善。 */ drop table t purge; create table t as select * from dba_objects; create index idx_object_id on t(object_id); set autotrace off select index_name,status from user_indexes where table_name='T'; INDEX_NAME STATUS ------------------------------ -------- IDX_OBJECT_ID VALID drop table t; flashback table t to before drop; --取回来了，其实索引丢了 select status from user_indexes where index_name='IDX_OBJECT_ID'; 未选定行 --后来系统运行的很慢，很慢.....
回收站恢复与约束的那点事：
/*
结论：关于误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
请注意这写法是案例：
/*
结论：避免对列进行运算，否则将用不到索引，除非使用函数索引。
之间已经看过了一个时间的例子
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(object_id);
VARIABLE id NUMBER;
EXECUTE :id := 8;
set linesize 1000
set autotrace traceonly
select * from t where object_id/2=:id;
执行计划
--------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------
1 - filter(TO_NUMBER("ID")=6)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
540 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 object_id=:id*2;
执行计划
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 685 | 138K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 685 | 138K| 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 274 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1407 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
早知道有虚拟索引就好了
/* 结论：在数据库优化中，索引的重要性不言而喻。但是，在性能调整过程中，一个索引是否能被查询用到，在索 引创建之前是无法确定的，而创建索引是一个代价比较高的操作，尤其是数据量较大的时候。这时你就应该考虑使用虚拟索引来做个试验 */ drop table t purge; create table t as select * from dba_objects; --创建虚拟索引，首先要将_use_nosegment_indexes的隐含参数设置为true alter session set "_use_nosegment_indexes"=true; --虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个nosegment关键字 create index ix_t_id on t(object_id) nosegment; explain plan for select * from t where object_id=1; set linesize 1000 select * from table(dbms_xplan.display()); set autotrace traceonly select * from t where object_id=1; set autotrace off --以下看的是真实执行计划，显然是用不到索引。 alter session set statistics_level=all; select * from t where object_id=1; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); --从数据字段中是无法找到这个索引的。 select index_name,status from user_indexes where table_name='T'; 注：虚拟索引的几个特点 1. 无法执行alter index 2. 不能创建和虚拟索引同名的实际索引 3. 可以创建和虚拟索引包含相同列但不同名的实际索引 4. 在10g使用回收站特性的时候，虚拟索引必须显式drop，或者在drop table后purge table后，才能创建同名的索引 5. 虚拟索引分析并且有效，但是数据字典里查不到结果，估计是oracle内部临时保存了分析结果