All Forums Database
ab186024 16 posts Joined 11/11
07 Sep 2015
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

ABH
ab186024 16 posts Joined 11/11
08 Sep 2015

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
 

ABH

dnoeth 4628 posts Joined 11/04
08 Sep 2015

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.

Dieter

ab186024 16 posts Joined 11/11
09 Sep 2015

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

ABH

ToddAWalter 316 posts Joined 10/11
09 Sep 2015

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?

ab186024 16 posts Joined 11/11
09 Sep 2015

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
 

ABH

dnoeth 4628 posts Joined 11/04
09 Sep 2015

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

 

Dieter

ToddAWalter 316 posts Joined 10/11
09 Sep 2015

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.

ab186024 16 posts Joined 11/11
09 Sep 2015

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. 

ABH

KhurramInayat 3 posts Joined 12/13
09 Sep 2015

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
 

ab186024 16 posts Joined 11/11
10 Sep 2015

Thanks for your reply.
The view return exactly 1 single row but the correct partition is still not hit, it result in full scan of all partitions.

ABH

ToddAWalter 316 posts Joined 10/11
10 Sep 2015

Your note above says TMP_RUNDATE is "not" a single row table, but then your most recent note says the view returns exactly one row. Can you explain this part further?
 
Not only does the view have to return exactly one row but the optimizer needs to know for certain that it is only one row. For instnace, if it is a one row table, put an artificial UPI on the table and insert the run date with a PI=1. Then in your view, select WHERE PI=1. Now the optimizer knows for sure that the view is guaranteed to be one row.
 
In the explain above, it appears that each year table has only a couple rows. Without a significant amount of data in those tables, you will not see the optimizations. It is to cheap to just do the brute force query...

ab186024 16 posts Joined 11/11
11 Sep 2015

Thanks for your reply ToddAWalter.
TMP_RUNDATE is single table. it has only one row. I've tried your suggestion above but it's still doing a full scan:
replace view V_TMP_RUNDATE AS SEL (RUN_DATE-1) as RUN_DATE FROM Test_DB.TMP_RUNDATE Where PI = 1;
 
DYNAMIC explain SELECT V.Test_Id 
FROM Test_DB.Test_View V
WHERE (select run_date from Test_DB.V_TMP_RUNDATE ) = V.Test_Dt;
 
 This request is eligible for incremental planning and execution (IPE)
 but does not meet cost thresholds. The following is the static plan
 for the request.
"  1) First, we lock Test_DB.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.TMP_RUNDATE by"
     way of an all-rows scan with a condition of (
"     ""Test_DB.TMP_RUNDATE.PI = 1"") into Spool 2 (all_amps), which is"
     built locally on the AMPs.  The size of Spool 2 is estimated with
     high confidence to be 1 row (25 bytes).  The estimated time for
     this step is 0.04 seconds.
  3) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 2 (Last Use)
     by way of an all-rows scan and send the rows back to the
     Dispatcher.  The size is estimated with high confidence to be 1
     row.  The estimated time for this step is 0.02 seconds.
  4) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal in view Test_View with a condition of (
     "Test_DB.Tbl_Bal in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal in view Test_View.Test_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
     Test_DB.Tbl_Bal_2014 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2014 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2014 in view
"     Test_View.Test_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
     Test_DB.Tbl_Bal_2013 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2013 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2013 in view
"     Test_View.Test_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.
  7) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2012 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2012 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2012 in view
"     Test_View.Test_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 4 rows (100 bytes).  The estimated time for
     this step is 0.03 seconds.
  8) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2011 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2011 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2011 in view
"     Test_View.Test_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 5 rows (125 bytes).  The estimated time for
     this step is 0.03 seconds.
  9) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2010 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2010 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2010 in view
"     Test_View.Test_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 6 rows (150 bytes).  The estimated time for
     this step is 0.03 seconds.
 10) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2009 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2009 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2009 in view
"     Test_View.Test_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 7 rows (175 bytes).  The estimated time for
     this step is 0.03 seconds.
 11) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2008 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2008 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2008 in view
"     Test_View.Test_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 8 rows (200 bytes).  The estimated time for
     this step is 0.03 seconds.
 12) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2007 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2007 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2007 in view
"     Test_View.Test_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 9 rows (225 bytes).  The estimated time for
     this step is 0.03 seconds.
 13) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2006 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2006 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2006 in view
"     Test_View.Test_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 10 rows (250 bytes).  The estimated time for
     this step is 0.03 seconds.
 14) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2005 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2005 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2005 in view
"     Test_View.Test_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 11 rows (275 bytes).  The estimated time for
     this step is 0.03 seconds.
 15) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2004 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2004 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2004 in view
