Suggestion for a query..

Database
l_k
Enthusiast

Suggestion for a query..

Hi All,

In our queries,most of them are having the END_DT column and we are checking END_DT is null in most of the queries..

for example one query:
SELECT S.Account_Num ,S.Account_Modifier_Num ,S.Acct_Bal_Type_Cd ,S.Time_Period_Cd ,S.Calculation_Flg ,S.Data_Source_Cd ,S.BusinessDate ,S.Acct_Bal_Amt ,S.Acct_Crncy_Acct_Bal_Amt ,'2011-01-05' ,877209444 ,0
FROM DP_EDW._TX03_877209444 S
WHERE ( S.Account_Num ,S.Account_Modifier_Num ,S.Acct_Bal_Type_Cd ,S.Time_Period_Cd ,S.Calculation_Flg ,S.Data_Source_Cd ,S.BusinessDate )
NOT IN ( SELECT Account_Num ,Account_Modifier_Num ,Acct_Bal_Type_Cd ,Time_Period_Cd ,Calculation_Flg ,Data_Source_Cd ,BusinessDate
FROM DP_EDW.TX0305_ACCT_BAL_TYPE_DD
WHERE End_Dt IS NULL

So,what type of INDEX should i use to access only END_DT is null part? ( i can't go for PPI currently)
(End_dt will be updated once the transaction status is closed instead of null value)..

Thanks for your help...
9 REPLIES
Senior Apprentice

Re: Suggestion for a query..

CREATE INDEX (END_DT) ON DP_EDW.TX0305_ACCT_BAL_TYPE_DD;

But i don't know if this will be actually helpfull:
What percentage of End_Dates are NULL, i.e. is the selectivity high enough for the optimizer to use the index?
Do i need to collect stats on End_Dt?
And when do i have to recollect?
Will the index be used for other queries, too?

Btw, updating an indexed column is a large overhead, so another option might be a Sparse Join Index:
CREATE JOIN INDEX ... AS
SELECT Account_Num ,Account_Modifier_Num ,Acct_Bal_Type_Cd ,Time_Period_Cd ,Calculation_Flg ,Data_Source_Cd ,BusinessDate
FROM DP_EDW.TX0305_ACCT_BAL_TYPE_DD
WHERE End_Dt IS NULL
PRIMARY INDEX (DP_EDW._TX03_877209444's PI columns)

Dieter
l_k
Enthusiast

Re: Suggestion for a query..

The column End_dt has 90% of NULL value..(we are using the condition End_dt is NULL in all the places )...Statistic is collected everyday (you may suggest if it's not required)..It's being used across all the queries..
I am giving below one flow for your kind reference:This is the common flow of execution..

Query 1: Temporary table creation:

CREATE SET table DP_UEDW.I$_TX0305_877209444 ,no fallback, no before journal,
no after journal ( Account_Num VARCHAR(255) NOT NULL, Account_Modifier_Num CHAR(18) NOT NULL,
Acct_Bal_Type_Cd CHAR(10) NOT NULL, Time_Period_Cd CHAR(10) NULL,
Calculation_Flg CHAR(1) NOT NULL, Acct_Bal_Amt DECIMAL(18,
2) NULL, Acct_Crncy_Acct_Bal_Amt DECIMAL(18,2) NULL, Data_Source_Cd CHAR(10) NULL,
BusinessDate DATE NULL )
PRIMARY INDEX (Account_Num,Account_Modifier_Num,
Acct_Bal_Type_Cd,Time_Period_Cd,Calculation_Flg)

Query 2:
-------
-- insertion INTO I$ TABLE
LOCKING TABLE DP_WEDW.XV_TX0305_S1240_01_D_01 FOR ACCESS
INSERT INTO DP_UEDW.I$_TX0305_877209444 ( Account_Num ,Account_Modifier_Num ,Acct_Bal_Type_Cd
,Time_Period_Cd ,Calculation_Flg ,Data_Source_Cd ,BusinessDate ,Acct_Bal_Amt ,Acct_Crncy_Acct_Bal_Amt )
SELECT TX0305.Account_Num ,TX0305.ACCOUNT_MODIFIER_NUM ,TX0305.Acct_Bal_Type_Cd ,TX0305.Time_Period_Cd ,TX0305.Calculation_Flg ,'COLL_MY' ,'2011-01-05'
,TX0305.Acct_Bal_Amt ,TX0305.Acct_Crncy_Acct_Bal_Amt
FROM DP_WEDW.XV_TX0305_S1240_01_D_01 TX0305
WHERE (1=1)

query 3:
-------
LOCKING DP_TEDW.CTLFW_TRANSFORM FOR ACCESS
UPDATE DP_TEDW.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 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' )

Query 4:
--------
UPDATE DP_TEDW.TX0305_ACCT_BAL_TYPE_DD
FROM DP_UEDW.I$_TX0305_877209444 S
SET End_Dt = (CAST ('2011-01-05' AS DATE FORMAT 'YYYY-MM-DD') - 1) ,Upd_Txf_BatchID = 877209444
WHERE S.Account_Num = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Account_Num AND S.Account_Modifier_Num = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Account_Modifier_Num AND S.Acct_Bal_Type_Cd = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Acct_Bal_Type_Cd AND S.Time_Period_Cd = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Time_Period_Cd AND S.Calculation_Flg = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Calculation_Flg AND S.Data_Source_Cd = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Data_Source_Cd AND S.BusinessDate = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.BusinessDate AND ( Record_Deleted_Flag = 1 OR ( (COALESCE(DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Acct_Bal_Amt,
'') <> COALESCE(S.Acct_Bal_Amt, '')) OR (COALESCE(DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Acct_Crncy_Acct_Bal_Amt,
'') <> COALESCE(S.Acct_Crncy_Acct_Bal_Amt, '')) ) )
AND DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Start_Dt < '2011-01-05'
AND DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.End_Dt IS NULL

Query 5:
-------
INSERT INTO DP_TEDW.TX0305_ACCT_BAL_TYPE_DD ( Account_Num ,Account_Modifier_Num ,Acct_Bal_Type_Cd ,Time_Period_Cd ,
Calculation_Flg ,Data_Source_Cd ,BusinessDate ,Acct_Bal_Amt ,Acct_Crncy_Acct_Bal_Amt ,Start_Dt ,Ins_Txf_BatchID ,Record_Deleted_Flag )

SELECT S.Account_Num ,S.Account_Modifier_Num ,S.Acct_Bal_Type_Cd ,S.Time_Period_Cd ,S.Calculation_Flg ,S.Data_Source_Cd ,S.BusinessDate
,S.Acct_Bal_Amt ,S.Acct_Crncy_Acct_Bal_Amt ,'2011-01-05' ,877209444 ,0
FROM DP_UEDW.I$_TX0305_877209444 S
WHERE S.Account_Num = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Account_Num AND S.Account_Modifier_Num = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Account_Modifier_Num
AND S.Acct_Bal_Type_Cd = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Acct_Bal_Type_Cd AND
S.Time_Period_Cd = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Time_Period_Cd AND S.Calculation_Flg = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Calculation_Flg
AND S.Data_Source_Cd = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Data_Source_Cd
AND S.BusinessDate = DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.BusinessDate
AND DP_TEDW.TX0305_ACCT_BAL_TYPE_DD.Upd_Txf_BatchID = 877209444

Query 6:
-------
INSERT INTO DP_TEDW.TX0305_ACCT_BAL_TYPE_DD ( Account_Num ,Account_Modifier_Num ,Acct_Bal_Type_Cd ,Time_Period_Cd ,Calculation_Flg ,Data_Source_Cd ,BusinessDate ,Acct_Bal_Amt ,Acct_Crncy_Acct_Bal_Amt ,Start_Dt ,Ins_Txf_BatchID ,Record_Deleted_Flag )
SELECT S.Account_Num ,S.Account_Modifier_Num ,S.Acct_Bal_Type_Cd ,S.Time_Period_Cd ,S.Calculation_Flg ,S.Data_Source_Cd ,S.BusinessDate ,S.Acct_Bal_Amt ,S.Acct_Crncy_Acct_Bal_Amt ,'2011-01-05' ,877209444 ,0
FROM DP_UEDW.I$_TX0305_877209444 S
WHERE ( S.Account_Num ,S.Account_Modifier_Num ,S.Acct_Bal_Type_Cd ,S.Time_Period_Cd ,S.Calculation_Flg ,S.Data_Source_Cd ,S.BusinessDate )
NOT IN ( SELECT Account_Num ,Account_Modifier_Num ,Acct_Bal_Type_Cd ,Time_Period_Cd ,Calculation_Flg ,Data_Source_Cd ,BusinessDate
FROM DP_TEDW.TX0305_ACCT_BAL_TYPE_DD
WHERE End_Dt IS NULL
)

In query no. 5,I introduced Upd_Txf_BatchID as SI (secondary Index),so it's impact CPU got reduced...
But Query no. 3,4 and 6 Is taking long time to run.....(PI is only acct_no and acct_modifier_num in the transaction table TX0305_ACCT_BAL_TYPE_DD)
Impact CPU for the query no. 6 is in 5 digit..This is the common flow across 2 batches..

I need your suggestion for the above 3 queries...It will be helpful to improve and reduce resource crunching also.. (End_dt is being updated in all the update queries only if ENd_dt is null,if it's not null,it means transaction got closed from ths source)

Would appreciate your help....
Senior Apprentice

Re: Suggestion for a query..

Did you actually check (using DBQL data) which steps consume most resources?

If the target table TX0305_ACCT_BAL_TYPE_DD is the same you posted in a previous thread, I$_TX0305_877209444 should have a matching PI on (Account_Num, Account_Modifier_Num).

If End_dt actually "has 90% of NULL" then no index will be helpfull, especially if you update that column.

Dieter
l_k
Enthusiast

Re: Suggestion for a query..

Hi Dieter,
I'll post the explain plan on coming friday..Sorry for the delay....
l_k
Enthusiast

Re: Suggestion for a query..

Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct Dp_WEDW."pseudo table" for read on a
RowHash to prevent global deadlock for
Dp_WEDW.I$_TX0305_877209444_T.
2) Next, we lock a distinct DP_WEDW."pseudo table" for write on a
RowHash to prevent global deadlock for
DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.
3) We lock Dp_WEDW.I$_TX0305_877209444_T for read, and we lock
DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T for write.
4) We do an all-AMPs RETRIEVE step from Dp_WEDW.I$_TX0305_877209444_T
by way of an all-rows scan with no residual conditions into Spool
2 (all_amps), which is redistributed by the hash code of (
Dp_WEDW.I$_TX0305_877209444_T.Account_Modifier_Num,
Dp_WEDW.I$_TX0305_877209444_T.Account_Num) to all AMPs. Then we
do a SORT to order Spool 2 by row hash and the sort key in spool
field1 eliminating duplicate rows. The size of Spool 2 is
estimated with low confidence to be 2,100 rows (919,800 bytes).
The estimated time for this step is 0.01 seconds.
5) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from a single partition of
DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T with a condition of (
"DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.End_Dt = NULL") with a
residual condition of (
"(DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.Record_Deleted_Flag = 0)
AND ((DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.End_Dt IS NULL) AND
(DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.Start_Dt < DATE
'2010-12-01'))"), which is joined to Spool 2 (Last Use) by
way of an all-rows scan. DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T
and Spool 2 are joined using a sliding-window exclusion merge
join (contexts = 1, 8), with a join condition of (
"(DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.Account_Num = Account_Num)
AND ((DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.Account_Modifier_Num
= Account_Modifier_Num) AND
((DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.Acct_Bal_Type_Cd =
Acct_Bal_Type_Cd) AND
(DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.Data_Source_Cd =
Data_Source_Cd )))"). The result goes into Spool 3
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. Then we do a SORT to order Spool 3 by
the hash code of (
DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.Ins_Txf_BatchID). The size
of Spool 3 is estimated with no confidence to be 1,137,790
rows (25,031,380 bytes). The estimated time for this step is
0.07 seconds.
2) We do a single-AMP RETRIEVE step from
DP_CTLFW.GCTLFW_TRANSFORM_GJI by way of the primary index
"DP_CTLFW.GCTLFW_TRANSFORM_GJI.Txf_Pkg_Name =
'PKG_I_TX0305_FNL_S0130_02_D'" with no residual conditions
into Spool 6 (all_amps), which is redistributed by the hash
code of (DP_CTLFW.GCTLFW_TRANSFORM_GJI.Txf_BatchID) 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 high confidence to be 11,194 rows (279,850
bytes). The estimated time for this step is 0.12 seconds.
6) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
an all-rows scan into Spool 5 (all_amps) (compressed columns
allowed), which is duplicated on all AMPs. Then we do a SORT to
order Spool 5 by the hash code of (
DP_CTLFW.GCTLFW_TRANSFORM_GJI.Txf_BatchID). The size of Spool 5
is estimated with high confidence to be 1,958,950 rows (
48,973,750 bytes).
7) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an
all-rows scan, which is joined to Spool 5 (Last Use) by way of an
all-rows scan. Spool 3 and Spool 5 are joined using an inclusion
merge join, with a join condition of ("Ins_Txf_BatchID =
Txf_BatchID"). The result goes into Spool 1 (all_amps), which is
redistributed by the rowkey of (
DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T.ROWID) to all AMPs. Then we do
a SORT to order Spool 1 by the sort key in spool field1. The size
of Spool 1 is estimated with no confidence to be 1,137,790 rows (
20,480,220 bytes). The estimated time for this step is 0.12
seconds.
8) We do an all-AMPs MERGE DELETE to
DP_WEDW.TX0305_ACCT_BAL_TYPE_DD_T from Spool 1 (Last Use) via the

