Perform an analysis optimization process for a 3-hour long sql: Meeting is NULL from the index to best practices

At the end of the month peak, to a typical project random analysis, found a super slow, full table scan of SQL, the statement is very simple, awr impressively in the column, in my statistical deadline is not over ...

Use V$active_session_history to further confirm that the SQL was executed for nearly 3 hours!

Get the full information of SQL, find the statement is not complex, but see is NULL seems to understand the problem, index invalidation, full table scan ...

Although the table has been created with an index of period and year two columns, but the selectivity is too low, the optimizer decides to use Table Access full, even if the Expensetype column is added after the index, and is invalidated by the IS null condition used by the column. Seems to be trapped in a desperate situation, there is no turnaround?

Consider carefully that this statement really updates the amount of data actually very little, that is, most of the data rows of the Expensetype column should be a value ... , immediately confirm, sure enough the data of the table 120 million, from the statistical information to see the uniqueness of Expensetype, although not high, but the amount of empty data can be negligible. If the properties of the year or the period column are non-null, they can be indexed by using their combined index (or by adjusting the order of existing indexed columns)!!!

confirmed that period and year are defined as non-empty columns, hehe, God help me also ...

Decisively create index try, after nearly 20 minutes of waiting, haha, success!!!

Create INDEX Idx_rofyjtpz2017_expensetype on ROFYJTPZ2017 (Expensetype, PERIOD) nologging online parallel 8;
Alter index Idx_rofyjtpz2017_expensetype Noparallel;

Summarize:

1. The B-Tree index itself does not store records where the key value is all null, so the operation of IS null cannot use a single-column index, but a composite index (such as this scenario, because subsequent columns are non-empty, have values) may store a portion of the null value, which makes the is null also available for indexing. It depends on the least cost, not the other.

2. Of course, the best advice for this issue is to set the Expensetype column to a design value of non-null, setting the initial value such as "NULL" and "none".

Perform an analysis optimization process for a 3-hour long sql: Meeting is NULL from the index to best practices