SQL Behaviour problem

[patnams]

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

[pando]

well write is always longer than read, what's your point?

[patnams]

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.

[chrisrlong]

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

[patnams]

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

[chrisrlong]

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:

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