performance tuning or query tuning

UDA
Enthusiast

performance tuning or query tuning

DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1
WHERE EXISTS(SELECT '1' FROM
L_DLYBCIDDB.CREDIT_ENTRIES_TEMP T2
WHERE T1.MI_ACCOUNT_IDENTIFIER = T2.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT = T2.ENTRY_AMOUNT
AND ( (T1.ENTRY_SOURCE_CODE = T2.ENTRY_SOURCE_CODE
AND T1.ENTRY_CODE = T2.ENTRY_CODE
AND T1.ENTRY_DATE <= T2.ENTRY_DATE )
OR T1.ENTRY_SEQUENCE_NUMBER = T2.ENTRY_SEQUENCE_NUMBER)
)
AND T1.ENTRY_AMOUNT_CODE = 4;

CREDIT_ENTRIES_CT has nearly 2.2 billon rows.
CREDIT_ENTRIES_TEMP has nearly 6 million rows.

i have all the recommended stats.can we change the query ?Please help .....

4 REPLIES
Enthusiast

Re: performance tuning or query tuning

Step 1 - Get rid of the correlated subquery by:

DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1
WHERE T1.MI_ACCOUNT_IDENTIFIER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_AMOUNT
AND ( (T1.ENTRY_SOURCE_CODE = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_SOURCE_CODE
AND T1.ENTRY_CODE = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_CODE
AND T1.ENTRY_DATE <= L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_DATE )
OR T1.ENTRY_SEQUENCE_NUMBER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_SEQUENCE_NUMBER)
)
AND T1.ENTRY_AMOUNT_CODE = 4;

If no better, post the DDL for the two tables and an explain by putting a SHOW in front of the query.
Enthusiast

Re: performance tuning or query tuning

Thanks for ur valuable suggestion, but there is no much change in the CPU time, efficiency.

The DDL's for the two tables are the following.

Table 1:

======

CREATE MULTISET TABLE A_BCIDDB.CREDIT_ENTRIES_CT ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

FREESPACE = 15 PERCENT,

CHECKSUM = DEFAULT

(

MI_ACCOUNT_IDENTIFIER DECIMAL(10,0) NOT NULL,

ENTRY_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,

ENTRY_TIME CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

ENTRY_SOURCE_SYSTEM_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('D01','D02','D03','D04','D16','N03','N04','N05','N07','N10','N17','N30','N32','N37'),

ENTRY_SEQUENCE_NUMBER CHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

ENTRY_POSTING_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,

ENTRY_REJECT_CODE SMALLINT NOT NULL COMPRESS 0 ,

REJECTED_ENTRY_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ORIGINATOR_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ORIGINATOR_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ENTRY_AMOUNT_CODE SMALLINT NOT NULL COMPRESS (4 ,6 ),

ENTRY_SOURCE_CODE SMALLINT NOT NULL COMPRESS (15 ,20 ,22 ,35 ,36 ,37 ,38 ,40 ,43 ,44 ,45 ,50 ,60 ,61 ,65 ),

ENTRY_CODE SMALLINT NOT NULL COMPRESS (10 ,12 ,14 ,15 ,20 ,21 ,23 ,27 ,40 ,41 ,45 ,59 ,60 ),

ENTRY_2B_CODE SMALLINT NOT NULL COMPRESS (285 ,83 ,84 ,85 ,185 ),

ENTRY_2B_TLA SMALLINT NOT NULL COMPRESS (0 ,1 ,2 ,3 ,5 ,6 ,7 ,8 ,-6 ),

ENTRY_DESCRIPTION INTEGER NOT NULL COMPRESS 0 ,

ENTRY_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS (100.00 ,200.00 ,3.00 ,50.00 ,4.00 ,30.00 ,25.00 ,2.00 ,20.00 ,15.00 ,10.00 ,5.00 ),

ENTRY_CANCELLED_INDICATOR CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',

ORIG_BENEFICIARY_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ORIG_BENEFICIARY_ACC_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ENTRY_SOURCE_RECORD_REFERENCE CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',

BENEFICIARY_IDENTIFIER INTEGER NOT NULL COMPRESS 0 ,

CLEARED_NOTICE_INDICATOR CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('C','M','U'),

ENTRY_NARRATIVE_ONE CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',

ENTRY_NARRATIVE_TWO CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',

SOURCE_SUB_BRANCH_IDENTIFIER SMALLINT NOT NULL COMPRESS 0 ,

SOURCE_BRANCH_SORT_CODE INTEGER NOT NULL COMPRESS 0 ,

ITEM_COUNT INTEGER NOT NULL COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ),

