RE: improve cardinality estimation
Date: Tue, 27 Oct 2020 15:22:50 -0400
Message-ID: <22b801d6ac96$8fb03510$af109f30$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, October 27, 2020 2:27 PM
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
Regards
Jonathan Lewis
On Tue, 27 Oct 2020 at 15:32, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:
Hello,
Thanks for your help.
To: laurentiu.oprea06_at_gmail.com
Cc: ORACLE-L (oracle-l_at_freelists.org)
Subject: Re: improve cardinality estimation
sysdate >= trunc(col_from_date)
and (sysdate <= trunc(col_to_date)
or trunc(col_to_date) is null
)
/
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 27 2020 - 20:22:50 CET