Re: LEFT OUTER instead of NOT IN...

l_k · ‎01-08-2011

Hi All,

Can anyone help me how to modify the NOT IN condition replaced by LEFT OUTER for the below query:
The below query is taking more than 1 hour to complete and having billion of records inside..

UPDATE sample.TX0305_ACCT_BAL_TYPE_DD
SET End_Dt = (CAST ('2011-01-05' AS DATE FORMAT 'YYYY-MM-DD') - 1)
,Upd_Txf_BatchID = 877209444 ,Record_Deleted_Flag = 1
WHERE ( DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Account_Num ,DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Account_Modifier_Num
,DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Acct_Bal_Type_Cd ,DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Time_Period_Cd ,DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Calculation_Flg
,DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Data_Source_Cd ,DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.BusinessDate )
NOT IN ( SELECT Account_Num ,Account_Modifier_Num ,Acct_Bal_Type_Cd ,Time_Period_Cd ,Calculation_Flg ,Data_Source_Cd ,BusinessDate
FROM sample.I$_TX0305_877209444 )
AND End_Dt IS NULL
AND Record_Deleted_Flag = 0
AND Start_Dt < '2011-01-05'
AND Ins_Txf_BatchID IN (
SELECT Txf_BatchID FROM sample.CTLFW_TRANSFORM WHERE Txf_Pkg_Name = 'PKG_I_TX0305_S1240_01_D' )

Thanks for your lot of information...

l_k · ‎01-09-2011

Hi All,

this is the EXPLAIN plan for the above query:

1) First, we lock a distinct DO_TEDW."pseudo table" for write on a
RowHash to prevent global deadlock for
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.
2) Next, we lock a distinct DO_TEDW."pseudo table" for read on a
RowHash to prevent global deadlock for DO_TEDW.I$_TX0305_877209444.
3) We lock DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST for write, and we
lock DO_TEDW.I$_TX0305_877209444 for read.
4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from a single partition of
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST with a condition of (
"DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.End_Dt = NULL") with a
residual condition of (
"(DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.End_Dt IS NULL) AND
((DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Record_Deleted_Flag =
0) AND (DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Start_Dt < DATE
'2011-01-05'))") into Spool 2 (all_amps) (compressed columns
allowed), which is built locally on the AMPs. Then we do a
SORT to order Spool 2 by the hash code of (
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Ins_Txf_BatchID). The
size of Spool 2 is estimated with low confidence to be
164,274 rows (4,271,124 bytes). The estimated time for this
step is 0.02 seconds.
2) We do a single-AMP RETRIEVE step from DO_TEDW.CTLFW_TRANSFORM
by way of the primary index
"DO_TEDW.CTLFW_TRANSFORM.Txf_Pkg_Name =
'PKG_I_TX0305_S1240_01_D'" with no residual conditions into
Spool 4 (all_amps), which is redistributed by the hash code
of (DO_TEDW.CTLFW_TRANSFORM.Txf_BatchID) to all AMPs. Then
we do a SORT to order Spool 4 by the sort key in spool field1
eliminating duplicate rows. The size of Spool 4 is estimated
with high confidence to be 110 rows (2,750 bytes). The
estimated time for this step is 0.00 seconds.
5) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by
way of an all-rows scan into Spool 3 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. Then we
do a SORT to order Spool 3 by the hash code of (
DO_TEDW.CTLFW_TRANSFORM.Txf_BatchID). The size of Spool 3 is
estimated with high confidence to be 5,940 rows (148,500
bytes).
2) We do an all-AMPs RETRIEVE step from
DO_TEDW.I$_TX0305_877209444 by way of an all-rows scan with
no residual conditions into Spool 5 (all_amps), which is
redistributed by the hash code of (
DO_TEDW.I$_TX0305_877209444.Account_Modifier_Num,
DO_TEDW.I$_TX0305_877209444.Account_Num) to all AMPs. Then
we do a SORT to order Spool 5 by row hash and the sort key in
spool field1 eliminating duplicate rows. The size of Spool 5
is estimated with low confidence to be 3,078 rows (1,462,050
bytes). The estimated time for this step is 0.01 seconds.
6) We execute the following steps in parallel.
1) 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 an inclusion merge join, with a join condition of (
"Ins_Txf_BatchID = Txf_BatchID"). The result goes into Spool
6 (all_amps) (compressed columns allowed), which is
duplicated on all AMPs. The size of Spool 6 is estimated
with low confidence to be 60,210 rows (1,083,780 bytes). The
estimated time for this step is 0.02 seconds.
2) We do an all-AMPs JOIN step from
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST by way of an all-rows
scan with no residual conditions, which is joined to Spool 5
(Last Use) by way of an all-rows scan.
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST and Spool 5 are joined
using a sliding-window exclusion merge join (contexts = 33, 1),
with a join condition of (
"(DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Account_Num =
Account_Num) AND
((DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Account_Modifier_Num =
Account_Modifier_Num) AND
((DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Acct_Bal_Type_Cd =
Acct_Bal_Type_Cd) AND
((DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Time_Period_Cd =
Time_Period_Cd) AND
((DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Calculation_Flg =
Calculation_Flg) AND
((DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Data_Source_Cd =
Data_Source_Cd) AND
(DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.BusinessDate =
BusinessDate ))))))"). The result goes into Spool 8
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. The size of Spool 8 is estimated with
index join confidence to be 1,084,770 rows. The estimated
time for this step is 0.65 seconds.
7) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
all-rows scan, which is joined to Spool 8 (Last Use) by way of an
all-rows scan. Spool 6 and Spool 8 are joined using a product
join, with a join condition of ("(1=1)"). The result goes into
Spool 1 (all_amps), which is redistributed by the rowkey of (
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.ROWID) to all AMPs. Then we
do a SORT to order Spool 1 by the sort key in spool field1. The
result spool file will not be cached in memory. The size of Spool
1 is estimated with index join confidence to be 1,209,518,550 rows
(21,771,333,900 bytes). The estimated time for this step is 6
minutes and 27 seconds.
8) We do an all-AMPs MERGE DELETE to
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST from Spool 1 (Last Use) via
the row id. New updated rows are built and the result goes into
Spool 10 (all_amps) (compressed columns allowed), which is built
locally on the AMPs. Then we do a SORT to partition Spool 10 by
rowkey. The size is estimated with index join confidence to be
1,209,518,550 rows. The estimated time for this step is 676 hours
and 56 minutes.
9) We do an all-AMPs MERGE into DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST
from Spool 10 (Last Use). The size is estimated with index join
confidence to be 1,209,518,550 rows. The estimated time for this
step is 3 hours and 3 minutes.