CASH_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_ZERO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_ONE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_TWO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_THREE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_FOUR_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

REVERSAL_CODE BYTEINT NOT NULL DEFAULT 0 COMPRESS (0 ,1 ,2 ,3 ))

PRIMARY INDEX ( MI_ACCOUNT_IDENTIFIER ,ENTRY_DATE ,ENTRY_SEQUENCE_NUMBER );

Table 2:

======

CREATE MULTISET TABLE L_DLYBCIDDB.CREDIT_ENTRIES_TEMP ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

FREESPACE = 15 PERCENT,

CHECKSUM = DEFAULT

(

MI_ACCOUNT_IDENTIFIER DECIMAL(10,0) NOT NULL,

ENTRY_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL

,

ENTRY_TIME CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

ENTRY_SOURCE_SYSTEM_CODE CHAR(3) CHARACTER SET LATIN

NOT CASESPECIFIC NOT NULL COMPRESS 'D01',

ENTRY_SEQUENCE_NUMBER CHAR(14) CHARACTER SET LATIN

NOT CASESPECIFIC NOT NULL,

ENTRY_POSTING_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL

COMPRESS (DATE '2002-03-28'),

ENTRY_REJECT_CODE SMALLINT NOT NULL COMPRESS 0 ,

REJECTED_ENTRY_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ORIGINATOR_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ORIGINATOR_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ENTRY_AMOUNT_CODE SMALLINT NOT NULL COMPRESS 4 ,

ENTRY_SOURCE_CODE SMALLINT NOT NULL COMPRESS 20 ,

ENTRY_CODE SMALLINT NOT NULL COMPRESS 45 ,

ENTRY_2B_CODE SMALLINT NOT NULL COMPRESS 84 ,

ENTRY_2B_TLA SMALLINT NOT NULL COMPRESS 6 ,

ENTRY_DESCRIPTION INTEGER NOT NULL COMPRESS 0 ,

ENTRY_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 100.00 ,

ENTRY_CANCELLED_INDICATOR CHAR(1) CHARACTER SET LATIN

NOT CASESPECIFIC NOT NULL COMPRESS ' ',

ORIG_BENEFICIARY_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ORIG_BENEFICIARY_ACC_NUMBER INTEGER NOT NULL COMPRESS 0 ,

ENTRY_SOURCE_RECORD_REFERENCE CHAR(4) CHARACTER SET LATIN

NOT CASESPECIFIC COMPRESS ' ',

BENEFICIARY_IDENTIFIER INTEGER NOT NULL COMPRESS 0 ,

CLEARED_NOTICE_INDICATOR CHAR(1) CHARACTER SET LATIN

NOT CASESPECIFIC NOT NULL COMPRESS 'C',

ENTRY_NARRATIVE_ONE CHAR(18) CHARACTER SET LATIN

NOT CASESPECIFIC NOT NULL COMPRESS ' ',

ENTRY_NARRATIVE_TWO CHAR(18) CHARACTER SET LATIN

NOT CASESPECIFIC NOT NULL COMPRESS ' ',

SOURCE_SUB_BRANCH_IDENTIFIER SMALLINT NOT NULL COMPRESS 0 ,

SOURCE_BRANCH_SORT_CODE INTEGER NOT NULL COMPRESS 0 ,

ITEM_COUNT INTEGER NOT NULL COMPRESS 0 ,

CASH_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_ZERO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_ONE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_TWO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_THREE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

UNCLEARED_DAY_FOUR_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,

REVERSAL_CODE BYTEINT NOT NULL COMPRESS 0,

UNIQUE_ID INTEGER NOT NULL)

PRIMARY INDEX (MI_ACCOUNT_IDENTIFIER ,ENTRY_DATE ,ENTRY_SEQUENCE_NUMBER);

Explain plan:This is for a sample of 3 million rows for table1 and 5000 rows in table2, but actually there are more than 2 billion rows in table1 and 4 million in table2.So it is taking 4 hrs to run.

======================================================================

1) First, we lock a distinct A_gis_centraldb."pseudo table" for write

