Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

Subj:   Re: Has any had success with views that UNION Tables
From:   Gregg, Bill

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


Bill Gregg

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