SQL & PL/SQL

Hi,

The following query takes long time to execute. Is there any better way to
re-writing the query to reduce the time it takes to execute.

INSERT INTO TT_TEMP_MAINGUI_SP_PERCENT_MOV
(prev_prc_dt,asset_id,pricing_pt_id,price_dt)
SELECT max(tpm2.prc_dt),
tpm2.asset_id ,
tpm2.pricing_pt_id ,
tpm1.prc_dt
FROM t_prc_master tpm1,
t_prc_master tpm2
WHERE tpm1.prc_dt = '19-Dec-07'
AND tpm1.asset_id = tpm2.asset_id
AND tpm1.pricing_pt_id = tpm2.pricing_pt_id
AND tpm2.prc_dt < tpm1.prc_dt
AND tpm2.accept_flg = 'Y'
AND tpm1.accept_flg = 'Y'
AND EXISTS (SELECT 1 FROM t_temp_prcmov
WHERE pca_flg = 'P'
AND tpm1.pricing_pt_id = prc_pt_cntry_atyp)
GROUP BY tpm2.asset_id, tpm2.pricing_pt_id,tpm1.prc_dt;


select count(*) from t_prc_master
where prc_dt = '19-Dec-07'

COUNT(*)
********
784161
********

-- Here is the TKPROF Output
-- ****************************
INSERT INTO TT_TEMP_MAINGUI_SP_PERCENT_MOV
(prev_prc_dt,asset_id,pricing_pt_id,price_dt)
SELECT max(tpm2.prc_dt),
tpm2.asset_id ,
tpm2.pricing_pt_id ,
tpm1.prc_dt
FROM t_prc_master tpm1,
t_prc_master tpm2
WHERE tpm1.prc_dt = '19-Dec-07'
AND tpm1.asset_id = tpm2.asset_id
AND tpm1.pricing_pt_id = tpm2.pricing_pt_id
AND tpm2.prc_dt < tpm1.prc_dt
AND tpm2.accept_flg = 'Y'
AND tpm1.accept_flg = 'Y'
AND EXISTS (SELECT 1 FROM t_temp_prcmov
WHERE pca_flg = 'P'
AND tpm1.pricing_pt_id = prc_pt_cntry_atyp)
GROUP BY tpm2.asset_id, tpm2.pricing_pt_id,tpm1.prc_dt

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 226.01 317.50 1980173 4915655 805927 780544
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 226.01 317.51 1980173 4915655 805927 780544

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 98 (PRSDBO)

Rows Row Source Operation
------- ---------------------------------------------------
780544 SORT GROUP BY (cr=4915236 r=1980165 w=0 time=312751120 us)
40416453 NESTED LOOPS (cr=4915236 r=1980165 w=0 time=245408132 us)
783459 NESTED LOOPS (cr=956325 r=92781 w=0 time=17974163 us)
55 TABLE ACCESS FULL T_TEMP_PRCMOV (cr=3 r=0 w=0 time=406 us)
783459 TABLE ACCESS BY INDEX ROWID T_PRC_MASTER (cr=956322 r=92781 w=0 time=17782856 us)
784161 INDEX RANGE SCAN PRC_DT_ASSET_ID (cr=412062 r=69776 w=0 time=14136725 us)(object id 450059)
40416453 INDEX RANGE SCAN ASSET_DT_ACCEPT_FLG (cr=3958911 r=1887384 w=0 time=217215303 us)(object id 450055)


Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
780544 SORT (GROUP BY)
40416453 NESTED LOOPS
783459 NESTED LOOPS
55 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T_TEMP_PRCMOV'
783459 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_PRC_MASTER'
784161 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PRC_DT_ASSET_ID'
(NON-UNIQUE)
40416453 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ASSET_DT_ACCEPT_FLG'
(UNIQUE)

Could somebody help me in resolving the issue? It would be appreciated...