SQL Behaviour problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL Behaviour problem

  1. #1
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Hi All,

    I have a SQL Statement with multiple joins.

    Select X Y Z from ......

    When I run this statement it runs very fast.

    But when I say
    Insert into
    Select X Y Z from ......
    it takes long time.

    It doing full scans on couple of tables in the second statement.

    What is the issue here ?

    Thanks
    Sridhar
    Sridhar R Patnam

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well write is always longer than read, what's your point?

  3. #3
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    I should have clarified more.

    Retrieves 5 rows. SELECT Statement 0.1 Secs
    INSERT .. SELECT takes 30 Seconds

    On Explaining the Plan, the INSERT...SELECT Statement
    is doing Full Table Scans on 2 tables, where as only SELECT statement is not doing it.

    Sridhar R Patnam

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    If you are saying that the plans are different, you are probably going to need to gives us some exact information - statements, plans, etc.

    Is there maybe on ORDER BY on the SELECT that is not on the INSERT?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    SQLPLUS SESSION,
    First is SELECT, next is INSERT .. SELECT
    **************************************

    1 SELECT dc.batch_number, 3 AS rec_type,
    2 dt.document_type_description GENERIC_FLD1,
    3 cd.name AS GENERIC_FLD2, ds.Status_Description AS GENERIC_FLD3, 0 AS Payment, df.fee_amount
    4 df.TC_PCA_OR AS Fund_Code, 0 AS Client_Id, 0 AS Fee_Exempt,
    5 dc.Document_Received_Date AS Received_date, '' AS Payor_Name,
    6 SYSDATE AS Last_Modified_Date, dc.Document_Number AS RegNumber, 'CORP401'
    7 FROM document dc, document_type dt, corp_filing_reference cfr, document_fee df,
    8 submitter_batch sb, corp_filing cf, corp_data cd, document_status ds
    9 WHERE dc.document_type_id = dt.document_type_id AND
    10 dc.document_number = df.document_number(+) AND
    11 dc.document_number = cfr.document_number(+) AND
    12 dc.document_number = cf.document_number(+) AND
    13 cf.filing_number = cd.filing_number(+) AND
    14 sb.batch_number = dc.batch_number(+) AND
    15 dc.document_status_id = ds.document_status_id(+) AND
    16 sb.batch_status_id = 10 AND
    17* sb.Entry_User_Id = 'RABARFIELD'
    SQL> /

    405 3 Entity filing document
    405 3 Subsequent Action
    405 3 Order
    411 3 Subsequent Action
    416 3 Annual Report, Franchise Tax
    426 3 Entity filing document

    6 rows selected.

    Elapsed: 00:00:00.71

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 MERGE JOIN (OUTER)
    2 1 SORT (JOIN)
    3 2 NESTED LOOPS
    4 3 NESTED LOOPS (OUTER)
    5 4 NESTED LOOPS (OUTER)
    6 5 NESTED LOOPS (OUTER)
    7 6 NESTED LOOPS (OUTER)
    8 7 NESTED LOOPS (OUTER)
    9 8 TABLE ACCESS (FULL) OF 'SUBMITTER_BATCH'
    10 8 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENT
    11 10 INDEX (RANGE SCAN) OF 'IDX_DOC_BATCHNUMB
    12 7 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENT_S
    13 12 INDEX (UNIQUE SCAN) OF 'PK_DS_DOCUMENTSTAT
    14 6 TABLE ACCESS (BY INDEX ROWID) OF 'CORP_FILING'
    15 14 INDEX (UNIQUE SCAN) OF 'PK_CF_DN' (UNIQUE)
    16 5 TABLE ACCESS (BY INDEX ROWID) OF 'CORP_DATA'
    17 16 INDEX (UNIQUE SCAN) OF 'PK_CD_FN' (UNIQUE)
    18 4 INDEX (UNIQUE SCAN) OF 'PK_CFR_DN' (UNIQUE)
    19 3 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENT_TYPE'
    20 19 INDEX (UNIQUE SCAN) OF 'PK_DT_DOCUMENTTYPEID' (UNI
    21 1 SORT (JOIN)
    22 21 TABLE ACCESS (FULL) OF 'DOCUMENT_FEE'




    Statistics
    ----------------------------------------------------------
    0 recursive calls
    8 db block gets
    98 consistent gets
    0 physical reads
    0 redo size
    1864 bytes sent via SQL*Net to client
    425 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    2 sorts (memory)
    0 sorts (disk)
    6 rows processed


    **********************************************************************
    **********************************************************************
    **********************************************************************

    SQL> INSERT INTO COB_DETAILS_TEMP
    2 SELECT dc.batch_number, 3 AS rec_type,
    3 dt.document_type_description GENERIC_FLD1,
    4 cd.name AS GENERIC_FLD2, ds.Status_Description AS GENERIC_FLD3, 0 AS Payme
    fee_amount AS fee,
    5 df.TC_PCA_OR AS Fund_Code, 0 AS Client_Id, 0 AS Fee_Exempt,
    6 dc.Document_Received_Date AS Received_date, '' AS Payor_Name,
    7 SYSDATE AS Last_Modified_Date, dc.Document_Number AS RegNumber, 'CORP401'
    8 FROM document dc, document_type dt, corp_filing_reference cfr, document_fee df,
    9 submitter_batch sb, corp_filing cf, corp_data cd, document_status ds
    10 WHERE dc.document_type_id = dt.document_type_id AND
    11 dc.document_number = df.document_number(+) AND
    12 dc.document_number = cfr.document_number(+) AND
    13 dc.document_number = cf.document_number(+) AND
    14 cf.filing_number = cd.filing_number(+) AND
    15 sb.batch_number = dc.batch_number(+) AND
    16 dc.document_status_id = ds.document_status_id(+) AND
    17 sb.batch_status_id = 10 AND
    18 sb.Entry_User_Id = 'RABARFIELD'
    19 /

    6 rows created.

    Elapsed: 00:00:29.32

    Execution Plan
    ----------------------------------------------------------
    0 INSERT STATEMENT Optimizer=CHOOSE (Cost=231726 Card=43259225
    1 0 NESTED LOOPS (OUTER) (Cost=231726 Card=43259225812265 Byte
    2 1 MERGE JOIN (OUTER) (Cost=231726 Card=43259225812265 Byte
    3 2 SORT (JOIN) (Cost=196067 Card=13119073282 Bytes=350279
    4 3 MERGE JOIN (OUTER) (Cost=196067 Card=13119073282 Byt
    5 4 MERGE JOIN (OUTER) (Cost=196065 Card=13119073282 B
    6 5 SORT (JOIN) (Cost=154863 Card=844722 Bytes=17316
    7 6 HASH JOIN (OUTER) (Cost=12487 Card=844722 Byte
    8 7 FILTER
    9 8 NESTED LOOPS (OUTER)
    10 9 MERGE JOIN (CARTESIAN) (Cost=165 Card=82
    11 10 TABLE ACCESS (FULL) OF 'DOCUMENT_TYPE'
    12 10 SORT (JOIN) (Cost=164 Card=1 Bytes=38)
    13 12 TABLE ACCESS (FULL) OF 'SUBMITTER_BA
    14 9 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUME
    15 14 INDEX (RANGE SCAN) OF 'IDX_DOC_BATCHNU
    16 7 TABLE ACCESS (FULL) OF 'DOCUMENT_STATUS' (Co
    17 5 SORT (JOIN) (Cost=41202 Card=1553064 Bytes=40379
    18 17 TABLE ACCESS (FULL) OF 'CORP_FILING' (Cost=288
    19 4 SORT (JOIN) (Cost=3 Card=82 Bytes=2952)
    20 19 TABLE ACCESS (FULL) OF 'DOCUMENT_FEE' (Cost=1 Ca
    21 2 SORT (JOIN) (Cost=35659 Card=329743 Bytes=47153249)
    22 21 TABLE ACCESS (FULL) OF 'CORP_DATA' (Cost=613 Card=32
    23 1 INDEX (UNIQUE SCAN) OF 'PK_CFR_DN' (UNIQUE)




    Statistics
    ----------------------------------------------------------
    27 recursive calls
    582 db block gets
    23410 consistent gets
    36586 physical reads
    944 redo size
    857 bytes sent via SQL*Net to client
    1642 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    14 sorts (memory)
    2 sorts (disk)
    6 rows processed

    Sridhar R Patnam

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Well, I'm not sure I have an answer yet, but I do have a few comments.

    There is one difference between the 2 statements, which could well be a typo:

    Payme
    fee_amount AS fee,

    Also, once we clean things up a little bit:

    Code:
    SELECT   
       DC.BATCH_NUMBER,   3   AS   REC_TYPE,
       DT.DOCUMENT_TYPE_DESCRIPTION   GENERIC_FLD1,
       CD.NAME   AS   GENERIC_FLD2,   DS.STATUS_DESCRIPTION   AS   GENERIC_FLD3,   0   AS   PAYMENT,
       DF.FEE_AMOUNT   AS   FEE,
       DF.TC_PCA_OR   AS   FUND_CODE,   0   AS   CLIENT_ID,   0   AS   FEE_EXEMPT,
       C.DOCUMENT_RECEIVED_DATE   AS   RECEIVED_DATE,   ''   AS   PAYOR_NAME,
       SYSDATE   AS   LAST_MODIFIED_DATE,   DC.DOCUMENT_NUMBER   AS   REGNUMBER,   'CORP401'
    FROM   
       SUBMITTER_BATCH         SB,   
       DOCUMENT                DC,   
       DOCUMENT_TYPE           DT,   
       DOCUMENT_FEE            DF,
       CORP_FILING_REFERENCE   CFR,   
       CORP_FILING             CF,   
       CORP_DATA               CD,   
       DOCUMENT_STATUS         DS
    WHERE   
       SB.BATCH_STATUS_ID         =   10                      AND
       SB.ENTRY_USER_ID           =   'RABARFIELD'            AND
       DC.BATCH_NUMBER         (+)=   SB.BATCH_NUMBER         AND
       DT.DOCUMENT_TYPE_ID        =   DC.DOCUMENT_TYPE_ID     AND   
       DF.DOCUMENT_NUMBER      (+)=   DC.DOCUMENT_NUMBER      AND
       CFR.DOCUMENT_NUMBER     (+)=   DC.DOCUMENT_NUMBER      AND
       CF.DOCUMENT_NUMBER      (+)=   DC.DOCUMENT_NUMBER      AND   
       CD.FILING_NUMBER        (+)=   CF.FILING_NUMBER        AND   
       DS.DOCUMENT_STATUS_ID   (+)=   DC.DOCUMENT_STATUS_ID
    ...we notice that you missed an outer join between DT and DC.


    Further, both plans seem to be bad. They are both doing full-scans on your primary table. Do you not have an index on the 2 fields you are restricting?

    The fact that both plans utilize merge joins is rather disturbing. There are only rare cases where Merge Joins make sense.

    Also, I'm afraid I have difficulty read non-indented plans... What, exactly, is that FILTER step connected to? I can't figure out what it could possibly be doing since everything is outer-joined.

    - Chris

    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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