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
it should not go in index full scanWhat 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?