Partitioning Issue

Database
Teradata Employee

Partitioning Issue

Hi All,

I need an urgent help and this will be very much appreciated. Thank you in advance

Our customer is space constrained so the requirement is to enable them to identify which data within their large transaction table (which holds transaction data for many years) is and is not being used without having to rewrite processes or impact performance. Once the data that is not being used is identified, a strategy for offload can then be implemented to put the data onto a lower cost platform and free up space.

Implementation now:

The large transaction table is split in many tables each holding data for partition, ex:

Table 1: - Created and insert from large transaction table

PARTITION BY RANGE_N(BAL_DT  BETWEEN DATE '2013-01-01' AND '2013-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Col1 , Col2 );

Table 2: - Created and insert from large transaction table

PARTITION BY RANGE_N(BAL_DT  BETWEEN DATE '2014-01-01' AND '2014-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Col1 , Col2 );

Table 3: - Created and insert from large transaction table

PARTITION BY RANGE_N(BAL_DT  BETWEEN DATE '2015-01-01' AND '2015-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Col1 , Col2 );

Then a View "View1" is created with UNION ALL on table 1, table 2 and table 3.

SQL goes to the right table when a hard coded date predicate is used but if the date is in a temporary table, a full table scan.

SELECT Tbl.Tbl_ID 

FROM View1 Tbl 

WHERE RUNDATE = '01-10-2014'

WORKS OK 

 

SELECT Tbl.Tbl_ID 

FROM View1 Tbl  

INNER JOIN TMP_RUNDATE ON 1=1 

WHERE DT=RUN_DATE-1;



DOESN'T WORKS

Can anyone pls suggest a better approach and why this wouldn't work? Thank you

18 REPLIES
Teradata Employee

Re: Partitioning Issue

I manage to get this to work half way through following one of dnoeth post and putting a subquery on the left:

SELECT AC1.ARRG_ID

FROM DB.View_290715 AC1 

WHERE (SEL RUN_DATE FROM V_TMP_RUNDATE) = AC1.BAL_DT;

 

This results in a better (simpler) explain, but it's still scanning all the partition instead of hitting only one partition:

We do an all-AMPs RETRIEVE step from a single partition of

     DB.DWH_DLY_ACCT_BAL in view View_290715 with

     a condition of ("DB.DWH_DLY_ACCT_BAL in view

     View_290715.BAL_DT = :%SSQ22") with a residual condition of (

     "DB.DWH_DLY_ACCT_BAL in view

"     View_290715.BAL_DT = :%SSQ22"") into Spool 1 (all_amps), which is"

     built locally on the AMPs.  The size of Spool 1 is estimated with

     low confidence to be 1 row (25 bytes).  The estimated time for

     this step is 0.03 seconds.

  5) We do an all-AMPs RETRIEVE step from a single partition of

     DB.DWH_DLY_ACCT_BAL_2014 in view View_290715

     with a condition of (

     "DB.DWH_DLY_ACCT_BAL_2014 in view

     View_290715.BAL_DT = :%SSQ22") with a residual condition of (

     "DB.DWH_DLY_ACCT_BAL_2014 in view

"     View_290715.BAL_DT = :%SSQ22"") into Spool 1 (all_amps), which is"

     built locally on the AMPs.  The size of Spool 1 is estimated with

     low confidence to be 2 rows (50 bytes).  The estimated time for

     this step is 0.03 seconds.

  6) We do an all-AMPs RETRIEVE step from a single partition of

     DB.DWH_DLY_ACCT_BAL_2013 in view View_290715

     with a condition of (

     "DB.DWH_DLY_ACCT_BAL_2013 in view

     View_290715.BAL_DT = :%SSQ22") with a residual condition of (

     "DB.DWH_DLY_ACCT_BAL_2013 in view

"     View_290715.BAL_DT = :%SSQ22"") into Spool 1 (all_amps), which is"

     built locally on the AMPs.  The size of Spool 1 is estimated with

     low confidence to be 3 rows (75 bytes).  The estimated time for

     this step is 0.03 seconds.

 

Using a hardcoded value works just fine:

SELECT AC1.ARRG_ID

FROM DB.View_290715 AC1 

WHERE AC1.BAL_DT = '2014-04-02';

"  1) First, we lock DB.DWH_DLY_ACCT_BAL_2014 in"

     view View_290715 for access.

"  2) Next, we do an all-AMPs RETRIEVE step from a single partition of"

     DB.DWH_DLY_ACCT_BAL_2014 in view View_290715

     with a condition of (

     "DB.DWH_DLY_ACCT_BAL_2014 in view

     View_290715.BAL_DT = DATE '2014-04-03'") with a residual condition

     of ("DB.DWH_DLY_ACCT_BAL_2014 in view

"     View_290715.BAL_DT = DATE '2014-04-03'"") into Spool 1 (group_amps),"

     which is built locally on the AMPs.  The size of Spool 1 is

     estimated with low confidence to be 1 row (33 bytes).  The

     estimated time for this step is 0.03 seconds.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1.  The total estimated time is 0.03 seconds.

 

I've tried dynamic query rewrite but doesn't help either:

set query_band='dynamicplan=system;' for session;

 

Any help will be gratelly appreciated. Thank you

 

Senior Apprentice

Re: Partitioning Issue

In TD14.10+ "Incremental Planning and Execution" should be switched on, so settting the Queryband will not help. 

But your test data seems to be small (based on estimated rows), so if you read "This request is eligible for incremental planning and execution (IPE) but does not meet cost thresholds" on top of Explain you might try it on bigger tables.

Teradata Employee

Re: Partitioning Issue

Hi Dnoeth,

Many thanks for your reply. Is there any other way to hit only the right partition if the date is in a temporary table and avoid a full scan (just like a hard coded value)? I've tried using a view as explained in your other post but it didn't change anything. Thank you

Teradata Employee

Re: Partitioning Issue

Please provide:

- Teradata release you are running

- view definition for V_TMP_RUNDATE and table def for the table it refers to

- full explain of your query including the first lines of the explain

- Did you place any constraints on the table defs for _2103, _2014 and current tables?

Teradata Employee

Re: Partitioning Issue

Many thanks for your reply and help.

- Running TD 14.10

- View and Table definition

CREATE NEW TABLES  (CURRENT YEAR ---- 2001)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

      Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00 ,

     CHECK (( Test_Dt>=1150101) AND (Test_Dt<=1151231))

     )

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2015-01-01' AND '2015-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2014 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00 ,

 CHECK (( Test_Dt>=1140101) AND Test_Dt<=1141231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2014-01-01' AND '2014-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2013 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1130101) AND Test_Dt<=1131231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2013-01-01' AND '2013-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2012 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1120101) AND Test_Dt<=1121231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2012-01-01' AND '2012-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2011 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1110101) AND Test_Dt<=1111231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2011-01-01' AND '2011-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2010 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1100101) AND Test_Dt<=1101231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2010-01-01' AND '2010-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2009 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1090101) AND Test_Dt<=1091231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2009-01-01' AND '2009-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2008 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1080101) AND Test_Dt<=1081231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2008-01-01' AND '2008-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2007 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1070101) AND Test_Dt<=1071231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2007-01-01' AND '2007-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2006 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1060101) AND Test_Dt<=1061231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2006-01-01' AND '2006-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2005 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

            Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1050101) AND Test_Dt<=1051231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2005-01-01' AND '2005-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2004 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

      Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1040101) AND Test_Dt<=1041231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2004-01-01' AND '2004-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2003 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

      Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1030101) AND Test_Dt<=1031231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2003-01-01' AND '2003-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE MULTISET TABLE Test_DB.Tbl_Bal_2002 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

