BETWEEN filters comparing SYSDATE or SYSTIMESTAMP can lead to bad join orders
Details
| Detail name | Value |
|---|---|
| Changelog Number | 5052 |
| Type | Improvement |
| Status | Resolved |
| Fix Versions | Exasol 7.0.0 |
| Resolution Date | 2020-09-11 |
Description
Filters like BETWEEN, <, >, <=, >= are estimated by the join optimizer to determine best join orders, index scan usage and UNION ALL optimization application.
Using date functions like SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP with the above predicates may lead to bad join orders due to incorrect estimates.
In addition no index scan or UNION ALL optimization is performed.
Improvement
The system date/timestamp between filter estimation has been fixed for join optimizer. Join ordering, index scan usage and UNION ALL optimization will benefit from this.