Pls suggest me how to optimize the above update query...

dnoeth · ‎01-09-2011

The product join in step 7 is suspicious, seems like a logical error in your query.
Could you post the *actual* query and the table/view DDL?

And you seem to update the partitioning column of the target table, which is bad, similar to updating the PI.

Dieter

l_k · ‎01-09-2011

Hi Dieter,

Thanks for your initial findings..

The above query is the actual query that we are updating..

Table-1:
CREATE SET TABLE sample.TX0305_ACCT_BAL_TYPE_DD ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Account_Num VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Account Num' NOT NULL,
Account_Modifier_Num CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Account Modifier Num' NOT NULL,
Acct_Bal_Type_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Account Balance Type Cd' NOT NULL COMPRESS ('ACCR_DINT ','ACCR_INT ','ACCR_INTL1','ACCR_INTL2','ACCR_INT_P','ACCR_PEN ','AGG_ACR ','AGG_NEGBAL','AGG_OS_BAL','AMR_COST ','AMT_ARRS ','AMT_CR ','AMT_DR ','AMT_LDEP ','ARR_PRI ','AUTH_AMT ','AVAL_BAL ','AVG_BAL ','AVL_LIM_GD','AVL_LIM_SH','BILL_OT ','CAP_INT ','CF_ACCR_I ','COMM_FEE ','CONTR_INT ','CS_ADV_OUT','CURR_BAL ','CUR_CAPL2 ','FECHG_OT_D','FLOAT_1DY ','FLOAT_4DY ','HOLD ','INTL_PMT ','INT_BIL ','INT_CR ','INT_DR ','INT_RBT ','INT_SUSP ','IN_CLR_DR ','IW_CHQ ','IW_CHQ_RET','LAST_INTCR','LTCHG_OUT ','LTCHG_SUSP','MAX_BAL ','MAX_BAL_IC','MAX_CL_BAL','MAX_PRIN ','MAX_PRIN_C','MIN_BAL ','MIN_BAL_IC','MIN_PRIN ','MIN_PRIN_C','MSCHG_OUT ','MSP_A_INT ','MTD_AVGCRB','MTD_AVGDRB','MTM_AMT ','MTM_LEG1 ','MTM_LEG2 ','OSD_AMT ','OSD_INT ','OS_BAL ','OTCHG_OUT ','OUT_CLR ','PREV_PAY ','PRIN_BAL ','PRIN_CR ','PRIN_DR ','PRV_CBAL ','PY_LEDGBAL','RETL_PUR ','RTL_OBAL ','RTL_PYMT ','RTL_SPD ','RTVF02 ','RTVF12 ','S35_AMT ','SERVICE CH','SRETL_PUR ','SRL_OBAL ','STM_BAL ','TOT_COLVAL','TOT_OUT ','TTL_FLOAT ','TTL_STP_CK','T_INT_CR ','T_PRIN_CR ','T_PRIN_DR ','T_TOT_AMT ','UERN_INT ','WRK_BAL ','YTD_CHGFEE','YTD_INTCR ','YTD_INTDR ','YTD_LATECH','YTD_SVCFEE'),
Time_Period_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Time Period Cd' COMPRESS ('12M ','1M ','3M ','6M ','BILL_STMT ','DY ','LTD ','M ','MATURITY ','MLY ','MT ','MTD ','ONCE ','YTD '),
Calculation_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
Acct_Bal_Amt DECIMAL(18,2) TITLE 'Account Balance Amt' COMPRESS 0.00 ,
Acct_Crncy_Acct_Bal_Amt DECIMAL(18,2) TITLE 'Acct Crncy Account Balance Amt' COMPRESS (0.00 ,0.01 ,0.02 ,0.03 ,0.04 ,0.05 ,0.06 ,0.07 ,0.08 ,0.09 ,0.10 ,0.11 ,0.12 ,0.13 ,0.14 ,0.15 ,0.16 ,100.00 ,0.17 ,0.18 ,0.19 ,0.20 ,0.21 ,0.22 ,0.23 ,0.24 ,0.25 ,0.26 ,0.27 ,0.28 ,0.29 ,0.30 ,0.31 ,0.32 ,200.00 ,5000.00 ,0.33 ,0.34 ,0.35 ,0.36 ,0.37 ,0.38 ,0.39 ,0.40 ,0.41 ,0.42 ,0.45 ,300.00 ,2000.00 ,80.00 ,10000.00 ,400.00 ,500.00 ,50.00 ,150.00 ,1000.00 ,30.00 ,2.00 ,20.00 ,3000.00 ,10.00 ,9999999999999.99 ),
Global_Crncy_Acct_Bal_Amt DECIMAL(18,2) TITLE 'Global Crncy Account Balance Amt' COMPRESS 0.00 ,
Acct_Bal_Dt DATE FORMAT 'YYYY-MM-DD' COMPRESS ,
Start_Dt DATE FORMAT 'YYYY-MM-DD' COMPRESS (DATE '2010-08-01',DATE '2010-08-02',DATE '2010-08-03',DATE '2010-08-04',DATE '2010-08-05',DATE '2010-08-06',DATE '2010-08-07',DATE '2010-08-08',DATE '2010-08-09',DATE '2010-08-10',DATE '2010-08-11',DATE '2010-08-12',DATE '2010-08-13',DATE '2010-08-14',DATE '2010-08-15',DATE '2010-08-16',DATE '2010-08-17',DATE '2010-08-18',DATE '2010-08-19',DATE '2010-08-20',DATE '2010-08-21',DATE '2010-08-22',DATE '2010-08-23',DATE '2010-08-24',DATE '2008-11-13',DATE '2010-08-25',DATE '2010-08-26',DATE '2010-08-27',DATE '2010-08-28',DATE '2010-08-29',DATE '2010-08-30',DATE '2010-08-31',DATE '2010-03-22',DATE '2010-03-23',DATE '2010-03-31',DATE '2010-06-01',DATE '2010-06-10',DATE '2010-06-25',DATE '2010-06-28',DATE '2010-06-29',DATE '2010-06-30',DATE '2008-09-25',DATE '2010-09-01',DATE '2010-09-02',DATE '2010-09-03',DATE '2010-09-04',DATE '2010-09-05',DATE '2010-09-06',DATE '2010-09-07',DATE '2010-09-08',DATE '2010-09-09',DATE '2010-09-10',DATE '2010-09-11',DATE '2010-09-12',DATE '2010-09-13',DATE '2010-09-14',DATE '2010-09-15',DATE '2010-09-16',DATE '2010-09-17',DATE '2010-09-18',DATE '2010-09-19',DATE '2010-09-20',DATE '2010-09-21',DATE '2010-09-22',DATE '2010-09-23',DATE '2010-09-24',DATE '2010-09-25',DATE '2010-09-26',DATE '2010-09-27',DATE '2010-04-16',DATE '2010-09-28',DATE '2010-09-29',DATE '2010-09-30',DATE '2008-12-19',DATE '2010-04-21',DATE '2010-04-29',DATE '2010-04-30',DATE '2010-07-01',DATE '2010-07-02',DATE '2009-12-31',DATE '2010-07-03',DATE '2010-07-04',DATE '2010-07-05',DATE '2010-07-06',DATE '2010-07-07',DATE '2010-07-08',DATE '2010-07-09',DATE '2010-07-10',DATE '2010-07-11',DATE '2010-07-12',DATE '2010-07-13',DATE '2010-07-14',DATE '2010-07-15',DATE '2010-07-16',DATE '2010-07-17',DATE '2010-07-18',DATE '2010-07-19',DATE '2010-07-20',DATE '2010-07-21',DATE '2010-07-22',DATE '2010-07-23',DATE '2010-07-24',DATE '2010-07-25',DATE '2010-07-26',DATE '2010-07-27',DATE '2010-07-28',DATE '2010-07-29',DATE '2010-07-30',DATE '2010-07-31',DATE '2008-05-16',DATE '2006-08-05',DATE '2006-08-15',DATE '2006-08-31',DATE '2009-05-29',DATE '2010-05-31'),
End_Dt DATE FORMAT 'YYYY-MM-DD',
Data_Source_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('CC ','CC_MY ','CPF ','DBTM_SG ','DEP ','DEP_MY ','FD ','FITAS ','FITAS_MY ','LNS ','LNS_MY ','MRX ','MRX_MY ','OPICS ','UT '),
Record_Deleted_Flag BYTEINT NOT NULL DEFAULT 0 COMPRESS 0 ,
BusinessDate DATE FORMAT 'YYYY-MM-DD' COMPRESS (DATE '2010-08-01',DATE '2010-08-02',DATE '2010-08-03',DATE '2010-08-04',DATE '2010-08-05',DATE '2010-08-06',DATE '2010-08-07',DATE '2010-08-08',DATE '2010-08-09',DATE '2010-08-10',DATE '2010-08-11',DATE '2010-08-12',DATE '2010-08-13',DATE '2010-08-14',DATE '2010-08-15',DATE '2010-08-16',DATE '2010-08-17',DATE '2010-08-18',DATE '2010-08-19',DATE '2010-08-20',DATE '2010-08-21',DATE '2010-08-22',DATE '2010-08-23',DATE '2010-08-24',DATE '2008-11-13',DATE '2010-08-25',DATE '2010-08-26',DATE '2010-08-27',DATE '2010-08-28',DATE '2010-08-29',DATE '2010-08-30',DATE '2010-08-31',DATE '2010-03-22',DATE '2010-03-23',DATE '2010-03-31',DATE '2010-06-01',DATE '2010-06-10',DATE '2010-06-25',DATE '2010-06-28',DATE '2010-06-29',DATE '2010-06-30',DATE '2008-09-25',DATE '2010-09-01',DATE '2010-09-02',DATE '2010-09-03',DATE '2010-09-04',DATE '2010-09-05',DATE '2010-09-06',DATE '2010-09-07',DATE '2010-09-08',DATE '2010-09-09',DATE '2010-09-10',DATE '2010-09-11',DATE '2010-09-12',DATE '2010-09-13',DATE '2010-09-14',DATE '2010-09-15',DATE '2010-09-16',DATE '2010-09-17',DATE '2010-09-18',DATE '2010-09-19',DATE '2010-09-20',DATE '2010-09-21',DATE '2010-09-22',DATE '2010-09-23',DATE '2010-09-24',DATE '2010-09-25',DATE '2010-09-26',DATE '2010-09-27',DATE '2010-04-16',DATE '2010-09-28',DATE '2010-09-29',DATE '2010-09-30',DATE '2008-12-19',DATE '2010-04-21',DATE '2010-04-29',DATE '2010-04-30',DATE '2010-07-01',DATE '2010-07-02',DATE '2009-12-31',DATE '2010-07-03',DATE '2010-07-04',DATE '2010-07-05',DATE '2010-07-06',DATE '2010-07-07',DATE '2010-07-08',DATE '2010-07-09',DATE '2010-07-10',DATE '2010-07-11',DATE '2010-07-12',DATE '2010-07-13',DATE '2010-07-14',DATE '2010-07-15',DATE '2010-07-16',DATE '2010-07-17',DATE '2010-07-18',DATE '2010-07-19',DATE '2010-07-20',DATE '2010-07-21',DATE '2010-07-22',DATE '2010-07-23',DATE '2010-07-24',DATE '2010-07-25',DATE '2010-07-26',DATE '2010-07-27',DATE '2010-07-28',DATE '2010-07-29',DATE '2010-07-30',DATE '2010-07-31',DATE '2008-05-16',DATE '2006-08-05',DATE '2006-08-15',DATE '2006-08-31',DATE '2009-05-29',DATE '2010-05-31'),
Ins_Txf_BatchID INTEGER COMPRESS (43581444 ,23453444 ,170659333 ,182851333 ,49891333 ,104899333 ,163555333 ,129347333 ,1738313222 ,1937961222 ,111111 ,8664333 ,27544333 ,163224333 ,1891934222 ,868004111 ,63533333 ,102861333 ,50189333 ,163309333 ,1753747222 ,1855667222 ,1846579222 ,1978963222 ,2015667222 ,1733683222 ,109218333 ,90818333 ,1814376222 ,2100616222 ,46111 ,869070111 ,108823333 ,1789597222 ,2128029222 ,962813222 ,1981053222 ,143436333 ,121420333 ,1990354222 ,1853074222 ,1799346222 ,1999698222 ,21243444 ,71035444 ,80033333 ,52705333 ,138049333 ,118657333 ,21345333 ,124577333 ,173601333 ,1778663222 ,1132487222 ,2109959222 ,1735815222 ,529901111 ,10960444 ,111542333 ,62550333 ,110550333 ,126806333 ,1997980222 ,1751836222 ,79691333 ,1861681222 ,1786257222 ,1922193222 ,2032241222 ,1930609222 ,789623111 ,72570444 ,160704333 ,11520333 ,1738406222 ,118517333 ,71861333 ,171029333 ,1882459222 ,1912411222 ,1837243222 ,2079835222 ,107370331 ,151914333 ,1742096222 ,1745840222 ,2054384222 ,54111 ,44511333 ,113887333 ,1970341222 ,1900677222 ,1940165222 ,1825285222 ,1735045222 ,2013317222 ,2146885222 ,2081285222 ,2000613222 ,72430444 ,29652333 ,43156333 ,18388333 ,60148333 ,2107770222 ,868192111 ,64771444 ,45219444 ,361333 ,8905333 ,2000559222 ,1865295222 ,53111 ,72312444 ,160926333 ,116638333 ,1835684222 ,1741444222 ,1843684222 ,2015460222 ,964420222 ,2139940222 ,54573444 ,86259333 ,1765081222 ,1997881222 ,1765177222 ,1970713222 ,1959513222 ,140744333 ,180072333 ,85576333 ,1735214222 ,1971406222 ,1949294222 ,30711444 ,3863444 ,91581333 ,151965333 ,1978403222 ,1882883222 ,1952419222 ,1882371222 ,32684444 ,13292444 ,1823224222 ,2053272222 ,1928696222 ,1807672222 ,2128344222 ,163911333 ,89735333 ,112167333 ,128167333 ,2044333222 ,2042509222 ,1894605222 ,1903533222 ,11222444 ,53270444 ,124348333 ,60476333 ,181788333 ,2147170222 ,2137410222 ,1804642222 ,2013250222 ,2069218222 ,1959778222 ,27729333 ,79985333 ,1756183222 ,2091351222 ,1743095222 ,1795991222 ,221111 ,109062333 ,41254333 ,99910333 ,147366333 ,193318333 ,2051148222 ,1788556222 ,1777228222 ,32277444 ,142427333 ,149403333 ,69851333 ,2034593222 ,1918945222 ,12807111 ,1434311111 ,172336333 ,430966222 ,1938102222 ,1988374222 ,1741590222 ,1891734222 ,1798486222 ,1996022222 ,2118710222 ,1922102222 ,5727444 ,55551444 ,3455444 ,132421333 ,1869963222 ,1931659222 ,1901195222 ,1988491222 ,2062219222 ,42996444 ,74132444 ,20852444 ,161274333 ,133626333 ,1808896222 ,2022880222 ,1996352222 ,1434342111 ,61929444 ,171087333 ,1911189222 ,2130549222 ,1864277222 ,1238869222 ,2098837222 ,1912981222 ,2064245222 ,2137781222 ,789595111 ,190788333 ,1949642222 ,952778222 ,952714222 ,2014922222 ,1873450222 ,1816586222 ,1434288111 ,72211444 ,18617333 ,116953333 ,73113333 ,2123263222 ,2116799222 ,1962207222 ,2089151222 ,61928444 ,143918333 ,99566333 ,101550333 ,172142333 ,2063828222 ,2025428222 ,1434362111 ),
Upd_Txf_BatchID INTEGER COMPRESS (111043331 ,43581444 ,23453444 ,170659333 ,49891333 ,142179333 ,104899333 ,129347333 ,120899333 ,120323333 ,2005961222 ,1937961222 ,46258444 ,8664333 ,151864333 ,27544333 ,163224333 ,2098430222 ,1891934222 ,1389333 ,102861333 ,50189333 ,130733333 ,163309333 ,1753747222 ,1855667222 ,1846579222 ,1978963222 ,2015667222 ,109218333 ,105890333 ,90818333 ,1814376222 ,2100616222 ,108823333 ,2128029222 ,2005757222 ,1981053222 ,119628333 ,121420333 ,1990354222 ,1853074222 ,1999698222 ,21243444 ,71035444 ,80033333 ,52705333 ,138049333 ,118657333 ,21345333 ,173601333 ,1778663222 ,2004903222 ,2109959222 ,2107143222 ,1874119222 ,10960444 ,111542333 ,62550333 ,110550333 ,126806333 ,1997980222 ,1751836222 ,63243333 ,79691333 ,1861681222 ,1786257222 ,1922193222 ,2032241222 ,1930609222 ,72570444 ,129632333 ,160704333 ,44288333 ,11520333 ,6735444 ,118517333 ,71861333 ,200181333 ,171029333 ,1882459222 ,1912411222 ,1837243222 ,2079835222 ,1861339222 ,107370331 ,151914333 ,193994333 ,44511333 ,113887333 ,1970341222 ,1900677222 ,1940165222 ,1825285222 ,2013317222 ,2146885222 ,2081285222 ,2000613222 ,72430444 ,29652333 ,43156333 ,18388333 ,60148333 ,118356333 ,2107770222 ,24387444 ,64771444 ,45219444 ,21897333 ,361333 ,8905333 ,2000559222 ,72312444 ,14456444 ,64472444 ,124926333 ,134302333 ,160926333 ,116638333 ,1835684222 ,1843684222 ,2015460222 ,2139940222 ,54573444 ,86259333 ,1765081222 ,1997881222 ,2116505222 ,1765177222 ,1970713222 ,1959513222 ,114504333 ,140744333 ,180072333 ,85576333 ,72744333 ,1971406222 ,2012526222 ,1949294222 ,30711444 ,3863444 ,91581333 ,181117333 ,151965333 ,1978403222 ,1952419222 ,1882371222 ,32684444 ,13292444 ,1823224222 ,2053272222 ,1928696222 ,1807672222 ,2128344222 ,89735333 ,112167333 ,119975333 ,2044333222 ,2042509222 ,1894605222 ,1903533222 ,1843245222 ,11222444 ,53270444 ,124348333 ,60476333 ,181788333 ,173276333 ,2147170222 ,2137410222 ,1804642222 ,2013250222 ,1959778222 ,56331444 ,27729333 ,79985333 ,164081333 ,162257333 ,1882551222 ,2091351222 ,1795991222 ,140582333 ,109062333 ,111494333 ,41254333 ,99910333 ,147366333 ,11974333 ,193318333 ,2051148222 ,1788556222 ,1777228222 ,32277444 ,142427333 ,149403333 ,69851333 ,53147333 ,2034593222 ,1918945222 ,102576333 ,172336333 ,1938102222 ,1988374222 ,1891734222 ,1798486222 ,1996022222 ,2118710222 ,1922102222 ,5727444 ,55551444 ,3455444 ,144805333 ,132421333 ,1869963222 ,1931659222 ,1901195222 ,1988491222 ,2062219222 ,42996444 ,74132444 ,20852444 ,161274333 ,133626333 ,1852576222 ,2022880222 ,61929444 ,171087333 ,1911189222 ,2130549222 ,1864277222 ,2098837222 ,1912981222 ,2137781222 ,190788333 ,80260333 ,171492333 ,1949642222 ,1873450222 ,1816586222 ,72211444 ,18617333 ,116953333 ,73113333 ,2116799222 ,1962207222 ,2089151222 ,75528444 ,61928444 ,143918333 ,99566333 ,101550333 ,117486333 ,2063828222 ,2025428222 ))
PRIMARY INDEX XPKT0305_ACCOUNT_BALANCE_TYPE ( Account_Num ,Account_Modifier_Num )
PARTITION BY RANGE_N(End_Dt BETWEEN '2001-01-01' AND '2020-12-31' EACH INTERVAL '1' DAY ,
NO RANGE, UNKNOWN);