,BLOCKCOMPRESSION=AUTOTEMP

     (

      Test_Id BIGINT NOT NULL,

      Test_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      Test_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Test1_Bal DECIMAL(15,2) COMPRESS 0.00 ,

      Cur CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'GBP',

      C_Trans INTEGER COMPRESS (0 ,1,9999999 ),  -- before: COMPRESS (0,1)

      D_Trans INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,9999999 ),

      Tx_Test DECIMAL(18,2) COMPRESS (0.00 ,0.01 ),

      Gy_Test DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Mn_Dev DECIMAL(18,2) COMPRESS (0.00 ,20.00 ),

      Exo_Bal DECIMAL(15,2) COMPRESS (0.00 ) , -- before: COMPRESS;

      Win_Amt DECIMAL(15,2) COMPRESS 0.00 ,

      Er_Amt DECIMAL(15,2) COMPRESS 0.00, 

 CHECK (( Test_Dt>=1020101) AND Test_Dt<=1021231))

PRIMARY INDEX ( Test_Id )

PARTITION BY RANGE_N(Test_Dt  BETWEEN DATE '2002-03-01' AND '2002-12-31' EACH INTERVAL '1' MONTH )

UNIQUE INDEX PKCOLS ( Test_Id ,Test_Dt );

 

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

REPLACE VIEW Test_DB.Test_View                                                                                                                                                                                                                                                                                                                                                                   

