Re: improve cardinality estimate

In many ways there's not a lot to choose between them. (You can find examples where the optimizer "misbehaves" wrong in either case.) As a general principle, though, I prefer to keep the "extended stats" method available for "column group" statistics and use virtual columns for user-visible expressions.

In passing, I wouldn't use "for all columns skewonly" and generally I'd avoid skewonly (or anything else that allowed the code to decide whether or not to create a histogram).

Regards
Jonathan Lewis

On Thu, 18 Mar 2021 at 15:09, mcpeakm_at_tempus-consulting-group.com < mcpeakm_at_tempus-consulting-group.com> wrote:

> Jonathan,
>
> Is creating a virtual column and then gathering a histogram on that
> different/better than gathering expression statistics via something like:
>
> BEGIN
> DBMS_STATS.GATHER_TABLE_STATS(user, 'ORDERS', method_opt => 'FOR ALL
> COLUMNS SIZE SKEWONLY FOR COLUMNS ( order_processing_date -
> business_processing_date) SIZE SKEWONLY');
> END;
>
> ?
>
>
>
>

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