Table-2:
=====
Request Text
CREATE SET TABLE DO_TEDW.I$_TX0305_877209444 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Account_Num VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Account_Modifier_Num CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Acct_Bal_Type_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Time_Period_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
Calculation_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Acct_Bal_Amt DECIMAL(18,2),
Acct_Crncy_Acct_Bal_Amt DECIMAL(18,2),
Data_Source_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
BusinessDate DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX ( Account_Num ,Account_Modifier_Num ,Acct_Bal_Type_Cd ,
Time_Period_Cd ,Calculation_Flg );

Table -3: (control log table)
======
Request Text
CREATE SET TABLE DO_TEDW.CTLFW_TRANSFORM ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Txf_Pkg_Name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
BusinessDate DATE FORMAT 'yyyymmdd',
Txf_BatchID INTEGER,
TD_Session_ID INTEGER,
T_TblName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
T_DBName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
Rows_Inserted INTEGER,
Rows_Updated INTEGER,
Rows_Deleted INTEGER,
RunDate DATE FORMAT 'yyyymmdd',
RunTime INTEGER,
TransformType BYTEINT)
PRIMARY INDEX ( Txf_Pkg_Name );

These 3 tables are used in the above update query..

Please let me know your suggestions on this to improve query performance..

dnoeth · ‎01-09-2011