(

-- ########################################################################

-- # DDL for view: Tbl_Bal

-- # UNION ALL VIEW for Base table database: Test_DB_YYYY (2002- Current_Year)

-- ########################################################################

  

Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

) AS                                                                                                                                                                                                                                                                                                                                                                                                            

LOCKING ROW FOR ACCESS                                                                                                                                                                                                                                                                                                                                                                                          

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2014

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2013

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2012

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2011

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2010

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2009

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2008

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2007

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2006

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2005

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2004

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2003

UNION ALL

SELECT Test_Id,Test_Dt,Test_Bal,Test1_Bal,Cur,C_Trans,D_Trans,Tx_Test,Gy_Test,Mn_Dev,Exo_Bal,Win_Amt,Er_Amt                                                                                                                                                                                                                                                                                                                                                                                             

FROM Test_DB.Tbl_Bal_2002;

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- COLLECT STATISTICS ON THE NEW TABLES

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2002;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2003;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2004;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2005;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2006;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2007;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2008;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2009;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2010;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2011;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2012;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2013;

COLLECT SUMMARY STATISTICS ON Test_DB.Tbl_Bal_2014;

               

 

COLLECT   STATISTICS ON Test_DB.Tbl_Bal COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2002 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2003 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2004 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2005 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2006 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2007 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2008 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2009 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2010 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2011 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2012 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2013 COLUMN (Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2014 COLUMN (Test_Id,Test_Dt);

 

COLLECT STATISTICS ON Test_DB.Tbl_Bal COLUMN (Test_Id);                

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2002 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2003 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2004 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2005 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2006 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2007 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2008 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2009 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2010 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2011 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2012 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2013 COLUMN (Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2014 COLUMN (Test_Id);

 

COLLECT STATISTICS ON Test_DB.Tbl_Bal COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2002 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2003 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2004 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2005 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2006 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2007 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2008 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2009 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2010 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2011 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2012 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2013 COLUMN (Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2014 COLUMN (Test_Dt);

 

COLLECT STATISTICS ON Test_DB.Tbl_Bal COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2002 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2003 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2004 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2005 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2006 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2007 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2008 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2009 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2010 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2011 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2012 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2013 COLUMN (Test1_Bal);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2014 COLUMN (Test1_Bal);

 

COLLECT STATISTICS ON Test_DB.Tbl_Bal COLUMN (PARTITION);               

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2002 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2003 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2004 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2005 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2006 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2007 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2008 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2009 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2010 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2011 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2012 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2013 COLUMN (PARTITION);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2014 COLUMN (PARTITION);

 

COLLECT STATISTICS ON Test_DB.Tbl_Bal COLUMN (PARTITION,Test_Id);               

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2002 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2003 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2004 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2005 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2006 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2007 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2008 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2009 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2010 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2011 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2012 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2013 COLUMN (PARTITION,Test_Id);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2014 COLUMN (PARTITION,Test_Id);

 

COLLECT STATISTICS ON Test_DB.Tbl_Bal COLUMN (PARTITION,Test_Id,Test_Dt);             

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2002 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2003 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2004 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2005 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2006 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2007 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2008 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2009 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2010 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2011 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2012 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2013 COLUMN (PARTITION,Test_Id,Test_Dt);

COLLECT STATISTICS ON Test_DB.Tbl_Bal_2014 COLUMN (PARTITION,Test_Id,Test_Dt);


- Full explain 

NB: SQL goes to the right table when a hard coded date predicate is used but if the date is in a temporary table, a full table scan.

SELECT V.Test_Id, 

        FROM Test_DB.Test_View V

INNER JOIN TMP_RUNDATE D ON 1=1 

        WHERE V.Test_Dt = D.RUN_DATE-1 

        GROUP BY 1 

 

"  1) First, we lock Test_DB.Tbl_Bal_2002 in view Test_View for access,"

"     we lock Test_DB.Tbl_Bal_2003 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2004 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2005 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2006 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2007 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2008 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2009 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2010 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2011 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2012 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2013 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2014 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal in view Test_View for access, and we lock"

     GB_DWHL_DWH_DLY_TXN_DB01.D for access.

"  2) Next, we do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal in view"

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 2 rows (50

     bytes).  The estimated time for this step is 0.04 seconds.

  3) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2014 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 4 rows (

     100 bytes).  The estimated time for this step is 0.04 seconds.

  4) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2013 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 6 rows (

     150 bytes).  The estimated time for this step is 0.04 seconds.

  5) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2012 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 8 rows (

     200 bytes).  The estimated time for this step is 0.04 seconds.

  6) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2011 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 10 rows (

     250 bytes).  The estimated time for this step is 0.04 seconds.

  7) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2010 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 12 rows (

     300 bytes).  The estimated time for this step is 0.04 seconds.

  8) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2009 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 14 rows (

     350 bytes).  The estimated time for this step is 0.04 seconds.

  9) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2008 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 16 rows (

     400 bytes).  The estimated time for this step is 0.04 seconds.

 10) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2007 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 18 rows (

     450 bytes).  The estimated time for this step is 0.04 seconds.

 11) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2006 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 20 rows (

     500 bytes).  The estimated time for this step is 0.04 seconds.

 12) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2005 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 22 rows (

     550 bytes).  The estimated time for this step is 0.04 seconds.

 13) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2004 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 24 rows (

     600 bytes).  The estimated time for this step is 0.04 seconds.

 14) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2003 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 26 rows (

     650 bytes).  The estimated time for this step is 0.04 seconds.

 15) We execute the following steps in parallel.

      1) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2002 in

         view Test_View by way of an all-rows scan with no residual

