Partition Elimination

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Partition Elimination

Hi, 

i have one table F_ACCESS_METHOD_NETWORK_USAGE which is very huge (at present contains around 70 billion record), and have PPI on column (SRC_USG_DT) with PI on ACCESS_METHOD_ID. Now i want to fetch data for specific date ranges whcih i am getting using the volatile tables. Now i have two approaches to get data from table F_ACCESS_METHOD_NETWORK_USAGE after finding the date range for which i have to fetch the data. Could anyone please help me which approach will be faster and why? Also in whcih approach PE will do partition elimination?

SyntaxEditor Code Snippet

CREATE VOLATILE TABLE XMINE_MAX_DATE_TBL, NO LOG  AS 
(SEL MAX(SRC_USG_DT) AS XMINE_MAX_SRC_USG_DT FROM TEMP_FADS_V.F_ACCESS_METHOD_NETWORK_USAGE 
WHERE SRC_USG_DT >= DATE'2017-01-01' AND src_usg_dt<= DATE'2017-10-31' 
SEL DATE'2017-10-29' AS XMINE_MAX_SRC_USG_DT)WITH DATA
  PRIMARY INDEX (XMINE_MAX_SRC_USG_DT)
  ON COMMIT PRESERVE ROWS;

SyntaxEditor Code Snippet
CREATE VOLATILE  TABLE XMINE_MAX_USG_DT_LST_RUN, NO LOG AS(SEL MAX(MAX_SRC_USG_DATE) AS LST_RUN_MAX_USG_DT FROM DL_U2CSR.XMINE_USG_DT_TRK_TABLE 
)  WITH DATA
PRIMARY INDEX(LST_RUN_MAX_USG_DT)ON COMMIT PRESERVE ROWS;


SyntaxEditor Code Snippet
CREATE VOLATILE TABLE XMINE_DISTINCT_MISNG_DATE_TBL, NO LOG AS(SELECT DISTINCT D_CALENDAR_DT  FROM FADS_V.D_CALENDAR WHERE 
D_CALENDAR_DT > (CASE WHEN DATE'2017-10-31' < (SEL LST_RUN_MAX_USG_DT FROM XMINE_MAX_USG_DT_LST_RUN) THEN DATE'2017-10-31' - INTERVAL '1' DAY
ELSE (CASE WHEN (SEL LST_RUN_MAX_USG_DT FROM XMINE_MAX_USG_DT_LST_RUN) = (SEL  XMINE_MAX_SRC_USG_DT FROM XMINE_MAX_DATE_TBL)THEN (SEL LST_RUN_MAX_USG_DT FROM XMINE_MAX_USG_DT_LST_RUN) - INTERVAL '1' DAY ELSE (SEL LST_RUN_MAX_USG_DT FROM XMINE_MAX_USG_DT_LST_RUN)END )END )AND D_CALENDAR_DT <= (SEL  XMINE_MAX_SRC_USG_DT FROM XMINE_MAX_DATE_TBL)
) WITH DATA
PRIMARY INDEX(D_CALENDAR_DT)ON COMMIT PRESERVE ROWS;

APPROACH 1

SELECT
B.BSCS_SUBSCRIPTION_ID
,A.ACCESS_METHOD_ID
,A.SRC_USG_DT
,A.LST_QLFYNG_USE_DTTM
,A.FRST_QLFYNG_USE_DTTM
,A.EQUIP_ID
,A.DEVC_LST_USE_DTTM
,A.DEVC_LST_ACTVN_DTTM
,A.SIM_CARD_ID
,B.SUB_STAT_CD
,B.SUB_ACTVN_DTTM1
,B.SUB_DEACTVN_DTTM1
FROM $TEMP_FADS_V.F_ACCESS_METHOD_NETWORK_USAGE A
INNER JOIN $DL_U2CSR.D_BSCS_SUBS_CURR_SNPSHT_WRK B
ON A.ACCESS_METHOD_ID = B.BSCS_ACCESS_METHOD_ID
WHERE UPPER(B.SUB_STAT_CD) NOT IN ('D') AND A.SRC_USG_DT >= (SELECT MIN(D_CALENDAR_DT) FROM XMINE_DISTINCT_MISNG_DATE_TBL)
AND A.SRC_USG_DT <= (SELECT MAX(D_CALENDAR_DT) FROM XMINE_DISTINCT_MISNG_DATE_TBL)
AND A.SRC_USG_DT (DATE)>=B.SUB_ACTVN_DTTM1(DATE) AND A.SRC_USG_DT<=DATE'$SAA_SNPSHT_DATE'
QUALIFY ROW_NUMBER() OVER (PARTITION BY A.ACCESS_METHOD_ID,B.BSCS_SUBSCRIPTION_ID ORDER BY A.SRC_USG_DT DESC)=1

Approach 2

SELECT
B.BSCS_SUBSCRIPTION_ID
,A.ACCESS_METHOD_ID
,A.SRC_USG_DT
,A.LST_QLFYNG_USE_DTTM
,A.FRST_QLFYNG_USE_DTTM
,A.EQUIP_ID
,A.DEVC_LST_USE_DTTM
,A.DEVC_LST_ACTVN_DTTM
,A.SIM_CARD_ID
,B.SUB_STAT_CD
,B.SUB_ACTVN_DTTM1
,B.SUB_DEACTVN_DTTM1
FROM $TEMP_FADS_V.F_ACCESS_METHOD_NETWORK_USAGE A
INNER JOIN $DL_U2CSR.D_BSCS_SUBS_CURR_SNPSHT_WRK B
ON A.ACCESS_METHOD_ID = B.BSCS_ACCESS_METHOD_ID
WHERE UPPER(B.SUB_STAT_CD) NOT IN ('D') AND A.SRC_USG_DT IN (SELECT (D_CALENDAR_DT) FROM XMINE_DISTINCT_MISNG_DATE_TBL)
AND A.SRC_USG_DT (DATE)>=B.SUB_ACTVN_DTTM1(DATE) AND A.SRC_USG_DT<=DATE'$SAA_SNPSHT_DATE'
QUALIFY ROW_NUMBER() OVER (PARTITION BY A.ACCESS_METHOD_ID,B.BSCS_SUBSCRIPTION_ID ORDER BY A.SRC_USG_DT DESC)=1



 Thanks in advance!!

1 REPLY
Senior Apprentice

Re: Partition Elimination

Hi,

 

To get 'partition elimination' you typically need to either select (WHERE clause) on the PPI column(s) and/or you need to join into a table using those columns. For joins you often with equality constraints but the straight selection can be equality or range constraints.

 

To check your query for partition elimination use the EXPLAIN feature. You are looking for the phrases "n partitions of" (where 'n' is a number) or "enhanced by dynamic partition elimination".

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com