How to optimize isnull in oraclesql (index creation and histogram)

Applicable scenarios

Imagine the following SQL

Select *

FromBD_INVMANDOC_bak t

Where t. negallowed is null

Or t. negallowed = 'n'

If the negallowed column has only two values: Y and N, and NULL exists

Therefore, when we create an index in the negallowed column, this statement still cannot take the index because the index does not store null values.

In the BD_INVMANDOC_bak table, 1.06 million rows are N, 50 thousand rows are Y, and the rest are null.

How to index

You need to create a composite index that contains virtual columns, so that the index can contain the null value of the negallowed column.

Create an index as follows:

CREATEINDEX ID_BD_INVMANDOC_bak_negallowed ON BD_INVMANDOC_bak (negallowed, 1 );

The query execution plan is changed to index:

SQL> set autotrace trace SQL> SQL> SQL> select --/* + INDEX (T ID_BD_INVMANDOC_bak_negallowed )*/ 2 * 3 from BD_INVMANDOC_bak t where 4 t. negallowed is null or 5 t. negallowed = 'n' 6; 61399 rows selected. Execution Plan ---------------------------------------------------------- -------------------------------------------------------------------------------- --------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------- --------------- | 0 | select statement | 58559 | 9492K | 1774 | | 1 | CONCATENATION | | | 2 | table access by index rowid | BD_INVMANDOC_BAK | 49153 | 7968ks | 1475 | | 3 | index range scan | ID_BD_INVMANDOC_BAK_NEGALLOWED | 49153 | | 1, 113 | | 4 | table access by index rowid | BD_INVMANDOC_BAK | 9406 | 1524K | 299 | | 5 | index range scan | ID_BD_INVMANDOC_BAK_NEGALLOWED | 9867 | | 25 | -------------------------------------------------------------------------------- ---------------

Analysis:

When the index contains a null query column, the statement can be indexed smoothly, and the statement execution cost is also significantly reduced.

However, through further tests, we found that indexes are used when negallowed = 'n' or negallowed = 'y'. The execution plan is not optimal when negallowed = 'y, therefore, histogram information is required to make the execution plan more accurate.

Influence of histogram

The negallowed column of this table only contains Y and N, and null exists. Does the histogram affect this column?

Use the following statement to collect data without a histogram:

Execdbms_stats.gather_table_stats (user, 'bd _ INVMANDOC_BAK ', method_opt =>' for allindexed columns size 1 ');

Execution Plan:

Execution Plan

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

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

| Id | Operation | Name | Rows | Bytes | Cost |

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

| 0 | select statement | 530K | 84M | 7750 |

| 1 | TABLE accesskey FULL | BD_INVMANDOC_BAK | 530K | 84M | 7750 |

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

The execution plan is a full table scan.

Statistical Histogram

Execdbms_stats.gather_table_stats (user, 'bd _ INVMANDOC_BAK ', method_opt =>' for allindexed columns size auto ');

Execution Plan:

Execution Plan

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

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

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

| Id | Operation | Name | Rows |

Bytes | Cost |

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

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

| 0 | select statement | 64950 |

10 M | 1969 |

| 1 | CONCATENATION |

|

| 2 | table access by index rowid | BD_INVMANDOC_BAK | 55605 |

9014kb | 1670 |

| 3 | index range scan | ID_BD_INVMANDOC_BAK_NEGALLOWED | 55605 |

| 1, 128 |

| 4 | table access by index rowid | BD_INVMANDOC_BAK | 9345 |

1514kb | 299 |

| 5 | index range scan | ID_BD_INVMANDOC_BAK_NEGALLOWED | 9867 |

| 25 |

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

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

-- Index the execution plan

Summary

When a query involves is null, we need to create a suitable composite index for this column. If there IS no good candidate column, we can add a virtual column to create a composite index, to index the statement execution plan.

When the value of this column has only a few unique values, and the number of rows involved in each value is heavily skewed, we recommend that you collect a histogram for the purpose of correctly indexing.