Using SI in view with analitical function

Database
Enthusiast

Using SI in view with analitical function

Hi All,

I've got a problem with performance whilst reading from view. I need to present state of contracts on each day. To avoid storing same information in table for every day a created table A with intervals and then created view that joins it with another table B(table has one column with STATE_DATE) and produces state for each day with last EVENT.

Below is example(in real table A has more than 20 millions of records)

table A:

ID    DATE_FROM    DATE_TO         EVENT_DATE     ID_FK

1        2014-01-01    2014-01-05        2014-01-01        10    

1        2014-01-01    2014-01-05        2014-01-03        10

1        2014-01-06    9999-12-31        2014-01-06        10

2        2014-01-01    2014-01-05        2014-01-01        10    

2        2014-01-01    2014-01-05        2014-01-03        10

2        2014-01-06    9999-12-31        2014-01-06        10

Primary Index - ID

Secondary Index - ID_FK

create view A_B as
select
B.STATE_DATE, A.ID, A.EVENT_DATE

qualify row_number() over(partition by B.STATE_DATE, A.ID order by case when B.STATE_DATE >= A.EVENT_DATE then 1 else 0 end desc, A.EVENT_DATE desc) = 1

from
A
--product join
join B on B.STATE_DATE between A.DATE_FROM and A.DATE_TO
;

View A_B is for business so They see the structure as expected. The problem is with performance. When they use

select * from A_B where ID = 1 - PI on table A is used first and then product join with table B - query results within few seconds

select * from A_B where ID_FK = 10 - SI in not used, first product join is performed and then the where clause - query results within 20 minutes.

I recognized that when I remove qualify... from view it causes that the SI is used - query results within few seconds

When I run query from view independently with where clause then optimizer also uses SI

select
B.STATE_DATE, A.ID, A.EVENT_DATE

qualify row_number() over(partition by B.STATE_DATE, A.ID order by case when B.STATE_DATE >= A.EVENT_DATE then 1 else 0 end desc, A.EVENT_DATE desc) = 1

from
A
--product join
join B on B.STATE_DATE between A.DATE_FROM and A.DATE_TO

where where ID_FK = 10;

So question Is, why optimizer doesn't use SI with qualify inside the view in this case, and is there some way to force it ?

1 REPLY
Senior Apprentice

Re: Using SI in view with analitical function

Logically WHERE is processed before QUALIFY, so both queries don't return the same result (at least the optimizer doesn't know might be the same).

And if you cross join all rows there's no advantage for using an index.

There's no efficient way to do what you want using Standard SQL, you might try Teradata's proprietary EXPAND ON syntax instead, e.g.

REPLACE VIEW A_B AS
SELECT BEGIN(pd) AS STATE_DATE, A.*
FROM A
EXPAND ON PERIOD(DATE_FROM, DATE_TO) AS pd
FOR PERIOD(DATE '2014-01-01', CURRENT_DATE)

Hopefully the optimizer might push conditions more easily, if not it's still much more efficient than a huge product join,