Index used sometimes and sometimes NOT?

Recently I came across One query using index some times sometimes not using index.


After trouble shoot long time came to know that histograms on that columns causing the index is not using sometimes.

In my Case :-

Schema Name : SNANABALA
Table name : Sunil
Column name : MASTERID


-- below query is using index

select * from SNANABALA.SUNIL where masterid='Event40_XXXXX41c0';

-- below query is not using index 

select * from SNANABALA.SUNIL where masterid='Event63_XXXXXXXX'; 

As a first step i deleted current stats on the table and regather stats on that specific table but still doing Full table scan on some times. 

When I regathers stats on Table it will create histograms on table  on all Columns. 

let me find histograms exists on table not by using below query 

SELECT OWNER, TABLE_NAME,COLUMN_NAME, HISTOGRAM, NUM_BUCKETS  FROM DBA_TAB_COL_STATISTICS where owner='SNANABALA' and table_name='SUNIL' and COLUMN_NAME='MASTERID';

If NUM_BUCKETS is greater than 1  that means that column having Histogram. 

-- As second option lets delete the Histogram on that column and retry the above query which is doing full table scan.

BEGIN
dbms_stats.delete_column_stats(
ownname=>'SNANABALA', tabname=>'SNANABALA', colname=>'MASTERID', col_stat_type=>'HISTOGRAM');
END;
/

-- Check the Histograms are deleted on that column 

SELECT OWNER, TABLE_NAME,COLUMN_NAME, HISTOGRAM, NUM_BUCKETS  FROM DBA_TAB_COL_STATISTICS where owner='SNANABALA' and table_name='SUNIL' and COLUMN_NAME='MASTERID';

If NUM_BUCKETS is equal to 1  that means Histograms are deleted. 

--  Reran the below query and check explain plan 

select * from SNANABALA.SUNIL where masterid='Event63_XXXXXXXX'; 

Hurry Query is using index and problem solved. 

*These are all my views, which I have added in the blog depending on the test cases that I have carried out. Any comments or suggestions on this write up would really help me in getting to know the things better.