【Oracle】含常数的复合索引

原来对于索引的认识只知道索引可以基于一个或者多个列,B-Tree索引不包含null,但有些情况下我们又需要通过where 列名 is null来查找一些数据,这时候数据库由于没办法使用索引就会使用全表扫描,导致执行效率低下,这时候我们可以通过使用含常数的复合索引来解决这个问题。

下面开始进行实验:

首先建立测试表

SYS@ORCL>create table test_objects nologging as select rownum id,a.* from dba_objects a where 1=2;

 

Table created.

插入500万条数据:

SYS@ORCL>declare

  l_cnt number;

  l_rows number:=&1;

begin

  insert /*+ append */ into test_objects select rownum,a.* from dba_objects a;

  l_cnt:=sql%rowcount;

  commit;

  while(l_cnt<l_rows)

  loop

    insert /*+ append */ into test_objects select rownum+l_cnt,

           owner,object_name,subobject_name,

           object_id,data_object_id,

           object_type,created,last_ddl_time,

           timestamp,status,temporary,

           generated,secondary

           from test_objects

           where rownum<=l_rows-l_cnt;

           l_cnt:=l_cnt+sql%rowcount;

     commit;

   end loop;

 end;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21  

 22  /

Enter value for 1: 5000000

old   3:   l_rows number:=&1;

new   3:   l_rows number:=5000000;

 

PL/SQL procedure successfully completed.

object_id列上建立一般的B-Tree索引:

SYS@ORCL>create index idx_oid_test_objects on test_objects(object_id);

 

Index created.

收集表信息:

SYS@ORCL>exec dbms_stats.gather_table_stats(‘SYS‘,‘TEST_OBJECTS‘);     

 

PL/SQL procedure successfully completed.

测试一下索引能否正常使用:

SYS@ORCL>select count(*) from test_objects where object_id=52457;

 

  COUNT(*)

----------

        99

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3877533889

 

--------------------------------------------------------------------------------

----------

 

| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)|

Time     |

 

--------------------------------------------------------------------------------

----------

 

|   0 | SELECT STATEMENT  |                      |     1 |     5 |     3   (0)|

00:00:01 |

 

|   1 |  SORT AGGREGATE   |                      |     1 |     5 |            |

         |

 

|*  2 |   INDEX RANGE SCANIDX_OID_TEST_OBJECTS |    99 |   495 |     3   (0)|

00:00:01 |

 

--------------------------------------------------------------------------------

----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=52457)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        411  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

可以看到索引可以正常使用,下面我们来进入正题,当where条件中为object_id is null的时候会如何:

SYS@ORCL>select count(*) from test_objects where object_id is null;

 

  COUNT(*)

----------

         0

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3799704240

 

--------------------------------------------------------------------------------

---

 

| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time

  |

 

--------------------------------------------------------------------------------

---

 

|   0 | SELECT STATEMENT   |              |     1 |     5 | 16612   (2)| 00:03:2

0 |

 

|   1 |  SORT AGGREGATE    |              |     1 |     5 |            |

  |

 

|*  2 |   TABLE ACCESS FULL| TEST_OBJECTS |     1 |     5 | 16612   (2)| 00:03:2

0 |

 

--------------------------------------------------------------------------------

---

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("OBJECT_ID" IS NULL)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

      74808  consistent gets

      74730  physical reads

        904  redo size

        410  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

因为B-Tree索引不会记录null所以只能使用全表扫描。产生了大量的consistent gets下面我们来建立含常数的复合索引,并查询执行:

SYS@ORCL>create index idx_oid2_test_objects on test_objects(object_id,1);

 

Index created.

 

SYS@ORCL>select count(*) from test_objects where object_id is null;

 

  COUNT(*)

----------

         0

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1238205220

 

--------------------------------------------------------------------------------

-----------

 

| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)|

 Time     |

 

--------------------------------------------------------------------------------

-----------

 

|   0 | SELECT STATEMENT  |                       |     1 |     5 |     2   (0)|

 00:00:01 |

 

|   1 |  SORT AGGREGATE   |                       |     1 |     5 |            |

          |

 

|*  2 |   INDEX RANGE SCAN| IDX_OID2_TEST_OBJECTS |     1 |     5 |     2   (0)|

 00:00:01 |

 

--------------------------------------------------------------------------------

-----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID" IS NULL)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          3  consistent gets

          2  physical reads

          0  redo size

        410  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

可以看到此时拥有含场数的复合索引之后就能够使用该索引查找null,极大的提升了执行效率。consistent gets74808降到了3.效果非常明显。

【Oracle】含常数的复合索引,布布扣,bubuko.com

【Oracle】含常数的复合索引

上一篇:sharepoint2007就地升级2010系列(四)升级数据库


下一篇:MVCC详解