深入理解Oracle索引(22)：索引和NULL 协同合作互惠共赢

     ㈠ 合作的基础

⑴ /* 全为 NULL 的列是无法存储到B*Tree里面的 */

hr@ORCL> create table t (x number,y number);
hr@ORCL> create index idx_t on t(x,y);
hr@ORCL> insert into t values(1,1);
hr@ORCL> insert into t values(1,null);
hr@ORCL> insert into t values(null,1);
hr@ORCL> insert into t values(null,null);
hr@ORCL> commit;
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T');
hr@ORCL> exec dbms_stats.gather_index_stats(ownname=>USER,indname=>'IDX_T');

/*插入了4行、而在索引中仅存储了3 行*/
hr@ORCL> select index_name,num_rows from user_indexes where index_name='IDX_T';

INDEX_NAME                       NUM_ROWS
------------------------------ ----------
IDX_T                                   3




⑵ /*只有当索引键中至少有一个列定义为NOT NULL 时查询才会使用索引 */

--走索引

hr@ORCL> drop table t purge;
hr@ORCL> create table t (x number,y number not null);
hr@ORCL> create unique index idx_t on t(x,y);
hr@ORCL> insert into t values(1,1);
hr@ORCL> insert into t values(NULL,1);
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T');
hr@ORCL> exec dbms_stats.gather_index_stats(ownname=>USER,indname=>'IDX_T');
hr@ORCL> set autot trace exp
hr@ORCL> select * from t where x IS NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 2296882198

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     5 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("X" IS NULL)


--不走索引

hr@ORCL> drop table t purge;
hr@ORCL> create table t (x number,y number);
hr@ORCL> create unique index idx_t on t(x,y);   
hr@ORCL> insert into t values(1,1);
hr@ORCL> insert into t values(1,null);
hr@ORCL> insert into t values(null,1);
hr@ORCL> insert into t values(null,null);
hr@ORCL> commit;
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T');
hr@ORCL> exec dbms_stats.gather_index_stats(ownname=>USER,indname=>'IDX_T');
hr@ORCL> set autot trace exp
hr@ORCL> select * from t where x IS NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |     8 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("X" IS NULL)

     ㈡ 互惠共赢
        
        
        ① 场景
           
           某表、某倾斜列、且只有 2 个可取值
           
        ② 需求
           
           ● 索引访问少数行
           ● 全表扫描访问多数行
           ● 节省索引维护成本、降低索引存储开销
           
        ③ 方案
           
           有 2：
           ● 对多数行使用 NULL、而对少数行使用你希望的任何值
           ● 使用函数索引、只索引函数的非NULL返回值、这是函数非常好的一个运用
           
        ④ 例子
           
           某张工单表t、status列只有处理(1)和未处理(0)两种状态、其中、未处理占：10%、已处理占90%
           经常查询未处理的、下面演示这个例子

hr@ORCL> drop table t purge;
hr@ORCL> create table t (x number,y number);
hr@ORCL> create index idx_t on t (decode(x,0,0,NULL));    
hr@ORCL> insert into t values(0,1);
hr@ORCL> ed
Wrote file afiedt.buf

  1  begin
  2    for i in 1..1000
  3   loop
  4       insert into t values(1,i);
  5   end loop;
  6   commit;
  7* end;
  8  /

PL/SQL procedure successfully completed.

hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T');
hr@ORCL> exec dbms_stats.gather_index_stats(ownname=>USER,indname=>'IDX_T');
/*索引只存储了 1 行、节省了索引存储空间 */
hr@ORCL> select index_name,num_rows from user_indexes where index_name='IDX_T';

INDEX_NAME                       NUM_ROWS
------------------------------ ----------
IDX_T                                   1

hr@ORCL> set autot trace exp
/* CBO选择走索引*/
hr@ORCL> select * from t where decode(x,0,0,NULL)=0;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(DECODE("X",0,0,NULL)=0)

By David Lin

2013-06-10

Good Luck

端午节快乐