It can't be the actual query as "sample" is a keyword which can't be used as a database name without double quotes and the target table is called
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST in explain but not
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD.

Step #7 is an unconstrained product join (1=1), which is plain wrong.
If this was a select it would be caused by using the table name instaed of the table alias.

When i run the creates and explain your query it's showing only 2 joins skipping that stupid product join.

Dieter

l_k · ‎01-09-2011

Dieter,

The original query is given below : (just i changed only database name because of some reason)

UPDATE DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST A
SET End_Dt = (CAST ('2011-01-05' AS DATE FORMAT 'YYYY-MM-DD') - 1)
,Upd_Txf_BatchID = 877209444 ,Record_Deleted_Flag = 1
WHERE
( DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Account_Num ,
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Account_Modifier_Num
)

NOT IN
(
SELECT Account_Num ,Account_Modifier_Num
FROM DO_TEDW.I$_TX0305_877209444
)
AND A.End_Dt IS NULL
AND A.Record_Deleted_Flag = 0
AND A.Start_Dt < '2011-01-05'
AND A.Ins_Txf_BatchID IN
(
SELECT Txf_BatchID
FROM DO_TEDW.CTLFW_TRANSFORM WHERE Txf_Pkg_Name = 'PKG_I_TX0305_S1240_01_D' )

