INDEX Access Path and NULL Data in Oracle.

When started my learning with oracle, had lots of confusion with Btree index and NULL data correlation.

  • Someone says index does not store NULL..
  • But says composite index store NULL..
  • Someone says when you are fetching only indexed column, it will perform index access no table access in the plan..
    And more…..

Based on my past experience with Oracle, will try to define criteria and constraint for Btree index access on indexed column projection (SELECT clause).
Will break discussion on basis of Index type(single column/composite column index)

Single column index:

Let’s start with basic questions.

Suppose we have two columns (col1, col2) in table (mytab) with col2 having normal btree index.
Let run below SQL.

select col2 from mytab;

Over here we are only fetching indexed column col2, so what optimizer will choose “INDEX ACCESS” OR “FULL TABLE SCAN”?
We asked above question in most of interviews and most of interviewee tends to say,

“INDEX ACCESS”
It will do index scan as all data that need to be fetch is in the index!
!

Do all data we demanded for column Col2 is in the index.?
It STRICTLY depends on constraint define on Col2, NOT NULL Constraint!!

For Not null constraint, we can say all data we are demanding is in the INDEX.
But for Nullable single column index, NULL Data is not stored in the index.Hence, through index access we can’t guarantee all data for Nullable column. we will miss NULL Data if INDEX ACCESS is perform, So for cases Optimizer will pick Table Access!!

IF Column is Defined as Nullable
It can have data as NULL, hence all entries won’t be present in index for a single column.
Hence, for data consistency, it will perform “TABLE FULL SCAN”

IF Column is Defined as Not Nullable
It cannot have NULL data, hence all data for col2 will be present in the INDEX.
Hence, data consistency will be guaranteed through index also, so “INDEX ACCESS(FULL/FAST FULL)”

If in above case sql was,
SELECT COL2 from mytab where col2 is not NULL;

Over here we are demanding data which is not null, hence index access will be applicable for NULLABLE Column!

Somewhat same applies to Where condition also.
SELECT COL2 from MYTAB WHERE COL2 IS NULL;

  • It will perform table access if col2 is defined as NULLABLE.
  • It will not do anything if col2 is defined as NOT NULLABLE, in plan oracle will add filter(1=2) to bypass any access table or index. As NOT NULL Defined Column cant have NULL.
    Total LIO for such case will be 0 (Hence it is said to define proper constraint on Column for better decision by Optimizer!)

If for NULL Data we want INDEX ACCESS, we would need to build a index as below.

CREATE INDEX t_col2_null on mytab(col2, 1);
It act like Function based index (NOT NULL) and no code change is required.

Multiple column index :
Composite index stored NULL Values, that True!!
But does a query that fetches only indexed column perform “Index Access”?

Lets see with field work.

SQL> create table my_tab as
2 (
3 select rownum col1, rownum+5 col2, lpad('A',rownum,'X') col3
4 from dual
5 connect by rownum < 10000;
Table Created.

SQL> create index idx_col1_col2 on my_tab(col1,col2);
Index created.

SQL> desc my_tab;
Name Null? Type
------- -------- ----------------------------
COL1 NUMBER
COL2 NUMBER
COL3 VARCHAR2(4000)

SQL> SET AUTOTRACE TRACEONLY;

Let run below SQL.
SQL> select col1,col2 from my_tab;
9999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2159419471

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 79992 | 2054 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| MY_TAB | 9999 | 79992 | 2054 (1)| 00:00:01 |
----------------------------------------------------------------------------

Statistics
--------------------------------
1 recursive calls
0 db block gets
7635 consistent gets
7446 physical reads
9999 rows processed

SQL running was fetching the data only from a composite indexed column, but Still No Index Access!.

For current table both column col1, col2 is Nullable.
Composite index stored NULL data, but only for the kind of data as below

Col1 Col2
1 NULL
Null 2
1 3
and so on…… (When either of column is not NULL or both are not NULL)

But as both are nullable, Col1 or Col2 can have below data also

Col1 col2
NULL NULL

Such data can’t be stored in the index as it can’t stored “All NULL/Only NULL” Data!.
As we are demanding all Data, Composite Index on all nullable column will not be able to guarantee complete data. hence oracle for data consistency didn’t read from INDEX!

Rule on the composite index for index access is, At least one column has to be NOT NULL.!!

Let mark one of the column as NOT NULL.
SQL> alter table my_tab
2 modify (col1 number not null);
Table altered.

lets re run sql.
SQL> select col1,col2 from my_tab;
9999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3316058179

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 79992 | 9 (0)|
| 1 | INDEX FAST FULL SCAN| IDX_COL1_COL2 | 9999 | 79992 | 9 (0)|
---------------------------------------------------------------------------

Statistics
---------------------------------
20 recursive calls
0 db block gets
708 consistent gets
9999 rows processed

Now (col1,col2):=(NULL,NULL) is no more possible because of NOT NULL Constraint on Col1.!!
As one of column is defined as NOT NULLABLE, Oracle can guarantee that index will have all set of data for both columns.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , . Bookmark the permalink.

One Response to INDEX Access Path and NULL Data in Oracle.

  1. Pingback: #SQLSERVERLearning : Does Unique Index Stored NULL Values in Index? | Oracle Insight and Performance concepts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s