Re: Curious as to why this index isn't being used
- From: Tony Adolph <tony.adolph.dba@xxxxxxxxx>
- To: Jay.Miller@xxxxxxxxxxxxxxxx
- Date: Mon, 10 May 2010 17:33:44 +1200
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'
E.g:
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.
Cheers
Tony
Other related posts:
