I need your help to optimize the following query.
Please note that:-
1) The table SWCDR is range-partitioned on column "ORIG_DT" and is also having table level parallel degree 2.
2) The table SWCDR contains 10 lacs data into it.
3) Composite BTREE Index (SWCDR_IDX_10) is created on columns (INSERT_DT & ORIG_DT) of SWCDR table.
4) Composite BTREE Index is created on columns (DS_NUMBER, TABLE_ID, LOG_DT) Of SUMMARY_LOG_HIST table.
The below query is taking 40 minutes for giving output on screen.
Can you please guide me (via looking into the explain plan as shown below) the following:-
1. What HINT/ INDEXES should I introuduce in order to optimize the below query.
2. Is there any alternative ways to write the same query as shown below:-.
- Code: Select all
--INSERT INTO DELAYED_CDR
SELECT a.insert_dt
FROM SWCDR a
JOIN (SELECT ds_number,
table_id,
max(log_dt) log_dt,
last_exec_dt,
next_exec_dt,
last_msg
FROM summary_log_hist
GROUP BY ds_number,
table_id,
last_exec_dt,
next_exec_dt,
last_msg) b
ON (a.insert_dt > b.log_dt AND
(a.orig_dt >= b.last_exec_dt AND a.orig_dt < b.next_exec_dt))
WHERE b.ds_number = 0
AND b.table_id = 1
AND b.last_msg = 'Complete'
AND a.insert_dt BETWEEN :start_dt AND :end_dt
LOG ERRORS INTO err$_DELAYED_CDR('INSERT') REJECT LIMIT UNLIMITED;
Explain Plan for the above query is shown below:-
- Code: Select all
COST CARDINALITY
SELECT STATEMENT, GOAL = ALL_ROWS 923065 266827
PX COORDINATOR
PX SEND QC (RANDOM) SYS :TQ10001 923065 266827
MERGE JOIN 923065 266827
SORT JOIN 257 2595
BUFFER SORT
PX RECEIVE 256 2595
PX SEND BROADCAST SYS :TQ10000 256 2595
VIEW DWH_SCHEMA1 256 2595
HASH GROUP BY 256 2595
TABLE ACCESS FULL DWH_SCHEMA1 SUMMARY_LOG_HIST 248 51886
FILTER
SORT JOIN 897512 30297257
PX PARTITION RANGE ALL 122072 30297257
TABLE ACCESS BY LOCAL INDEX ROWID DWH_SCHEMA1 SWCDR 122072 30297257
INDEX RANGE SCAN DWH_SCHEMA1 SWCDR_IDX_10 8506 30297257
Thanks in advance.
Thanks & Regards,
Anand Kumar Ojha