Re: Curious as to why this index isn't being used

From: Tony Adolph <>
Date: Mon, 10 May 2010 17:33:44 +1200
Message-ID: <>

Are you sure there aren't any histograms as they come "out-of-the-box" in 10. Have you dropped them and changed the METHOD_OPT setting?

"having no histograms" could mean one of 2 things too (could be wrong here,
but this is what I've found):

If you drop table stats, then gather with method_opt='FOR COLUMNS' you don't get an entry in dba_tab_col_statistics for that table and no entries in dba_histograms. So definately no histograms,..

But if you follow a lot of instructions to drop histograms as
"method_opt='FOR <column spec> COLUMNS size 1", you do get an entry in
dba_tab_col_statistics for each column and dba_histograms. But dba_tab_col_statistics.histogram ='NONE'


select * from dba_histograms where table_name in ( select table_name from dba_tab_col_statistics where histogram ='NONE')

Having histograms or not affects the CBO's calculation of density and therefor selectivity. What does oracle think the selectivity of the index is? Is it taking into account the nulls? Are there a lot of nulls (see dba_tab_col_statistics.num_nulls). Run a 10053 trace. You'll only get a trace if the query hard parses though, so flush the shared pool or change your query (a comment will do).

Just some ramblings, I hope they help.


Received on Mon May 10 2010 - 00:33:44 CDT

Original text of this message