-
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.
-
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
-
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(..).