I hope this PJ is due to NOT IN condition....please let me know how to rewrite using LEFt JOIN instead of NOT IN? and also share your ideas
based on the above DDLs and Actual query..

Thanks in advance..

dnoeth · ‎01-10-2011

Now it's easy:
When you define an alias "A" for DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST you have to use it throughout the query, it's not a secondary name, it's *replacing* the original tablename.
In the first WHERE-condition you still use the original name, which results in the product join.
It's a Teradata-specific behaviour and quite dangerous, other DBMSes might return an error message or silentky replace the name with the alias.

Your query is a mixture of the old (pre-SQL) TEQUEL-syntax and SQL, in TEQUEL this was valid:
RETRIEVE DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Account_Num;
You can still run that query using BTEQ.
Btw, SQL Assistant will complain about an "invalid query", because he parses text locally before submitting it.

So simply change the code to:

WHERE
( A.Account_Num ,
A.Account_Modifier_ Num
)
NOT IN ...

Dieter

l_k · ‎01-13-2011

Hi Dieter,

After modifying the query as you suggested,it is using product join...

Modified query is :

UPDATE DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER
SET
End_Dt = (CAST ('2011-01-05' AS DATE FORMAT 'YYYY-MM-DD') - 1)
,Upd_Txf_BatchID = 877209444
,Record_Deleted_Flag = 1
WHERE
( DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Account_Num
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Account_Modifier_Num
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Acct_Bal_Type_Cd
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Time_Period_Cd
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Calculation_Flg
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Data_Source_Cd
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.BusinessDate )
NOT IN
(
SELECT
Account_Num
,Account_Modifier_Num
,Acct_Bal_Type_Cd
,Time_Period_Cd
,Calculation_Flg
,Data_Source_Cd
,BusinessDate
FROM DP_UEDW.I$_TX0305_877209444 )
AND End_Dt IS NULL
AND Record_Deleted_Flag = 0
AND Start_Dt < '2011-01-05'
AND Ins_Txf_BatchID
IN
(
SELECT Txf_BatchID
FROM DP_TEDW.CTLFW_TRANSFORM
WHERE Txf_Pkg_Name = 'PKG_I_TX0305_S1240_01_D' )

