Archives of the TeradataForum

Message Posted: Tue, 12 Apr 2005 @ 12:54:52 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Yes. It works, it is not perfect, but it works. I recently used this logic to boost performance during a daily accounting job (Saves off, cleans out, and aggregates DBC.ACTG). This brought a process that normally took 30 - 90 minutes down to 2-3 minutes (No, I am not exaggerating).

Say you have three tables a, b, c, (Primary index of a' with a view d that unions each table.

Say you have a simplified query

     SELECT * from d
     WHERE a' = 'xyz';

Under V2R3, the optimizer would create the set (a U b U c) and then apply the where clause a' = 'xyz'

Under V2R4 and above, it seems to apply the where condition to each of the tables and then union the results.

     (a where a' = 'xyz') U (b where a' = 'xyz') U (c where a' = 'xyz')

However, it is not perfect.. For instance:

I have seen it ignore a secondary index that an individual table query would utilize. I suspect that it loses track of statistics. I cannot see any other reason for it acting this way.

It still scans tables that cannot participate in a query. Even when table specific conditions are applied to the views. Say a has a condition where a' is between 'abc' and 'def' and you build the view with that where clause in it. Since 'xyz' is not between 'abc' and 'def', one would expect it to skip that table. Alas and alack, it still attempts to scan the table (the explain has both conditions listed in the selection criteria. The "WITH CHECK OPTION" clause does not effect the behavior.

It builds a spool file with the result set and then sums that spool file into the output spool. This step is meant to eliminate duplicates, even when duplicates are not possible. (I have not tried UNION ALL instead of UNION - I will have to try that).

Still it does work and you can gain a lot of performance in data maintenance..

Glen

	<Prev	Next>		<<First	<Prev	Next>	Last>>