on a RowHash to prevent global deadlock for A_gis_centraldb.T1.

2) Next, we lock a distinct A_gis_centraldb."pseudo table" for read

on a RowHash to prevent global deadlock for A_gis_centraldb.T2.

3) We lock A_gis_centraldb.T1 for write, and we lock

A_gis_centraldb.T2 for read.

4) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from A_gis_centraldb.T1 by

way of an all-rows scan with a condition of (

"A_gis_centraldb.T1.ENTRY_AMOUNT_CODE = 4") into Spool 2

(all_amps) (compressed columns allowed), which is

redistributed by hash code to all AMPs. Then we do a SORT to

order Spool 2 by row hash. The size of Spool 2 is estimated

with high confidence to be 2,993,685 rows. The estimated

time for this step is 0.57 seconds.

2) We do an all-AMPs RETRIEVE step from A_gis_centraldb.T2 by

way of an all-rows scan with no residual conditions 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 and

the sort key in spool field1 eliminating duplicate rows. The

size of Spool 3 is estimated with high confidence to be 4,554

rows. The estimated time for this step is 0.02 seconds.

5) 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 ("(((ENTRY_SOURCE_CODE =

ENTRY_SOURCE_CODE) AND ((ENTRY_CODE = ENTRY_CODE) AND (ENTRY_DATE

<= ENTRY_DATE ))) OR (ENTRY_SEQUENCE_NUMBER =

ENTRY_SEQUENCE_NUMBER )) AND ((ENTRY_AMOUNT = ENTRY_AMOUNT) AND

(MI_ACCOUNT_IDENTIFIER = MI_ACCOUNT_IDENTIFIER ))"). The result

goes into Spool 1 (all_amps), which is redistributed by hash code

to all AMPs. Then we do a SORT to order Spool 1 by row hash and

the sort key in spool field1 eliminating duplicate rows. The size

of Spool 1 is estimated with index join confidence to be 4,547

rows. The estimated time for this step is 0.41 seconds.

6) We do an all-AMPs MERGE DELETE to A_gis_centraldb.T1 from Spool 1

(Last Use) via the row id.

7) Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

NOTE: The databases in the explain plan is the test database.

Thanks in advance
Enthusiast

Re: performance tuning or query tuning

The Primary Index on both these tables do not make sense, at least for this job. The suggested index is far more paractical, as long as spread is OK.
I enclose DDL for suggested primary index for both tables - you will need to set up copies using this DDL and load them with test data, then try the query shown. (Send a Show if any problems, as before.)
The problem is arising because both tables have to be redistributed before the query will run. Also, I have converted your OR'd join to two separate Deletes, using a multi-statement request.

After loading the data, check the distribution to make sure it is fairly even.
It may be worth suggesting that someone take a look at your physical design!

CREATE MULTISET TABLE A_BCIDDB.CREDIT_ENTRIES_CT
( MI_ACCOUNT_IDENTIFIER DECIMAL(10,0) NOT NULL,
ENTRY_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ENTRY_TIME CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENTRY_SOURCE_SYSTEM_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('D01','D02','D03','D04','D16','N03','N04','N05','N07','N10','N17','N30','N32','N37'),
ENTRY_SEQUENCE_NUMBER CHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENTRY_POSTING_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ENTRY_REJECT_CODE SMALLINT NOT NULL COMPRESS 0 ,
REJECTED_ENTRY_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT_CODE SMALLINT NOT NULL COMPRESS (4 ,6 ),
ENTRY_SOURCE_CODE SMALLINT NOT NULL COMPRESS (15 ,20 ,22 ,35 ,36 ,37 ,38 ,40 ,43 ,44 ,45 ,50 ,60 ,61 ,65 ),
ENTRY_CODE SMALLINT NOT NULL COMPRESS (10 ,12 ,14 ,15 ,20 ,21 ,23 ,27 ,40 ,41 ,45 ,59 ,60 ),
ENTRY_2B_CODE SMALLINT NOT NULL COMPRESS (285 ,83 ,84 ,85 ,185 ),
ENTRY_2B_TLA SMALLINT NOT NULL COMPRESS (0 ,1 ,2 ,3 ,5 ,6 ,7 ,8 ,-6 ),
ENTRY_DESCRIPTION INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT DECIMAL(15,2) NOT NULL
COMPRESS (100.00 ,200.00 ,3.00 ,50.00 ,4.00 ,30.00 ,25.00 ,2.00 ,20.00 ,15.00 ,10.00 ,5.00 ),
ENTRY_CANCELLED_INDICATOR CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ORIG_BENEFICIARY_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIG_BENEFICIARY_ACC_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_SOURCE_RECORD_REFERENCE CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
BENEFICIARY_IDENTIFIER INTEGER NOT NULL COMPRESS 0 ,
CLEARED_NOTICE_INDICATOR CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('C','M','U'),
ENTRY_NARRATIVE_ONE CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ENTRY_NARRATIVE_TWO CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',
SOURCE_SUB_BRANCH_IDENTIFIER SMALLINT NOT NULL COMPRESS 0 ,
SOURCE_BRANCH_SORT_CODE INTEGER NOT NULL COMPRESS 0 ,
ITEM_COUNT INTEGER NOT NULL COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ),
CASH_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ZERO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ONE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_TWO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_THREE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_FOUR_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
REVERSAL_CODE BYTEINT NOT NULL DEFAULT 0 COMPRESS (0 ,1 ,2 ,3 ))
PRIMARY INDEX ( MI_ACCOUNT_IDENTIFIER );

