Thanks both for your feedback, it is highly appreciated.
-> The index is not working, is using trunc(col_from_date) as access
predicate for the index then trunc(nvl(col_to_date,sysdate+1)) as filter
predicate when retrieving rows from the table. True, the non-deterministic
sysdate mess all up and I had to replace it with a constant. Same
applies to the extended stats.
-> Using virtual columns works perfect for the presented code
refactored, cardinality is estimated on point . But using this version of
code works perfect with my initial method as well using extended stats on
function based columns ( method_opt for columns (trunc(col_from_date)) and
method_opt for columns (trunc( col_to_date)). To be honest I have no idea
what will be the best approach between these 2 but the hardest part will be
actually changing the code). With code presented into initial form none of
these methods work,
To fix the particular problematic report I just baselined the plan that
uses the HJ to avoid any headaches ( the crappy code is included in a view
used into "problematic" report so I suspect the view is used in multiple
places ... although is an assumption at this point I should actually check
this).
Thus my desire to find a smarter approach that will actually fix the view
and fix any other report that is dependent on this view and might get
impacted.
DB version is 12.1, I forgot to mention this
În mar., 27 oct. 2020 la 21:23, Mark W. Farnham <mwf@xxxxxxxx> a scris:
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.
