Performance Question

Database
Enthusiast

Performance Question

Hello,

We have one complex view (VIEWA) created on TABLE1 (~2BIL transactional data), TABLE2 (~5K dimensional data), TABLE3 (~6K dimensional data), TABLE4 (~5K dimensional data)) and returning ~900Mil data for my data warehouse. After all the filters suggested by business, VIEWA returns past 9 years + present year data. Now, if I do a "SELECT * FROM VIEWA;", then it takes about 1 hour and 30 mins to show me all data. If I run a "SELECT *" on each of the tables, then they actually show me results within 2 mins since we have collected stats on them and we have join indices on them.

Now, users want to check one period (can be read as fiscal month as well -- lets say there are 13 fiscal months in a year) data through ad-hoc BO report created by them. Here, users want to see "01" period of "2015" year. I have one more view (VIEWB) which contains 3 columns to get the corresponding period information. VIEWB has only 2013, 2014, 2015 periods listed.

Columns in VIEWA: FISCAL_END_DATE, DIM_1_ATTR, DIM_2_ATTR, DIM_3_ATTR, ....., DIM_40_ATTR, TOTAL_SALES_AMOUNT

Columns in VIEWB: FISCAL_END_DATE, YR_ID, PRD_ID

When BO pulls both these objects, the underlying query gets created as (QUERY1):

SELECT * FROM VIEWA A, VIEWB B

WHERE A.FISCAL_END_DATE = B.FISCAL_END_DATE

AND B.YR_ID = '2015' AND B.PRD_ID='01';

This query runs for hours. I tried writing this query in below different ways and they also took hours to execute (QUERY2, QUERY3, QUERY4).

SELECT * FROM VIEWA A,

(SELECT FISCAL_END_DATE FROM VIEWB WHERE YR_ID = '2015' AND PRD_ID='01') B

WHERE A.FISCAL_END_DATE = B.FISCAL_END_DATE;

SELECT * FROM VIEWA AS A

WHERE EXISTS (SELECT B.FISCAL_END_DATE FROM VIEWB AS B

WHERE A.FISCAL_END_DATE = B.FISCAL_END_DATE AND B.YR_ID = '2015' AND B.PRD_ID='01');

SELECT * FROM VIEWA A

INNER JOIN VIEWB B

ON A.FISCAL_END_DATE = B.FISCAL_END_DATE

WHERE B.YR_ID = '2015'

AND B.PRD_ID='01';

When I write the query in this below way, the query returns data in 8 mins (QUERY5).

SELECT * FROM VIEWA A

WHERE A.FISCAL_END_DATE = ( SELECT B.FISCAL_END_DATE FROM VIEWA B WHERE

B.YR_ID = '2015' AND B.PRD_ID='01');

Now, ad-hoc reports in BO isn't allowing ad-hoc queries to run in them. So, our BO team isn't able to customize the query as QUERY5.

So, the users are anxious to know why QUERY1 takes so long time whereas QUERY5 runs in 8 mins. We are researching on this and haven't found an answer yet. Can someone please help to find the answer? Thanks in advance!

5 REPLIES
Enthusiast

Re: Performance Question

Somdebroy,

Can you post the ddl of the underlying tables and some explains? 

The explains will help determine if your join indexes are being used by the optimizer, which is where i would start.

There are several options that could improve your performance, including some horizontal partitioning, expecially on the repeated predicate fields, yr_id and prd_id.  Are your JI"s partitioned on these fields as well?

Cheers!

Enthusiast

Re: Performance Question

Hi VandeBergB,

Thanks for looking into it!

DDL of the driver table looks like:

/* BASE TRANSACTIONAL TABLE CONTAINING ~2BIL DATA */

CREATE SET TABLE DATABASE1.TABLE1 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

DIM_1_ATTR INTEGER TITLE 'DIM_1_ATTR' NOT NULL,

DIM_2_ATTR INTEGER TITLE 'DIM_2_ATTR' NOT NULL COMPRESS (1, 2, 3, ... , 299),

DIM_3_ATTR INTEGER TITLE 'DIM_3_ATTR' NOT NULL COMPRESS (1001, 1002, 1003, ... , 1499),

SALE_ATTR_DT1 DATE FORMAT 'YYYY-MM-DD' TITLE 'SALE_ATTR_DT1' NOT NULL,

SALE_ATTR_FLG1 CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'SALE_ATTR_FLG1' NOT NULL COMPRESS 'N',

SALE_ATTR_CODE1 CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'SALE_ATTR_CODE1' NOT NULL COMPRESS '1',

DOC_NBR CHAR(12) CHARACTER SET LATIN CASESPECIFIC TITLE 'DOC_NBR' NOT NULL,

DOC_TIME INTEGER FORMAT '99:99:99' TITLE 'DOC_TIME' NOT NULL,

SALE_ATTR_DT2 DATE FORMAT 'YYYY-MM-DD' TITLE 'SALE_ATTR_DT2' NOT NULL COMPRESS (DATE '2006-05-28', ... , DATE '2015-05-28'),

SALE_ATTR_AMT1 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT1' NOT NULL COMPRESS (0.0000 , ... ,2.7900 ),

SALE_ATTR_QTY1 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY1' NOT NULL COMPRESS (0., 1., 2., ... , -4., -3., -2., -1.),

SALE_ATTR_WT1 DECIMAL(11,4) TITLE 'SALE_ATTR_WT1' NOT NULL COMPRESS (0.0000, ... , 2.4063),

SALE_ATTR_AMT2 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT2' NOT NULL COMPRESS (0.0000, ... , 2.7900),

SALE_ATTR_QTY2 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY2' NOT NULL COMPRESS (0., 1., 2., ... , -4., -3., -2., -1.),

SALE_ATTR_WT2 DECIMAL(11,4) TITLE 'SALE_ATTR_WT2' NOT NULL COMPRESS (0.0000, ... , 2.4063),

SALE_ATTR_AMT3 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT3' NOT NULL COMPRESS (0.0000, ... , 2.7900),

SALE_ATTR_QTY3 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY3' NOT NULL COMPRESS (0., 1., 2., ... , -4., -3., -2., -1.),

SALE_ATTR_WT3 DECIMAL(11,4) TITLE 'SALE_ATTR_WT3' NOT NULL COMPRESS (0.0000, ... , 2.4063),

SALE_ATTR_AMT4 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT4' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_QTY4 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY4' NOT NULL COMPRESS 0. ,

SALE_ATTR_AMT5 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT5' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_QTY5 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY5' NOT NULL COMPRESS 0. ,

SALE_ATTR_AMT6 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT6' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_AMT7 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT7' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_AMT8 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT8' NOT NULL COMPRESS (0.0000, ... , 0.2300 ),

SALE_ATTR_AMT9 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT9' NOT NULL COMPRESS (0.0000, ... , 2.7900),

SALE_ATTR_QTY9 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY9' NOT NULL COMPRESS (0., ... , 60. ),

SALE_ATTR_AMT10 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT10' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_QTY10 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY10' NOT NULL COMPRESS 0. ,

SALE_ATTR_AMT11 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT11' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_QTY12 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY12' NOT NULL COMPRESS 0. ,

SALE_ATTR_AMT13 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT13' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_CODE2 CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'SALE_ATTR_CODE2' NOT NULL DEFAULT ' ' COMPRESS (' ','0','1'),

DIM_1_ATTR19 CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'DIM_1_ATTR19' NOT NULL DEFAULT ' ' COMPRESS (' ','0','1'),

SRCE_INDIC CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'SRCE_INDIC' COMPRESS ('IND1','IND2','IND3','IND4','IND5'),

FISCAL_END_DATE DATE FORMAT 'YYYY-MM-DD' TITLE 'FISCAL_END_DATE' NOT NULL,

SALE_ATTR_AMT14 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT14' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_QTY14 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY14' NOT NULL COMPRESS (0., 1. ... , 20.),

SALE_ATTR_AMT15 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT15' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_QTY15 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY15' NOT NULL COMPRESS (0., 1. ... , 20.),

SALE_ATTR_AMT15 DECIMAL(11,4) TITLE 'SALE_ATTR_AMT15' NOT NULL COMPRESS 0.0000 ,

SALE_ATTR_QTY15 DECIMAL(11,0) TITLE 'SALE_ATTR_QTY15' NOT NULL COMPRESS (0., 1. ... , 20.),

FOREIGN KEY ( DIM_1_ATTR ) REFERENCES WITH NO CHECK OPTION DATABASE1.DIM_1_TABLE ( DIM_1_ATTR ),

FOREIGN KEY ( DIM_1_ATTR ) REFERENCES WITH NO CHECK OPTION DATABASE1.DIM_1_TABLE_XREF ( DIM_1_ATTR ),

FOREIGN KEY ( DIM_2_ATTR ,DIM_3_ATTR ) REFERENCES WITH NO CHECK OPTION DATABASE1.DIM_2_TABLE_XREF ( DIM_2_ATTR ,

DIM_3_ATTR ))

PRIMARY INDEX TABLE1_NUPI ( DIM_1_ATTR )

PARTITION BY ( RANGE_N(SALE_ATTR_DT1 BETWEEN DATE '2006-01-01' AND DATE '2006-12-31' EACH INTERVAL '7' DAY ,

DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '7' DAY ,

DATE '2008-01-01' AND DATE '2008-12-31' EACH INTERVAL '7' DAY ,

DATE '2009-01-01' AND DATE '2009-12-31' EACH INTERVAL '7' DAY ,

DATE '2010-01-01' AND DATE '2010-12-31' EACH INTERVAL '7' DAY ,

DATE '2011-01-01' AND DATE '2011-12-31' EACH INTERVAL '7' DAY ,

DATE '2012-01-01' AND DATE '2012-12-31' EACH INTERVAL '7' DAY ,

DATE '2013-01-01' AND DATE '2013-12-31' EACH INTERVAL '7' DAY ,

DATE '2014-01-01' AND DATE '2014-12-31' EACH INTERVAL '7' DAY ,

DATE '2015-01-01' AND DATE '2015-12-31' EACH INTERVAL '7' DAY ),

RANGE_N(FISCAL_END_DATE BETWEEN DATE '2006-01-01' AND DATE '2006-12-31' EACH INTERVAL '7' DAY ,

DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '28' DAY ,

DATE '2008-01-01' AND DATE '2008-12-31' EACH INTERVAL '28' DAY ,

DATE '2009-01-01' AND DATE '2009-12-31' EACH INTERVAL '28' DAY ,

DATE '2010-01-01' AND DATE '2010-12-31' EACH INTERVAL '28' DAY ,

DATE '2011-01-01' AND DATE '2011-12-31' EACH INTERVAL '28' DAY ,

DATE '2012-01-01' AND DATE '2012-12-31' EACH INTERVAL '28' DAY ,

DATE '2013-01-01' AND DATE '2013-12-31' EACH INTERVAL '28' DAY ,

DATE '2014-01-01' AND DATE '2014-12-31' EACH INTERVAL '28' DAY ,

DATE '2015-01-01' AND DATE '2015-12-31' EACH INTERVAL '28' DAY ) )