Explain plan:
6) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from a single partition of
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST with a condition of (
"DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.End_Dt = NULL") with a
residual condition of (
"(DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Record_Deleted_Flag =
0) AND ((DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.End_Dt IS NULL)
AND (DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Start_Dt < DATE
'2011-01-05'))"), which is joined to Spool 2 (Last Use) by
way of an all-rows scan.
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST and Spool 2 are joined
using an exclusion product join, with a join condition of (
"(DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Account_Num =
Account_Num) AND
(DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Account_Modifier_Num =
Account_Modifier_Num)"). The result goes into Spool 3
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. Then we do a SORT to order Spool 3 by the hash
code of (
DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST.Ins_Txf_BatchID). The
size of Spool 3 is estimated with no confidence to be 216
rows (4,752 bytes). The estimated time for this step is 0.00
seconds.

*** It is showing Product join,i hope this is due to NOT IN condition,i have created Ins_Txf_BatchID as a Secondary index,but PE is not using that..
How to force the PE to use the SI and remove the PJ ?

dnoeth · ‎01-13-2011

The NOT IN is the "exclusion" join, the PJ is because the optimizer thinks (based on the existing statistics) this is the most efficient way (based on the join-condition he could use other join types).

But the previous unconstrained PJ is missing now and this was your main problem.

