Archives of the TeradataForum

Message Posted: Wed, 14 Dec 2005 @ 19:26:00 GMT

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

David,

You could use the horizontal partitioning concept to split the one table between to three tables - each having only one type code and adding table level constraints to each of the three tables.

These constraints feed the optimizer "hints" that the data in fact is partitioned. The three tables are presented back to the end user with a view that unions the results together.

Note: The constraints have to be a numeric or date type to be used as optimizer "hints".

When queried using the table level constraint, the tables that cannot satisfy the query due to the table-level constraints are dropped from the view at parse/optimization - thus further partitioning the data.

The other good thing is the MLOAD/TPUMP utilities can load up to 5 tables in one process. These three tables can still be loaded with on pass of data in a single script.

See the following example - hope this helps:

Example:

     -- change table to three tables split by type code with constraints

     CREATE MULTISET TABLE conkpi_new_1 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           CON_SRKY_ID INTEGER NOT NULL,
           CON_TYPE_CD BYTEINT NOT NULL,
           DTE_YRWK_NR INTEGER NOT NULL,
           CON_DELIVERY_DT DATE FORMAT 'yyyy-mm-dd',
           CON_DELIVERY_TM TIME(0),
           CON_POD_DT DATE FORMAT 'yyyy-mm-dd',
           CON_POD_TM TIME(0),
           CONSTRAINT conkpi_type_chk CHECK ( con_type_cd =  1  ))
     PRIMARY INDEX CONKPI_NUPI ( CON_SRKY_ID )
     PARTITION BY RANGE_N(DTE_YRWK_NR  BETWEEN
     200201  AND 200253  EACH 1 ,
     200301  AND 200353  EACH 1 ,
     200401  AND 200453  EACH 1 ,
     200501  AND 200553  EACH 1 ,
     200601  AND 200652  EACH 1 ,
     200701  AND 200752  EACH 1 );

     CREATE MULTISET TABLE conkpi_new_2 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           CON_SRKY_ID INTEGER NOT NULL,
           CON_TYPE_CD BYTEINT NOT NULL,
           DTE_YRWK_NR INTEGER NOT NULL,
           CON_DELIVERY_DT DATE FORMAT 'yyyy-mm-dd',
           CON_DELIVERY_TM TIME(0),
           CON_POD_DT DATE FORMAT 'yyyy-mm-dd',
           CON_POD_TM TIME(0),
           CONSTRAINT conkpi_type_chk CHECK ( con_type_cd =  2  ))
     PRIMARY INDEX CONKPI_NUPI ( CON_SRKY_ID )
     PARTITION BY RANGE_N(DTE_YRWK_NR  BETWEEN
     200201  AND 200253  EACH 1 ,
     200301  AND 200353  EACH 1 ,
     200401  AND 200453  EACH 1 ,
     200501  AND 200553  EACH 1 ,
     200601  AND 200652  EACH 1 ,
     200701  AND 200752  EACH 1 );

     CREATE MULTISET TABLE conkpi_new_3 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           CON_SRKY_ID INTEGER NOT NULL,
           CON_TYPE_CD BYTEINT NOT NULL,
           DTE_YRWK_NR INTEGER NOT NULL,
           CON_DELIVERY_DT DATE FORMAT 'yyyy-mm-dd',
           CON_DELIVERY_TM TIME(0),
           CON_POD_DT DATE FORMAT 'yyyy-mm-dd',
           CON_POD_TM TIME(0),
           CONSTRAINT conkpi_type_chk CHECK ( con_type_cd =  3  ))
     PRIMARY INDEX CONKPI_NUPI ( CON_SRKY_ID )
     PARTITION BY RANGE_N(DTE_YRWK_NR  BETWEEN
     200201  AND 200253  EACH 1 ,
     200301  AND 200353  EACH 1 ,
     200401  AND 200453  EACH 1 ,
     200501  AND 200553  EACH 1 ,
     200601  AND 200652  EACH 1 ,
     200701  AND 200752  EACH 1 );


     --Add view to present table to user
     replace view conkpi_new
     as
     locking row for access
     select *
     from conkpi_new_1
     union all
     select *
     from conkpi_new_2
     union all
     select *
     from conkpi_new_3;


     --execute query against the view by type and look at the explain
     Select *
     >From conkpi_new
     Where con_type_cd = 3
       and dte_yrwk_nr between 200401 and 200452;

     --tables 1 and 2 are not in the query explain
     Explanation
       1) First, we lock conkpi_new_3 for access.
       2) Next, we do an all-AMPs RETRIEVE step from 52 partitions of
          conkpi_new_3 with a condition of (
          "(conkpi_new_3.CON_TYPE_CD = 3) AND
          ((conkpi_new_3.DTE_YRWK_NR <= 200452) AND
          (conkpi_new_3.DTE_YRWK_NR >= 200401 ))") into Spool 1
          (all_amps), which is built locally on the AMPs.  The size of Spool
          1 is estimated with no confidence to be 6 rows.  The estimated
          time for this step is 0.02 seconds.
       3) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
          an all-rows scan with a condition of ("(CON_TYPE_CD = 3) AND
          ((DTE_YRWK_NR <= 200452) AND (DTE_YRWK_NR >= 200401 ))") into
          Spool 2 (all_amps), which is built locally on the AMPs.  The size
          of Spool 2 is estimated with no confidence to be 6 rows.  The
          estimated time for this step is 0.03 seconds.
       4) Finally, we send out an END TRANSACTION step to all AMPs involved
          in processing the request.
       -> The contents of Spool 2 are sent back to the user as the result of
          statement 1.  The total estimated time is 0.05 seconds.

Kyle Prescott
UnumProvident

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