I was asked to optimise a query and it involves multiple joins in the sel. i am attaching a small part of the query wher in which i think adding the index would help.
WHERE (uact.Email_Addr (UC)) NOT LIKE '%@%TEST%' AND (uact.Email_Addr (UC)) NOT LIKE '%RTEST%' AND uact.Effective_Start_Ts >= '2007-07-16 00:00:00'-- VONUSI/NUSI can be defined AND Effective_End_Ts = TIMESTAMP'9999-12-31 00:00:00' -- VONUSI/NUSI can be defined AND NOT ( uact.Creation_Source = 'LEGACY' -- Although SI can be defined,qry will not make use of this index since the condition is NOT( condition1 ). AND ( uact.Last_Auth_Ts < TIMESTAMP'2007-07-17 00:00:00' OR uact.Last_Auth_Ts IS NULL OR uact.Last_Auth_Ts <= CAST((uact.Signup_Date||' '|| CAST(uact.Signup_Time AS CHAR(8))) AS TIMESTAMP(0)) ) );
These are some of the facts that i gatherd:
Total rows in User_account table(alias uact) : 68,451,329 -- 6 million Max Effective_Start_Ts: 2009-05-06 23:59:58 Min Effective_Start_Ts: 2007-06-13 19:45:21 Max Effective_END_Ts :9999-12-31 00:00:00 Min Effective_END_Ts : 2007-07-16 00:40:37 Total distinct Effective_END_Ts values: ~2 million distinct values for creation_source is :37 max(Last_auth_ts): 8000-05-02 15:14:00 min(Last_auth_ts):1997-10-13 00:00:00 count(last_auth_ts) = 55,003,524 distinct last_auth_ts = 1,776,547
My questions: ===========
1.Last_Auth_Ts has got significant NULL but the column's datatype is Varchar. Now is it possible that we can include compress for Null 2.The following statement after the NOT are AND statements . so if i plan to create a NUSI/VONUSI on uact.Last_Auth_Ts will the query make use of Nusi.
1) yes u can't apply compression on varchar field. 2) Yes it will.And go for VONUSI in this case as u are working on date ragne.
Now see in u'r query as u are using LIKE which may go for FTS in this case. So if possible either remove this and use "IN" kind of thing. If this is not possible then before applying this condition try to filter out as much as possible data.
Also if u are using teradata 12.0 then I will suggest for MLPPI(uact.Effective_Start_Ts , uact.Last_Auth_Ts). Or it's V2R6 then work on partitioning for eithrt uact.Last_Auth_Ts or uact.Effective_Start_Ts.
Also try to collect stats on these columns.
try these option. And let me know the result.
And whats the current response time for this query?
Thanks for your insights . Actually the process that we do is normal insert ..select.. normally it takes 20 mins to end . sometimes it gets over by 30 to 40 mins. Stats are uptodate . so no worries on that . secondly what i found out was 1. VONUSI cant be used on a timestamp and it can be used only on Int or date datatype. 2. Timestamp Partitioning is not available in V2R6.. so option of partitioning the table is left out. 3. Last option is only NUSI, but inspite of adding a NUSI the query didnt utilize the Index because i think selectivity was less.
i think we need to do a query rewrite .. if we need to optimse. if u know , u can suggest or else i plan not to touch the query for tuning.
1) What if you add a DATE column to the table for the Effective Start (or Effective End) that convert the values to DATE data types that you can in turn use to partition the table.
2) What if you add a column to the table that combines the signup date and time values into a true timestamp value. The optimizer may be able to better estimate the rows impacted when compared to the Last_Auth_TS with stats.