Archives of the TeradataForum
Message Posted: Fri, 22 Aug 2015 @ 01:07:14 GMT
Dieter - Let me explain this in a bit more detail. Here is the complete problem query. Any direction in optimizing this will be very helpful.
INSERT INTO RBAKTHA.vwMEDSTG ( Col1,col2,col3........col35 ) SELECT distinct col1,col2....col35 FROM RBAKTHA.viewMEDFPOP AS MED INNER JOIN ( SELECT ID, HCI, Code, CASE WHEN CODE IN ('NV','CO') THEN '2006-10-01' ELSE '2006-01-01' END AS Servcdate1 FROM RBAKTHA.viewIDMem ) AS ELIG ON MED.HCI = ELIG.HCI WHERE Servcdate >= Servcdate
The query that I sent in the first email is actually the viewMEDFPOP (mentioned below as well). I was trying to make it to a VOLATILE table instead to see if I can get better performance and then use it in the INSERT query. One more big issue is the INNER JOIN in the INSERT query. The viewIDMem has 59M records that is joined with viewMEDPOP which has 5.5 B records. There is sliding window merge join that is happening.There are 159M records out of 5.5 B that are duplicates( row level duplicates).
PI of viewMEDFPOP base table - HCI, Servdate
PI of viewIDMem base table - HCI
I was trying to sort the records in both the tables, thinking that will be faster, however it worsened the performance.
REPLACE VIEW VIEWMEDFPOP AS SELECT distinct 0 as ID, HCI, date, Type, optin, D1, D2, D3, D4, . . . . . CASE WHEN POP = 'A' THEN 1 WHEN POP = 'B' THEN 2 WHEN POP = 'C' THEN 3 WHEN POP = 'D' THEN 5 END AS Flag, 1 As TFlag FROM viewMED
Below is the EXPLAIN on the VIEWMEDFPOP.
Explain on the VIEWMEDFPOP
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|