Let me tell you a story about a developer, let’s call her Sue, that had proved that Oracle was having mood swings and just didn’t use a perfectly good index even though it was the perfect index to use.

This of course made Sue very upset and she spared nobody from her bitter words about how unreliable and temperamental the database was. It just wasn’t willing to use the index because it was stubborn.

Poor Susan was the DBA having to try to defend against the onslaught. She knew there could be many reasons a SQL did not use an index, from data type mismatches to plan stability to… so many things.

As it was a very critical to the business to have this SQL be fast Susan offered to look into the test case. Send it over and let me see if what it can be. It may even be that Oracle does not think the index will improve the performance. Sue quipped “How would one know, not even with a hint will the database use it.”.

Soon enough Susan got a “You’ve got mail” ping from he inbox. She opened up the test case and to her delight it was complete with table creation and data, Sue at least knows how to set up a proper test case she thought.

drop table index_test_runs;

create table index_test_runs
  ( test_id     number
  , char_string varchar(2)
  , category_id number);

alter table index_test_runs add constraint index_test_pk primary key (test_id);

create index index_test_ix on index_test_runs (category_id);

insert into index_test_runs values( 1, 'aa', 1);
insert into index_test_runs values( 2, 'bb', 2);
insert into index_test_runs values( 3, 'cc', 3);

commit;

select category_id
  from index_test_runs a
 where category_id is null 
    or category_id in (2,3);

Susan looked it over and thought, good it is set up to be executed time after time. So we have a table and and indexed column, three rows and a select using that column.

However that not null condition is looking mighty suspicious. I wonder what the execution plan says.

select plan_table_output 
  from table(dbms_xplan.display_cursor( dbms_sql_translator.sql_id
(q'#select category_id
  from index_test_runs a
 where category_id is null 
    or category_id in (2,3)#')
                                      , null
                                      , 'basic'));
-----------------------------------------------------
| Id  | Operation                 | Name            |
-----------------------------------------------------
|   0 | SELECT STATEMENT          |                 |
|   1 |  TABLE ACCESS STORAGE FULL| INDEX_TEST_RUNS |
-----------------------------------------------------

Ah, not too surprising the index cannot be used when we want to also get rows that has nulls in that column. Now, how to work around that issue

STORAGE FULL reveals that Susan had her test environment on an EXADATA. Not everyone has a personal database running on EXADATA, but Susan wouldn’t have it any other way. Life had been so much nicer since they moved to the Oracle’s cloud and everyone can use autonomous databases for their testing.

There is no null data in the code Susan thought to herself, what happens if I flip it to make sure there are no nulls and that the category is 2 or 3?

select category_id
  from index_test_runs a
 where category_id is not null 
   and category_id in (2,3);

select plan_table_output 
  from table(dbms_xplan.display_cursor( dbms_sql_translator.sql_id
(q'#select category_id
  from index_test_runs a
 where category_id is not null 
   and category_id in (2,3)#')
                                      , null
                                      , 'basic'));
-------------------------------------------
| Id  | Operation         | Name          |
-------------------------------------------
|   0 | SELECT STATEMENT  |               |
|   1 |  INLIST ITERATOR  |               |
|   2 |   INDEX RANGE SCAN| INDEX_TEST_IX |
-------------------------------------------

To Susans delight it worked. Now the index is used. Hope this is the solution. Time to call Sue.

Susan: – Hello, I think I know the problem. It is this check if category is null.
Susan: – My understanding is that that should not happen in this solution.
Sue: – True, but we want to be safe in case someone has inserted it that way anyway.
Susan: – Right so we can validate that when the insert is made.
Sue: – We could but sometimes it is not done via our application.
Susan: – Right, so we validate this in the database.
Sue: – You know we don’t want any logic in the database. We validate it in the application.
Susan: – Well, then validate on insert and change this SQL to not allow nulls.
Sue: – Oh we cannot change the SQL. It will force us into a whole test cycle. You need to fix it.
Susan: – Right, let me test one thing without changing the code, but it will require validating in the DB.
Susan: – You can still check in the application, but if bad data reaches the DB, we will reject it.
Sue: – Alright, I guess we can let you do that.

alter table index_test_runs modify category_id not null;

select category_id
  from index_test_runs a
 where category_id is null 
    or category_id in (2,3);
    
select plan_table_output 
  from table(dbms_xplan.display_cursor( dbms_sql_translator.sql_id
(q'#select category_id
  from index_test_runs a
 where category_id is null 
    or category_id in (2,3)#')
                                      , null
                                      , 'basic'));
-------------------------------------------
| Id  | Operation         | Name          |
-------------------------------------------
|   0 | SELECT STATEMENT  |               |
|   1 |  INLIST ITERATOR  |               |
|   2 |   INDEX RANGE SCAN| INDEX_TEST_IX |
-------------------------------------------

Great so with a constraint forcing data to never have nulls it works with the original SQL even though it allows for nulls. Mission accomplished

Susan: – Hi Sue. Yes, it does work with a constraint in the database. No change to the code.
Sue: – Great. Stupid database.
Susan: – Maybe not the database.
Sue: – WHAT?
Susan: – Oh, nothing.
Susan: – Sue, you know what we could do in the future?
Sue: – Not happening. I know you’re going back to your old tired soapbox.
Susan: – But hear me out. These things and other would not occur if we did data modelling.
Sue: – We developers understand the need of the application and the modelling of it best.
Sue: – You just make the database behave.
Susan: – Sure, you’re right. There is no need to understand the database to build great apps.
Susan: – Have a good day, talk to you later.

And thus the story repeats the next day, and the day after, and…

The moral of the story is not that developers and database experts do not see the world the same way. they don’t but that is not the intended conclusion. Datamodelling matters and small nuances like nullability can make big differences in performance.