On Sun, 3 Sept 2023 at 23:52, veem v <veema0000@gmail.com> wrote:
> Additionally, is it true that optimizer will also get fooled on getting the math correct during cardinality
estimates,as because there is a big difference between , comparing or substracting, two date values VS two number
values.And storing the dates in the number columns will pose this problem for the optimizer. Is my understanding
correcthere?
The query planner does not do any subtracting of values which are the
target of the statistics. There are comparisons, but comparing a DATE
or an INT are equally as cheap.
To me, the design with the PART_DATE_YM_NM INT column looks very
strange. Why bother partitioning by RANGE when there's just a single
value? The partition pruning done for LIST partitioning will work
equally as well when given ranges of values. Also, don't they ever
want to store the day of the month anywhere in the table? The INT
partitioned table won't allow that, but the DATE one will.
Several jobs ago in a land far far away, I worked with someone who
would tell engineers to not use EXISTs clauses in their SQLs as
"they're not optimised very well". I questioned him about this and as
it turned out, some version of Oracle once didn't optimise these very
well and when he learned this, he took that knowledge and seemingly
applied it to all versions of all RDBMSs in the universe. Rather
bizarre, but perhaps that's what's going on here too.
David