RE: "Multi-Column" Histogram / Histogram for Concatenated Index
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>, "Wolfgang Breitling" <centrex@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 15 Dec 2007 01:03:51 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
