Archives of the TeradataForum
Message Posted: Tue, 18 Aug 2015 @ 19:31:32 GMT
Hello Forum - I am trying to tune the below query and I am out of options.
The below query runs over 2 hours. Could anyone give me a direction here?
It's doing an all rows scan due to the CASE logic and on top of that , I also need to remove duplicates also (that's why using a SET table).
More details below.
Underlying table count - 5.5 Billion records PI of the table - HCI,date The date column is also the PPI Stats are collected on columns - HCI,date and POP There are no 'Low' or 'no' confidence in EXPLAIN plan.
Below is POP column count grouped by A,B,C,D
POP Count(*) 1 A 4,603,656,794.00 2 B 10,544,361.00 3 C 355,998,280.00 4 D 548,883,167.00
Things I tried -
Created a secondary index on POP and it didn't help.
Tried splitting the query into two using UNION. That didn't help either
CREATE VOLATILE SET TABLE MEDICAL AS ( SELECT 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 WITH DATA PRIMARY INDEX (HCI,date,) ON COMMIT PRESERVE ROWS;
Thanks in advance for all the suggestions and inputs!
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|