Re: improve cardinality estimation

Was there a version somewhere ?
12c is better than 11g since virtual columns can then be declared invisible, but for 11g:

alter table XXX add from_virtual generated always as (trunc(col_from_date)) virtual;
alter table XXX add to_virtual generated always as (trunc(col_to_date)) virtual;
execute dbms_stats.gather_table_stats(user,'XXX');

Very important to gather the stats so Oracle doesn't have to use the 5% guess etc. for "function of column".

select count(*) from XXX where
where

        sysdate >= trunc(col_from_date)
and     (sysdate <= trunc(col_to_date)
         or trunc(col_to_date) is null
        )

Regards
Jonathan Lewis

On Tue, 27 Oct 2020 at 15:32, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Hello,
>
> I found a query with a very bad execution plan due to low
> cardinality evaluation of below where clause
>
> where
> sysdate between trunc(col_from_date) and trunc(nvl(col_to_date,sysdate+1))
>
> Is there any way I can improve the estimate (like extended stats, etc) ?
>
> Thanks for your help.
>

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