 |
 |
Archives of the TeradataForum
Message Posted: Wed, 30 Nov 2011 @ 09:20:23 GMT
| 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
| |
