怎么有效的查找null值？

转自：To search for NULL values efficiently (文档 ID 255449.1)

***
This article is being delivered in Draft form and may contain
errors.  Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
***

PURPOSE
-------

To search for NULL values efficiently

 
SCOPE & APPLICATION
-------------------

All dba's.
 
To search for NULL values efficiently
-------------------------------------

Searching for NULL values in large tables using full table scans can be a very 
slow and resource intensive operation. There is potential to use 
more efficient methods:

1.  Using a suitable bitmap index
2.  Using a function-based index with NVL function


Using a bitmap index
--------------------

Bitmap indexes store NULL values, whereas all other index types do not. 
This property of bitmap indexes can be used to efficiently search for 
NULL values in a column.

Example:

SQL> select count(*) from test_null where c1 is null;

  COUNT(*)
----------
      2048

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST_NULL'


SQL> create bitmap index I_test_null on test_null(c1);

Index created.

SQL> select count(*) from test_null where c1 is null;

  COUNT(*)
----------
      2048


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT)
   3    2       BITMAP INDEX (SINGLE VALUE) OF 'I_TEST_NULL'


Using a function-based index:
-----------------------------

To use a function-based index to search for NULL values in a column, 
rewrite the query using NVL function as follows:

    select count(*) from test_null where nvl(c1,-99999) = '-99999';

Then create a function-based index on function nvl(c1,-99999):

    create index i_test_null on test_null(nvl(c1,-99999));

Ensure that query_rewrite_enabled = TRUE to make use of
function-based index.

Example:

SQL> select count(*) from test_null where nvl(c1,-99999) = '-99999';

  COUNT(*)
----------
     32768


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'I_TEST_NULL' (NON-UNIQUE) (Cost=1
          83 Card=49152 Bytes=147456)