Re: Extensions and/or Multi Column Indexes

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_at_performing-db.com // https://www.performing-databases.com 



> Von: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>

> An: "Oracle-L Freelists" <oracle-l_at_freelists.org>

> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of

> Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com>

> 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_at_performing-db.com // https://www.performing-databases.com

> --

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


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