Archives of the TeradataForum

Message Posted: Wed, 13 Apr 2005 @ 19:49:48 GMT

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

Many thanks to those that shared their experiences a UNION ALL approach to handling the division of humungous (technical term) tables.

I tried the UNION ALL of smaller sub tables under an overarching view and captured some results which are below.

Some background:

The test queries were run a test box with 8 nodes, 48 amps. My ID was the only ID active on the box. The big table had about 900M rows. Due to space considerations, three sub-tables were created (each holding about 220M rows.) The three sub-tables were joined in a view which I will call the UNION ALL View (UA_vw).

No check constraints were used on the columns within the sub table definitions.

A multi table join that did not hit the primary index of the tables in question was used in the test. (In order for this approach to fly in the production world, queries other than PriIndx based joins must be considered.)

Bottom line:

Note: wgregg was the only ID running queries on the box at this time for all 4 runs

     ORDER   USR   Prog      Run   Start_Dt      Start_tm
     End_dt      End_Tm   Run_tm      CPU   IO

     first   wgregg   UNION      1   4/13/2005   9:39:35
     4/13/2005   9:47:16      0:07:41      6,838   3,530,917
     second   wgregg   NOUNION    1   4/13/2005   9:47:18
     4/13/2005   9:49:18      0:02:00      296   845,588

     first   wgregg   NOUNION    2   4/13/2005   10:48:22
     4/13/2005   10:50:05      0:01:43      294   877,724
     second   wgregg   UNION      2   4/13/2005   10:50:07
     4/13/2005   10:57:48      0:07:41      6,840
     3,525,262

Rgrds,

Bill Gregg

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