ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131013]索引部分数据.txt

[20131013]索引部分数据.txt

原创 Linux操作系统 作者:lfree 时间:2013-10-14 08:48:46 0 删除 编辑
[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 <=1e4 - 3;

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有许多介绍。
这里不再重复了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-774304/,如需转载,请注明出处,否则将追究法律责任。

上一篇: [20131013]ps_mem.txt
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2455
  • 访问量
    6257657