Skip to Main Content
  • Questions
  • Tuning where column name is not null

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, krishna.

Asked: February 25, 2020 - 8:24 am UTC

Last updated: February 25, 2020 - 4:01 pm UTC

Version: 12.1.2.0

Viewed 1000+ times

You Asked

Hi Team ,

I am trying to create test scenario where the table name TEST is created with 1 column ID.

 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ID                                                                                                                         NUMBER


value in TEST table :


SQL> select count(*) ,id from test  group by id;

  COUNT(*)         ID
---------- ----------
         9          1
     99991



my query is when in trying to fire below statement :

select count(*) from Test where id is not null ;


I see in a plan , that index full scan is happening.we have collected histogram for ID still we see INDEX FULL SCAN

my question is if i am trying to select whole table , like in where clause : where id is null , if this goes to FTS , i can understand.

However, if my where clause has where id is not null , it should not go in index full scan , especially when I have histogram in place

select /* 002 */ count(*) from test where id is not  null

Plan hash value: 3822811674

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |         |     1 |     1 |            |          |
|*  2 |   INDEX FULL SCAN| AMRIT_1 |     9 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter("ID" IS NOT NULL)


Request you to guide here and let us know more on it

Regards,
Krishna




and Chris said...

it should not go in index full scan

What is it you want the database to do instead? Full scan the table? Why? And why would having a histogram make any difference?

The key thing to remember is an index is usually physically smaller than the table it's on.

So full scanning an index reads less data than full scanning the table.

So it should be quicker to read the index.

In this case the index is much smaller than the table. Because Oracle Database excludes null entries from BTrees. So there's a lot less data to read!

Re-creating your example with char(1000) columns to make rows "big".

Pay close attention to the Buffers column when using the index and the table:

create table t (
  c1 char(1000)
);

insert into t 
with rws as (
  select level x from dual
  connect by level <= 100000
)
  select case when x < 10 then x end 
  from   rws;

create index i 
  on t ( c1 );
  
alter session set statistics_level = all;
cl scr
select count(*) from t where c1 is not null ;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------    
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
----------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT      |      |      1 |        |      1 |00:00:00.01 |       6 |    
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:00:00.01 |       6 |    
|*  2 |   INDEX FAST FULL SCAN| I    |      1 |      8 |      9 |00:00:00.01 |       6 |    
----------------------------------------------------------------------------------------

select /*+ full ( t ) */count(*) from t where c1 is not null ;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

-------------------------------------------------------------------------------------    
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
-------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     191 |    
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     191 |    
|*  2 |   TABLE ACCESS FULL| T    |      1 |      8 |      9 |00:00:00.01 |     191 |    
-------------------------------------------------------------------------------------  


Buffers => logical I/O operations

So

Full index scan = 6 I/Os
Full table scan = 191 I/Os

So reading the table is orders of magnitude more work than reading the index!

So, why do you NOT want to read the index again?

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.