Your re-write is indeed correct now, Laurentiu’s that I also mistook as correct
was wrong. I missed that it would include any future “from_dates” based on my
earlier misreading that it was the same column. Sigh.
But Laurentiu can’t change the code at all, so I am still stuck on how to make
the CBO get it without actually changing the nulls to “tomorrow” every day.
That might also speed up a lot of their stuff as long as it doesn’t break
anything. I’m not even sure that fixes it with the function reference on top of
it, so Laurentiu might end up with the 5% guess anyway.
I suppose Laurentiu could slap in a huge number of additional rows (to make up
the 95%) and give them a future from_date, so the 5% guess turns out to be
accurate. Sigh. Before you would do that you would punch the stats to a lie,
right? Run a count(*) and multiply by 20, put that in the relevant stats? Maybe
this doesn’t change in number very often. Just lie about the stats, and if all
the code has this awkward “null means not ended yet” translation that should
work out okay.
Anything I can think of leaves out the indeterminate future col_to_date
represented by null. Someone, somewhere, decided that from start until null end
date and then eventually updating the end date was less update work than
current_flag=’Y’ until current_flag is toggled to null. Or a to date using high
values for the date being updated to a real date, which does take some storage,
but this is tiny. But we can’t change the code, so we would still get that 5%
function estimate, right?
We can’t reference sysdate in the creation of a virtual column, and this is way
too small to use your virtual column partitioning trick to a productive result.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Tuesday, October 27, 2020 2:27 PM
To: laurentiu.oprea06@xxxxxxxxx
Cc: ORACLE-L (oracle-l@xxxxxxxxxxxxx)
Subject: 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@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.
