Oracle学习笔记索引分类（十五）
哪些表的索引个数过多：
prompt <p>当前用户下,哪些表的索引个数字超过5个的 select table_name, count(*) cnt from user_indexes group by table_name having count(*) >= 5 order by cnt desc ;
哪些表的外键未建索引：
prompt <p>将外键未建索引的情况列出
select table_name,
constraint_name,
cname1 || nvl2(cname2, ',' || cname2, null) ||
nvl2(cname3, ',' || cname3, null) ||
nvl2(cname4, ',' || cname4, null) ||
nvl2(cname5, ',' || cname5, null) ||
nvl2(cname6, ',' || cname6, null) ||
nvl2(cname7, ',' || cname7, null) ||
nvl2(cname8, ',' || cname8, null) columns
from (select b.table_name,
b.constraint_name,
max(decode(position, 1, column_name, null)) cname1,
max(decode(position, 2, column_name, null)) cname2,
max(decode(position, 3, column_name, null)) cname3,
max(decode(position, 4, column_name, null)) cname4,
max(decode(position, 5, column_name, null)) cname5,
max(decode(position, 6, column_name, null)) cname6,
max(decode(position, 7, column_name, null)) cname7,
max(decode(position, 8, column_name, null)) cname8,
count(*) col_cnt
from (select substr(table_name, 1, 30) table_name,
substr(constraint_name, 1, 30) constraint_name,
substr(column_name, 1, 30) column_name,
position
from user_cons_columns) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name) cons
where col_cnt > ALL
(select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4, cname5,
cname6, cname7, cname8)
and i.column_position <= cons.col_cnt
group by i.index_name);
哪些表组合索引列过多
prompt <p>当前用户下，哪些组合索引组合列超过4个的 select table_name, index_name, count(*) from user_ind_columns group table_name, index_name having count(*) >= 4 order by count(*) desc;
哪些大表未建任何索引
--针对普通表（大于2GB的表未建任何索引） select segment_name, bytes/1024/1024/1024 "GB", blocks, tablespace_name from user_segments where segment_type = 'TABLE' and segment_name not in (select table_name from user_indexes) and bytes / 1024 / 1024 / 1024 >= 2 order by GB desc; --针对分区表（大于2GB的分区表未建任何索引） --无论是建了局部索引还是全局索引，在user_indexes都可以查到，只是status不一样。 select segment_name, sum(bytes)/1024/1024/1024 "GB", sum(blocks) from user_segments where segment_type = 'TABLE PARTITION' and segment_name not in (select table_name from user_indexes) group by segment_name having sum(bytes)/1024/1024/1024>=2 order by GB desc; --注：无论是建了局部索引还是全局索引，在user_indexes都可以查到，只是status不一样。
哪些聚索引列合因子差
prompt <p>当前用户下，哪些索引的聚合因子特别大。
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 a.clustering_factor is not null
and a.clustering_factor / b.num_rows>0.9
order by cluster_rate desc ;
哪些类型的索引已失效
prompt <p>失效-普通索引
select t.index_name,
t.table_name,
blevel,
t.num_rows,
t.leaf_blocks,
t.distinct_keys
from user_indexes t
where status = 'UNUSABLE' ;
prompt <p>失效-分区索引
select t1.blevel,
t1.leaf_blocks,
t1.INDEX_NAME,
t2.table_name,
t1.PARTITION_NAME,
t1.STATUS
from user_ind_partitions t1, user_indexes t2
where t1.index_name = t2.index_name
and t1.STATUS = 'UNUSABLE';
哪些索引单列组合有叉
prompt <p>当前用户下，哪些表的组合索引与单列索引存在交叉的情况。 select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate from user_ind_columns group by table_name having count(distinct(column_name)) / count(*) < 1 order by cross_idx_rate desc; ---------例子 drop table t purge; drop table t1 purge; create table t1 as select * from dba_objects where object_id is not null; create index idx_t1_objid_owner on t1(object_id ,owner); create index idx_t1_object_id on t1(object_id ); drop table t2 purge; create table t2 as select * from dba_objects where object_id is not null; create index idx_t2_objid_owner on t2(object_id,owner); create index idx_t2_object_id on t2(object_id); create index idx_t2_owner on t2(owner); drop table t3 purge; create table t3 as select * from dba_objects where object_id is not null; create index idx_t3_objid_owner on t3(object_id,owner); create index idx_t3_owner_objid on t3(owner,object_id); create index idx_t3_object_id on t3(object_id); create index idx_t3_owner on t3(owner); ---执行如下语句，发现当前用户下，T3,T2,T1表存在索引单列组合有交叉，最严重的是T3 select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate from user_ind_columns group by table_name having count(distinct(column_name)) / count(*) < 1 order by cross_idx_rate ; TABLE_NAME CROSS_IDX_RATE ------------------------------ -------------- T3 .33 T2 .5 T1 .66
哪些索引的高度比较高
prompt <p>当前用户下，哪些索引的高度比较高，大于5层（LEVEL=4)
select table_name,
index_name,
blevel,
leaf_blocks,
num_rows,
last_analyzed,
degree,
status
from user_indexes
where blevel>=4;
哪些索引建后从未使用
prompt <p>当前用户下，哪些索引最近30天内从未被使用过。 set linesize 166 col INDEX_NAME for a10 col TABLE_NAME for a10 col MONITORING for a10 col USED for a10 col START_MONITORING for a25 col END_MONITORING for a25 --以下判断在最近30天内未被使用过的索引有哪些 select * from v$object_usage where USED = 'NO' and START_MONITORING <= sysdate - 30 and END_MONITORING is not null; --注，之前需有对索引进行监控，如 alter index idx_t_id monitoring usage; ---如果取消监控就是 alter index idx_t_id nomonitoring usage;
哪些索引设置并行属性
prompt <p>当前用户下，哪些索引被设置了并行。
select table_name,
index_name,
blevel,
leaf_blocks,
num_rows,
last_analyzed,
degree,
status
from user_indexes
where degree>1;
哪些索引统计信息太旧
--普通索引（从未收集过统计信息或者是最近10天内未收集过统计信息的表）
select index_name, table_name, last_analyzed, num_rows, temporary, status
from user_indexes
where status <> 'N/A'
and (last_analyzed is null or last_analyzed < sysdate - 10);
--分区索引（从未收集过统计信息或者是最近10天内未收集过统计信息的分区）
select t2.table_name,
t1.INDEX_NAME,
t1.PARTITION_NAME,
t1.last_analyzed,
t1.blevel,
t1.leaf_blocks,
t1.STATUS
from user_ind_partitions t1, user_indexes t2
where t1.index_name = t2.index_name
and (t1.last_analyzed is null or t1.last_analyzed < sysdate - 10);
哪些主外键约束失效了
prompt <p>当前用户下，哪些外键的约束失效了。
SELECT TABLE_NAME,
CONSTRAINT_NAME,
STATUS,
CONSTRAINT_TYPE,
R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE STATUS='DISABLED';
--试验
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;
ALTER TABLE T_C DISABLE CONSTRAINT FK_T_C;
CREATE INDEX IND_T_C_FID ON T_C (FID);
函数索引
基于函数的索引是将一个函数计算得到的结果存储在行的列中， 而不是存储列数据本身。可以把基于函数的索引看作一个虚拟列上 的索引（这个列不是物理的存储在表中）。
反向键索引
反向键索引就是普通的B*TREE索引，只不过键中的字节会“反转”。利 用反向键索引，如果索引中填充的是递增的值，索引条目在索引中可以得 到更均匀的分布。如687002、687003、687004等值是顺序的，如果是传统 B*TREE索引，这些值就会在同一个右侧块上，加剧了块的竞争。如果反向 键索引：Oracle会逻辑的将687002、687003、00786都转换。一 下子距离变得很远，于是索引的插入立即分布到多块上去了。
全文索引
Oracle实现全文检索，其机制其实很简单。即通过Oracle词法分析器 (lexer),将所有的表意单元(Oracle 称为 term)找出来，记录在一组以dr$开 头的表中，同时记下该term出现的位置、次数、hash值等信息。检索时， Oracle从这组表中查找相应的term，并计算其出现频率，根据某个算法来 计算每个文档的得分(score),即所谓的‘匹配率’。而lexer则是该机制的核 心，它决定了全文检索的效率。Oracle针对不同的语言提供了不同的lexer, 而我们通常能用到其中的三个
位图索引之如何高效即席查询
/*
总结“本质圆滑音其实就是位图索引之间的与非运算非常高效!
*/
---做位图索引与即席查询试验前的准备
drop table t purge;
set autotrace off
create table t
(name_id,
gender not null,
location not null,
age_group not null,
data
)
as
select rownum,
decode(ceil(dbms_random.value(0,2)),
1,'M',
2,'F')gender,
ceil(dbms_random.value(1,50)) location,
decode(ceil(dbms_random.value(0,3)),
1,'child',
2,'young',
3,'middle_age',
4,'old'),
rpad('*',400,'*')
from dual
connect by rownum<=100000;
--注意，以下收集统计信息必须先执行。
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
---查询即席查询中应用全表扫描的代价
set linesize 1000
set autotrace traceonly
select *
from t
where gender='M'
and location in (1,10,30)
and age_group='child';
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 489 | 113K| 1674 (1)| 00:00:21 |
|* 1 | TABLE ACCESS FULL| T | 489 | 113K| 1674 (1)| 00:00:21 |
--------------------------------------------------------------------------
1 - filter("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR
"LOCATION"=30) AND "AGE_GROUP"='child')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6112 consistent gets
0 physical reads
0 redo size
15885 bytes sent via SQL*Net to client
943 bytes received via SQL*Net from client
50 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
723 rows processed
-- 以下是即席查询中，Oracle选择组合索引情况的代价和逻辑读(注意，回表的代价特别大）。
drop index idx_union;
create index idx_union on t(gender,location,age_group);
select *
from t
where gender='M'
and location in (1,10,30)
and age_group='child';
普通联合索引执行计划
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 810 | 180K| 793 (0)| 00:00:10 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 810 | 180K| 793 (0)| 00:00:10 |
|* 3 | INDEX RANGE SCAN | IDX_UNION | 810 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
3 - access("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR "LOCATION"=30)
AND "AGE_GROUP"='child')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1071 consistent gets
0 physical reads
0 redo size
318987 bytes sent via SQL*Net to client
943 bytes received via SQL*Net from client
50 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
731 rows processed
--- 即席查询应用到位图索引，性能有飞跃，ORACLE自己选择了使用位图索引
create bitmap index gender_idx on t(gender);
create bitmap index location_idx on t(location);
create bitmap index age_group_idx on t(age_group);
select *
from t
where gender='M'
and location in (1,10,30)
and age_group='child';
位图索引执行计划
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 810 | 180K| 236 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 810 | 180K| 236 (0)| 00:00:03 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP OR | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| LOCATION_IDX | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE| LOCATION_IDX | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE| LOCATION_IDX | | | | |
|* 8 | BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX | | | | |
|* 9 | BITMAP INDEX SINGLE VALUE | GENDER_IDX | | | | |
-----------------------------------------------------------------------------------------------
5 - access("LOCATION"=1)
6 - access("LOCATION"=10)
7 - access("LOCATION"=30)
8 - access("AGE_GROUP"='child')
9 - access("GENDER"='M')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
722 consistent gets
0 physical reads
0 redo size
318987 bytes sent via SQL*Net to client
943 bytes received via SQL*Net from client
50 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
731 rows processed
位图索引之如何快速统计条数：
/*
总结：本质原因：其实就是位图索引存放的是0,1的比特位，占字节数特别少。
*/
--位图索引跟踪前准备
drop table t purge;
set autotrace off
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;
update t set object_id=rownum;
commit;
--观察COUNT(*)全表扫描的代价
set autotrace on
set linesize 1000
select count(*) from t;
COUNT(*)
----------
4684992
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20420 (11)| 00:04:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 294M| 20420 (11)| 00:04:06 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
66731 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
--观察COUNT(*)用普通索引的代价
create index idx_t_obj on t(object_id);
alter table T modify object_id not null;
set autotrace on
select count(*) from t;
COUNT(*)
----------
4684992
普通索引的执行计划
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3047 (2)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_OBJ | 4620K| 3047 (2)| 00:00:37 |
---------------------------------------------------------------------------
普通索引的统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
10998 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
--观察COUNT(*)用位图索引的代价（注意，这里我们特意取了status这个重复度很高的列做索引）
create bitmap index idx_bitm_t_status on t(status);
select count(*) from t;
SQL> select count(*) from t;
COUNT(*)
----------
4684992
位图索引的执行计划
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 4620K| 115 (0)| 00:00:02 |
| 3 | BITMAP INDEX FAST FULL SCAN| IDX_BITM_T_STATUS | | | |
-------------------------------------------------------------------------------------------
位图索引的统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
125 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
函数索引陷阱之30553的错误
/*
总结：大家在工作中用到自定义函数的时候也很多，因为自定义函数可以直接在SQL中调用，简化代码，
给编写数据库应用带来了很大的方便，但是与此同时我们要
考虑SQL中用到的自定义函数是否能用的上索引，因此我们在建自定义函数的时候尽量考虑加上DETERMINISTIC的
关键字，以方便将来用上函数索引。
引申联想：如果要用到自定义函数的函数索引，必须要有DETERMINISTIC的关键字，
对于指定了DETERMINISTIC的函数，
在一次调用中，对于相同的输入，只进行一次调用。
这要求函数的创建者来保证DETERMINISTIC的正确性，如果这个函数的返回值和输入参数没有确定性关系，会导致
函数结果异常的。
*/
ORA-30553错误
--建函数索引一般都是对ORACLE的自带函数做函数索引，如upper()等等，
--但是如果我们要进行基于自定义函数的索引的建立的时候，必须使用DETERMINISTIC关键字，
--否则会报ORA-30553错误，这点要引起大家的注意。
drop table test;
create table test as select * from user_objects ;
create or replace function f_minus1(i int)
return int
is
begin
return(i-1);
end;
/
---建完函数后我们试着建立函数索引，发现建立失败
create index idx_ljb_test on test (f_minus1(object_id));
将会出现如下错误：
ORA-30553: 函数不能确定
将函数加上DETERMINISTIC关键字重建
create or replace function f_minus1(i int)
return int DETERMINISTIC
is
begin
return(i-1);
end;
/
--现在发现加上DETERMINISTIC关键字后的自定义函数可以建立函数索引成功了！
create index idx_test on test (f_minus1(object_id));
explain plan for select * from test where f_minus1(object_id)=23;
set linesize 1000
select * from table(dbms_xplan.display);
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
建立一个带DETERMINISTIC关键字的函数，功能就是返回值为1
SQL> CREATE OR REPLACE FUNCTION F_DETERMINISTIC RETURN NUMBER DETERMINISTIC
2 AS
3 BEGIN
4 DBMS_LOCK.SLEEP(0.1);
5 RETURN 1;
6 END;
7 /
Function created
SQL> set timing on
发现执行时间非常快，0.765秒完成
SQL> SELECT F_DETERMINISTIC FROM user_tables;
F_DETERMINISTIC
---------------
1
1
这里略去另外73个记录（值都为1）
75 rows selected
Executed in 0.765 seconds
用不带关键字DETERMINISTIC的方式建立函数，功能也是返回值为1
SQL> CREATE OR REPLACE FUNCTION F_DETERMINISTIC RETURN NUMBER
2 AS
3 BEGIN
4 DBMS_LOCK.SLEEP(0.1);
5 RETURN 1;
6 END;
7 /
Function created
Executed in 0.047 seconds
发现执行速度慢了好多，8.469秒
SQL> SELECT F_DETERMINISTIC FROM user_tables;
F_DETERMINISTIC
---------------
1
1
这里也略去另外73个记录（值都为1）
75 rows selected
Executed in 8.469 seconds
--带DETERMINISTIC的函数对于相同的输入只会运算一次，
--认为值是一样的，就把上次算出来的结果直接引用了，所以为什么第一种会执行速度这么快，
--因为第一种情况下根本该自定义函数就只调用了1次，然后另外那74次结果都是直接考虑到输入相同，
--直接把第1次调用的结果拿来用而已。而第二种其实是调用了75次。
--没有输入就是表示输入相同值的含义！（有输入的情况也，一样！）
--为什么ORACLE要对自定义函数做这个DETERMINISTIC限制呢？
--应该是有这两个原因吧：
--1、可避免我们建立一个不确定输出值的函数！（不过那不是建立自定义函数，是ORACLE自带函数失败），
--就和相同输入返回不同结果有关系。
--2、SQL中写函数函数很容易出现性能问题，比如你写个不好的函数，又被大量调用，
--那估计执行起来麻烦要大了，ORACLE这样做了，可以让写自定义函数的人在调用的时候一次执行多次使用结果，
--速度也可以快很多。
函数索引与各种列的等式转换：
--测函数索引前准备
drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
create index idx_object_name on t(object_name);
create index idx_created on t(created);
---比较where object_id-10<=30和where object_id<=40写法的性能
set autotrace traceonly
set linesize 1000
select * from t where object_id-10<=30;
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 293 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 2484 | 293 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1051 consistent gets
0 physical reads
0 redo size
2898 bytes sent via SQL*Net to client
437 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39 rows processed
--其实你应该这么写代码的，才可以让oracle 用上索引。
select * from t where object_id<=30+10;
已选择39行。
执行计划
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 8073 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 39 | 8073 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 39 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
4781 bytes sent via SQL*Net to client
437 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39 rows processed
--当然，你也可以这样建索引，如果不难为情的话。
create index idx_object_id_2 on t(object_id-10);
--确实走索引了，建这样的索引，你真是够有勇气了!
select * from t where object_id-10<=30;
执行计划
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3873 | 832K| 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 3873 | 832K| 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID_2 | 697 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
2761 bytes sent via SQL*Net to client
437 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39 rows processed
函数索引与各种列的函数转换
--测函数索引前准备
drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
create index idx_object_name on t(object_name);
create index idx_created on t(created);
--对列做UPPER操作，无法用到索引
set autotrace traceonly
set linesize 1000
---以下语句由于列运算，所以走的是全表扫描
select * from t where upper(object_name)='T' ;
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 293 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 2484 | 293 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
1500 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)
2 rows processed
--去掉列的UPPER操作后立即用索引
select * from t where object_name='T' ;
执行计划
----------------------------------------------------------
Plan hash value: 1138138579
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 414 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 414 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_NAME | 2 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1506 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)
2 rows processed
--如果必须用upper的条件，那你想用到索引，就得去建函数索引
create index idx_func_ojbnam on t(upper(object_name));
--继续执行，终于走索引了。
select * from t where upper(object_name)='T' ;
执行计划
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 775 | 206K| 152 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 775 | 206K| 152 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX_FUNC_OJBNAM | 310 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1500 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)
2 rows processed
函数索引与各种列的类型转换
/*
结论：什么类型就放什么值，否则会发生类型转换，导致系能问题！
（是存放字符的字段就设varchar2类型,是存放数值的字段就设置number类型,是存放日期的字段就设置date类型）
这里的案例宏中
select * from t_col_type where id=6; 用不到索引，要改成select * from t_col_type where id='6';
如果送来的参数无法保证是'6',只能写成select * from t_col_type where to_number(id)=6;并且建to_number(id)的函数索引
方可。
*/
--举例说明：
drop table t_col_type purge;
create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));
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;
执行计划
--------------------------------------------------------------------------------
| 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_col_type where id='6';
执行计划
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_COL_TYPE | 1 | 36 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
2 - access("ID"='6')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
544 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
create index idx_func_tonumber_id on t_col_type(to_number(id));
select * from t_col_type where to_number(id)=6;
执行计划
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4900 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_COL_TYPE | 100 | 4900 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FUNC_TONUMBER_ID | 40 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
2 - access(TO_NUMBER("ID")=6)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
540 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
反向键索引妙用之能减少争用
/
总结：好处：
反转前 －－反转后
123 －－－321
124 －－－421
125 －－－521
消除了热块竞争
坏处：
范围查询根本无法使用!
*/
drop table t1 purge;
create table t1 as select * from dba_objects;
insert into t1 select * from t1;
update t1 set object_id=rownum ;
create index idx_t1_rev_objn on t1(object_id) reverse ;
drop table t2 purge;
create table t2 as select * from dba_objects;
insert into t2 select * from t1;
update t2 set object_id=rownum ;
create index idx_t2_objn on t2(object_id) ;
create or replace procedure p_reverse as
begin
for j in 1..100 loop
for i in (select * from t1 where object_id=10008) loop
null;
end loop;
for i in (select * from t1 where object_id=10009) loop
null;
end loop;
for i in (select * from t1 where object_id=10010) loop
null;
end loop;
for i in (select * from t1 where object_id=10011) loop
null;
end loop;
for i in (select * from t1 where object_id=10012) loop
null;
end loop;
for i in (select * from t1 where object_id=10013) loop
null;
end loop;
for i in (select * from t1 where object_id=10014) loop
null;
end loop;
for i in (select * from t1 where object_id=10015) loop
null;
end loop;
for i in (select * from t1 where object_id=10016) loop
null;
end loop;
for i in (select * from t1 where object_id=10017) loop
null;
end loop;
for i in (select * from t1 where object_id=10018) loop
null;
end loop;
for i in (select * from t1 where object_id=10019) loop
null;
end loop;
for i in (select * from t1 where object_id=10020) loop
null;
end loop;
for i in (select * from t1 where object_id=10021) loop
null;
end loop;
end loop;
end p_reverse;
/
create or replace procedure p_no_reverse as
begin
for j in 1..100 loop
for i in (select * from t2 where object_id=10008) loop
null;
end loop;
for i in (select * from t2 where object_id=10009) loop
null;
end loop;
for i in (select * from t2 where object_id=10010) loop
null;
end loop;
for i in (select * from t2 where object_id=10011) loop
null;
end loop;
for i in (select * from t2 where object_id=10012) loop
null;
end loop;
for i in (select * from t2 where object_id=10013) loop
null;
end loop;
for i in (select * from t2 where object_id=10014) loop
null;
end loop;
for i in (select * from t2 where object_id=10015) loop
null;
end loop;
for i in (select * from t2 where object_id=10016) loop
null;
end loop;
for i in (select * from t2 where object_id=10017) loop
null;
end loop;
for i in (select * from t2 where object_id=10018) loop
null;
end loop;
for i in (select * from t2 where object_id=10019) loop
null;
end loop;
for i in (select * from t2 where object_id=10020) loop
null;
end loop;
for i in (select * from t2 where object_id=10021) loop
null;
end loop;
end loop;
end p_no_reverse;
/
create or replace procedure p_remove_job as
BEGIN
for i in (select job from user_jobs )loop
DBMS_JOB.remove(i.job);
end loop;
END p_remove_job;
/
create or replace procedure p_test_reverse as
JOBNO NUMBER;
BEGIN
for i in 1..100 loop
DBMS_JOB.SUBMIT( JOBNO,
'p_reverse;',
SYSDATE,
'SYSDATE+1/1440');
end loop;
DBMS_LOCK.sleep(120);
END p_test_reverse;
/
create or replace procedure p_test_no_reverse as
JOBNO NUMBER;
BEGIN
for i in 1..100 loop
DBMS_JOB.SUBMIT( JOBNO,
'p_no_reverse;',
SYSDATE,
'SYSDATE+1/1440');
end loop;
DBMS_LOCK.sleep(120);
END p_test_no_reverse;
/
drop table test_latch purge;
create table test_latch (type varchar2(10),id number,gets number , misses number, sleeps number, immediate_gets number);
--测试1.
delete from test_latch where type='reverse';
insert into test_latch select 'reverse',1,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
exec p_test_reverse;
insert into test_latch select 'reverse',2,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--测试结束
exec p_remove_job;
delete from test_latch where type='no_reverse';
insert into test_latch select 'no_reverse',1,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
exec p_test_no_reverse;
insert into test_latch select 'no_reverse',2,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--测试结束
exec p_remove_job;
SELECT WHAT, INTERVAL, JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM USER_JOBS WHERE INTERVAL = 'SYSDATE+1/1440';
--实际执行情况可能由于数据量不够大，并发不够大，而有差异。
select type,
misses - lag_misses
from (select t.*,lag(misses) over(partition by type order by misses) lag_misses
from test_latch t) k
where k.lag_misses is not null;
全文索引性能优势之妙用索引
drop table test purge;
create table test as select * from dba_objects;
update test set object_name='高兴' where rownum<=2;
create index idx_object_name on test(object_name);
set autotrace traceonly explain
select * from test where object_name like '%高兴%';
exit;
grant ctxapp to ljb;
alter user ctxsys account unlock;
alter user ctxsys identified by ctxsys;
connect ctxsys/ctxsys;
grant execute on ctx_ddl to ljb;
connect ljb/ljb
--第一次执行无需注释掉其中头两条
Begin
ctx_ddl.drop_preference('club_lexer');
ctx_ddl.drop_preference('mywordlist');
ctx_ddl.create_preference('club_lexer','CHINESE_LEXER');
ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',1);
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 5);
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
end;
/
create index id_cont_test on TEST (object_name) indextype is ctxsys.context
parameters (
'DATASTORE CTXSYS.DIRECT_DATASTORE FILTER
CTXSYS.NULL_FILTER LEXER club_lexer WORDLIST mywordlist');
exec ctx_ddl.sync_index('id_cont_TEST', '20M');
set autotrace traceonly
set linesize 1000
select * from test where OBJECT_NAME like '%高兴%';
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 7038 | 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 34 | 7038 | 292 (1)| 00:00:04 |
--------------------------------------------------------------------------
1 - filter("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%高兴%')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
1498 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)
2 rows processed
select * from test where contains(OBJECT_NAME,'高兴')>0;
执行计划
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 10731 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 49 | 10731 | 14 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | ID_CONT_TEST | | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("OBJECT_NAME",'高兴')>0)
统计信息
----------------------------------------------------------
11 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
1504 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)
2 rows processed
select * from test where OBJECT_NAME like '%高%';
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 7038 | 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 34 | 7038 | 292 (1)| 00:00:04 |
--------------------------------------------------------------------------
1 - filter("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%高%')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
1498 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)
2 rows processed
select * from test where contains(OBJECT_NAME,'高')>0;
执行计划
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 10731 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 49 | 10731 | 14 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | ID_CONT_TEST | | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("OBJECT_NAME",'高')>0)
统计信息
----------------------------------------------------------
11 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
1504 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)
2 rows processed
具体理解
Oracle实现全文检索，其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元（Oracle 称为 term，此处我理解为单词或者一些有
意义的词语） 找出来，记录在一组以 dr$开头的表中，同时记下该term出现的位置、次数、hash 值等信息。检索时，Oracle 从这组表中查找相应的 term，
并计算其出现频率，根据某个算法来计算每个文档的得分（score）,即所谓的'匹配率'。而lexer则是该机制的核心，它决定了全文检索的效率。
Oracle 针对不同的语言提供了不同的 lexer, 而我们通常能用到其中的三个：
basic_lexer: 针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为'垃圾'处理，
如if , is 等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为
一个term,事实上失去检索能力。以'中国人民站起来了'这句话为例，basic_lexer 分析的结果只有一个term ,就是'中国人民站起来了'。此时若检索'中国'，
将检索不到内容。
chinese_vgram_lexer: 专门的汉语分析器，支持所有汉字字符集。该分析器按字为单元来分析汉语句子。'中国人民站起来了'这句话，会被它分析成如下几个
term: '中'，'中国'，'国人'，'人民'，'民站'，'站起'，起来'，'来了'，'了'。可以看出，这种分析方法，实现算法很简单，并且能实现'一网打尽'，但效率则
是差强人意。
chinese_lexer: 这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常
机械，像上面的'民站'，'站起'在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese_lexer的最大改进就是该分析器能认识大部分常
用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大提高了效率。但是它只支持 utf8, 如果你的数据库是zhs16gbk字符集，则只
能使用Chinese vgram lexer。
位图索引陷阱之更新列容易死锁
--位图索引遭遇锁困扰试验步骤1
sqlplus ljb/ljb
select sid from v$mystat where rownum=1;
insert into t(name_id,gender,location ,age_group ,data) values (100001,'M',45,'child',rpad('*',20,'*'));
--位图索引遭遇锁困扰试验步骤2
sqlplus ljb/ljb
select sid from v$mystat where rownum=1;
insert into t(name_id,gender,location ,age_group ,data) values (100002,'M',46, 'young', rpad('*',20,'*'));
--位图索引遭遇锁困扰试验步骤3
select sid from v$mystat where rownum=1;
insert into t(name_id,gender,location ,age_group ,data) values (100003,'F',47, 'middle_age', rpad('*',20,'*'));
--位图索引遭遇锁困扰试验步骤4
select sid from v$mystat where rownum=1;
insert into t(name_id,gender,location ,age_group ,data) values (100003,'F',48, ' old', rpad('*',20,'*'));
--暂且删除location和age_group列的位图索引，为下一试验做准备
--分别进刚才几个SESSION执行如下操作，完成回退
rollback;
--删除location和age_group列的位图索引
drop index location_idx;
drop index age_group_idx;
/*请自行测试锁的情况
位图索引之锁持有者的DELETE的实验
*/
--SESSION 1（持有者）
DELETE FROM T WHERE GENDER='M' AND LOCATION=25;
---SESSION 2(其他会话) 插入带M的记录就立即被阻挡，以下三条语句都会被阻止
insert into t (name_id,gender,location ,age_group ,data) values (100001,'M',78, 'young','TTT');
update t set gender='M' WHERE LOCATION=25;
delete from T WHERE GENDER='M';
--以下是可以进行不受阻碍的
insert into t (name_id,gender,location ,age_group ,data) values (100001,'F',78, 'young','TTT');
delete from t where gender='F' ;
UPDATE T SET LOCATION=100 WHERE ROWID NOT IN ( SELECT ROWID FROM T WHERE GENDER='F' AND LOCATION=25) ;
--update只要不更新位图索引所在的列即可
位图索引陷阱之列重复度低慎建
---测试位图索引重复度前准备工作
drop table t purge;
set autotrace off
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;
update t set object_id=rownum;
commit;
--COUNT(*)在列重复度低时一般不会考虑使用位图索引
create bitmap index idx_bit_object_id on t(object_id);
create bitmap index idx_bit_status on t(status);
--注意，以下收集统计信息必须先执行。
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
set linesize 1000
set autotrace traceonly
/*
在object_id列建位图索引后，是啥情况
*/
--create bitmap index idx_bit_object_id on t(object_id);
select /*+index(t,idx_bit_object_id)*/ count(*) from t;
执行计划
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17245 (1)| 00:03:27 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT| | 4688K| 17245 (1)| 00:03:27 |
| 3 | BITMAP INDEX FULL SCAN| IDX_BIT_OBJECT_ID | | | |
--------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
16837 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
--create bitmap index idx_bit_status on t(status);
select /*+index(t,index idx_bit_status)*/ count(*) from t;
执行计划
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 4688K| 105 (0)| 00:00:02 |
| 3 | BITMAP INDEX FAST FULL SCAN| IDX_BIT_STATUS | | | |
----------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
125 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
--原理分析：
set autotrace off
select segment_name,blocks,bytes/1024/1024 "SIZE(M)"
from user_segments
where segment_name in( 'IDX_BIT_OBJECT_ID','IDX_BIT_STATUS');
SEGMENT_NAME BLOCKS SIZE(M)
----------------------------- ----------
IDX_BIT_OBJECT_ID 17408 136
IDX_BIT_STATUS 128 1
函数索引妙用之部分记录建索引
drop table t purge;
set autotrace off
create table t (id int ,status varchar2(2));
--建立普通索引
create index id_normal on t(status);
insert into t select rownum ,'Y' from dual connect by rownum<=1000000;
insert into t select 1 ,'N' from dual;
commit;
analyze table t compute statistics for table for all indexes for all indexed columns;
set linesize 1000
set autotrace traceonly
select * from t where status='N';
执行计划
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ID_NORMAL | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
2 - access("STATUS"='N')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
416 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
analyze index id_normal validate structure;
select name,btree_space,lf_rows,height from index_stats;
set autotrace off
analyze index id_normal validate structure;
select name,btree_space,lf_rows,height from index_stats;
NAME BTREE_SPACE LF_ROWS HEIGHT
------------------------------ ----------- ---------- ----------
ID_NORMAL 22960352 1000001 3
--建函数索引
drop index id_normal;
create index id_status on t (Case when status= 'N' then 'N' end);
analyze table t compute statistics for table for all indexes for all indexed columns;
/*以下这个select * from t where (case when status='N' then 'N' end)='N'
写法不能变,如果是select * from t where status='N'将无效!我见过有些人设置了选择性索引，
却这样调用的，结果根本起不到任何效果！
*/
set autotrace traceonly
select * from t where (case when status='N' then 'N' end)='N';
执行计划
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ID_STATUS | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
479 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--接着观察id_status（即函数索引）索引的情况
set autotrace off
analyze index id_status validate structure;
select name,btree_space,lf_rows,height from index_stats;
NAME BTREE_SPACE LF_ROWS HEIGHT
------------------------------ ----------- ---------- ----------
ID_STATUS 8000 1 1
函数索引妙用之减少递归的调用：
drop table t1 purge;
drop table t2 purge;
create table t1 (first_name varchar2(200),last_name varchar2(200),id number);
create table t2 as select * from dba_objects where rownum<=1000;
insert into t1 (first_name,last_name,id) select object_name,object_type,rownum from dba_objects where rownum<=1000;
commit;
create or replace function get_obj_name(p_id t2.object_id%type) return t2.object_name%type DETERMINISTIC is
v_name t2.object_name%type;
begin
select object_name
into v_name
from t2
where object_id=p_id;
return v_name;
end;
/
set linesize 1000
set autotrace traceonly
select *
from t1 where get_obj_name(id)='TEST' ;
执行计划
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2170 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 10 | 2170 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1057 recursive calls
0 db block gets
16007 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
create index idx_func_id on t1(get_obj_name(id));
执行计划
----------------------------------------------------------
Plan hash value: 4083325411
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 22190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 22190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FUNC_ID | 4 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
函数索引陷阱之函数变更与影响
--函数索引陷阱 --自定义函数使用函数索引要注意函数代码改变后的影响。 drop table t purge; create table t ( x number, y varchar2(30)); set autotrace off insert into t SELECT rownum, rownum||'a' FROM dual connect by rownum < 1000; create or replace package pkg_f is function f(p_value varchar2) return varchar2 deterministic; end; / create or replace package body pkg_f is function f(p_value varchar2) return varchar2 deterministic is begin return p_value; end; end; / create index idx_pkg_f_y on t ( pkg_f.f(y)); analyze table t compute statistics for table for all indexes for all indexed columns; set autotrace on explain SELECT * FROM t WHERE pkg_f.f(y)= '8a'; 将包的代码修改如下： create or replace package body pkg_f is function f(p_value varchar2) return varchar2 deterministic is begin return p_value||'b'; end; end; / 惊奇地发现查询出错误的值： SELECT * FROM t WHERE pkg_f.f(y)= '8a'; 在索引重建查询没有记录，这才是正确的结果： drop index idx_pkg_f_y; create index idx_pkg_f_y on t ( pkg_f.f(y)); SELECT * FROM t WHERE pkg_f.f(y)= '8a';
反向键索引陷阱之不能范围查询
/
总结：好处：
反转前 －－反转后
123 －－－321
124 －－－421
125 －－－521
消除了热块竞争
坏处：
范围查询根本无法使用!
*/
drop table t purge;
create table t as select * from dba_objects;
update t set CREATED=sysdate-rownum ;
create index idx_rev_objn on t(object_name) reverse ;
create index idx_rev_created on t(created) reverse ;
set autotrace traceonly
set linesize 1000
select * from t where created=sysdate-1;
执行计划
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 290 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 12 | 2484 | 290 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IDX_REV_CREATED | 336 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1184 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
select * from t where created>=sysdate-10 and created<=sysdate-1;
执行计划
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 153 | 31671 | 296 (2)| 00:00:04 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T | 153 | 31671 | 296 (2)| 00:00:04 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
1763 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
全文索引负面之缘何查不到记录
drop table test purge;
create table test as select * from dba_objects;
update test set object_name='高兴' where rownum<=2;
create index idx_object_name on test(object_name);
set autotrace traceonly explain
select * from test where object_name like '%高兴%';
exit;
sqlplus "/ as sysdba"
grant ctxapp to ljb;
alter user ctxsys account unlock;
alter user ctxsys identified by ctxsys;
connect ctxsys/ctxsys;
grant execute on ctx_ddl to ljb;
connect ljb/ljb
--第一次执行无需注释掉其中头两条
Begin
--ctx_ddl.drop_preference('club_lexer');
--ctx_ddl.drop_preference('mywordlist');
ctx_ddl.create_preference('club_lexer','CHINESE_LEXER');
ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',1);
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 5);
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
end;
/
create index id_cont_test on TEST (object_name) indextype is ctxsys.context
parameters (
'DATASTORE CTXSYS.DIRECT_DATASTORE FILTER
CTXSYS.NULL_FILTER LEXER club_lexer WORDLIST mywordlist');
exec ctx_ddl.sync_index('id_cont_TEST', '20M');
set autotrace off
set linesize 1000
select count(*) from test where contains(OBJECT_NAME,'高兴')>0;
COUNT(*)
--------
2
select count(*) from test where contains(OBJECT_NAME,'高')>0;
COUNT(*)
--------
2
select count(*) from test where contains(OBJECT_NAME,'兴')>0;
COUNT(*)
--------
0
全文索引负面之值谨防数据更新
sqlplus ljb/ljb
drop table test purge;
create table test as select * from dba_objects;
update test set object_id=rownum;
commit;
update test set object_name='高兴' where object_id<=2;
create index idx_object_name on test(object_name);
set autotrace traceonly explain
select * from test where object_name like '%高兴%';
exit;
sqlplus "/ as sysdba"
grant ctxapp to ljb;
alter user ctxsys account unlock;
alter user ctxsys identified by ctxsys;
connect ctxsys/ctxsys;
grant execute on ctx_ddl to ljb;
connect ljb/ljb
--第一次执行无需注释掉其中头两条
Begin
ctx_ddl.drop_preference('club_lexer');
ctx_ddl.drop_preference('mywordlist');
ctx_ddl.create_preference('club_lexer','CHINESE_LEXER');
ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',1);
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 5);
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
end;
/
create index id_cont_test on TEST (object_name) indextype is ctxsys.context
parameters (
'DATASTORE CTXSYS.DIRECT_DATASTORE FILTER
CTXSYS.NULL_FILTER LEXER club_lexer WORDLIST mywordlist');
exec ctx_ddl.sync_index('id_cont_TEST', '20M');
set autotrace on explain
set linesize 1000
select count(*) from test where contains(OBJECT_NAME,'高兴')>0;
COUNT(*)
----------
2
执行计划
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 4 (0)| 00:00:01|
| 1 | SORT AGGREGATE | | 1 | 78 | | |
|* 2 | DOMAIN INDEX | ID_CONT_TEST | 35 | 2730 | 4 (0)| 00:00:01|
--------------------------------------------------------------------------------
update test set object_name='高兴' where object_id>=3 and object_id<=5;
commit;
--发现由于又修改了3条记录，查询本应该由2条变更为5条记录，但是发现再查，依然是2条!
select count(*) from test where contains(OBJECT_NAME,'高兴')>0;
COUNT(*)
----------
2
---继续执行同步命令后
exec ctx_ddl.sync_index('id_cont_test', '20M');
---再次查询后，终于发现这下是5条没错了。
SQL> select count(*) from test where contains(OBJECT_NAME,'高兴')>0;
COUNT(*)
----------
5
--解决方案，定时任务，自动同步
create or replace procedure sync_id_cont_test as
begin
ctx_ddl.sync_index('id_cont_test', '20M');
end;
/
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'sync_id_cont_test;', SYSDATE, 'SYSDATE + (1/24/4)');
commit;
END;
/
索引的一些检查函数：
select t1.index_name,
t1.table_name,
t2.column_name,
t2.column_position,
t1.distinct_keys,
t1.num_rows,
t1.distinct_keys / t1.num_rows
from user_indexes t1, user_ind_columns t2
where t1.index_name = t2.INDEX_NAME
and t1.index_type = 'BITMAP'
and t1.distinct_keys / t1.num_rows >= 0.1;
---测试用例如下：
---测试位图索引重复度前准备工作
drop table t_bitmap purge;
set autotrace off
create table t_bitmap as select * from dba_objects;
insert into t_bitmap select * from t_bitmap;
insert into t_bitmap select * from t_bitmap;
insert into t_bitmap select * from t_bitmap;
insert into t_bitmap select * from t_bitmap;
update t_bitmap set object_id=rownum;
commit;
create bitmap index idx_tbit_object_id on t_bitmap(object_id);
create bitmap index idx_tbit_status on t_bitmap(status);
哪些SQL存在列运算：
select sql_text,
sql_id,
module,
t.service,
first_load_time,
last_load_time,
executions
from v$sql t
where (upper(sql_text) like '%TRUNC%'
or upper(sql_text) like '%TO_DATE%'
or upper(sql_text) like '%SUBSTR%')
and t.SERVICE not like 'SYS$%';
系统有哪些函数索引
select t1.table_name,
t1.index_name,
t2.COLUMN_NAME,
t2.COLUMN_POSITION,
t1.status,
t1.funcidx_status
from user_indexes t1, user_ind_columns t2
where t1.index_name = t2.INDEX_NAME
and t1.index_type = 'FUNCTION-BASED NORMAL';
系统有哪些全文索引
select t1.table_name,
t1.index_name,
t1.parameters,
t2.column_name,
t2.column_position,
t1.status,
t1.domidx_status
from user_indexes t1, user_ind_columns t2
where t1.index_name = t2.INDEX_NAME
and t1.index_type = 'DOMAIN';
系统有哪些位图索引
select t1.table_name,
t1.index_name,
t2.COLUMN_NAME,
t2.COLUMN_POSITION,
t1.status
from user_indexes t1, user_ind_columns t2
where t1.index_name = t2.INDEX_NAME
and t1.index_type = 'BITMAP';
系统有无反向键索引
select t1.table_name,
t1.index_name,
t2.COLUMN_NAME,
t2.COLUMN_POSITION,
t1.status
from user_indexes t1, user_ind_columns t2
where t1.index_name = t2.INDEX_NAME
and t1.index_type = 'NORMAL/REV';
