Printable View
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
well write is always longer than read, what's your point?
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.
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
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
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:
...we notice that you missed an outer join between DT and DC.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
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