CREATE SET TABLE RPDM30.SHOPPING_TRANSACTION ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      Shopping_Transaction_Id INTEGER NOT NULL,
      Shopping_Tran_Type_Cd VARCHAR( 50 ) CHARACTER SET LATIN NOT CASESPECIFIC,
      Shopping_Tran_Status_Cd VARCHAR( 50 ) CHARACTER SET LATIN NOT CASESPECIFIC,
      Visit_Id INTEGER NOT NULL,
      POS_Register_Id INTEGER,
      Transaction_Dttm TIMESTAMP( 6 ) FORMAT 'yyyy-mm-ddbhh:mi:ss.s(6)',
      Reported_As_Dttm TIMESTAMP( 6 ) FORMAT 'yyyy-mm-ddbhh:mi:ss.s(6)',
      Order_Num VARCHAR( 50 ) CHARACTER SET LATIN NOT CASESPECIFIC,
      Related_Shopping_Trans_Id INTEGER,
      Sales_Associate_Id INTEGER,
      Bill_To_Persona_Id INTEGER,
      Ship_To_Persona_Id INTEGER,
      Bill_To_Address_Id INTEGER,
      Ship_To_Address_Id INTEGER)
PRIMARY INDEX XIE1SHOPPING_TRANSACTION ( Shopping_Transaction_Id );

CREATE SET TABLE RPDM30.SHOPPING_TRANSACTION_LINE ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      Shopping_Transaction_Id INTEGER NOT NULL,
      Shopping_Transaction_Line_Num VARCHAR( 50 ) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      Shopping_Tran_Line_Status_Cd VARCHAR( 50 ) CHARACTER SET LATIN NOT CASESPECIFIC,
      Unit_Selling_Price_Amt DECIMAL( 18 , 4 ) NOT NULL,
      Item_Qty DECIMAL( 18 , 4 ) NOT NULL,
      Item_Id INTEGER NOT NULL,
      Unit_Cost_Amt DECIMAL( 18 , 4 ),
      Transaction_Line_Dttm TIMESTAMP( 6 ) FORMAT 'yyyy-mm-ddbhh:mi:ss.s(6)',
      Unit_Retail_Price_Amt DECIMAL( 18 , 4 ) NOT NULL,
      Orig_Shopping_Transaction_Id INTEGER,
      Orig_Shopping_Tran_Line_Num VARCHAR( 50 ) CHARACTER SET LATIN NOT CASESPECIFIC,
      Promised_Fulfillment_Dt DATE FORMAT 'YYYY-MM-DD',
      Page_View_Id INTEGER)
PRIMARY INDEX XIE1SHOPPING_TRANSACTION_LINE ( Shopping_Transaction_Id ,
Orig_Shopping_Transaction_Id );

EXPLAIN
SELECT
  *
FROM
  SHOPPING_TRANSACTION st
INNER JOIN
  SHOPPING_TRANSACTION_LINE stl
ON
  st.Transaction_Dttm=stl.Transaction_Line_Dttm

Explanation
   1 ) First, we lock a distinct RPDM30."pseudo table" for read on a
     RowHash to prevent global deadlock for RPDM30.stl. 
   2 ) Next, we lock a distinct RPDM30."pseudo table" for read on a
     RowHash to prevent global deadlock for RPDM30.st. 
   3 ) We lock RPDM30.stl for read, and we lock RPDM30.st for read. 
   4 ) We do an all-AMPs RETRIEVE step from RPDM30.st by way of an
     all-rows scan with a condition of ("NOT
     (RPDM30.st.Transaction_Dttm IS NULL)") into Spool  2  (all_amps)
     fanned out into  29  hash join partitions, which is duplicated on
     all AMPs.  The result spool file will not be cached in memory. 
     The size of Spool  2  is estimated with no confidence to be  24 , 488 
     rows.  The estimated time for this step is  1 . 09  seconds. 
   5 ) We do an all-AMPs RETRIEVE step from RPDM30.stl by way of an
     all-rows scan with a condition of ("NOT
     (RPDM30.stl.Transaction_Line_Dttm IS NULL)") into Spool  3 
     (all_amps) fanned out into  29  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  3  is
     estimated with no confidence to be  108 , 508  rows.  The estimated
     time for this step is  6 . 73  seconds. 
   6 ) We do an all-AMPs JOIN step from Spool  2  (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  2  and Spool  3  are joined using a hash join
     of  29  partitions, with a join condition of ("Transaction_Dttm =
     Transaction_Line_Dttm").  The result goes into Spool  1 
     (group_amps), which is built locally on the AMPs.  The result
     spool file will not be cached in memory.  The size of Spool  1  is
     estimated with no confidence to be  4 , 033 , 241  rows.  The estimated
     time for this step is  2  minutes and  17  seconds. 
   7 ) 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  2  minutes and  25  seconds. 

EXPLAIN
SELECT
  *
FROM
  SHOPPING_TRANSACTION_LINE stl1
INNER JOIN
  SHOPPING_TRANSACTION_LINE stl2
ON
  stl1.Transaction_Line_Dttm=stl2.Transaction_Line_Dttm

Explanation
   1 ) First, we lock a distinct RPDM30."pseudo table" for read on a
     RowHash to prevent global deadlock for RPDM30.stl2. 
   2 ) Next, we lock RPDM30.stl2 for read. 
   3 ) We execute the following steps in parallel. 
        1 ) We do an all-AMPs RETRIEVE step from RPDM30.stl2 by way of an
          all-rows scan with a condition of ("NOT
          (RPDM30.stl2.Transaction_Line_Dttm IS NULL)") into Spool  2 
          (all_amps), which is redistributed by hash code to all AMPs. 
          Then we do a SORT to order Spool  2  by row hash.  The result
          spool file will not be cached in memory.  The size of Spool  2 
          is estimated with no confidence to be  108 , 508  rows.  The
          estimated time for this step is  7 . 38  seconds. 
        2 ) We do an all-AMPs RETRIEVE step from RPDM30.stl1 by way of an
          all-rows scan with a condition of ("NOT
          (RPDM30.stl1.Transaction_Line_Dttm IS NULL)") into Spool  3 
          (all_amps), which is redistributed by hash code to all AMPs. 
          Then we do a SORT to order Spool  3  by row hash.  The result
          spool file will not be cached in memory.  The size of Spool  3 
          is estimated with no confidence to be  108 , 508  rows.  The
          estimated time for this step is  7 . 38  seconds. 
   4 ) We do an all-AMPs JOIN step from Spool  2  (Last Use) by way of a
     RowHash match scan, which is joined to Spool  3  (Last Use) by way
     of a RowHash match scan.  Spool  2  and Spool  3  are joined using a
     merge join, with a join condition of ("Transaction_Line_Dttm =
     Transaction_Line_Dttm").  The result goes into Spool  1 
     (group_amps), which is built locally on the AMPs.  The result
     spool file will not be cached in memory.  The size of Spool  1  is
     estimated with no confidence to be  35 , 743 , 135  rows.  The estimated
     time for this step is  21  minutes and  11  seconds. 
   5 ) 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  21  minutes and  19 
     seconds.