Re: improve cardinality estimation

  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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
> *Sent:* Tuesday, October 27, 2020 2:27 PM
> *To:* laurentiu.oprea06_at_gmail.com
> *Cc:* ORACLE-L (oracle-l_at_freelists.org)
> *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_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