转自: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)