Indexing Nulls by including constants in the index? Careful now

This is just a quick demo to show how sometimes Oracle can get a bit too keen on replacing expressions with virtual columns that are declared. The problem you might have is one that was seen on the OTN recently https://community.oracle.com/message/14281753#14281753; the basic scenario is you have a nullable column that you wish to index  the NULL values for. The problem with indexing nulls is that there’ll be no entry in an index if ALL columns of the index are NULL so a typical workaround is to introduce another column to the index that won’t be NULL, an easy way of doing this is to use a fixed value. Of course, you would normally choose a small value so that your index wasn’t too large – in the past I’ve used ‘x’. The OP of this thread chose -999999 which is rather large (byte-wise) but shouldn’t strike you as being particular bad.

Here’s the problem, if you then use this value as a filter on the table, Oracle can (and will) do some transformation to replace your value with the hidden virtual column (which it created to support the function based index).

A demo using 12.2.0.1:

ANDY@pdb1>create table careful_of_workarounds
  2  as
  3  select * from all_objects;

Table created.

ANDY@pdb1>alter table careful_of_workarounds modify object_id null;

Table altered.

ANDY@pdb1>create index careful_of_workarounds_idx on careful_of_workarounds (object_id);

Index created.

ANDY@pdb1>select min(object_id), max(object_id) from CAREFUL_OF_WORKAROUNDS;

MIN(OBJECT_ID) MAX(OBJECT_ID)                                                                                                                                                                           
-------------- --------------                                                                                                                                                                           
             2          73632                                                                                                                                                                          

ANDY@pdb1>explain plan for select * from careful_of_workarounds where object_id = 987654321;

Explained.
ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1786522280                                                                                                                                                                             
                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------                                                                                      
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                      
------------------------------------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT                    |                            |     1 |   135 |     2   (0)| 00:00:01 |                                                                                      
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |     2   (0)| 00:00:01 |                                                                                      
|*  2 |   INDEX RANGE SCAN                  | CAREFUL_OF_WORKAROUNDS_IDX |     1 |       |     1   (0)| 00:00:01 |                                                                                      
------------------------------------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - access("OBJECT_ID"=987654321)                                                                                                                                                                   

14 rows selected.

ANDY@pdb1>create index careful_of_workarounds_eg on careful_of_workarounds (object_id,987654321);

Index created.

ANDY@pdb1>explain plan for select * from careful_of_workarounds where object_id = 987654321;

Explained.
ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1786522280                                                                                                                                                                             
                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------                                                                                      
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                      
------------------------------------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT                    |                            |     1 |   135 |     2   (0)| 00:00:01 |                                                                                      
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |     2   (0)| 00:00:01 |                                                                                      
|*  2 |   INDEX RANGE SCAN                  | CAREFUL_OF_WORKAROUNDS_IDX |     1 |       |     1   (0)| 00:00:01 |                                                                                      
------------------------------------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - access("OBJECT_ID"=987654321)                                                                                                                                                                   

14 rows selected.

ANDY@pdb1>create index careful_of_workarounds_eg2 on careful_of_workarounds (object_id,-987654321);

Index created.

ANDY@pdb1>explain plan for select * from careful_of_workarounds where object_id = -987654321;

Explained.
ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2863211044                                                                                                                                                                             
                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------                                                                                                            
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                            
--------------------------------------------------------------------------------------------                                                                                                            
|   0 | SELECT STATEMENT  |                        |     1 |   135 |   375   (1)| 00:00:01 |                                                                                                            
|*  1 |  TABLE ACCESS FULL| CAREFUL_OF_WORKAROUNDS |     1 |   135 |   375   (1)| 00:00:01 |                                                                                                            
--------------------------------------------------------------------------------------------                                                                                                            
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   1 - filter("OBJECT_ID"=(-987654321))                                                                                                                                                                

13 rows selected.
ANDY@pdb1>explain plan for select /*+index(careful_of_workarounds CAREFUL_OF_WORKAROUNDS_IDX)*/* from careful_of_workarounds where object_id = -987654321;

Explained.

ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2312772364

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |     1 |   135 |   154   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |   154   (1)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                   | CAREFUL_OF_WORKAROUNDS_IDX |     1 |       |   153   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"=(-987654321))
See how we are now forced to use our predicate as a filter rather than an access predicate on the index – causing us to have to read the ENTIRITY of the index. This seems to only be the case with the negative valued expression (using -1 did exactly the same), but not for positive. A brief check against dba_tab_cols might shed some hints:
ANDY@pdb1>select column_name, data_default, column_id from dba_tab_cols where table_name ='CAREFUL_OF_WORKAROUNDS' and data_default is not null
  2  /

COLUMN_NAME                    DATA_DEFAULT                                        COLUMN_ID
------------------------------ -------------------------------------------------- ----------
SYS_NC00027$                   987654321
SYS_NC00028$                   (-987654321)
SYS_NC00029$                   (-1)
The positive valued virtual column has no brackets (or parenthesise) surrounding it but the negatively valued ones do – perhaps this is what would class the negatives as a replaceable expression rather than a fixed value.
Okay, that might not be so bad because it’s just one column right? Well…
ANDY@pdb1>create index care_of_workarounds_really on careful_of_workarounds (created_vsnid);

Index created.

ANDY@pdb1>explain plan for select /*+index(careful_of_workarounds care_of_workarounds_really)*/* from careful_of_workarounds where created_vsnid = -987654321;

Explained.

ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2532905546

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |     1 |   135 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |     0   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                   | CARE_OF_WORKAROUNDS_REALLY |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CREATED_VSNID"=(-987654321))
Oh dear!
Are you safe if you use binds though?
ANDY@pdb1>var object_id number=-987654321
ANDY@pdb1>select /*+index(careful_of_workarounds careful_of_workarounds_idx)*/* from careful_of_workarounds where object_id = :object_id;

no rows selected

ANDY@pdb1>select * from table(dbms_xplan.display_cursor(format=>'typical'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9cjjrakjnam15, child number 0
-------------------------------------
select /*+index(careful_of_workarounds careful_of_workarounds_idx)*/*
from careful_of_workarounds where object_id = :object_id

Plan hash value: 1786522280

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CAREFUL_OF_WORKAROUNDS_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=:OBJECT_ID)
Yes! You may have guessed this but it’s always better to verify. Also, I should clarify that using ‘x’ doesn’t fall into the same problem
Advertisements