"     Test_View.Test_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 12 rows (300 bytes).  The estimated time for
     this step is 0.03 seconds.
 16) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2003 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2003 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2003 in view
"     Test_View.Test_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 13 rows (325 bytes).  The estimated time for
     this step is 0.03 seconds.
 17) We do an all-AMPs RETRIEVE step from a single partition of
     Test_DB.Tbl_Bal_2002 in view Test_View with a condition of (
     "Test_DB.Tbl_Bal_2002 in view Test_View.Test_Dt = :%SSQ22") with a
     residual condition of ("Test_DB.Tbl_Bal_2002 in view
"     Test_View.Test_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 14 rows (350 bytes).  The estimated time for
     this step is 0.03 seconds.
 18) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
     an all-rows scan with a condition of ("V.TEST_DT = :%SSQ22") into
"     Spool 3 (group_amps), which is built locally on the AMPs.  The"
     size of Spool 3 is estimated with low confidence to be 14 rows (
     406 bytes).  The estimated time for this step is 0.04 seconds.
  -> The contents of Spool 3 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.52 seconds.
 

ABH

ToddAWalter 316 posts Joined 10/11
11 Sep 2015

Making progress... At the top it says that it is not doing incremental planning because it does not meet cost thresholds. That means that the brute force plan is too cheap to execute to spend time on further optimization. In the previous example it appeared the year tables were test versions with very little data in them. Try the explain against a set of tables with significant numbers of rows. 

dnoeth 4628 posts Joined 11/04
11 Sep 2015

A single row table can be replaced by a view without accessing any table. In that case the optimizer always knows the actual value, similar to the dynamic plan in TD14.10, but without cost thresholds :-) 

REPLACE VIEW TMP_RUNDATE2 AS SELECT DATE '2014-10-01' AS RUN_DATE;

EXPLAIN
SELECT V.Test_Id
        FROM Test_View V
INNER JOIN TMP_RUNDATE2 D ON 1=1 
        WHERE V.Test_Dt = D.RUN_DATE-1 
        GROUP BY 1;

  1) First, we lock Test_DB.Tbl_Bal_2014 in view Test_View
     for access.
  2) Next, we do an all-AMPs SUM step to aggregate from a single
     partition of Test_DB.Tbl_Bal_2014 in view Test_View
     with a condition of ("Test_DB.Tbl_Bal_2014 in view
     Test_View.Test_Dt = DATE '2014-09-30'") with a residual condition
     of ("Test_DB.Tbl_Bal_2014 in view Test_View.Test_Dt =
     DATE '2014-09-30'") , grouping by field1 (
     Test_DB.Tbl_Bal_2014.Test_Id).  Aggregate Intermediate
     Results are computed locally, then placed in Spool 1.  The size of
     Spool 1 is estimated with low confidence to be 1 row (29 bytes).
     The estimated time for this step is 0.05 seconds.
  3) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.05 seconds.

 

Dieter

ab186024 16 posts Joined 11/11
14 Sep 2015

Hi All,

Many thanks for your quick reply.

The requirement is to get the date from a work table because the date will be inserted into that table i.e: Delete all from work table and insert Date-1 into work table . It works fine with a hard coded date and my understanding is creating the view as suggested above is like using a hard coded date or am I missing something here?

Is there any other way this can be done or any other way performance can be improved here? Thank you

ABH

ToddAWalter 316 posts Joined 10/11
14 Sep 2015

I haven't seen the explain using the =1 view and tables with a real amount of data rather than test tables with just a couple rows each.

KhurramInayat 3 posts Joined 12/13
15 Sep 2015

so you mean the variable :%SSQ22 is not being replaced with the date from subquery at execution time? I havent tested it but I would've assume that worked!!!
anyways, if your solution design allows it, an altenative is to use something like DBC.SysExecSQL within a stored procedure or a program to generate a view dynamially at runtime using the date from TMP_RUNDATE table as input.
For example: CALL DBC.SysExecSQL ('REPLACE VIEW V_TMP_RUNDATE AS SELECT ''' || input_date ||''' AS RUN_DATE');
Now use the resulting view V_TMP_RUNDATE in the actual SQL. hope this helps!
 

ab186024 16 posts Joined 11/11
17 Sep 2015

Hi,
Sorry for the late reply, been sick, caught a nasty flu. Many thanks for all your help and suggestion. The requirement now is that the date will have to come from a work table, which will always result to full scan. 
Does anyone has a better approach to identify cold or hot data? Can DBQL help? I know in ferret you can but at the block level. Thanking you in advance

ABH

You must sign in to leave a comment.