UNIQUE INDEX TABLE1_USI ( DIM_1_ATTR ,DIM_2_ATTR ,DIM_3_ATTR ,

SALE_ATTR_DT1 ,SALE_ATTR_FLG1 ,SALE_ATTR_CODE1 ,DOC_NBR ,

DOC_TIME ,SALE_ATTR_DT2 );

Join index on this table looks like:

/* JOIN INDEX ON THE BASE TABLE*/

CREATE JOIN INDEX DATABASE1.AJI_TABLE1_FCD ,NO FALLBACK ,CHECKSUM = DEFAULT AS

SELECT COUNT(*)(FLOAT, NAMED COUNTSTAR ),DATABASE1.SALES.DIM_1_ATTR ,

DATABASE1.SALES.SALE_ATTR_DT1 ,DATABASE1.SALES.SALE_ATTR_FLG1 ,

DATABASE1.SALES.SALE_ATTR_CODE1 ,DATABASE1.SALES.SRCE_INDIC ,

DATABASE1.SALES.FISCAL_END_DATE ,SUM(DATABASE1.SALES.SALE_ATTR_AMT1 )(FLOAT, NAMED SALE_ATTR_AMT1 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY1 )(FLOAT, NAMED SALE_ATTR_QTY1 ),

SUM(DATABASE1.SALES.SALE_ATTR_WT1 )(FLOAT, NAMED SALE_ATTR_WT1 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT2 )(FLOAT, NAMED SALE_ATTR_AMT2 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY2 )(FLOAT, NAMED SALE_ATTR_QTY2 ),

SUM(DATABASE1.SALES.SALE_ATTR_WT2 )(FLOAT, NAMED SALE_ATTR_WT2 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT3 )(FLOAT, NAMED SALE_ATTR_AMT3 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY3 )(FLOAT, NAMED SALE_ATTR_QTY3 ),

SUM(DATABASE1.SALES.SALE_ATTR_WT3 )(FLOAT, NAMED SALE_ATTR_WT3 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT4 )(FLOAT, NAMED SALE_ATTR_AMT4 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY4 )(FLOAT, NAMED SALE_ATTR_QTY4 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT5 )(FLOAT, NAMED SALE_ATTR_AMT5 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY5 )(FLOAT, NAMED SALE_ATTR_QTY5 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT6 )(FLOAT, NAMED SALE_ATTR_AMT6 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT7 )(FLOAT, NAMED SALE_ATTR_AMT7 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT8 )(FLOAT, NAMED SALE_ATTR_AMT8 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT9 )(FLOAT, NAMED SALE_ATTR_AMT9 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY9 )(FLOAT, NAMED SALE_ATTR_QTY9 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT10 )(FLOAT, NAMED SALE_ATTR_AMT10 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY10 )(FLOAT, NAMED SALE_ATTR_QTY10 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT11 )(FLOAT, NAMED SALE_ATTR_AMT11 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY12 )(FLOAT, NAMED SALE_ATTR_QTY12 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT13 )(FLOAT, NAMED SALE_ATTR_AMT13 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT14 )(FLOAT, NAMED SALE_ATTR_AMT14 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY14 )(FLOAT, NAMED SALE_ATTR_QTY14 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT15 )(FLOAT, NAMED SALE_ATTR_AMT15 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY15 )(FLOAT, NAMED SALE_ATTR_QTY15 ),

SUM(DATABASE1.SALES.SALE_ATTR_AMT15 )(FLOAT, NAMED SALE_ATTR_AMT15 ),

SUM(DATABASE1.SALES.SALE_ATTR_QTY15 )(FLOAT, NAMED SALE_ATTR_QTY15 )

FROM DATABASE1.TABLE1 SALES

GROUP BY DATABASE1.SALES.DIM_1_ATTR ,DATABASE1.SALES.SALE_ATTR_DT1 ,

DATABASE1.SALES.SALE_ATTR_FLG1 ,DATABASE1.SALES.SALE_ATTR_CODE1 ,

DATABASE1.SALES.SRCE_INDIC ,DATABASE1.SALES.FISCAL_END_DATE

PRIMARY INDEX ( DIM_1_ATTR )

PARTITION BY RANGE_N(DATABASE1.SALES.FISCAL_END_DATE BETWEEN

DATE '2006-01-01' AND DATE '2006-12-31' EACH INTERVAL '28' DAY ,

DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '28' DAY ,

DATE '2008-01-01' AND DATE '2008-12-31' EACH INTERVAL '28' DAY ,

DATE '2009-01-01' AND DATE '2009-12-31' EACH INTERVAL '28' DAY ,

DATE '2010-01-01' AND DATE '2010-12-31' EACH INTERVAL '28' DAY ,

DATE '2011-01-01' AND DATE '2011-12-31' EACH INTERVAL '28' DAY ,

DATE '2012-01-01' AND DATE '2012-12-31' EACH INTERVAL '28' DAY ,

DATE '2013-01-01' AND DATE '2013-12-31' EACH INTERVAL '28' DAY ,

DATE '2014-01-01' AND DATE '2014-12-31' EACH INTERVAL '28' DAY ,

DATE '2015-01-01' AND DATE '2015-12-31' EACH INTERVAL '28' DAY );

Definition of the VIEWB looks like:

CREATE MULTISET TABLE TABLE_DATABASE1.TABLEB ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      YR_ID CHAR(4) CHARACTER SET LATIN CASESPECIFIC,
      PRD_ID CHAR(2) CHARACTER SET LATIN CASESPECIFIC,
      FISCAL_END_DATE DATE FORMAT 'MM/DD/YYYY')
UNIQUE PRIMARY INDEX ( FISCAL_END_DATE )
INDEX (  YR_ID,PRD_ID );

REPLACE VIEW VIEW_DATABASE1.VIEWB
(
 YR_ID
,PRD_ID
,FISCAL_END_DATE
)
AS LOCKING ROW FOR ACCESS
SELECT
 YR_ID
,PRD_ID
,FISCAL_END_DATE
FROM TABLE_DATABASE1.TABLEB
;

Explain looks like:

