SQL & PL/SQL

Hi All,

I am using merge statement to update 30000 records from the tables having 55 lacs records.
But it is taking much time as i have to kill the session after 12 hours,as it was still going on.

If,Same update i m doing using cursors,it will finish in less than 3 hours.

Merge i was using is :-

MERGE INTO Table1 a
USING (SELECT MAX (TO_DATE ( TO_CHAR (contact_date, 'dd/mm/yyyy')
|| contact_time,
'dd/mm/yyyy HH24:Mi:SS'
)
) m_condate,
appl_id
FROM Table2 b,
(SELECT DISTINCT acc_no acc_no
FROM Table3, Table1
WHERE acc_no=appl_id AND delinquent_flag= 'Y'
AND financier_id='NEWACLOS') d
WHERE d.acc_no = b.appl_id
AND ( contacted_by IS NOT NULL
OR followup_branch_code IS NOT NULL
)
GROUP BY appl_id) c
ON (a.appl_id = c.appl_id AND a.delinquent_flag = 'Y')
WHEN MATCHED THEN
UPDATE
SET last_contact_date = c.m_condate;

In this query table 1 has 30000 records and table2 and table 3 have 3670955 and 555674 records respectively.

Please suggest,what i am doing wrong in merge,because as per my understanding merge statement is much better than updates or updates using cursors.

Required info is as follows:

SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /opt/oracle/admin/FINCLUAT/udu
mp
SQL> show parameter optimizer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL> show parameter db_file_multi

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> column sname format a20
SQL> column pname format a20
SQL> column pval2 format a20
SQL> select
2 sname ,
3 pname ,
4 pval1 ,
5 pval2
6 from
7 sys.aux_stats$;
sys.aux_stats$
*
ERROR at line 7:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.05
SQL> explain plan for
2 -- put your statement here
3 MERGE INTO cs_case_info a
4 USING (SELECT MAX (TO_DATE ( TO_CHAR (contact_date, 'dd/mm/yyyy')
5 || contact_time,
6 'dd/mm/yyyy HH24:Mi:SS'
7 )
8 ) m_condate,
9 appl_id
10 FROM CS_CASE_DETAILS_ACLOS b,
11 (SELECT DISTINCT acc_no acc_no
12 FROM NEWACLOS_RESEARCH_HIST_AYLA, cs_case_info
13 WHERE acc_no=appl_id AND delinquent_flag= 'Y'
14 AND financier_id='NEWACLOS') d
15 WHERE d.acc_no = b.appl_id
16 AND ( contacted_by IS NOT NULL
17 OR followup_branch_code IS NOT NULL
18 )
19 GROUP BY appl_id) c
20 ON (a.appl_id = c.appl_id AND a.delinquent_flag = 'Y')
21 WHEN MATCHED THEN
22 UPDATE
23 SET last_contact_date = c.m_condate
24 ;

Explained.

Elapsed: 00:00:00.08
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 47156 | 874K| | 128K (1)|
| 1 | MERGE | CS_CASE_INFO | | | | |
| 2 | VIEW | | | | | |
| 3 | HASH JOIN | | 47156 | 36M| 5672K| 128K (1)|
| 4 | VIEW | | 47156 | 5111K| | 82339 (1)|
| 5 | SORT GROUP BY | | 47156 | 4236K| 298M| 82339 (1)|
| 6 | HASH JOIN | | 2820K| 247M| 10M| 60621 (1)|
| 7 | HASH JOIN | | 216K| 7830K| | 6985 (1)|
| 8 | VIEW | index$_join$_012 | 11033 | 258K| | 1583 (1)|
| 9 | HASH JOIN | | | | | |
| 10 | INDEX RANGE SCAN | IDX_CCI_DEL | 11033 | 258K| | 768 (1)|
| 11 | INDEX RANGE SCAN | CS_CASE_INFO_UK | 11033 | 258K| | 821 (1)|
| 12 | INDEX FAST FULL SCAN| IDX_NACL_RSH_ACC_NO | 5539K| 68M| | 5382 (1)|
| 13 | TABLE ACCESS FULL | CS_CASE_DETAILS_ACLOS | 3670K| 192M| | 41477 (1)|
| 14 | TABLE ACCESS FULL | CS_CASE_INFO | 304K| 205M| | 35975 (1)|
--------------------------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

24 rows selected.

Elapsed: 00:00:01.04
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.03
SQL> set autotrace traceonly arraysize 100
SQL> alter session set events '10046 trace name context forever, level 8';
ERROR:
ORA-01031: insufficient privileges


Elapsed: 00:00:00.04
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> spool off

Edited by: user4528984 on May 5, 2009 10:37 PM