[20131013]索引部分数据.txt
在实际的生产系统中，比如一个标识状态的字段，'0'的行很少，'1'以及其他值的行很多，一个通过特殊的函数索引，
利用oracle索引不保存NULL的特性（注意如果索引2个字段，2个必须为NULL，索引才不会保存），索引仅仅为'0'，
减少索引的大小，实际上网上有许多的例子，正好别人问到，顺手写一个小例子:
1.建立测试例子:
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t as select rownum id,lpad('x',80,'x') pad, '1' flag from dual connect by level
insert into t values (9998,lpad('y',80,'y'),'0');
insert into t values (9999,lpad('y',80,'y'),'0');
insert into t values (1000,lpad('y',80,'y'),'0');
commit ;
2.建立函数索引:
create index if_t_flag on t (decode(flag,'0',flag));
validate index if_t_flag;
cat i.sql
set linesize 200;
set linesize 200;
column name format a10
/* select height, blocks, lf_blks, lf_rows_len, lf_blk_len, br_blks, br_rows, br_rows_len, br_blk_len, btree_space, used_space, pct_used from index_stats; */
select HEIGHT, BLOCKS, NAME, LF_ROWS, LF_BLKS, LF_ROWS_LEN, LF_BLK_LEN, BR_ROWS, BR_BLKS, BR_ROWS_LEN, BR_BLK_LEN,
DEL_LF_ROWS, DEL_LF_ROWS_LEN, DISTINCT_KEYS from index_stats;
select MOST_REPEATED_KEY, BTREE_SPACE, USED_SPACE, PCT_USED, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS, PRE_ROWS, PRE_ROWS_LEN,
OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;
--查询index_stats内容太多，分2次查询
SCOTT@test01p> @i
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
1 8 IF_T_FLAG 3 1 39 8000 0 0 0 0 0 0 1
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
3 8000 39 1 3 3 0 0 0 0
--可以发现LF_ROWS＝3，也就是仅仅索引3个值。
3.测试：
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T', method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SCOTT@test01p> select * from t where flag='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2puf1jju69u3q, child number 0
-------------------------------------
select * from t where flag='0'
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
|* 1 | TABLE ACCESS FULL| T | 5000 | 53 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"='0')
--可以发现并没有使用我们建立的函数索引。当然这些写sql没有问题。
SCOTT@test01p> select * from t where decode(flag,'0',flag)='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fh0kpz3trj8m7, child number 0
-------------------------------------
select * from t where decode(flag,'0',flag)='0'
Plan hash value: 3731093196
-------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 3 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IF_T_FLAG | 3 | 1 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='0')
4.看看加入hint如何?
SCOTT@test01p> select /*+ index(t if_t_flag) */ * from t where flag='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gzdxqc9dgz0vv, child number 0
-------------------------------------
select /*+ index(t if_t_flag) */ * from t where flag='0'
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
|* 1 | TABLE ACCESS FULL| T | 5000 | 53 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"='0')
5.在谓词是flag='0'，不会使用函数索引。在测试使用case函数。
create index if1_t_flag on t( case flag when '0' then flag end);
SCOTT@test01p> validate index if1_t_flag ;
Index analyzed.
SCOTT@test01p> @i
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
1 8 IF1_T_FLAG 3 1 39 8000 0 0 0 0 0 0 1
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
3 8000 39 1 3 3 0 0 0 0
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T', method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SCOTT@test01p> select * from t where flag='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2puf1jju69u3q, child number 0
-------------------------------------
select * from t where flag='0'
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
|* 1 | TABLE ACCESS FULL| T | 5000 | 53 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"='0')
--依旧没使用函数索引。
6.加提示看看:
SCOTT@test01p> select /*+ index(t if1_t_flag) */ count(*) from t where flag='0';
COUNT(*)
----------
3
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6hu6t6w2f8gdg, child number 0
-------------------------------------
select /*+ index(t if1_t_flag) */ count(*) from t where flag='0'
Plan hash value: 2966233522
---------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| T | 5000 | 53 (0)|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"='0')
--依旧不行。
7.加入约束后：
SCOTT@test01p> alter table t modify flag not null;
Table altered.
--结果也一样，不再另行测试。
也就是讲要使用这种方式，需要开发与DBA的配合来完成。
8.在11G后，可以建立分区索引解决这个问题：
create index IF2_T_FLAG on T(flag) global partition by range (flag) (
partition p_flag0 values less than ('1'),
partition p_OTHER values less than (MAXVALUE)
) unusable;
--注意我使用unusable参数，没有空间的使用。
SCOTT@test01p> select table_name ,index_name,segment_created from user_indexes where table_name='T';
TABLE_NAME INDEX_NAME SEG
---------- -------------------- ---
T IF2_T_FLAG N/A
T IF1_T_FLAG YES
T IF_T_FLAG YES
SCOTT@test01p> alter index if2_t_flag rebuild partition p_flag0;
Index altered.
SCOTT@test01p> select segment_name ,PARTITION_NAME,segment_type ,bytes from user_segments where segment_name='IF2_T_FLAG' ;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES
-------------------- -------------------- ------------------ ----------
IF2_T_FLAG P_FLAG0 INDEX PARTITION 65536
SCOTT@test01p> select * from t where flag='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gjpvzujvambrj, child number 0
-------------------------------------
select * from t where flag='0'
Plan hash value: 2463861812
---------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | PARTITION RANGE SINGLE | | 3 | 1 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 3 | 1 (0)|
|* 3 | INDEX RANGE SCAN | IF2_T_FLAG | 3 | 1 (0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='0')
9.12c下方法更多，看http://richardfoote.wordpress.com的blog有许多介绍。
这里不再重复了。