I have a "simple" view V made by a "driving" table T (250 Mln rows) and a "left table" L(220 Mln rows)with a window function row_number inside:
REPLACE VIEW V AS LOCK ROW FOR ACCESS
(CURRENT_DATE -1 ) AS DAT_RIF,
KTO1.COD_SNDG AS COD_SNDG_ORIGIN,
KTO2.COD_SNDG AS COD_SNDG_TARGET,
FROM T TAB_MIG
LEFT OUTER JOIN L as KTO1
ON TAB_MIG.COD_ABI_CEDENTE = KTO1.COD_ABI
AND TAB_MIG.COD_KEY_RAPP_CEDENTE = KTO1.COD_KTO
LEFT OUTER JOIN L as KTO2
ON TAB_MIG.COD_ABI_TARGET = KTO2.COD_ABI
AND TAB_MIG.COD_KEY_RAPP_TARGET = KTO2.COD_KTO
WHERE TMS_INIZIO_VALIDITA <= (current_date) -1
QUALIFY ROW_NUMBER() OVER (PARTITION BY
ORDER BY TMS_INIZIO_VALIDITA DESC) = 1
When I run the view with a “tactical” where condition:
WHERE COD_ABI_TARGET = '00001'
AND COD_KEY_RAPP_TARGET = '01234567890'
AND COD_ABI_CEDENTE= '00002'
AND COD_KEY_RAPP_CEDENTE = '09876543210'
The optimizer generate a plan to calculate first the overall view and then apply the where conditions. I know that this is due to row_number() function.
In details the optimizer do a full table scan on T and two full table scan on L !
Why the constants values in the where condition don’t “pass” throught the window function ?
That’s say why the optimizer don’t apply first the where conditions on T and L and then do the joins and the row_number() ?
Obviously all the stats are defined on both tables.
How can I tell to the optimizer to apply first the where conditions ?
Why the optimizer don't apply the where condition on "left" table L ?
Note that, If in the view definition I comment the fifth column DAT_CARICO (leaving only the where condition columns), the optimizer do the “push down”: it first apply the where condition on both tables and then do the join and the row_number() function. But you know if I suggest to apply this comment, the business value of the view will change.