Archives of the TeradataForum
Message Posted: Wed, 13 Apr 2005 @ 19:49:48 GMT
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.
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.)
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|