Mark / Wolfgang, This is a Peoplesoft database. Initially I was told that no changes to Peoplesoft indexes would be allowed, although I could create new indexes. However, now the DBA is agreeable to changing the existing index. So we will consider re-ordering the index columns. Wolfgang has also suggested optimizer_dynamic_sampling to 4 or higher. Will try that as well. Thanks At 01:01 AM Friday, Mark W. Farnham wrote:
Is this index required for relational integrity? IF *NOT*, have you considered redefining the index without column C_5. That might result in an overall improvement, depending on possible extra cost to visit the table when the index would have been enough on some queries and possibly less good plans when there is an equijoin or in-list for *NOT* "881". IF *REQUIRED*, second caveat above, consider putting C_5 last instead of third. Paradoxically, putting it first might result in a much poorer cost estimate for input to the optimizer. I am not aware of way to raise the cost the optimizer puts on a skip scan. For the 55% combination it seems to me if any C_2 results in 55% of the rows (or presumably more, since even with the other columns pinned to particular values you get 55%, then C_2 is pretty non-selective for that value. So probably you need a histogram on that column as well. So ditto on moving C_2 lower in the food chain in the order of the columns in the index. Am I missing something.
At 01:20 AM Friday, Wolfgang Breitling wrote:
Have you tried to set optimizer_dynamic_sampling to 4 (or higher)? Can be doneon the system or session (with a logon trigger) level or even on the sql with ahint (not applicable in this case). With that the cbo samples the combined selectivity of multi-column predicates on the same table.
Hemant K Chitale http://web.singnet.com.sg/~hkchital and http://hemantscribbles.blogspot.com and http://hemantoracledba.blogspot.com "There is more to life than increasing its speed."Mohandas Gandhi Quotes : http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
-- //www.freelists.org/webpage/oracle-l