Urgent!! How to improve this qry with insert

[gsprince]

Hello,

I have to run the following in production. the following does a cartesian produc bettwen the two tables with 4000 records selected form P2 and 700 from I. I do not have sequences defined currently, so I do need the "+ rownum"

This is taking almost 4 hrs to run in the devp area to insert 2.5million rows. (I already have 0.5+ records in the table.

INSERT INTO PERM
SELECT I.CNY#, (select NVL(max(record#),0)+1 from PERM where cny# = I.CNY#)+rownum RECORD#,
'T' STATUS, I.RECORD#, P2.ELEMENT, P2.PERMISSION
FROM PERM P2, USERINFO I WHERE P2.CNY#= 571 AND P2.USERREC = 1 AND I.CNY# != 571 AND I.ADMIN = '1';

Is there any was I can improve this? If so how?

Note: I have indexes on the relevent columns.

[sambavan]

Did you do an explain plan on this to see how your query executes?:

SELECT I.CNY#, (select NVL(max(record#),0)+1 from PERM where cny# = I.CNY#)+rownum RECORD#,
'T' STATUS, I.RECORD#, P2.ELEMENT, P2.PERMISSION
FROM PERM P2, USERINFO I WHERE P2.CNY#= 571 AND P2.USERREC = 1 AND I.CNY# != 571 AND I.ADMIN = '1';

Sam

[gsprince]

I sure did, Please see the explain plan


0 SELECT STATEMENT Optimizer=CHOOSE (Cost=284 Card=33166 Bytes
=895482)

1 0 COUNT
2 1 MERGE JOIN (CARTESIAN) (Cost=284 Card=33166 Bytes=895482
)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'PERM' (Cost=4 Card=14 Bytes=238)

4 3 INDEX (RANGE SCAN) OF 'IX_PERM_USERREC' (NON-UNI
QUE) (Cost=3 Card=14) --actual carinality is approx 700

5 2 SORT (JOIN) (Cost=280 Card=2369 Bytes=23690)
6 5 TABLE ACCESS (FULL) OF 'USERINFO' (Cost=20 Card=2369
Bytes=23690)


Statistics
----------------------------------------------------------
0 recursive calls
6 db block gets
13386603 consistent gets
13858 physical reads
0 redo size
144006455 bytes sent via SQL*Net to client
633237 bytes received via SQL*Net from client
5703 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
2850834 rows processed

note: there is lot of consistent reads, I guess, caused by the inner query with the nvl(max(..).