Partition Elimination

Database
Teradata Employee

Partition Elimination

Hi All,

Please can someone help find out why selecting from the view below doesn't get partition elimination and always does full scan? Thank you

We are trying to associated page view data from Celebrus with the the ProfileUiid which identify a customer. This is not set in the page data until the user is logged on.
We want the pages for the session before the user logged on to be associated with the user after logging in. To do this we're joining the page table to itself and using a qualify to get the next
page view where the user is identify. There is a PPI on the EventTimestamp but we get a full table scan when selecting from this table. 

REPLACE VIEW HK_ISTD03_CIM_CUSTOMER.t1_firstCustIdPg_v_BH
(
    SessionNumber
    ,ProfileUiid
    ,EventTimestamp
    ,EventDate
    ,EventTime
    ,PageInstanceId
    ,PageSequenceInSession
)
AS LOCKING ROW FOR ACCESS
SELECT
    SessionNumber
    ,ProfileUiid
    ,EventTimestamp
    ,EventDate
    ,EventTime
    ,PageInstanceId
    ,PageSequenceInSession
FROM
(
    SELECT
        pg2.SessionNumber
        ,pg2.ProfileUiid
        ,pg2.EventTimestamp
        ,CAST(pg2.EventTimestamp AS DATE) AS EventDate
        ,CAST(pg2.EventTimestamp AS TIME) AS EventTime
        ,pg2.PageInstanceId
        ,pg2.PageSequenceInSession
    FROM    HK_ISTD03_ICI_DLWORK.page               pg2
    LEFT OUTER JOIN
            HK_ISTD03_ICI_DLWORK.page               pg1
    ON      pg1.SessionNumber = pg2.SessionNumber
    AND     pg1.PageSequenceInSession+1 = pg2.PageSequenceInSession
    WHERE   pg1.ProfileUiid NE pg2.ProfileUiid
    OR      (pg1.ProfileUiid IS NULL AND pg2.ProfileUiid IS NOT NULL)
) t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY t1.SessionNumber ORDER BY t1.eventTimestamp) = 1;


The base table is partition on EventTimestamp and all stats are collected:

CREATE MULTISET TABLE HK_ISTD03_ICI_DLWORK.page ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      SessionNumber BIGINT NOT NULL,
      TrackingUuid VARCHAR(36) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
      PageLocationDomain VARCHAR(1000) CHARACTER SET UNICODE NOT CASESPECIFIC,
      PageTitle VARCHAR(1000) CHARACTER SET UNICODE NOT CASESPECIFIC,
      ReferringPageInstanceID BIGINT,
      PageInstanceID BIGINT,
      EventTimestamp TIMESTAMP(3) NOT NULL,
      PageLocation VARCHAR(1000) CHARACTER SET UNICODE NOT CASESPECIFIC,
      AttributionSequenceInSession INTEGER,
      PageSequenceInSession INTEGER,
      PageSequenceInAttribution INTEGER,
      TopLevelWindowID VARCHAR(1000) CHARACTER SET UNICODE NOT CASESPECIFIC,
      ProfileUiid VARCHAR(250) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX ( SessionNumber )
PARTITION BY RANGE_N(CAST((EventTimestamp ) AS DATE AT TIME ZONE INTERVAL '0:00' HOUR TO MINUTE ) BETWEEN DATE '2012-01-01' AND DATE '2022-01-01' EACH INTERVAL '1' DAY )
INDEX ( SessionNumber ,PageSequenceInSession )
INDEX ( SessionNumber ,PageInstanceID )
INDEX ( ProfileUiid );


The query explain is bellow:

explain sel * from HK_ISTD03_CIM_CUSTOMER.t1_firstCustIdPg_v_BH
 WHERE  EventTimestamp between '2016-06-21 00:00:00.000' and '2016-06-21 23:59:59.'

Doesn't work either -- 
explain sel * from HK_ISTD03_CIM_CUSTOMER.t1_firstCustIdPg_v_BH
 WHERE  CAST(EventTimestamp as date) between '2016-06-21' and '2016-06-22'

  1) First, we lock HK_ISTD03_ICI_DLWORK.pg2 in view
     HK_ISTD03_CIM_CUSTOMER.t1_firstCustIdPg_v_BH for access.
  2) Next, we do an all-AMPs RETRIEVE step from
     HK_ISTD03_ICI_DLWORK.pg2 in view
     HK_ISTD03_CIM_CUSTOMER.t1_firstCustIdPg_v_BH by way of an all-rows
     scan with no residual conditions into Spool 3 (all_amps), which is
     built locally on the AMPs.  Then we do a SORT to order Spool 3 by
     the hash code of (HK_ISTD03_ICI_DLWORK.pg2.SessionNumber).  The
     result spool file will not be cached in memory.  The size of Spool
     3 is estimated with high confidence to be 1,048,021,270 rows (
     221,132,487,970 bytes).  The estimated time for this step is 40
     minutes and 49 seconds.
  3) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a
     RowHash match scan, which is joined to Spool 3 by way of a RowHash
     match scan.  Spool 3 and Spool 3 are left outer joined using a
     merge join, with a join condition of ("((PageSequenceInSession + 1
     )= PageSequenceInSession) AND (SessionNumber = SessionNumber)").
     The result goes into Spool 5 (all_amps), which is built locally on
     the AMPs.  The result spool file will not be cached in memory.
     The size of Spool 5 is estimated with low confidence to be
     1,048,023,271 rows (397,200,819,709 bytes).  The estimated time
     for this step is 5 minutes and 39 seconds.
  4) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
     an all-rows scan with a condition of ("((ProfileUiid <>
     ProfileUiid) OR (NOT (ProfileUiid IS NULL ))) AND ((ProfileUiid <>
     ProfileUiid) OR (ProfileUiid IS NULL ))") into Spool 2 (all_amps),
     which is built locally on the AMPs.  The result spool file will
     not be cached in memory.  The size of Spool 2 is estimated with
     low confidence to be 1,048,023,271 rows (223,228,956,723 bytes).
     The estimated time for this step is 6 minutes and 57 seconds.
  5) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by
     way of an all-rows scan into Spool 10 (Last Use), which is
     redistributed by hash code to all AMPs.  The result rows are put
     into Spool 8 (all_amps), which is built locally on the AMPs.  The
     size is estimated with low confidence to be 1,048,023,271 rows (
     256,765,701,395 bytes).
  6) We do an all-AMPs RETRIEVE step from Spool 8 (Last Use) by way of
     an all-rows scan with a condition of ("Field_9 = 1") into Spool 1
     (used to materialize view, derived table, table function or table
     operator t1_firstCustIdPg_v_BH) (all_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 low confidence to be
     76,588,557 rows (17,538,779,553 bytes).  The estimated time for
     this step is 7 minutes and 13 seconds.
  7) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
     an all-rows scan with a condition of (
     "(t1_firstCustIdPg_v_BH.EVENTTIMESTAMP <= TIMESTAMP '2016-06-21
     23:59:59.000') AND (t1_firstCustIdPg_v_BH.EVENTTIMESTAMP >=
     TIMESTAMP '2016-06-21 00:00:00.000')") into Spool 13 (group_amps),
     which is built locally on the AMPs.  The size of Spool 13 is
     estimated with low confidence to be 76,588,557 rows (
     13,326,408,918 bytes).  The estimated time for this step is 31.63
     seconds.
  8) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 13 are sent back to the user as the result
     of statement 1.