Dieter

l_k · ‎01-15-2011

Hi Dieter,

Thanks for your quick response..

Moreover,i have introduced the column "Ins_Txf_BatchID" as SI (secondary Index) but the above
update query is not using the secondary index when i saw the EXPLAIN plan..
The above query is:
UPDATE DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER
SET
End_Dt = (CAST ('2011-01-05' AS DATE FORMAT 'YYYY-MM-DD') - 1)
,Upd_Txf_BatchID = 877209444
,Record_Deleted_Flag = 1
WHERE
( DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Account_Num
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Account_Modifi er_Num
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Acct_Bal_Type_ Cd
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Time_Period_Cd
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Calculation_Fl g
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.Data_Source_Cd
,DD_TEDW.TX0305_ACCT_BAL_TYPE_DD_TWOPER.BusinessDate )
NOT IN
(
SELECT
Account_Num
,Account_Modifier_Num
,Acct_Bal_Type_Cd
,Time_Period_Cd
,Calculation_Flg
,Data_Source_Cd
,BusinessDate
FROM DP_UEDW.I$_TX0305_877209444 )
AND End_Dt IS NULL
AND Record_Deleted_Flag = 0
AND Start_Dt < '2011-01-05'
AND Ins_Txf_BatchID
IN
(
SELECT Txf_BatchID
FROM DP_TEDW.CTLFW_TRANSFORM
WHERE Txf_Pkg_Name = 'PKG_I_TX0305_S1240_01_D' )

May i know,why it's not using SI? (if it's because of NOT IN,then how to modify the above one to use SI)

Thanks.