关于查询空值列的优化案例

SQL> desc all_objects
 名称                                                                                是否为空? 类型
 ----------------------------------------------------------------------------------- -------- ------------------------
 OWNER                                                                               NOT NULL VARCHAR2(30)
 OBJECT_NAME                                                                         NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                                                               VARCHAR2(30)
 OBJECT_ID                                                                           NOT NULL NUMBER
 DATA_OBJECT_ID                                                                               NUMBER
 OBJECT_TYPE                                                                                  VARCHAR2(19)
 CREATED                                                                             NOT NULL DATE
 LAST_DDL_TIME                                                                       NOT NULL DATE
 TIMESTAMP                                                                                    VARCHAR2(19)
 STATUS                                                                                       VARCHAR2(7)
 TEMPORARY                                                                                    VARCHAR2(1)
 GENERATED                                                                                    VARCHAR2(1)
 SECONDARY                                                                                    VARCHAR2(1)
SQL> create table tb_test as select * from all_objects;

表已创建。

SQL> select count(*)from tb_test where object_id<1000;

  COUNT(*)
----------
       117
SQL> alter table tb_test modify(object_id null);

表已更改。

SQL> desc tb_test
 名称                                                                                是否为空? 类型
 ----------------------------------------------------------------------------------- -------- --------------------------
 OWNER                                                                               NOT NULL VARCHAR2(30)
 OBJECT_NAME                                                                         NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                                                               VARCHAR2(30)
 OBJECT_ID                                                                                    NUMBER
 DATA_OBJECT_ID                                                                               NUMBER
 OBJECT_TYPE                                                                                  VARCHAR2(19)
 CREATED                                                                             NOT NULL DATE
 LAST_DDL_TIME                                                                       NOT NULL DATE
 TIMESTAMP                                                                                    VARCHAR2(19)
 STATUS                                                                                       VARCHAR2(7)
 TEMPORARY                                                                                    VARCHAR2(1)
 GENERATED                                                                                    VARCHAR2(1)
 SECONDARY                                                                                    VARCHAR2(1)

SQL> update tb_test set object_id=null where object_id<1000;

已更新117行。

提交完成。

SQL> select count(*)from tb_test where object_id<1000;

  COUNT(*)
----------
         0

SQL> explain plan for
  2  select count(*) from tb_test
  3  where object_id>=1000 or object_id is null;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 3721940647

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     5 |    19   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| TB_TEST |  6171 | 30855 |    19   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID">=1000 OR "OBJECT_ID" IS NULL)

已选择14行。

SQL> create index tb_test_idx01 on tb_test(decode(object_id,null,-1,object_id));

索引已创建。

SQL> select count(*) from tb_test
  2  where decode(object_id,null,-1,object_id)>=1000 or decode(object_id,null,-1,object_id)=-1;

  COUNT(*)
----------
      6171

SQL> explain plan for
  2  select count(*) from tb_test
  3  where decode(object_id,null,-1,object_id)>=1000 or decode(object_id,null,-1,object_id)=-1;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Plan hash value: 1122137230

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |     5 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| TB_TEST_IDX01 |  6052 | 30260 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - filter(DECODE(TO_CHAR("OBJECT_ID"),NULL,(-1),"OBJECT_ID")>=1000 OR
              DECODE(TO_CHAR("OBJECT_ID"),NULL,(-1),"OBJECT_ID")=(-1))

已选择15行。

通过创建函数索引使得oracle,在查询时能够使用上索引,从而加快查询速度.

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

转载于:http://blog.itpub.net/11134237/viewspace-682744/