Re: CBO: Extensions and/or Multi Column Indexes

Hi Martin, happy new year to you too :-)

"My question is, does it make sense to manually create extended statstics for two columns, that are already part of a two-column index?"

It matters to create extended statistics if there is a big estimation error even you have multicolumn index. In the case of absence of extended statistics the optimizer tries to use DISTINCT_KEYS of the multi column index in order to estimate cardinality. So, if your data (column) is highly skewed in this case the multicolumn index may not help optimizer well enough to calculate cardinality properly, Therefore , you can create extended statistics to solve the problem. But you can keep multi column index in order to retrieve data efficiently.

Thanks

On Wed, Jan 9, 2019 at 2:26 PM Martin Klier - Performing Databases GmbH < martin.klier_at_performing-db.com> wrote:

> 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
>

-- 
*Chinar Aliyev*


Visit My         :Blog <http://chinaraliyev.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <https://www.linkedin.com/in/chinaraliyev/>*

My Twitter <https://twitter.com/MohamedHouri>      - ChinarAliyev
<https://twitter.com/ChinarAliyev>

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