1) First, we lock TABLE_DATABASE1.TABLE2 in view
     VIEW_DATABASE1.VIEWA for access, we lock
     TABLE_DATABASE1.TABLE2 in view VIEW_DATABASE1.VIEWA
     for access, we lock TABLE_DATABASE1.TABLE3 in view
     VIEW_DATABASE1.VIEWA for access, we lock
     TABLE_DATABASE1.TABLE3 in view VIEW_DATABASE1.VIEWA
     for access, we lock TABLE_DATABASE1.TABLE2_REFERENCE in view
     VIEW_DATABASE1.VIEWA for access, we lock
     TABLE_DATABASE1.TABLE4 in view
     VIEW_DATABASE1.VIEWA for access, we lock
     TABLE_DATABASE_TRN.TABLE5 in view
     VIEW_DATABASE1.VIEWA for access, we lock
     TABLE_DATABASE2.TABLEB in view VIEW_DATABASE1.VIEWA for
     access, we lock SRC_TABLE_DATABASE1.TABLE1 in view
     VIEW_DATABASE1.VIEWA for access, we lock
     SRC_TABLE_DATABASE2.DAILY_TIME_TBL in view VIEW_DATABASE1.VIEWA
     for access, and we lock SRC_TABLE_DATABASE2.DLY_FUTR_TIME_TBL in view
     VIEW_DATABASE1.VIEWA for access.
  2) Next, we do an all-AMPs RETRIEVE step from TABLE_DATABASE2.TABLEB
     in view VIEW_DATABASE1.VIEWA by way of an all-rows scan
     with no residual conditions into Spool 16 (all_amps) (compressed
     columns allowed), which is duplicated on all AMPs.  The size of
     Spool 16 is estimated with high confidence to be 4,680 rows (
     79,560 bytes).  The estimated time for this step is 0.01 seconds.
  3) We execute the following steps in parallel.
       1) We do an all-AMPs JOIN step from Spool 16 by way of an
          all-rows scan, which is joined to TABLE_DATABASE1.TABLE2_REFERENCE in view
          VIEW_DATABASE1.VIEWA by way of an all-rows scan
          with no residual conditions.  Spool 16 and TABLE_DATABASE1.TABLE2_REFERENCE
          are joined using a product join, with a join condition of (
          "(FISCAL_END_DATE >= TABLE_DATABASE1.TABLE2_REFERENCE.EFF_START_DT) AND
          (FISCAL_END_DATE <= TABLE_DATABASE1.TABLE2_REFERENCE.EFF_END_DT)").  The result
          goes into Spool 11 (all_amps), which is redistributed by hash
          code to all AMPs.  The size of Spool 11 is estimated with low
          confidence to be 618,748 rows (30,318,652 bytes).  The
          estimated time for this step is 0.08 seconds.
       2) We do an all-AMPs RETRIEVE step from
          SRC_TABLE_DATABASE2.DAILY_TIME_TBL in view
          VIEW_DATABASE1.VIEWA by way of an all-rows scan
          with no residual conditions into Spool 9 (all_amps), which is
          redistributed by hash code to all AMPs.  The size of Spool 9
          is estimated with high confidence to be 1,128 rows (101,520
          bytes).  The estimated time for this step is 0.01 seconds.
  4) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from
          SRC_TABLE_DATABASE2.DLY_FUTR_TIME_TBL in view
          VIEW_DATABASE1.VIEWA by way of an all-rows scan
          with no residual conditions into Spool 9 (all_amps), which is
          redistributed by hash code to all AMPs.  Then we do a SORT to
          order Spool 9 by the sort key in spool field1 eliminating
          duplicate rows.  The size of Spool 9 is estimated with low
          confidence to be 1,691 rows (152,190 bytes).  The estimated
          time for this step is 0.01 seconds.
       2) We do a single-AMP RETRIEVE step from all partitions of
          TABLE_DATABASE2.FISCAL_TIMEFRAME in view VIEW_DATABASE1.VIEWA by
          way of the unique primary index "TABLE_DATABASE2.FISCAL_TIMEFRAME in view
          VIEW_DATABASE1.VIEWA.TIMEFRAME_ID = 20" extracting
          row ids only with no residual conditions locking row for
          access into Spool 19 (group_amps), which is built locally on
          that AMP.  The size of Spool 19 is estimated with high
          confidence to be 1 row.  The estimated time for this step is
          0.00 seconds.
  5) We do a single-AMP RETRIEVE step from all partitions of
     TABLE_DATABASE2.FISCAL_TIMEFRAME in view VIEW_DATABASE1.VIEWA by way
     of the unique primary index "TABLE_DATABASE2.FISCAL_TIMEFRAME in view
     VIEW_DATABASE1.VIEWA.TIMEFRAME_ID = 142" extracting row
     ids only with no residual conditions locking row for access into
     Spool 19 (group_amps), which is built locally on that AMP.  The
     size of Spool 19 is estimated with high confidence to be 2 rows.
     The estimated time for this step is 0.00 seconds.
  6) We do a group-AMP SORT to order Spool 19 (group_amps) by row id
     eliminating duplicate rows.  The estimated time for this step is
     0.00 seconds.
  7) We do a group-AMP RETRIEVE step from TABLE_DATABASE2.FISCAL_TIMEFRAME in view
     VIEW_DATABASE1.VIEWA by way of row ids from Spool 19
     (Last Use) with no residual conditions into Spool 20 (all_amps)
     (compressed columns allowed), which is duplicated on all AMPs.
     The size of Spool 20 is estimated with high confidence to be 360
     rows (7,560 bytes).  The estimated time for this step is 0.01
     seconds.
  8) We do an all-AMPs JOIN step from Spool 20 (Last Use) by way of an
     all-rows scan, which is joined to Spool 9 (Last Use) by way of an
     all-rows scan.  Spool 20 and Spool 9 are joined using a product
     join, with a join condition of ("(SALE_ATTR_DT1 >=
     BEGIN_TIMFR_DT) AND (SALE_ATTR_DT1 <= END_TIMFR_DT)").  The
     result goes into Spool 18 (all_amps) (compressed columns allowed),
     which is built locally on the AMPs.  The size of Spool 18 is
     estimated with no confidence to be 2,392 rows (59,800 bytes).  The
     estimated time for this step is 0.02 seconds.
  9) We do an all-AMPs SUM step to aggregate from Spool 18 (Last Use)
     by way of an all-rows scan, and the grouping identifier in field 1.
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 21.  The size of Spool 21 is estimated with no confidence
     to be 887 rows (29,271 bytes).  The estimated time for this step
     is 0.02 seconds.
 10) We do an all-AMPs RETRIEVE step from Spool 21 (Last Use) by way of
     an all-rows scan into Spool 10 (used to materialize view, derived
     table, table function or table operator PRD) (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 10 is estimated with no confidence to be 887
     rows (31,045 bytes).  The estimated time for this step is 0.01
     seconds.
 11) We do an all-AMPs RETRIEVE step from Spool 10 (Last Use) by way of
     an all-rows scan into Spool 23 (all_amps) (compressed columns
     allowed), which is duplicated on all AMPs.  The size of Spool 23
     is estimated with no confidence to be 159,660 rows (3,352,860
     bytes).  The estimated time for this step is 0.02 seconds.
 12) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from Spool 23 (Last Use) by way of
         an all-rows scan, which is joined to TABLE_DATABASE1.TABLE2_REFERENCE in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking TABLE_DATABASE1.TABLE2_REFERENCE for
         access.  Spool 23 and TABLE_DATABASE1.TABLE2_REFERENCE are joined using a
         product join, with a join condition of (
         "(TABLE_DATABASE1.TABLE2_REFERENCE.EFF_END_DT >= FINCL_STRT_DT) AND
         (TABLE_DATABASE1.TABLE2_REFERENCE.EFF_END_DT <= FISCAL_END_DATE)").  The result
         goes into Spool 11 (all_amps), which is redistributed by hash
         code to all AMPs.  The size of Spool 11 is estimated with no
         confidence to be 1,327,514 rows (65,048,186 bytes).  The
         estimated time for this step is 0.11 seconds.
      2) We do an all-AMPs RETRIEVE step from
         SRC_TABLE_DATABASE2.DAILY_TIME_TBL in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking for access into Spool 7
         (all_amps), which is redistributed by hash code to all AMPs.
         The size of Spool 7 is estimated with high confidence to be
         1,128 rows (101,520 bytes).  The estimated time for this step
         is 0.01 seconds.
 13) We execute the following steps in parallel.
      1) We do an all-AMPs RETRIEVE step from
         SRC_TABLE_DATABASE2.DLY_FUTR_TIME_TBL in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking for access into Spool 7
         (all_amps), which is redistributed by hash code to all AMPs.
         Then we do a SORT to order Spool 7 by the sort key in spool
         field1 eliminating duplicate rows.  The size of Spool 7 is
         estimated with low confidence to be 1,691 rows (152,190 bytes).
         The estimated time for this step is 0.01 seconds.
      2) We do a single-AMP RETRIEVE step from all partitions of
         TABLE_DATABASE2.FISCAL_TIMEFRAME in view VIEW_DATABASE1.VIEWA by
         way of the unique primary index "TABLE_DATABASE2.FISCAL_TIMEFRAME in view
         VIEW_DATABASE1.VIEWA.TIMEFRAME_ID = 20" extracting row
         ids only with no residual conditions locking row for access
         into Spool 26 (group_amps), which is built locally on that AMP.
         The size of Spool 26 is estimated with high confidence to be 1
         row.  The estimated time for this step is 0.00 seconds.
 14) We do a single-AMP RETRIEVE step from all partitions of
     TABLE_DATABASE2.FISCAL_TIMEFRAME in view VIEW_DATABASE1.VIEWA by way
     of the unique primary index "TABLE_DATABASE2.FISCAL_TIMEFRAME in view
     VIEW_DATABASE1.VIEWA.TIMEFRAME_ID = 142" extracting row
     ids only with no residual conditions locking row for access into
     Spool 26 (group_amps), which is built locally on that AMP.  The
     size of Spool 26 is estimated with high confidence to be 2 rows.
     The estimated time for this step is 0.00 seconds.
 15) We do a group-AMP SORT to order Spool 26 (group_amps) by row id
     eliminating duplicate rows.  The estimated time for this step is
     0.00 seconds.
 16) We do a group-AMP RETRIEVE step from TABLE_DATABASE2.FISCAL_TIMEFRAME in view
     VIEW_DATABASE1.VIEWA by way of row ids from Spool 26
     (Last Use) with no residual conditions into Spool 27 (all_amps)
     (compressed columns allowed), which is duplicated on all AMPs.
     The size of Spool 27 is estimated with high confidence to be 360
     rows (7,560 bytes).  The estimated time for this step is 0.01
     seconds.
 17) We do an all-AMPs JOIN step from Spool 27 (Last Use) by way of an
     all-rows scan, which is joined to Spool 7 (Last Use) by way of an
     all-rows scan.  Spool 27 and Spool 7 are joined using a product
     join, with a join condition of ("(SALE_ATTR_DT1 >=
     BEGIN_TIMFR_DT) AND (SALE_ATTR_DT1 <= END_TIMFR_DT)").  The
     result goes into Spool 25 (all_amps) (compressed columns allowed),
     which is built locally on the AMPs.  The size of Spool 25 is
     estimated with no confidence to be 2,392 rows (59,800 bytes).  The
     estimated time for this step is 0.02 seconds.
 18) We do an all-AMPs SUM step to aggregate from Spool 25 (Last Use)
     by way of an all-rows scan, and the grouping identifier in field 1.
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 28.  The size of Spool 28 is estimated with no confidence
     to be 887 rows (29,271 bytes).  The estimated time for this step
     is 0.02 seconds.
 19) We do an all-AMPs RETRIEVE step from Spool 28 (Last Use) by way of
     an all-rows scan into Spool 8 (used to materialize view, derived
     table, table function or table operator PRD) (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 8 is estimated with no confidence to be 887 rows
     (31,045 bytes).  The estimated time for this step is 0.01 seconds.
 20) We do an all-AMPs RETRIEVE step from Spool 8 (Last Use) by way of
     an all-rows scan into Spool 30 (all_amps) (compressed columns
     allowed), which is duplicated on all AMPs.  The size of Spool 30
     is estimated with no confidence to be 159,660 rows (3,352,860
     bytes).  The estimated time for this step is 0.02 seconds.
 21) We do an all-AMPs JOIN step from Spool 30 (Last Use) by way of an
     all-rows scan, which is joined to TABLE_DATABASE1.TABLE2_REFERENCE in view
     VIEW_DATABASE1.VIEWA by way of an all-rows scan with no
     residual conditions locking TABLE_DATABASE1.TABLE2_REFERENCE for access.  Spool
     30 and TABLE_DATABASE1.TABLE2_REFERENCE are joined using a product join, with a
     join condition of ("(TABLE_DATABASE1.TABLE2_REFERENCE.EFF_START_DT >=
     FISCAL_STRT_DATE) AND (TABLE_DATABASE1.TABLE2_REFERENCE.EFF_START_DT <= FISCAL_END_DATE)").
     The result goes into Spool 11 (all_amps), which is redistributed
     by hash code to all AMPs.  Then we do a SORT to order Spool 11 by
     the sort key in spool field1 eliminating duplicate rows.  The size
     of Spool 11 is estimated with no confidence to be 1,099,643 rows (
     53,882,507 bytes).  The estimated time for this step is 0.18
     seconds.
 22) We execute the following steps in parallel.
      1) We do an all-AMPs SUM step to aggregate from Spool 11 (Last
         Use) by way of an all-rows scan, and the grouping identifier
         in field 1.  Aggregate Intermediate Results are computed
         globally, then placed in Spool 32.  The size of Spool 32 is
         estimated with no confidence to be 824,733 rows (33,814,053
         bytes).  The estimated time for this step is 0.11 seconds.
      2) We do an all-AMPs JOIN step from Spool 16 by way of an
         all-rows scan, which is joined to TABLE_DATABASE1.TABLE2_REFERENCE in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking TABLE_DATABASE1.TABLE2_REFERENCE for
         access.  Spool 16 and TABLE_DATABASE1.TABLE2_REFERENCE are joined using a
         product join, with a join condition of ("(1=1)").  The result
         goes into Spool 35 (all_amps) (compressed columns allowed),
         which is redistributed by hash code to all AMPs.  The size of
         Spool 35 is estimated with high confidence to be 618,748 rows
         (314,323,984 bytes).  The estimated time for this step is 0.50
         seconds.
 23) We do an all-AMPs RETRIEVE step from Spool 32 (Last Use) by way of
     an all-rows scan into Spool 36 (all_amps) (compressed columns
     allowed), which is redistributed by hash code to all AMPs.  The
     size of Spool 36 is estimated with no confidence to be 824,733
     rows (20,618,325 bytes).  The estimated time for this step is 0.09
     seconds.
 24) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from Spool 35 by way of an
         all-rows scan, which is joined to Spool 36 (Last Use) by way
         of an all-rows scan.  Spool 35 and Spool 36 are joined using a
         single partition inclusion hash join, with a join condition of
         ("(FISCAL_END_DATE = FISCAL_END_DATE) AND ((DIM_3_ATTR = DIM_3_ATTR) AND
         (EFF_END_DT = Field_4 ))").  The result goes into Spool 12
         (all_amps), which is redistributed by hash code to all AMPs.
         The size of Spool 12 is estimated with no confidence to be
         618,748 rows (686,810,280 bytes).  The estimated time for this
         step is 0.54 seconds.
      2) We do an all-AMPs RETRIEVE step from
         TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions into Spool 38 (all_amps)
         (compressed columns allowed), which is duplicated on all AMPs.
         The size of Spool 38 is estimated with high confidence to be
         1,620 rows (840,780 bytes).  The estimated time for this step
         is 0.01 seconds.
 25) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from TABLE_DATABASE2.TABLEB in
         view VIEW_DATABASE1.VIEWA by way of an all-rows
         scan with no residual conditions, which is joined to Spool 38
         by way of an all-rows scan locking TABLE_DATABASE2.TABLEB for
         access.  TABLE_DATABASE2.TABLEB and Spool 38 are joined using
         a product join, with a join condition of (
         "(TABLE_DATABASE2.TABLEB.FISCAL_END_DATE >= EFF_START_DT) AND
         (TABLE_DATABASE2.TABLEB.FISCAL_END_DATE <= EFF_END_DT)").  The
         result goes into Spool 12 (all_amps), which is redistributed
         by hash code to all AMPs.  Then we do a SORT to order Spool 12
         by the sort key in spool field1 eliminating duplicate rows.
         The size of Spool 12 is estimated with no confidence to be
         96,000 rows (106,560,000 bytes).  The estimated time for this
         step is 0.18 seconds.
      2) We do an all-AMPs JOIN step from
         TABLE_DATABASE1.TABLE4 in view
         VIEW_DATABASE1.VIEWA (with temporal qualifier as
         "NONSEQUENCED TRANSACTIONTIME") by way of an all-rows scan
         with no residual conditions, which is joined to Spool 16 by
         way of an all-rows scan.
         TABLE_DATABASE1.TABLE4 and Spool 16 are
         joined using a product join, with a join condition of (
         "(FISCAL_END_DATE >= (CAST((BEGIN({LeftTable}.DW_TT )) AS DATE)))
         AND (FISCAL_END_DATE <= (CAST((END({LeftTable}.DW_TT )) AS
         DATE)))").  The result goes into Spool 43 (all_amps)
         (compressed columns allowed), which is built locally on the
         AMPs.  The size of Spool 43 is estimated with low confidence
         to be 1,100 rows (45,100 bytes).  The estimated time for this
         step is 0.02 seconds.
 26) We do an all-AMPs SUM step to aggregate from Spool 43 (Last Use)
     by way of an all-rows scan, and the grouping identifier in field 1.
     Aggregate Intermediate Results are computed locally, then placed
     in Spool 45.  The size of Spool 45 is estimated with low
     confidence to be 105 rows (3,045 bytes).  The estimated time for
     this step is 0.01 seconds.
 27) We execute the following steps in parallel.
      1) We do an all-AMPs RETRIEVE step from Spool 45 (Last Use) by
         way of an all-rows scan into Spool 47 (all_amps) (compressed
         columns allowed), which is redistributed by hash code to all
         AMPs.  Then we do a SORT to order Spool 47 by row hash.  The
         size of Spool 47 is estimated with low confidence to be 105
         rows (2,835 bytes).  The estimated time for this step is 0.00
         seconds.
      2) We do an all-AMPs RETRIEVE step from
         TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with a condition of ("(NOT (TABLE_DATABASE1.TABLE2 in
         view VIEW_DATABASE1.VIEWA.DIM_38_ATTR
         IS NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_39_ATTR IS
         NULL )) AND ((TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_4_ATTR = 'A ') AND
         (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_40_ATTR =
         '2')))") into Spool 48 (all_amps) (compressed columns allowed),
         which is duplicated on all AMPs.  The size of Spool 48 is
         estimated with low confidence to be 131,040 rows (3,669,120
         bytes).  The estimated time for this step is 0.02 seconds.
 28) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from
         TABLE_DATABASE1.TABLE4 in view
         VIEW_DATABASE1.VIEWA (with temporal qualifier as
         "NONSEQUENCED TRANSACTIONTIME") by way of an all-rows scan
         with a condition of ("NOT
         (TABLE_DATABASE1.TABLE4 in view
         VIEW_DATABASE1.VIEWA.DIM_36_ATTR IS NULL)"),
         which is joined to Spool 47 (Last Use) by way of an all-rows
         scan locking TABLE_DATABASE1.TABLE4 for
         access.  TABLE_DATABASE1.TABLE4 and Spool 47
         are joined using an inclusion merge join, with a join
         condition of (
         "(TABLE_DATABASE1.TABLE4.DIM_36_ATTR =
         DIM_36_ATTR) AND ((BEGIN({LeftTable}.DW_TT ))= Field_3)").
         The result goes into Spool 50 (all_amps) (compressed columns
         allowed), which is duplicated on all AMPs.  The size of Spool
         50 is estimated with low confidence to be 18,900 rows (
         774,900 bytes).  The estimated time for this step is 0.02
         seconds.
      2) We do an all-AMPs JOIN step from
         TABLE_DATABASE1.TABLE4 in view
         VIEW_DATABASE1.VIEWA (with temporal qualifier as
         "NONSEQUENCED TRANSACTIONTIME") by way of an all-rows scan
         with a condition of ("NOT
         (TABLE_DATABASE1.TABLE4 in view
         VIEW_DATABASE1.VIEWA.DIM_36_ATTR IS NULL)"),
         which is joined to Spool 16 by way of an all-rows scan locking
         TABLE_DATABASE1.TABLE4 for access.
         TABLE_DATABASE1.TABLE4 and Spool 16 are
         joined using a product join, with a join condition of (
         "(FISCAL_END_DATE <= (CAST((END({LeftTable}.DW_TT )) AS DATE)))
         AND (FISCAL_END_DATE >= (CAST((BEGIN({LeftTable}.DW_TT )) AS
         DATE)))").  The result goes into Spool 51 (all_amps)
         (compressed columns allowed), which is duplicated on all AMPs.
         The size of Spool 51 is estimated with low confidence to be
         198,000 rows (4,158,000 bytes).  The estimated time for this
         step is 0.02 seconds.
      3) We do an all-AMPs RETRIEVE step from
         TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with a condition of ("(NOT (TABLE_DATABASE1.TABLE2 in
         view VIEW_DATABASE1.VIEWA.DIM_38_ATTR
         IS NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_39_ATTR IS
         NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_37_ATTR IS
         NULL )) AND ((TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_40_ATTR
         (FLOAT, FORMAT '-9.99999999999999E-999'))= 3.00000000000000E
         000 )))") locking for access into Spool 52 (all_amps)
         (compressed columns allowed), which is duplicated on all AMPs.
         Then we do a SORT to order Spool 52 by row hash.  The size of
         Spool 52 is estimated with low confidence to be 180 rows (
         5,940 bytes).  The estimated time for this step is 0.01
         seconds.
 29) We do an all-AMPs JOIN step from Spool 48 (Last Use) by way of an
     all-rows scan, which is joined to TABLE_DATABASE1.TABLE3 in
     view VIEW_DATABASE1.VIEWA by way of an all-rows scan
     with a condition of ("NOT (TABLE_DATABASE1.TABLE3 in view
     VIEW_DATABASE1.VIEWA.DIM_3_ATTR IS NULL)").  Spool 48
     and TABLE_DATABASE1.TABLE3 are right outer joined using a
     dynamic hash join, with condition(s) used for non-matching on
     right table ("(NOT
     (TABLE_DATABASE1.TABLE3.DIM_39_ATTR IS NULL )) AND
     ((NOT (TABLE_DATABASE1.TABLE3.DIM_38_ATTR IS NULL
     )) AND (NOT (TABLE_DATABASE1.TABLE3.FISCAL_END_DATE IS NULL )))"),
     with a join condition of (
     "(TABLE_DATABASE1.TABLE3.DIM_39_ATTR =
     (DIM_39_ATTR )) AND
     ((TABLE_DATABASE1.TABLE3.DIM_38_ATTR =
     (DIM_38_ATTR )) AND
     (TABLE_DATABASE1.TABLE3.FISCAL_END_DATE = FISCAL_END_DATE ))").  The
     result goes into Spool 53 (all_amps) (compressed columns allowed),
     which is built locally on the AMPs.  Then we do a SORT to order
     Spool 53 by row hash.  The size of Spool 53 is estimated with low
     confidence to be 423,661 rows (19,488,406 bytes).  The estimated
     time for this step is 0.07 seconds.
 30) We do an all-AMPs JOIN step from Spool 52 (Last Use) by way of a
     RowHash match scan, which is joined to Spool 53 (Last Use) by way
     of a RowHash match scan.  Spool 52 and Spool 53 are right outer
     joined using a merge join, with condition(s) used for non-matching
     on right table ("(NOT (DIM_37_ATTR IS NULL )) AND
     ((NOT (DIM_39_ATTR IS NULL )) AND ((NOT
     (DIM_38_ATTR IS NULL )) AND (NOT (FISCAL_END_DATE IS NULL
     ))))"), with a join condition of ("((DIM_37_ATTR )=
     DIM_37_ATTR) AND (((DIM_39_ATTR )=
     DIM_39_ATTR) AND (((DIM_38_ATTR )=
     DIM_38_ATTR) AND (FISCAL_END_DATE = FISCAL_END_DATE )))").
     The result is split into Spool 56 (all_amps) with a condition of (
     "(DIM_3_ATTR, FISCAL_END_DATE) IN (:*)") to qualify rows matching
     skewed rows of the skewed relation and Spool 59 (all_amps) with
     remaining rows.  Spool 56 is duplicated on all AMPs.  The size of
     Spool 56 is estimated with low confidence to be 4,680 rows (
     210,600 bytes).  Spool 59 is built locally on the AMPs.  The size
     of Spool 59 is estimated with low confidence to be 423,636 rows (
     19,063,620 bytes).  The estimated time for this step is 0.03
     seconds.
 31) We do an all-AMPs JOIN step from Spool 51 by way of an all-rows
     scan, which is joined to TABLE_DATABASE1.TABLE3 in view
     VIEW_DATABASE1.VIEWA by way of an all-rows scan with no
     residual conditions.  Spool 51 and TABLE_DATABASE1.TABLE3 are right outer
     joined using a dynamic hash join, with condition(s) used for
     non-matching on right table ("(NOT (TABLE_DATABASE1.TABLE3.FISCAL_END_DATE IS
     NULL )) AND (NOT (TABLE_DATABASE1.TABLE3.DIM_36_ATTR IS NULL ))"),
     with a join condition of ("(DIM_36_ATTR =
     TABLE_DATABASE1.TABLE3.DIM_36_ATTR) AND (TABLE_DATABASE1.TABLE3.FISCAL_END_DATE =
     FISCAL_END_DATE)").  The result is split into Spool 60 (all_amps)
     with a condition of ("(DIM_6_ATTR, FISCAL_END_DATE) IN (:*)") to qualify
     skewed rows and Spool 63 (all_amps) with remaining rows.  Spool 60
     is built locally on the AMPs.  The size of Spool 60 is estimated
     with low confidence to be 859,187 rows (40,381,789 bytes).  Spool
     63 is redistributed by hash code to all AMPs.  The size of Spool
     63 is estimated with low confidence to be 14,151,271 rows (
     665,109,737 bytes).  The estimated time for this step is 2.21
     seconds.
 32) We execute the following steps in parallel.
      1) We do an all-AMPs RETRIEVE step from Spool 12 (Last Use) by
         way of an all-rows scan with a condition of ("(NOT
         (TABLE2.FISCAL_END_DATE IS NULL )) AND (NOT (TABLE2.FISCAL_END_DATE IS NULL
         ))") into Spool 64 (all_amps) (compressed columns allowed)
         fanned out into 11 hash join partitions, which is duplicated
         on all AMPs.  The size of Spool 64 is estimated with no
         confidence to be 17,280,000 rows (967,680,000 bytes).  The
         estimated time for this step is 0.48 seconds.
      2) We do an all-AMPs RETRIEVE step from
         SRC_TABLE_DATABASE1.TABLE1 in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with a condition of ("SRC_TABLE_DATABASE1.TABLE1 in view
         VIEW_DATABASE1.VIEWA.SRCE_INDIC = 'IND3 '") into
         Spool 65 (all_amps) (compressed columns allowed) fanned out
         into 11 hash join partitions, which is built locally on the
         AMPs.  The input table will not be cached in memory, but it is
         eligible for synchronized scanning.  The result spool file
         will not be cached in memory.  The size of Spool 65 is
         estimated with high confidence to be 732,398,060 rows (
         118,648,485,720 bytes).  The estimated time for this step is 1
         minute and 13 seconds.
 33) We do an all-AMPs JOIN step from Spool 56 (Last Use) by way of an
     all-rows scan, which is joined to Spool 60 (Last Use) by way of an
     all-rows scan.  Spool 56 and Spool 60 are joined using a single
     partition hash join, with a join condition of ("(NOT (( CASE WHEN
     ((DIM_3_ATTR = '03') AND (DIM_4_ATTR = 'S3')) THEN (DIM_5_ATTR) WHEN
     ((DIM_3_ATTR = '04') AND (DIM_4_ATTR = 'S4')) THEN (DIM_5_ATTR) WHEN
     ((DIM_4_ATTR = 'S ') AND ((DIM_3_ATTR <> '03') AND
     (DIM_3_ATTR <> '04'))) THEN (DIM_5_ATTR) ELSE (NULL) END )IS NULL ))
     AND ((DIM_6_ATTR = DIM_3_ATTR) AND (FISCAL_END_DATE = FISCAL_END_DATE ))").
     The result goes into Spool 66 (all_amps) fanned out into 12 hash
     join partitions, which is redistributed by hash code to all AMPs.
     The size of Spool 66 is estimated with low confidence to be
     859,187 rows (64,439,025 bytes).  The estimated time for this step
     is 0.01 seconds.
 34) We do an all-AMPs JOIN step from Spool 59 (Last Use) by way of an
     all-rows scan, which is joined to Spool 63 (Last Use) by way of an
     all-rows scan.  Spool 59 and Spool 63 are joined using a single
     partition hash join, with a join condition of ("(NOT (( CASE WHEN
     ((DIM_3_ATTR = '03') AND (DIM_4_ATTR = 'S3')) THEN (DIM_5_ATTR) WHEN
     ((DIM_3_ATTR = '04') AND (DIM_4_ATTR = 'S4')) THEN (DIM_5_ATTR) WHEN
     ((DIM_4_ATTR = 'S ') AND ((DIM_3_ATTR <> '03') AND
     (DIM_3_ATTR <> '04'))) THEN (DIM_5_ATTR) ELSE (NULL) END )IS NULL ))
     AND ((DIM_6_ATTR = DIM_3_ATTR) AND (FISCAL_END_DATE = FISCAL_END_DATE ))").
     The result goes into Spool 66 (all_amps) fanned out into 12 hash
     join partitions, which is redistributed by hash code to all AMPs.
     The size of Spool 66 is estimated with low confidence to be
     14,151,338 rows (1,061,350,350 bytes).  The estimated time for
     this step is 2.08 seconds.
 35) We do an all-AMPs JOIN step from Spool 64 (Last Use) by way of an
     all-rows scan, which is joined to Spool 65 (Last Use) by way of an
     all-rows scan.  Spool 64 and Spool 65 are joined using a hash join
     of 11 partitions, with a join condition of ("(FISCAL_END_DATE =
     FISCAL_END_DATE) AND ((PROD_SLBL = DIM_3_ATTR) AND (FISCAL_END_DATE =
     FISCAL_END_DATE ))").  The result goes into Spool 67 (all_amps)
     (compressed columns allowed) fanned out into 12 hash join
     partitions, which is built locally on the AMPs.  The size of Spool
     67 is estimated with no confidence to be 349,972,692 rows (
     69,644,565,708 bytes).  The estimated time for this step is 27.76
     seconds.
 36) We do an all-AMPs JOIN step from TABLE_DATABASE2.TABLEB in view
     VIEW_DATABASE1.VIEWA by way of an all-rows scan with no
     residual conditions, which is joined to Spool 50 (Last Use) by way
     of an all-rows scan locking TABLE_DATABASE2.TABLEB for access.
     TABLE_DATABASE2.TABLEB and Spool 50 are joined using a product
     join, with a join condition of (
     "(TABLE_DATABASE2.TABLEB.FISCAL_END_DATE >=
     (CAST((BEGIN({RightTable}.DW_TT )) AS DATE))) AND
     (TABLE_DATABASE2.TABLEB.FISCAL_END_DATE <=
     (CAST((END({RightTable}.DW_TT )) AS DATE)))").  The result goes
     into Spool 68 (all_amps) (compressed columns allowed), which is
     duplicated on all AMPs.  The size of Spool 68 is estimated with
     low confidence to be 148,500 rows (2,524,500 bytes).  The
     estimated time for this step is 0.02 seconds.
 37) We do an all-AMPs JOIN step from Spool 66 (Last Use) by way of an
     all-rows scan, which is joined to Spool 67 (Last Use) by way of an
     all-rows scan.  Spool 66 and Spool 67 are joined using a hash join
     of 12 partitions, with a join condition of ("(FISCAL_END_DATE =
     FISCAL_END_DATE) AND ((FISCAL_END_DATE = FISCAL_END_DATE) AND
     ((FISCAL_END_DATE = FISCAL_END_DATE) AND ((FISCAL_END_DATE = FISCAL_END_DATE)
     AND ((DIM_2_ATTR = DIM_2_ATTR) AND (FISCAL_END_DATE = FISCAL_END_DATE )))))").
     The result goes into Spool 69 (all_amps) (compressed columns
     allowed), which is built locally on the AMPs.  The result spool
     file will not be cached in memory.  The size of Spool 69 is
     estimated with no confidence to be 386,408,351 rows (
     96,215,679,399 bytes).  The estimated time for this step is 46.47
     seconds.
 38) We do an all-AMPs JOIN step from Spool 68 by way of an all-rows
     scan, which is joined to Spool 69 (Last Use) by way of an all-rows
     scan.  Spool 68 and Spool 69 are right outer joined using a single
     partition hash join, with condition(s) used for non-matching on
     right table ("NOT (DIM_36_ATTR IS NULL)"), with a join
     condition of ("DIM_36_ATTR = DIM_36_ATTR").  The result
     goes into Spool 40 (all_amps) (compressed columns allowed), which
     is built locally on the AMPs.  The result spool file will not be
     cached in memory.  The size of Spool 40 is estimated with no
     confidence to be 3,033,549,318 rows (767,487,977,454 bytes).  The
     estimated time for this step is 3 minutes and 35 seconds.
 39) We do an all-AMPs SUM step to aggregate from Spool 40 (Last Use)
     by way of an all-rows scan, and the grouping identifier in field 1.
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 72.  The aggregate spool file will not be cached in
     memory.  The size of Spool 72 is estimated with no confidence to
     be 2,275,161,989 rows (1,233,137,798,038 bytes).  The estimated
     time for this step is 1 hour and 26 minutes.
 40) We execute the following steps in parallel.
      1) We do an all-AMPs RETRIEVE step from Spool 72 (Last Use) by
         way of an all-rows scan into Spool 13 (all_amps), which is
         redistributed by hash code to all AMPs.  The result spool file
         will not be cached in memory.  The size of Spool 13 is
         estimated with no confidence to be 2,275,161,989 rows (
         1,895,209,936,837 bytes).  The estimated time for this step is
         16 minutes and 35 seconds.
      2) We do an all-AMPs JOIN step from Spool 16 (Last Use) by way of
         an all-rows scan, which is joined to TABLE_DATABASE1.TABLE2_REFERENCE in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking TABLE_DATABASE1.TABLE2_REFERENCE for
         access.  Spool 16 and TABLE_DATABASE1.TABLE2_REFERENCE are joined using a
         product join, with a join condition of ("(FISCAL_END_DATE >=
         TABLE_DATABASE1.TABLE2_REFERENCE.EFF_START_DT) AND (FISCAL_END_DATE <=
         TABLE_DATABASE1.TABLE2_REFERENCE.EFF_END_DT)").  The result goes into Spool 5
         (all_amps), which is redistributed by hash code to all AMPs.
         The size of Spool 5 is estimated with low confidence to be
         618,748 rows (30,318,652 bytes).  The estimated time for this
         step is 0.08 seconds.
      3) We do an all-AMPs RETRIEVE step from
         SRC_TABLE_DATABASE2.DAILY_TIME_TBL in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking for access into Spool 3
         (all_amps), which is redistributed by hash code to all AMPs.
         The size of Spool 3 is estimated with high confidence to be
         1,128 rows (101,520 bytes).  The estimated time for this step
         is 0.01 seconds.
 41) We execute the following steps in parallel.
      1) We do an all-AMPs RETRIEVE step from
         SRC_TABLE_DATABASE2.DLY_FUTR_TIME_TBL in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking for access into Spool 3
         (all_amps), which is redistributed by hash code to all AMPs.
         Then we do a SORT to order Spool 3 by the sort key in spool
         field1 eliminating duplicate rows.  The size of Spool 3 is
         estimated with low confidence to be 1,691 rows (152,190 bytes).
         The estimated time for this step is 0.01 seconds.
      2) We do a single-AMP RETRIEVE step from all partitions of
         TABLE_DATABASE2.FISCAL_TIMEFRAME in view VIEW_DATABASE1.VIEWA by
         way of the unique primary index "TABLE_DATABASE2.FISCAL_TIMEFRAME in view
         VIEW_DATABASE1.VIEWA.TIMEFRAME_ID = 20" extracting row
         ids only with no residual conditions locking row for access
         into Spool 78 (group_amps), which is built locally on that AMP.
         The size of Spool 78 is estimated with high confidence to be 1
         row.  The estimated time for this step is 0.00 seconds.
 42) We do a single-AMP RETRIEVE step from all partitions of
     TABLE_DATABASE2.FISCAL_TIMEFRAME in view VIEW_DATABASE1.VIEWA by way
     of the unique primary index "TABLE_DATABASE2.FISCAL_TIMEFRAME in view
     VIEW_DATABASE1.VIEWA.TIMEFRAME_ID = 142" extracting row
     ids only with no residual conditions locking row for access into
     Spool 78 (group_amps), which is built locally on that AMP.  The
     size of Spool 78 is estimated with high confidence to be 2 rows.
     The estimated time for this step is 0.00 seconds.
 43) We do a group-AMP SORT to order Spool 78 (group_amps) by row id
     eliminating duplicate rows.  The estimated time for this step is
     0.00 seconds.
 44) We do a group-AMP RETRIEVE step from TABLE_DATABASE2.FISCAL_TIMEFRAME in view
     VIEW_DATABASE1.VIEWA by way of row ids from Spool 78
     (Last Use) with no residual conditions into Spool 79 (all_amps)
     (compressed columns allowed), which is duplicated on all AMPs.
     The size of Spool 79 is estimated with high confidence to be 360
     rows (7,560 bytes).  The estimated time for this step is 0.01
     seconds.
 45) We do an all-AMPs JOIN step from Spool 79 (Last Use) by way of an
     all-rows scan, which is joined to Spool 3 (Last Use) by way of an
     all-rows scan.  Spool 79 and Spool 3 are joined using a product
     join, with a join condition of ("(SALE_ATTR_DT1 >=
     BEGIN_TIMFR_DT) AND (SALE_ATTR_DT1 <= END_TIMFR_DT)").  The
     result goes into Spool 77 (all_amps) (compressed columns allowed),
     which is built locally on the AMPs.  The size of Spool 77 is
     estimated with no confidence to be 2,392 rows (59,800 bytes).  The
     estimated time for this step is 0.02 seconds.
 46) We do an all-AMPs SUM step to aggregate from Spool 77 (Last Use)
     by way of an all-rows scan, and the grouping identifier in field 1.
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 80.  The size of Spool 80 is estimated with no confidence
     to be 887 rows (29,271 bytes).  The estimated time for this step
     is 0.02 seconds.
 47) We do an all-AMPs RETRIEVE step from Spool 80 (Last Use) by way of
     an all-rows scan into Spool 4 (used to materialize view, derived
     table, table function or table operator PRD) (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 4 is estimated with no confidence to be 887 rows
     (31,045 bytes).  The estimated time for this step is 0.01 seconds.
 48) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
     an all-rows scan into Spool 82 (all_amps) (compressed columns
     allowed), which is duplicated on all AMPs.  The size of Spool 82
     is estimated with no confidence to be 159,660 rows (3,352,860
     bytes).  The estimated time for this step is 0.02 seconds.
 49) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from Spool 82 (Last Use) by way of
         an all-rows scan, which is joined to TABLE_DATABASE1.TABLE2_REFERENCE in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking TABLE_DATABASE1.TABLE2_REFERENCE for
         access.  Spool 82 and TABLE_DATABASE1.TABLE2_REFERENCE are joined using a
         product join, with a join condition of (
         "(TABLE_DATABASE1.TABLE2_REFERENCE.EFF_END_DT >= FISCAL_STRT_DATE) AND
         (TABLE_DATABASE1.TABLE2_REFERENCE.EFF_END_DT <= FISCAL_END_DATE)").  The result
         goes into Spool 5 (all_amps), which is redistributed by hash
         code to all AMPs.  The size of Spool 5 is estimated with no
         confidence to be 1,327,514 rows (65,048,186 bytes).  The
         estimated time for this step is 0.11 seconds.
      2) We do an all-AMPs RETRIEVE step from
         SRC_TABLE_DATABASE2.DAILY_TIME_TBL in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking for access into Spool 1
         (all_amps), which is redistributed by hash code to all AMPs.
         The size of Spool 1 is estimated with high confidence to be
         1,128 rows (101,520 bytes).  The estimated time for this step
         is 0.01 seconds.
 50) We execute the following steps in parallel.
      1) We do an all-AMPs RETRIEVE step from
         SRC_TABLE_DATABASE2.DLY_FUTR_TIME_TBL in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with no residual conditions locking for access into Spool 1
         (all_amps), which is redistributed by hash code to all AMPs.
         Then we do a SORT to order Spool 1 by the sort key in spool
         field1 eliminating duplicate rows.  The size of Spool 1 is
         estimated with low confidence to be 1,691 rows (152,190 bytes).
         The estimated time for this step is 0.01 seconds.
      2) We do a single-AMP RETRIEVE step from all partitions of
         TABLE_DATABASE2.FISCAL_TIMEFRAME in view VIEW_DATABASE1.VIEWA by
         way of the unique primary index "TABLE_DATABASE2.FISCAL_TIMEFRAME in view
         VIEW_DATABASE1.VIEWA.TIMEFRAME_ID = 20" extracting row
         ids only with no residual conditions locking row for access
         into Spool 85 (group_amps), which is built locally on that AMP.
         The size of Spool 85 is estimated with high confidence to be 1
         row.  The estimated time for this step is 0.00 seconds.
 51) We do a single-AMP RETRIEVE step from all partitions of
     TABLE_DATABASE2.FISCAL_TIMEFRAME in view VIEW_DATABASE1.VIEWA by way
     of the unique primary index "TABLE_DATABASE2.FISCAL_TIMEFRAME in view
     VIEW_DATABASE1.VIEWA.TIMEFRAME_ID = 142" extracting row
     ids only with no residual conditions locking row for access into
     Spool 85 (group_amps), which is built locally on that AMP.  The
     size of Spool 85 is estimated with high confidence to be 2 rows.
     The estimated time for this step is 0.00 seconds.
 52) We do a group-AMP SORT to order Spool 85 (group_amps) by row id
     eliminating duplicate rows.  The estimated time for this step is
     0.00 seconds.
 53) We do a group-AMP RETRIEVE step from TABLE_DATABASE2.FISCAL_TIMEFRAME in view
     VIEW_DATABASE1.VIEWA by way of row ids from Spool 85
     (Last Use) with no residual conditions into Spool 86 (all_amps)
     (compressed columns allowed), which is duplicated on all AMPs.
     The size of Spool 86 is estimated with high confidence to be 360
     rows (7,560 bytes).  The estimated time for this step is 0.01
     seconds.
 54) We do an all-AMPs JOIN step from Spool 86 (Last Use) by way of an
     all-rows scan, which is joined to Spool 1 (Last Use) by way of an
     all-rows scan.  Spool 86 and Spool 1 are joined using a product
     join, with a join condition of ("(SALE_ATTR_DT1 >=
     BEGIN_TIMFR_DT) AND (SALE_ATTR_DT1 <= END_TIMFR_DT)").  The
     result goes into Spool 84 (all_amps) (compressed columns allowed),
     which is built locally on the AMPs.  The size of Spool 84 is
     estimated with no confidence to be 2,392 rows (59,800 bytes).  The
     estimated time for this step is 0.02 seconds.
 55) We do an all-AMPs SUM step to aggregate from Spool 84 (Last Use)
     by way of an all-rows scan, and the grouping identifier in field 1.
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 87.  The size of Spool 87 is estimated with no confidence
     to be 887 rows (29,271 bytes).  The estimated time for this step
     is 0.02 seconds.
 56) We do an all-AMPs RETRIEVE step from Spool 87 (Last Use) by way of
     an all-rows scan into Spool 2 (used to materialize view, derived
     table, table function or table operator PRD) (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 2 is estimated with no confidence to be 887 rows
     (31,045 bytes).  The estimated time for this step is 0.01 seconds.
 57) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
     an all-rows scan into Spool 89 (all_amps) (compressed columns
     allowed), which is duplicated on all AMPs.  The size of Spool 89
     is estimated with no confidence to be 159,660 rows (3,352,860
     bytes).  The estimated time for this step is 0.02 seconds.
 58) We do an all-AMPs JOIN step from Spool 89 (Last Use) by way of an
     all-rows scan, which is joined to TABLE_DATABASE1.TABLE2_REFERENCE in view
     VIEW_DATABASE1.VIEWA by way of an all-rows scan with no
     residual conditions locking TABLE_DATABASE1.TABLE2_REFERENCE for access.  Spool
     89 and TABLE_DATABASE1.TABLE2_REFERENCE are joined using a product join, with a
     join condition of ("(TABLE_DATABASE1.TABLE2_REFERENCE.EFF_START_DT >=
     FISCAL_STRT_DATE) AND (TABLE_DATABASE1.TABLE2_REFERENCE.EFF_START_DT <= FISCAL_END_DATE)").
     The result goes into Spool 5 (all_amps), which is redistributed by
     hash code to all AMPs.  Then we do a SORT to order Spool 5 by the
     sort key in spool field1 eliminating duplicate rows.  The size of
     Spool 5 is estimated with no confidence to be 1,099,643 rows (
     53,882,507 bytes).  The estimated time for this step is 0.18
     seconds.
 59) We do an all-AMPs SUM step to aggregate from Spool 5 (Last Use) by
     way of an all-rows scan, and the grouping identifier in field 1.
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 91.  The size of Spool 91 is estimated with no confidence
     to be 824,733 rows (33,814,053 bytes).  The estimated time for
     this step is 0.11 seconds.
 60) We do an all-AMPs RETRIEVE step from Spool 91 (Last Use) by way of
     an all-rows scan into Spool 95 (all_amps) (compressed columns
     allowed), which is redistributed by hash code to all AMPs.  The
     size of Spool 95 is estimated with no confidence to be 824,733
     rows (20,618,325 bytes).  The estimated time for this step is 0.09
     seconds.
 61) We do an all-AMPs JOIN step from Spool 35 (Last Use) by way of an
     all-rows scan, which is joined to Spool 95 (Last Use) by way of an
     all-rows scan.  Spool 35 and Spool 95 are joined using a single
     partition inclusion hash join, with a join condition of (
     "(FISCAL_END_DATE = FISCAL_END_DATE) AND ((DIM_3_ATTR = DIM_3_ATTR) AND
     (EFF_END_DT = Field_4 ))").  The result goes into Spool 6
     (all_amps), which is redistributed by hash code to all AMPs.  The
     size of Spool 6 is estimated with no confidence to be 618,748 rows
     (686,810,280 bytes).  The estimated time for this step is 0.54
     seconds.
 62) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from TABLE_DATABASE2.TABLEB in
         view VIEW_DATABASE1.VIEWA by way of an all-rows
         scan with no residual conditions, which is joined to Spool 38
         (Last Use) by way of an all-rows scan locking
         TABLE_DATABASE2.TABLEB for access.  TABLE_DATABASE2.TABLEB and
         Spool 38 are joined using a product join, with a join
         condition of ("(TABLE_DATABASE2.TABLEB.FISCAL_END_DATE >=
         EFF_START_DT) AND (TABLE_DATABASE2.TABLEB.FISCAL_END_DATE <=
         EFF_END_DT)").  The result goes into Spool 6 (all_amps), which
         is redistributed by hash code to all AMPs.  Then we do a SORT
         to order Spool 6 by the sort key in spool field1 eliminating
         duplicate rows.  The size of Spool 6 is estimated with no
         confidence to be 96,000 rows (106,560,000 bytes).  The
         estimated time for this step is 0.18 seconds.
      2) We do an all-AMPs RETRIEVE step from
         TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with a condition of ("(NOT (TABLE_DATABASE1.TABLE2 in
         view VIEW_DATABASE1.VIEWA.DIM_38_ATTR
         IS NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_39_ATTR IS
         NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_37_ATTR IS
         NULL )) AND ((TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_4_ATTR = 'S4') AND
         (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_40_ATTR =
         '3'))))") locking for access into Spool 105 (all_amps)
         (compressed columns allowed), which is duplicated on all AMPs.
         The size of Spool 105 is estimated with low confidence to be
         28,080 rows (870,480 bytes).  The estimated time for this step
         is 0.02 seconds.
      3) We do an all-AMPs RETRIEVE step from
         TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with a condition of ("(NOT (TABLE_DATABASE1.TABLE2 in
         view VIEW_DATABASE1.VIEWA.DIM_38_ATTR
         IS NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_39_ATTR IS
         NULL )) AND ((TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_4_ATTR = 'A ') AND
         (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_40_ATTR =
         '2')))") locking for access into Spool 106 (all_amps)
         (compressed columns allowed), which is duplicated on all AMPs.
         Then we do a SORT to order Spool 106 by row hash.  The size of
         Spool 106 is estimated with low confidence to be 131,040 rows
         (3,669,120 bytes).  The estimated time for this step is 0.01
         seconds.
 63) We do an all-AMPs JOIN step from Spool 105 (Last Use) by way of an
     all-rows scan, which is joined to TABLE_DATABASE1.TABLE3 in
     view VIEW_DATABASE1.VIEWA by way of an all-rows scan
     with a condition of ("NOT (TABLE_DATABASE1.TABLE3 in view
     VIEW_DATABASE1.VIEWA.DIM_3_ATTR IS NULL)") locking
     TABLE_DATABASE1.TABLE3 for access.  Spool 105 and
     TABLE_DATABASE1.TABLE3 are right outer joined using a dynamic
     hash join, with condition(s) used for non-matching on right table
     ("(NOT (TABLE_DATABASE1.TABLE3.DIM_37_ATTR IS NULL
     )) AND ((NOT (TABLE_DATABASE1.TABLE3.DIM_39_ATTR
     IS NULL )) AND ((NOT
     (TABLE_DATABASE1.TABLE3.DIM_38_ATTR IS NULL )) AND
     (NOT (TABLE_DATABASE1.TABLE3.FISCAL_END_DATE IS NULL ))))"), with
     a join condition of (
     "(TABLE_DATABASE1.TABLE3.DIM_37_ATTR =
     (DIM_37_ATTR )) AND
     ((TABLE_DATABASE1.TABLE3.DIM_39_ATTR =
     (DIM_39_ATTR )) AND
     ((TABLE_DATABASE1.TABLE3.DIM_38_ATTR =
     (DIM_38_ATTR )) AND
     (TABLE_DATABASE1.TABLE3.FISCAL_END_DATE = FISCAL_END_DATE )))").
     The result goes into Spool 107 (all_amps) (compressed columns
     allowed), which is built locally on the AMPs.  Then we do a SORT
     to order Spool 107 by row hash.  The size of Spool 107 is
     estimated with low confidence to be 423,661 rows (19,488,406
     bytes).  The estimated time for this step is 0.07 seconds.
 64) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from Spool 106 (Last Use) by way
         of a RowHash match scan, which is joined to Spool 107 (Last
         Use) by way of a RowHash match scan.  Spool 106 and Spool 107
         are right outer joined using a merge join, with condition(s)
         used for non-matching on right table ("(NOT
         (DIM_39_ATTR IS NULL )) AND ((NOT
         (DIM_38_ATTR IS NULL )) AND (NOT (FISCAL_END_DATE IS
         NULL )))"), with a join condition of (
         "(DIM_39_ATTR = (DIM_39_ATTR )) AND
         ((DIM_38_ATTR = (DIM_38_ATTR )) AND
         (FISCAL_END_DATE = FISCAL_END_DATE ))").  The result goes into Spool
         110 (all_amps) (compressed columns allowed), which is built
         locally on the AMPs.  Then we do a SORT to order Spool 110 by
         row hash.  The size of Spool 110 is estimated with low
         confidence to be 423,662 rows (21,606,762 bytes).  The
         estimated time for this step is 0.03 seconds.
      2) We do an all-AMPs RETRIEVE step from
         TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with a condition of ("(NOT (TABLE_DATABASE1.TABLE2 in
         view VIEW_DATABASE1.VIEWA.DIM_38_ATTR
         IS NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_39_ATTR IS
         NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_37_ATTR IS
         NULL )) AND ((TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_4_ATTR = 'A2') AND
         (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_40_ATTR =
         '3'))))") locking for access into Spool 113 (all_amps)
         (compressed columns allowed), which is duplicated on all AMPs.
         Then we do a SORT to order Spool 113 by row hash.  The size of
         Spool 113 is estimated with low confidence to be 28,080 rows (
         870,480 bytes).  The estimated time for this step is 0.01
         seconds.
 65) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from Spool 110 (Last Use) by way
         of a RowHash match scan, which is joined to Spool 113 (Last
         Use) by way of a RowHash match scan.  Spool 110 and Spool 113
         are left outer joined using a merge join, with condition(s)
         used for non-matching on left table ("(NOT
         (DIM_37_ATTR IS NULL )) AND ((NOT
         (DIM_39_ATTR IS NULL )) AND ((NOT
         (DIM_38_ATTR IS NULL )) AND (NOT (FISCAL_END_DATE IS
         NULL ))))"), with a join condition of (
         "(DIM_37_ATTR = (DIM_37_ATTR )) AND
         ((DIM_39_ATTR = (DIM_39_ATTR )) AND
         ((DIM_38_ATTR = (DIM_38_ATTR )) AND
         (FISCAL_END_DATE = FISCAL_END_DATE )))").  The result goes into
         Spool 114 (all_amps) (compressed columns allowed), which is
         built locally on the AMPs.  Then we do a SORT to order Spool
         114 by row hash.  The size of Spool 114 is estimated with low
         confidence to be 423,663 rows (23,725,128 bytes).  The
         estimated time for this step is 0.03 seconds.
      2) We do an all-AMPs RETRIEVE step from
         TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with a condition of ("(NOT (TABLE_DATABASE1.TABLE2 in
         view VIEW_DATABASE1.VIEWA.DIM_38_ATTR
         IS NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_39_ATTR IS
         NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_37_ATTR IS
         NULL )) AND ((TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_4_ATTR = 'S3') AND
         (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_40_ATTR =
         '3'))))") locking for access into Spool 115 (all_amps)
         (compressed columns allowed), which is duplicated on all AMPs.
         Then we do a SORT to order Spool 115 by row hash.  The size of
         Spool 115 is estimated with low confidence to be 239,040 rows
         (7,410,240 bytes).  The estimated time for this step is 0.02
         seconds.
 66) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from Spool 114 (Last Use) by way
         of a RowHash match scan, which is joined to Spool 115 (Last
         Use) by way of a RowHash match scan.  Spool 114 and Spool 115
         are left outer joined using a merge join, with condition(s)
         used for non-matching on left table ("(NOT
         (DIM_37_ATTR IS NULL )) AND ((NOT
         (DIM_39_ATTR IS NULL )) AND ((NOT
         (DIM_38_ATTR IS NULL )) AND (NOT (FISCAL_END_DATE IS
         NULL ))))"), with a join condition of (
         "(DIM_37_ATTR = (DIM_37_ATTR )) AND
         ((DIM_39_ATTR = (DIM_39_ATTR )) AND
         ((DIM_38_ATTR = (DIM_38_ATTR )) AND
         (FISCAL_END_DATE = FISCAL_END_DATE )))").  The result goes into
         Spool 119 (all_amps) (compressed columns allowed), which is
         built locally on the AMPs.  Then we do a SORT to order Spool
         119 by row hash.  The size of Spool 119 is estimated with low
         confidence to be 423,664 rows (25,843,504 bytes).  The
         estimated time for this step is 0.04 seconds.
      2) We do an all-AMPs RETRIEVE step from
         TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA by way of an all-rows scan
         with a condition of ("(NOT (TABLE_DATABASE1.TABLE2 in
         view VIEW_DATABASE1.VIEWA.DIM_38_ATTR
         IS NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_39_ATTR IS
         NULL )) AND ((NOT (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_37_ATTR IS
         NULL )) AND ((TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_4_ATTR = 'S ') AND
         (TABLE_DATABASE1.TABLE2 in view
         VIEW_DATABASE1.VIEWA.DIM_40_ATTR =
         '3'))))") locking for access into Spool 120 (all_amps)
         (compressed columns allowed), which is duplicated on all AMPs.
         Then we do a SORT to order Spool 120 by row hash.  The size of
         Spool 120 is estimated with low confidence to be 589,680 rows
         (18,280,080 bytes).  The estimated time for this step is 0.02
         seconds.
 67) We do an all-AMPs JOIN step from Spool 119 (Last Use) by way of a
     RowHash match scan, which is joined to Spool 120 (Last Use) by way
     of a RowHash match scan.  Spool 119 and Spool 120 are left outer
     joined using a merge join, with condition(s) used for non-matching
     on left table ("(NOT (DIM_37_ATTR IS NULL )) AND ((NOT
     (DIM_39_ATTR IS NULL )) AND ((NOT
     (DIM_38_ATTR IS NULL )) AND (NOT (FISCAL_END_DATE IS NULL
     ))))"), with a join condition of ("(DIM_37_ATTR =
     (DIM_37_ATTR )) AND ((DIM_39_ATTR =
     (DIM_39_ATTR )) AND ((DIM_38_ATTR =
     (DIM_38_ATTR )) AND (FISCAL_END_DATE = FISCAL_END_DATE )))").
     The result goes into Spool 121 (all_amps) (compressed columns
     allowed), which is built locally on the AMPs.  The size of Spool
     121 is estimated with low confidence to be 423,665 rows (
     24,572,570 bytes).  The estimated time for this step is 0.03
     seconds.
 68) We do an all-AMPs JOIN step from Spool 51 (Last Use) by way of an
     all-rows scan, which is joined to TABLE_DATABASE1.TABLE3 in view
     VIEW_DATABASE1.VIEWA by way of an all-rows scan with no
     residual conditions locking TABLE_DATABASE1.TABLE3 for access.  Spool 51
     and TABLE_DATABASE1.TABLE3 are right outer joined using a dynamic hash join,
     with condition(s) used for non-matching on right table ("(NOT
     (TABLE_DATABASE1.TABLE3.FISCAL_END_DATE IS NULL )) AND (NOT
     (TABLE_DATABASE1.TABLE3.DIM_36_ATTR IS NULL ))"), with a join
     condition of ("(DIM_36_ATTR = TABLE_DATABASE1.TABLE3.DIM_36_ATTR)
     AND (TABLE_DATABASE1.TABLE3.FISCAL_END_DATE = FISCAL_END_DATE)").  The result
     goes into Spool 122 (all_amps) (compressed columns allowed), which
     is redistributed by hash code to all AMPs.  The size of Spool 122
     is estimated with low confidence to be 15,010,458 rows (
     705,491,526 bytes).  The estimated time for this step is 2.74
     seconds.
 69) We do an all-AMPs RETRIEVE step from
     TABLE_DATABASE_TRN.TABLE5 in view
     VIEW_DATABASE1.VIEWA by way of an all-rows scan with no
     residual conditions into Spool 125 (all_amps) (compressed columns
     allowed) fanned out into 4 hash join partitions, which is
     redistributed by hash code to all AMPs.  The size of Spool 125 is
     estimated with high confidence to be 14,175,926 rows (
     1,275,833,340 bytes).  The estimated time for this step is 2.03
     seconds.
 70) We do an all-AMPs JOIN step from Spool 121 (Last Use) by way of an
     all-rows scan, which is joined to Spool 122 (Last Use) by way of
     an all-rows scan.  Spool 121 and Spool 122 are joined using a
     single partition hash join, with a join condition of (
     "(FISCAL_END_DATE = FISCAL_END_DATE) AND ((DIM_3_ATTR = DIM_6_ATTR) AND
     ((FISCAL_END_DATE = FISCAL_END_DATE) AND ((NOT (( CASE WHEN (NOT (DIM_5_ATTR IS
     NULL )) THEN (DIM_5_ATTR) ELSE (DIM_5_ATTR) END )IS NULL )) OR ((NOT (( CASE WHEN
     (DIM_3_ATTR = '03') THEN (DIM_5_ATTR) WHEN (DIM_3_ATTR = '04')
     THEN (DIM_5_ATTR) WHEN ((DIM_3_ATTR <> '03') AND (DIM_3_ATTR <>
     '04')) THEN (DIM_5_ATTR) ELSE (NULL) END )IS NULL )) OR (NOT (DIM_5_ATTR IS NULL
     ))))))").  The result goes into Spool 126 (all_amps) (compressed
     columns allowed) fanned out into 4 hash join partitions, which is
     redistributed by hash code to all AMPs.  The size of Spool 126 is
     estimated with low confidence to be 15,010,636 rows (
     1,335,946,604 bytes).  The estimated time for this step is 2.41
     seconds.
 71) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
     an all-rows scan with a condition of ("NOT (TABLE2.FISCAL_END_DATE IS
     NULL)") into Spool 127 (all_amps) (compressed columns allowed)
     fanned out into 10 hash join partitions, which is duplicated on
     all AMPs.  The size of Spool 127 is estimated with no confidence
     to be 17,280,000 rows (933,120,000 bytes).  The estimated time for
     this step is 0.46 seconds.
 72) We do an all-AMPs JOIN step from Spool 125 (Last Use) by way of an
     all-rows scan, which is joined to Spool 126 (Last Use) by way of
     an all-rows scan.  Spool 125 and Spool 126 are joined using a hash
     join of 4 partitions, with a join condition of ("(FISCAL_END_DATE =
     FISCAL_END_DATE) AND ((DIM_2_ATTR = DIM_2_ATTR) AND ((FISCAL_END_DATE =
     FISCAL_END_DATE) AND (FISCAL_END_DATE = FISCAL_END_DATE )))").  The result
     goes into Spool 128 (all_amps) (compressed columns allowed) fanned
     out into 10 hash join partitions, which is built locally on the
     AMPs.  The size of Spool 128 is estimated with low confidence to
     be 15,651,894 rows (2,566,910,616 bytes).  The estimated time for
     this step is 1.74 seconds.
 73) We do an all-AMPs JOIN step from Spool 127 (Last Use) by way of an
     all-rows scan, which is joined to Spool 128 (Last Use) by way of
     an all-rows scan.  Spool 127 and Spool 128 are joined using a hash
     join of 10 partitions, with a join condition of ("(DIM_3_ATTR =
     DIM_3_ATTR) AND ((FISCAL_END_DATE = FISCAL_END_DATE) AND ((FISCAL_END_DATE =
     FISCAL_END_DATE) AND (FISCAL_END_DATE = FISCAL_END_DATE )))").  The result
     goes into Spool 130 (all_amps) (compressed columns allowed), which
     is built locally on the AMPs.  The size of Spool 130 is estimated
     with no confidence to be 15,651,894 rows (2,942,556,072 bytes).
     The estimated time for this step is 2.02 seconds.
 74) We do an all-AMPs JOIN step from Spool 68 (Last Use) by way of an
     all-rows scan, which is joined to Spool 130 (Last Use) by way of
     an all-rows scan.  Spool 68 and Spool 130 are right outer joined
     using a single partition hash join, with condition(s) used for
     non-matching on right table ("NOT (DIM_36_ATTR IS NULL)"),
     with a join condition of ("DIM_36_ATTR = DIM_36_ATTR").
     The result goes into Spool 13 (all_amps), which is redistributed
     by hash code to all AMPs.  Then we do a SORT to order Spool 13 by
     the sort key in spool field1 eliminating duplicate rows.  The size
     of Spool 13 is estimated with no confidence to be 2 rows (1,666
     bytes).  The estimated time for this step is 15 minutes and 31
     seconds.
 75) We do an all-AMPs SUM step to aggregate from Spool 13 (Last Use)
     by way of an all-rows scan with a condition of ("NOT
     (Y.FISCAL_END_DATE IS NULL)"), and the grouping identifier in field 1.
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 134.  The size of Spool 134 is estimated with no
     confidence to be 2 rows (1,298 bytes).  The estimated time for
     this step is 0.02 seconds.
 76) We do an all-AMPs RETRIEVE step from Spool 134 (Last Use) by way
     of an all-rows scan into Spool 14 (used to materialize view,
     derived table, table function or table operator A) (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 14 is estimated with no confidence to be 2 rows
     (790 bytes).  The estimated time for this step is 0.01 seconds.
 77) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of
     an all-rows scan into Spool 137 (all_amps) (compressed columns
     allowed), which is redistributed by hash code to all AMPs.  Then
     we do a SORT to order Spool 137 by row hash.  The size of Spool
     137 is estimated with no confidence to be 2 rows (774 bytes).  The
     estimated time for this step is 0.00 seconds.
 78) We do an all-AMPs JOIN step from TABLE_DATABASE2.TABLEB in view
     VIEW_DATABASE1.TABLEB by way of a RowHash match scan with a condition
     of ("(TABLE_DATABASE2.TABLEB in view VIEW_DATABASE1.TABLEB.FISCAL_PERIOD_ID
     = '01') AND (TABLE_DATABASE2.TABLEB in view
     VIEW_DATABASE1.TABLEB.YR_ID = '2015')"), which is joined to
     Spool 137 (Last Use) by way of a RowHash match scan locking
     TABLE_DATABASE2.TABLEB for access.  TABLE_DATABASE2.TABLEB and
     Spool 137 are joined using a merge join, with a join condition of
     ("FISCAL_END_DATE = TABLE_DATABASE2.TABLEB.FISCAL_END_DATE").  The
     result goes into Spool 136 (group_amps), which is built locally on
     the AMPs.  The size of Spool 136 is estimated with no confidence
     to be 1 row (355 bytes).  The estimated time for this step is 0.02
     seconds.
 79) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 136 are sent back to the user as the result
     of statement 1.  The total estimated time is 2 hours and 4 minutes.
Enthusiast

Re: Performance Question

Sorry about the long post and delay in reply. Took me some time to mock up the entire scenario since I can't post the exact table/view definitions and the original explain.

Enthusiast

Re: Performance Question

Somdebroy,

It looks like your JI and NUSI aren't being used, as neither are referenced in the explain plan.  Unless you've got other queries that show them being used, you can probably drop them both.  

Can you post the DDL for ViewA as referenced in the explain?

Thanks

Enthusiast

Re: Performance Question

Hello VandeBergB,

These JI and NUSI are being referenced in other downstream applications.

Unfortunately, I am not authorized to post the exact definition of the view.

We tried to do some tweaking by materializing a part of the business logic and used that materialized data to reduce the join on multiple tables/views and it did improve the performance a bit. Also, per users consent, our BO team changed this ad-hoc report to a regular report in BO universe where they were able to plug in the ad-hoc QUERY5.

Thanks for checking on this!