怎么有效的查找null值？

最新推荐文章于 2024-04-25 15:53:03 发布

转载最新推荐文章于 2024-04-25 15:53:03 发布 · 473 阅读

文章标签：

#find #null #value #查找 #空值

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文介绍两种有效的方法来搜索大型表中的NULL值：使用合适的位图索引和使用带有NVL函数的函数基索引。这两种方法可以显著提高搜索效率并减少资源消耗。

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