My apologies if that was already mentioned. (This thread is rather long).
sysdate <= sysdate + 1 always holds true. I do not think the optimizer
is capable of analysis at that level which goes beyond a straight
logical calculus transformation.
You could use some constant "where sysdate between trunc(col_from_date)
and trunc(nvl(col_to_date,to_date('12312999','MMDDYYYY')))".
This approach has his disadvantages too . It can lead to underestimation
as it extend the time window unrealistically. It might hold in your
case. That's a matter of testing.
Your rewrite is cleaner and I believe that is the preferred solution.
Instead of changing the code there is always the option of the sql
translation, although I would feel a bit uneasy about it.
http://kerryosborne.oracle-guy.com/2013/07/13/sql-translation-framework/
Regards
Lothar
Am 28.10.2020 um 08:39 schrieb Laurentiu Oprea:
Overall, I start to suspect that the only solution is code re-write . But not sure if this is a legitimate question: if there is an equivalent code shape should oracle rewrite the query behind the scenes with that shape? Or in this situation the "sysdate" scares him being non-deterministic, even if the code with the different shape is not influenced by non-deterministic character of sysdate?
În mie., 28 oct. 2020 la 07:08, Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx <mailto:laurentiu.oprea06@xxxxxxxxx>> a scris:
Dynamic sampling 11 was the nightmare until not so long. Due to a
bug the automatic determined value was 11. It was generating a
parsing time of over 5 minutes to parallel queries and when
downgrade was happening due to various reasons I could see even 30
minutes of parsing (there were even extreme cases of 2-3 hours).
Thanks for suggestion, although indeed solves the cardinality
issues in my case the side effect is crazy parse time for some
other more important queries.
În mie., 28 oct. 2020 la 04:12, Mladen Gogala
<gogala.mladen@xxxxxxxxx <mailto:gogala.mladen@xxxxxxxxx>> a scris:
Is this an OLTP instance? If it is not, you may try with
OPTIMIZER_DYNAMIC_SAMPLING=11. If that is an OLTP instance, using
dynamic sampling will increase parsing time and lower the
performance,
albeit not drastically.
On 10/27/20 11:31 AM, Laurentiu Oprea 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.
-- Mladen Gogala
Database Consultant
Tel: (347) 321-1217
--
//www.freelists.org/webpage/oracle-l
