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@xxxxxxxxx>
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.
