RE: "Multi-Column" Histogram / Histogram for Concatenated Index

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 done
>on the system or session (with a logon trigger) level or even on the
>sql with a
>hint (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

--
http://www.freelists.org/webpage/oracle-l