Composite Index with NULL Columns

Suppose you create a composite index on a Oracle table such that some columns of composite index could be null. In that case composite index will be used as long as predicate for leading column is not null. I think I had forgotten this concept. So here is a script to explain it.

Create table with four columns and create unique index on first three columns. All columns of this table can be null.

CREATE TABLE qry_Test ( a VARCHAR2(9), b VARCHAR2(9), c VARCHAR2(9), d VARCHAR2(9));

CREATE UNIQUE INDEX qry_test_u1 ON qry_test(a,b,c);

Now insert few records
INSERT INTO qry_test VALUES (NULL,NULL,'c1','d1');

INSERT INTO qry_test VALUES ('a2',NULL,'c2','d2');

INSERT INTO qry_test VALUES ('a3','b3','c3','d3');

INSERT INTO qry_test VALUES ('a4','b4','c4','d4');

INSERT INTO qry_test VALUES ('a5','b5','c5','d5');

SELECT * FROM qry_test ;

Now get plan for this query

SELECT a, b FROM qry_test
WHERE a = :a
AND b IS NULL
AND c = :c
;

Plan for the above query will be

---------------------------------------------------------------------           
| Id  | Operation            |  Name        | Rows  | Bytes | Cost  |           
---------------------------------------------------------------------           
|   0 | SELECT STATEMENT     |              |       |       |       |           
|   1 |  INDEX RANGE SCAN    | QRY_TEST_U1  |       |       |       |           
---------------------------------------------------------------------  

Plan is using RANGE SCAN because query is checking value of second column in index to be NULL, so index will be scanned for all values of bind ‘a’ and then additional filtering criteria will be applied to the selected index rows. Please note table is not being accesses because all the columns in selected list as well as where clause are available in index.

Now run the plan for query
SELECT a, b FROM qry_test
WHERE a IS NULL
AND b = :b
AND c = :c
/

Plan for the above query will be

--------------------------------------------------------------------            
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |            
--------------------------------------------------------------------            
|   0 | SELECT STATEMENT     |             |       |       |       |            
|   1 |  TABLE ACCESS FULL   | QRY_TEST    |       |       |       |            
--------------------------------------------------------------------    

Since query is checking for NULL value of leading index column, it does not have any option but to do full table scan.

Now run the plan for query
SELECT a, b FROM qry_test
WHERE a = :a
AND b = :b
AND c = :c
/

Plan for the above query will be

---------------------------------------------------------------------           
| Id  | Operation            |  Name        | Rows  | Bytes | Cost  |           
---------------------------------------------------------------------           
|   0 | SELECT STATEMENT     |              |       |       |       |           
|   1 |  INDEX UNIQUE SCAN   | QRY_TEST_U1  |       |       |       |           
---------------------------------------------------------------------           

As expected, plan will use unique index. No table access in this case too.

Now run the plan for query
SELECT a, b, d FROM qry_test
WHERE a = :a
AND b = :b
AND c = :c
/

Plan for the above query will be

----------------------------------------------------------------------------    
| Id  | Operation                   |  Name        | Rows  | Bytes | Cost  |    
----------------------------------------------------------------------------    
|   0 | SELECT STATEMENT            |              |       |       |       |    
|   1 |  TABLE ACCESS BY INDEX ROWID| QRY_TEST     |       |       |       |    
|   2 |   INDEX UNIQUE SCAN         | QRY_TEST_U1  |       |       |       |    
----------------------------------------------------------------------------    

Plan uses unique index but it does table access too because in order to get value of column ‘d’, it has to access table.