"         conditions into Spool 1 (all_amps), which is built locally on"

         the AMPs.  The size of Spool 1 is estimated with low

         confidence to be 28 rows (700 bytes).  The estimated time for

         this step is 0.04 seconds.

      2) We do an all-AMPs RETRIEVE step from

         GB_DWHL_DWH_DLY_TXN_DB01.D by way of an all-rows scan with no

"         residual conditions into Spool 5 (all_amps), which is"

         duplicated on all AMPs.  Then we do a SORT to order Spool 5 by

         the hash code of ((GB_DWHL_DWH_DLY_TXN_DB01.D.Run_Date )- 1

         (DATE)).  The size of Spool 5 is estimated with high

         confidence to be 2 rows (36 bytes).  The estimated time for

         this step is 0.01 seconds.

 16) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of

"     an all-rows scan into Spool 6 (all_amps), which is built locally"

     on the AMPs.  Then we do a SORT to order Spool 6 by the hash code

     of (TEST_DT).  The size of Spool 6 is estimated with low

     confidence to be 28 rows (700 bytes).  The estimated time for this

     step is 0.02 seconds.

 17) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of a

"     RowHash match scan, which is joined to Spool 6 (Last Use) by way"

     of a RowHash match scan.  Spool 5 and Spool 6 are joined using a

"     merge join, with a join condition of (""TEST_DT = ((Run_Date )- 1)"")."

"     The result goes into Spool 4 (all_amps), which is built locally on"

     the AMPs.  The size of Spool 4 is estimated with low confidence to

     be 2 rows (46 bytes).  The estimated time for this step is 0.06

     seconds.

 18) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by

"     way of an all-rows scan , grouping by field1 ( TEST_ID)."

"     Aggregate Intermediate Results are computed locally, then placed"

     in Spool 2.  The size of Spool 2 is estimated with low confidence

     to be 2 rows (58 bytes).  The estimated time for this step is 0.04

     seconds.

  -> The contents of Spool 2 are sent back to the user as the result of

 

     statement 1.  The total estimated time is 0.63 seconds.

 

Thanks in advance

 

Senior Apprentice

Re: Partitioning Issue

Assuming that TMP_RUNDATE is a single row table this should work as expected if you replace it with a View:

replace view TMP_RUNDATE as select date '2014-10-01' as RUN_DATE
Teradata Employee

Re: Partitioning Issue

Thank you for all the information, very helpful. I see that you have the appropriate check constraints in the underlying table.

Still looking for the view definition of TMP_RUNDATE and the full explain of the case that uses the TMP_RUNDATE view.

Teradata Employee

Re: Partitioning Issue

Hi Dnoeth, ToddAWalter,

Many thanks for your quick response.

TMP_RUNDATE is not a single row table. I have created a view on top of this table as suggested by Dnoeth but it's still a full table scan.

replace view V_TMP_RUNDATE AS select cast(run_date-1 as DATE FORMAT 'YYYY-MM-DD') as run_date from Test_DB.TMP_RUNDATE;

---------------

SELECT V.Test_Id 

FROM Test_DB.Test_View V

INNER JOIN V_TMP_RUNDATE D ON 1=1 

        WHERE D.RUN_DATE = V.Test_Dt 

        GROUP BY 1;

-----------------

"  1) First, we lock Test_DB.TMP_RUNDATE in view V_TMP_RUNDATE for"

"     access, we lock Test_DB.Tbl_Bal_2002 in view Test_View for access,"

