Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 30 Nov 2011 @ 09:20:23 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Performance issue after splitting the big table
From:   rishi.agarwal

Todd Walter wrote:

  PPI would not help select rows from the table but it would avoid the need to materialize the table via the UNION prior to executing the rest of the query.  

  In the case of your queries by region, this is where you should make sure that the CHECK constraints are in place on the region column. This will allow the optimizer to recognize that the other tables do not have to be accessed.  

  If your query is materializing the UNION before doing the rest of the query, then the only solution is to rewrite the query to execute the query against each of the tables and union the results rather than just executing against the UNION view. Much more complex for the query writer but more performant.  

  The optimizer keeps getting more smarts in this area in the most recent releases but there are still cases where we will materialize the UNION.  

  Also consider if you are collecting stats too often, on too many columns of multi-column sets, not using SAMPLE when appropriate,...  

Hi Todd,

I understand we cannot do much to avoid UNION but query can be made faster if all split tables are not scanned completely but only those where REGION data is available. Here I have another problem that REGION MLPPI is not working because we have implemented that in form of a join and not as explicit filter condition. Basically I am dealing with two problems :

a) I have got MLPPI on DATE & REGION but REGION partition is not working properly. This has nothing to do with the splitting of tables as this was the case earlier also. We have a configuration table that maintains a mapping of all user_ids against the list of REGION they are allowed to access. This configuration table is joined with main txn table in view to handle the REGION level data security. In this scenario, only DATE partition is working and REGION is not, but it works fine if we put explicit filter condition on REGION which I cannot do during runtime.

b) Second issue is that after splitting the tables queries started taking more time. I want to highlight one more observation here that mostly queries are getting stuck at aggregation step (explain plan step: We do a SUM step to aggregate from Spool 21920 (Last Use) by way of an all-rows scan.

Aggregate Intermediate Results are computed globally, then placed in Spool 21930). Could it be due to the fact that while splitting lot of data movement & purging took place in the system as big tables were re-created and packdisk can help in this case?

Thanks & Regards

Rishi Agarwal

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023