Urgent!! How to improve this qry with insert
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Urgent!! How to improve this qry with insert

  1. #1
    Join Date
    Aug 2000
    Posts
    194
    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Aug 2000
    Posts
    194
    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(..).


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width