The above is the EXPLAIN plan and the update step is taking time.....
Senior Apprentice

Re: Suggestion for a query..

Why do you want to create an index on end_dt?
This seems to be the partitioning column for TX0305_ACCT_BAL_TYPE_DD.
Updating it is the same as delete (step 8) and re-insert the rows (step 9).
Basic rules in Teradata include:
1. The PI + partition should be rarely updated.
2. Source and target tables should have matching PIs for fastest performance.

The update might also suffer from duplicate row checks in a SET table. What's the average number of rows per value per partition for the PI?

Dieter
l_k
Enthusiast

Re: Suggestion for a query..

Yeah..End_dt is a PPI...Pls find the below average no. of records for your checking..

End_Dt Count(*) Avg. rows(%)
? 198676276 10.19
20/01/2010 118 0.00
21/01/2010 124 0.00
24/01/2010 1105 0.00
25/01/2010 150 0.00
26/01/2010 103 0.00
27/01/2010 133 0.00
28/01/2010 119 0.00
31/01/2010 1125 0.00
01/02/2010 129 0.00
02/02/2010 107 0.00
03/02/2010 88 0.00
04/02/2010 162 0.00
07/02/2010 1072 0.00
08/02/2010 161 0.00
09/02/2010 122 0.00
10/02/2010 125 0.00
11/02/2010 107 0.00
16/02/2010 1211 0.00
17/02/2010 187 0.00
18/02/2010 331669 0.00
19/02/2010 289145 0.00
20/02/2010 238637 0.00
21/02/2010 332910 0.00
22/02/2010 353180 0.00
23/02/2010 339640 0.00
24/02/2010 367726 0.00
25/02/2010 321304 0.00
26/02/2010 278693 0.00
27/02/2010 818902 0.00
28/02/2010 7205955 0.00
01/03/2010 512202 0.00
02/03/2010 366048 0.00
03/03/2010 359616 0.00
04/03/2010 363108 0.00
05/03/2010 600175 0.00
06/03/2010 302099 0.00
07/03/2010 355557 0.00
08/03/2010 394842 0.00
09/03/2010 346912 0.00
10/03/2010 340373 0.00
11/03/2010 339494 0.00
12/03/2010 302998 0.00
13/03/2010 244320 0.00
14/03/2010 409463 0.00
15/03/2010 409630 0.00
16/03/2010 1168640 0.00
17/03/2010 21803770 0.01
18/03/2010 344248 0.00
19/03/2010 311428 0.00
20/03/2010 241619 0.00
21/03/2010 25949626 0.00
22/03/2010 346203 0.00
23/03/2010 390593 0.00
24/03/2010 356494 0.00
25/03/2010 364109 0.00
26/03/2010 302183 0.00
27/03/2010 244591 0.00
28/03/2010 372827 0.00
29/03/2010 418368 0.00
30/03/2010 900750 0.00
31/03/2010 7247171 0.00
01/04/2010 389341 0.00
02/04/2010 401244 0.00
03/04/2010 290511 0.00
04/04/2010 395654 0.00
05/04/2010 448477 0.00
06/04/2010 396517 0.00
07/04/2010 2675727 0.00
08/04/2010 414196 0.00
09/04/2010 1272145 0.00
10/04/2010 314495 0.00
11/04/2010 374327 0.00
12/04/2010 397239 0.00
13/04/2010 357289 0.00
14/04/2010 356587 0.00
15/04/2010 423313 0.00
16/04/2010 294062 0.00
17/04/2010 244008 0.00
18/04/2010 352986 0.00
19/04/2010 389124 0.00
20/04/2010 376566 0.00
21/04/2010 363488 0.00
22/04/2010 356981 0.00
23/04/2010 294552 0.00
24/04/2010 243007 0.00
25/04/2010 2791728 0.00
26/04/2010 398537 0.00
27/04/2010 377927 0.00
28/04/2010 15918824 0.00
29/04/2010 995662 0.00
30/04/2010 6416090 0.00
01/05/2010 312023 0.00
02/05/2010 1014834 0.00
03/05/2010 516784 0.00
04/05/2010 402713 0.00
05/05/2010 388283 0.00
06/05/2010 390461 0.00
07/05/2010 740897 0.00
08/05/2010 311107 0.00
09/05/2010 383435 0.00
10/05/2010 414460 0.00
11/05/2010 367258 0.00
12/05/2010 359029 0.00
13/05/2010 361342 0.00
14/05/2010 298989 0.00
15/05/2010 243042 0.00
16/05/2010 366683 0.00
17/05/2010 459012 0.00
18/05/2010 357005 0.00
19/05/2010 354745 0.00
20/05/2010 380388 0.00
21/05/2010 310687 0.00
22/05/2010 246659 0.00
23/05/2010 364463 0.00
24/05/2010 401159 0.00
25/05/2010 395929 0.00
26/05/2010 387442 0.00
27/05/2010 307111 0.00
28/05/2010 252838 0.00
29/05/2010 253209 0.00
30/05/2010 907992 0.00
31/05/2010 7227877 0.00
01/06/2010 556284 0.00
02/06/2010 387890 0.00
03/06/2010 390189 0.00
04/06/2010 872706 0.00
05/06/2010 297191 0.00
06/06/2010 381600 0.00
07/06/2010 414546 0.00
08/06/2010 371076 0.00
09/06/2010 365517 0.00
10/06/2010 371101 0.00
11/06/2010 298970 0.00
12/06/2010 322069 0.00
13/06/2010 363132 0.00
14/06/2010 411921 0.00
15/06/2010 428297 0.00
16/06/2010 342832 0.00
17/06/2010 358783 0.00
18/06/2010 295542 0.00
19/06/2010 249911 0.00
20/06/2010 379473 0.00
21/06/2010 401651 0.00
22/06/2010 355679 0.00
23/06/2010 348349 0.00
24/06/2010 364242 0.00
25/06/2010 317253 0.00
26/06/2010 248968 0.00
27/06/2010 386760 0.00
28/06/2010 418596 0.00
29/06/2010 963639 0.00
30/06/2010 7111247 0.00
01/07/2010 547825 0.00
02/07/2010 804117 0.00
03/07/2010 295614 0.00
04/07/2010 399627 0.00
05/07/2010 447094 0.00
06/07/2010 391626 0.00
07/07/2010 381515 0.00
08/07/2010 380966 0.00
09/07/2010 307308 0.00
10/07/2010 322713 0.00
11/07/2010 376838 0.00
12/07/2010 401996 0.00
13/07/2010 362234 0.00
14/07/2010 360679 0.00
15/07/2010 431666 0.00
16/07/2010 295561 0.00
17/07/2010 250821 0.00
18/07/2010 390694 0.00
19/07/2010 395739 0.00
20/07/2010 376972 0.00
21/07/2010 369927 0.00
22/07/2010 364432 0.00
23/07/2010 301796 0.00
24/07/2010 253820 0.00
25/07/2010 373162 0.00
26/07/2010 404322 0.00
27/07/2010 383327 0.00
28/07/2010 373312 0.00
29/07/2010 392767 0.00
30/07/2010 877770 0.00
31/07/2010 6902124 0.00
01/08/2010 768832 0.00
02/08/2010 542705 0.00
03/08/2010 393903 0.00
04/08/2010 394158 0.00
05/08/2010 399754 0.00
06/08/2010 798032 0.00
07/08/2010 571509 0.00
08/08/2010 317416 0.00
09/08/2010 424507 0.00
10/08/2010 397287 0.00
11/08/2010 356951 0.00
12/08/2010 374962 0.00
13/08/2010 300479 0.00
14/08/2010 257827 0.00
15/08/2010 374641 0.00
16/08/2010 495860 0.00
17/08/2010 357517 0.00
18/08/2010 357448 0.00
19/08/2010 373915 0.00
20/08/2010 321334 0.00
21/08/2010 332121 0.00
22/08/2010 371346 0.00
23/08/2010 396472 0.00
24/08/2010 545489 0.00
25/08/2010 370836 0.00
26/08/2010 373237 0.00
27/08/2010 296974 0.00
28/08/2010 266305 0.00
29/08/2010 428044 0.00
30/08/2010 1872899 0.00
31/08/2010 6621987 0.00
01/09/2010 9456326 0.00
02/09/2010 9594244 0.00
03/09/2010 8233270 0.00
04/09/2010 1500465 0.00
05/09/2010 10811616 0.00
06/09/2010 10233303 0.00
07/09/2010 10192819 0.00
08/09/2010 10408064 0.00
09/09/2010 2317499 0.00
10/09/2010 9048953 0.00
11/09/2010 1466720 0.00
12/09/2010 10475777 0.00
13/09/2010 10310800 0.00
14/09/2010 10483619 0.00
15/09/2010 9962345 0.00
16/09/2010 10377720 0.00
17/09/2010 8408425 0.00
18/09/2010 1449982 0.00
19/09/2010 10987278 0.00
20/09/2010 10555547 0.00
21/09/2010 10342690 0.00
22/09/2010 10273539 0.00
23/09/2010 10363897 0.00
24/09/2010 8775234 0.00
25/09/2010 1625453 0.00
26/09/2010 11183533 0.00
27/09/2010 10987971 0.00
28/09/2010 10843184 0.00
29/09/2010 34702325 0.00
30/09/2010 30366232 0.00
01/10/2010 7683624 0.00
02/10/2010 1628626 0.00
03/10/2010 10228395 0.00
04/10/2010 9835997 0.00
05/10/2010 9981157 0.00
06/10/2010 9989845 0.00
07/10/2010 10197064 0.00
08/10/2010 8103222 0.00
09/10/2010 1609353 0.00
10/10/2010 11150637 0.00
11/10/2010 10153707 0.00
12/10/2010 10124459 0.00
13/10/2010 10092437 0.00
14/10/2010 10523942 0.00
15/10/2010 8541435 0.00
16/10/2010 1597289 0.00
17/10/2010 10945056 0.00
18/10/2010 10486608 0.00
19/10/2010 10445073 0.00
20/10/2010 10323199 0.00
21/10/2010 10383488 0.00
22/10/2010 8635767 0.00
23/10/2010 1540189 0.00
24/10/2010 10988637 0.00
25/10/2010 10861596 0.00
26/10/2010 10697541 0.00
27/10/2010 10855760 0.00
28/10/2010 10945344 0.00
29/10/2010 29200863 0.00
30/10/2010 6992698 0.00
31/10/2010 30751261 0.00
01/11/2010 9733109 0.00
02/11/2010 9740718 0.00
03/11/2010 9912753 0.00
04/11/2010 2329810 0.00
05/11/2010 8459019 0.00
06/11/2010 1617989 0.00
07/11/2010 11045087 0.00
08/11/2010 10193086 0.00
09/11/2010 10480449 0.00
10/11/2010 10016472 0.00
11/11/2010 11375159 0.00
12/11/2010 8813661 0.00
13/11/2010 1706727 0.00
14/11/2010 10940151 0.00
15/11/2010 10499654 0.00
16/11/2010 2835039 0.00
17/11/2010 11008568 0.00
18/11/2010 10386888 0.00
19/11/2010 8613305 0.00
20/11/2010 1626485 0.00
21/11/2010 10814942 0.00
22/11/2010 10518202 0.00
23/11/2010 10446217 0.00
24/11/2010 10565911 0.00
25/11/2010 10951250 0.00
26/11/2010 8908522 0.00
27/11/2010 1644770 0.00
28/11/2010 11395614 0.00
29/11/2010 36080181 0.00
30/11/2010 30515993 0.00
01/12/2010 9645410 0.00
02/12/2010 9789433 0.00
03/12/2010 8283694 0.00
04/12/2010 1561434 0.00
05/12/2010 11043525 0.00
06/12/2010 10101786 0.00
07/12/2010 10416347 0.00
08/12/2010 10017453 0.00
09/12/2010 10841793 0.00
10/12/2010 8387291 0.00
11/12/2010 1690340 0.00
12/12/2010 10731047 0.00
13/12/2010 10583349 0.00
14/12/2010 10917523 0.00
15/12/2010 10500496 0.00
16/12/2010 10692036 0.00
17/12/2010 8690097 0.00
18/12/2010 1636701 0.00
19/12/2010 11256897 0.00
20/12/2010 10930462 0.00
21/12/2010 10885429 0.00
22/12/2010 10889315 0.00
23/12/2010 11267639 0.00
24/12/2010 1717096 0.00
25/12/2010 1696734 0.00
26/12/2010 11568276 0.00
27/12/2010 11284335 0.00
28/12/2010 11098743 0.00
29/12/2010 11141434 0.00
30/12/2010 37969237 0.00
31/12/2010 10980168 0.00
01/01/2011 1512113 0.00
02/01/2011 28928527 0.00
03/01/2011 10634496 0.00
04/01/2011 10499008 0.00
05/01/2011 10815023 0.00
06/01/2011 10674108 0.00
07/01/2011 9161715 0.00
08/01/2011 1654812 0.00
09/01/2011 11572651 0.00
10/01/2011 10993686 0.00
11/01/2011 10588557 0.00
12/01/2011 10630525 0.00
13/01/2011 10874805 0.00
14/01/2011 8810839 0.00
15/01/2011 1607563 0.00
16/01/2011 21029644 0.00
17/01/2011 10945346 0.00
18/01/2011 10953819 0.00
19/01/2011 10536247 0.00
20/01/2011 11887778 0.00
21/01/2011 9025211 0.00
22/01/2011 1664006 0.00
23/01/2011 11572693 0.00
24/01/2011 11215860 0.00
25/01/2011 11242770 0.00
26/01/2011 11212345 0.00
27/01/2011 11632495 0.00
28/01/2011 9295794 0.00
29/01/2011 1730414 0.00
30/01/2011 36712318 0.00
31/01/2011 31130272 0.00
01/02/2011 12289281 0.00
02/02/2011 2657115 0.00
03/02/2011 2571480 0.00
04/02/2011 7660826 0.00
05/02/2011 1541731 0.00
06/02/2011 10941458 0.00
07/02/2011 83369 0.00
31/12/2099 381665 0.00

2023813642
Senior Apprentice

Re: Suggestion for a query..

i didn't mean the number of rows per partition, although now i know that there's almost 10 percent of the row in the UNKNOWN partition :-)

What's the approximate avg/max number of rows for each PI-column + partition-column combination?
Especially in the UNKNOWN partition this might be a large number.
Based on this one can estimate the number of duplicate row checks.

I ask you again, did you check the QueryLog which step is consuming most resources?
Any of the SELECT steps or the UPDATE/INSERT steps?

Some of the problems are:
1. non-matching PI of the temp table
2. bad partitioning of the target table for UPDATES, don't know if it can be changed
3. probably lots of duplicate row checks for INSERT/UPDATE

#1 is easy to change and test, but for #2  you have to insert/select the data into a new table.

Dieter
l_k
Enthusiast

Re: Suggestion for a query..

Yeah..You are correct.

When i checked the avg/max number of rows for each PI + Partition column combination is - 4 values per PI + per partition.

DBQLExplaintbl is not enabled here...when i checked via dbqlogtbl it was showing line no 1,1,2,3,4,4 ...I couldn't able to find out 1,1 coming twice...is it two steps for the line no. 1?

Thanks.