CREATE MULTISET TABLE L_DLYBCIDDB.CREDIT_ENTRIES_TEMP
( MI_ACCOUNT_IDENTIFIER DECIMAL(10,0) NOT NULL,
ENTRY_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ENTRY_TIME CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENTRY_SOURCE_SYSTEM_CODE CHAR(3) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS 'D01',
ENTRY_SEQUENCE_NUMBER CHAR(14) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL,
ENTRY_POSTING_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL
COMPRESS (DATE '2002-03-28'),
ENTRY_REJECT_CODE SMALLINT NOT NULL COMPRESS 0 ,
REJECTED_ENTRY_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT_CODE SMALLINT NOT NULL COMPRESS 4 ,
ENTRY_SOURCE_CODE SMALLINT NOT NULL COMPRESS 20 ,
ENTRY_CODE SMALLINT NOT NULL COMPRESS 45 ,
ENTRY_2B_CODE SMALLINT NOT NULL COMPRESS 84 ,
ENTRY_2B_TLA SMALLINT NOT NULL COMPRESS 6 ,
ENTRY_DESCRIPTION INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 100.00 ,
ENTRY_CANCELLED_INDICATOR CHAR(1) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ORIG_BENEFICIARY_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIG_BENEFICIARY_ACC_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_SOURCE_RECORD_REFERENCE CHAR(4) CHARACTER SET LATIN
NOT CASESPECIFIC COMPRESS ' ',
BENEFICIARY_IDENTIFIER INTEGER NOT NULL COMPRESS 0 ,
CLEARED_NOTICE_INDICATOR CHAR(1) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS 'C',
ENTRY_NARRATIVE_ONE CHAR(18) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ENTRY_NARRATIVE_TWO CHAR(18) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS ' ',
SOURCE_SUB_BRANCH_IDENTIFIER SMALLINT NOT NULL COMPRESS 0 ,
SOURCE_BRANCH_SORT_CODE INTEGER NOT NULL COMPRESS 0 ,
ITEM_COUNT INTEGER NOT NULL COMPRESS 0 ,
CASH_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ZERO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ONE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_TWO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_THREE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_FOUR_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
REVERSAL_CODE BYTEINT NOT NULL COMPRESS 0,
UNIQUE_ID INTEGER NOT NULL)
PRIMARY INDEX (MI_ACCOUNT_IDENTIFIER );

DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1
WHERE T1.MI_ACCOUNT_IDENTIFIER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT_CODE = 4;
AND T1.ENTRY_AMOUNT = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_AMOUNT
AND T1.ENTRY_SOURCE_CODE = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_SOURCE_CODE
AND T1.ENTRY_CODE = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_CODE
AND T1.ENTRY_DATE <= L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_DATE
; DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1
WHERE T1.MI_ACCOUNT_IDENTIFIER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT_CODE = 4;
AND T1.ENTRY_AMOUNT = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_AMOUNT
AND T1.ENTRY_SEQUENCE_NUMBER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_SEQUENCE_NUMBER
;
Enthusiast

Re: performance tuning or query tuning

Thanku worked some what better.