Hi Jonathan, Chinar and Stefan,
outstanding information, helped me a lot. Thank you very much.
--
Martin Klier // Performing Databases GmbH
Managing Partner // Senior DB Consultant
Oracle ACE Director
martin.klier@xxxxxxxxxxxxxxxxx // https://www.performing-databases.com ;
Von: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
An: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
Gesendet: Mittwoch, 9. Januar 2019 11:36:37
Betreff: Re: Extensions and/or Multi Column Indexes
If you have a multi-column index and use equality on all columns of that index
(whether in a join or in a simple table access) the optimizer will use the
distinct_keys value for the index in cardinality calculations in all the cases
where it could otherwise use extended stats.
There are many cases, however, where the distinct_keys AND extended stats
cease
to be valid options as far as the optimizer is concerned. i've written various
notes about them on my lock (search for "column groups").
Possibly the only cse where you would want a column group that is an exact
match
for the index is when you have a histogram on one (or more) of the columns in
the index. Oracle will not use the distinct_keys in that case. If you want it
to do so you have to drop the histogram on the base column(s) or create a
column group for the index and generate a historgam on the column group.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of
Martin Klier - Performing Databases GmbH <martin.klier@xxxxxxxxxxxxxxxxx>
Sent: 09 January 2019 10:25:19
To: Oracle-L Freelists
Subject: CBO: Extensions and/or Multi Column Indexes
Hi listers,
happy new year to all of you!
I was wondering if it is useful to still have manually created extended
statistics (to enhance cardinality calculation for logically connected
columns)
and multi-column indexes of the same columns at the same time.
I need the two- or three-column indexes for query performance reasons anyway,
so
leaving them out is not an option.
My question is, does it make sense to manually create extended statstics for
two
columns, that are already part of a two-column index?
My field of using this stuff is versions 11.2, 12.1, 12.2 and 18.3 on Linux.
Background: I can't see an extension in dba_stat_extensions if I do not have
the
extended stats created, so I was thinking, if the CBO can calculate the
correct
cardinality from the index / index stats, I could spare my stats jobs the
effort to calculate many of my virtual column stats.
Thank you in advance, and please don't laugh if I am way off the path... :)
Best regards
--
Martin Klier // Performing Databases GmbH
Managing Partner // Senior DB Consultant
Oracle ACE Director
martin.klier@xxxxxxxxxxxxxxxxx // https://www.performing-databases.com
--
//www.freelists.org/webpage/oracle-l
