On Sun, 3 Sept 2023 at 23:52, veem v <veema0...@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 correct here?
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
