TACTICAL QUERIES ON WINDOWS FUNCTION

Database
Teradata Employee

TACTICAL QUERIES ON WINDOWS FUNCTION

Dear ATRs,

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

SELECT

(CURRENT_DATE -1 )   AS     DAT_RIF,

  COD_ABI_CEDENTE  ,

  COD_KEY_RAPP_CEDENTE,

  COD_ABI_TARGET ,

COD_KEY_RAPP_TARGET ,

 

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

AND FLG_RIFACIMENTO='0'

 

QUALIFY ROW_NUMBER() OVER (PARTITION BY

   COD_ABI_CEDENTE

  ,COD_KEY_RAPP_CEDENTE

  ,COD_ABI_TARGET

,COD_KEY_RAPP_TARGET

  ,DAT_CARICO

  ORDER BY TMS_INIZIO_VALIDITA DESC) = 1

)

 

When I run the view with a “tactical” where condition:

 

SELECT *

FROM V

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.

 

Many thanks,

regards,

Pietro.