"     we lock Test_DB.Tbl_Bal_2003 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2004 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2005 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2006 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2007 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2008 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2009 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2010 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2011 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2012 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2013 in view Test_View for access, we lock"

"     Test_DB.Tbl_Bal_2014 in view Test_View for access, and we lock"

     Test_DB.Tbl_Bal in view Test_View for access.

"  2) Next, we do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal in view"

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 2 rows (50

     bytes).  The estimated time for this step is 0.04 seconds.

  3) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2014 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 4 rows (

     100 bytes).  The estimated time for this step is 0.04 seconds.

  4) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2013 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 6 rows (

     150 bytes).  The estimated time for this step is 0.04 seconds.

  5) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2012 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 8 rows (

     200 bytes).  The estimated time for this step is 0.04 seconds.

  6) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2011 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 10 rows (

     250 bytes).  The estimated time for this step is 0.04 seconds.

  7) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2010 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 12 rows (

     300 bytes).  The estimated time for this step is 0.04 seconds.

  8) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2009 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 14 rows (

     350 bytes).  The estimated time for this step is 0.04 seconds.

  9) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2008 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 16 rows (

     400 bytes).  The estimated time for this step is 0.04 seconds.

 10) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2007 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 18 rows (

     450 bytes).  The estimated time for this step is 0.04 seconds.

 11) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2006 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 20 rows (

     500 bytes).  The estimated time for this step is 0.04 seconds.

 12) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2005 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 22 rows (

     550 bytes).  The estimated time for this step is 0.04 seconds.

 13) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2004 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 24 rows (

     600 bytes).  The estimated time for this step is 0.04 seconds.

 14) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2003 in view

     Test_View by way of an all-rows scan with no residual conditions

"     into Spool 1 (all_amps), which is built locally on the AMPs.  The"

     size of Spool 1 is estimated with low confidence to be 26 rows (

     650 bytes).  The estimated time for this step is 0.04 seconds.

 15) We execute the following steps in parallel.

      1) We do an all-AMPs RETRIEVE step from Test_DB.Tbl_Bal_2002 in

         view Test_View by way of an all-rows scan with no residual

"         conditions into Spool 1 (all_amps), which is built locally on"

         the AMPs.  The size of Spool 1 is estimated with low

         confidence to be 28 rows (700 bytes).  The estimated time for

         this step is 0.04 seconds.

      2) We do an all-AMPs RETRIEVE step from Test_DB.TMP_RUNDATE in

         view V_TMP_RUNDATE by way of an all-rows scan with no residual

"         conditions into Spool 5 (all_amps), which is duplicated on all"

         AMPs.  Then we do a SORT to order Spool 5 by the hash code of

         ((Test_DB.TMP_RUNDATE in view V_TMP_RUNDATE.Run_Date )- 1

         (DATE)).  The size of Spool 5 is estimated with high

         confidence to be 6 rows (108 bytes).  The estimated time for

         this step is 0.01 seconds.

 16) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of

"     an all-rows scan into Spool 6 (all_amps), which is built locally"

     on the AMPs.  Then we do a SORT to order Spool 6 by the hash code

     of (TEST_DT).  The size of Spool 6 is estimated with low

     confidence to be 28 rows (700 bytes).  The estimated time for this

     step is 0.02 seconds.

 17) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of a

"     RowHash match scan, which is joined to Spool 6 (Last Use) by way"

     of a RowHash match scan.  Spool 5 and Spool 6 are joined using a

"     merge join, with a join condition of (""((Run_Date )- 1 )= TEST_DT"")."

"     The result goes into Spool 4 (all_amps), which is built locally on"

     the AMPs.  The size of Spool 4 is estimated with low confidence to

     be 6 rows (138 bytes).  The estimated time for this step is 0.06

     seconds.

 18) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by

"     way of an all-rows scan , grouping by field1 ( TEST_ID)."

"     Aggregate Intermediate Results are computed locally, then placed"

     in Spool 2.  The size of Spool 2 is estimated with low confidence

     to be 5 rows (145 bytes).  The estimated time for this step is

     0.04 seconds.

  -> The contents of Spool 2 are sent back to the user as the result of

     statement 1.  The total estimated time is 0.63 seconds.

-------------------------------

Thanks again for more suggestion or idea. 

Re: Partitioning Issue

Your view  V_TMP_RUNDATE must return exactly 1 single row to hit the correct partition.

Have a look at one of Dieter's old post for ref:

http://developer.teradata.com/blog/dnoeth/2011/03/global-and-session-level-parameters-in-sql