| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Comparing the same SELECT in CTAS, INSERT and SELECT
Oracle 9.2.0.5 RAC on 32-bit Linux
Comparing the same SELECT used in a CTAS, an INSERT and a SELECT-only
operation,
I find that when it is used in the INSERT, Row Source Operations are very high
and there seems to a Full Table scan which is not evident in the Explain Plan.
(note that I "CTRL-C"d the INSERT statement after "waiting" for 45minutes)
The target table TEST_SL has no indexes. The source table does have indexes, seperately on CLAIM_TIME and LOT_ID. I'm not yet comfortable with the Inline View and the GROUP BY on CLAIM_TIME.
For the CTAS :
tkprof of the 10046 Trace
CREATE /*+ CTAS */ TABLE hemant.TEST_SL AS
SELECT SOURCE_TABLE.LOT_ID, SOURCE_TABLE.STORE_TIME, SOURCE_TABLE.CLAIM_TIME, SOURCE_TABLE.MOVE_TYPE, SOURCE_TABLE.OPE_CATEGORY, SOURCE_TABLE.OPE_NO, SOURCE_TABLE.PREV_OPE_NO, SOURCE_TABLE.MAINPD_ID,
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.46 12.90 1301 28730 161
2374
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 2 0.46 12.90 1301 28730 161
2374
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (HEMANT)
Rows Execution Plan
------- ---------------------------------------------------
0 CREATE TABLE STATEMENT GOAL: CHOOSE
0 LOAD AS SELECT
0 SORT (GROUP BY)
0 NESTED LOOPS
0 TABLE ACCESS (BY INDEX ROWID) OF 'SOURCE_TABLE'
0 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
0 AND-EQUAL
0 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX02' (NON-UNIQUE)
0 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache pin 1 0.00 0.00
row cache lock 18 0.00 0.00
control file sequential read 4 0.00 0.00
db file sequential read 1301 0.03 12.26
global cache cr request 726 0.04 0.18
global cache s to x 1 0.00 0.00
global cache open x 5 0.00 0.00
direct path write 2 0.00 0.00
rdbms ipc reply 1 0.00 0.00
log file sync 1 0.01 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 4.66 4.66
********************************************************************************
10053 Trace
QUERY
explain plan for
CREATE /*+ CTAS */ TABLE hemant.TEST_SL AS
SELECT SOURCE_TABLE.LOT_ID, SOURCE_TABLE.STORE_TIME, SOURCE_TABLE.CLAIM_TIME, SOURCE_TABLE.MOVE_TYPE, SOURCE_TABLE.OPE_CATEGORY, SOURCE_TABLE.OPE_NO, SOURCE_TABLE.PREV_OPE_NO, SOURCE_TABLE.MAINPD_ID,
CREATE TABLE STATEME 0 LOAD AS SELECT 1 SORT GROUP BY 2 1 NESTED LOOPS 3 2 TABLE ACCESS SOURCE_TABLE BY INDEX ROWID 4 3 INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 5 4 AND-EQUAL 6 3 INDEX SOURCE_TABLE_INDEX02 RANGE SCAN 7 6 INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 8 6 =========================================================================
For the INSERT :
tkprof of the 10046 Trace :
INSERT /*+ INSERT */ INTO TEST_SL
SELECT SOURCE_TABLE.LOT_ID, SOURCE_TABLE.STORE_TIME, SOURCE_TABLE.CLAIM_TIME, SOURCE_TABLE.MOVE_TYPE, SOURCE_TABLE.OPE_CATEGORY, SOURCE_TABLE.OPE_NO, SOURCE_TABLE.PREV_OPE_NO, SOURCE_TABLE.MAINPD_ID,
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 507.13 2722.39 2538409 4009254 77
0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 2 507.13 2722.39 2538409 4009254 77
0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (HEMANT)
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS
29968053 TABLE ACCESS FULL OBJ#(269240)
0 TABLE ACCESS BY INDEX ROWID OBJ#(269240)
134801 AND-EQUAL
401316 INDEX RANGE SCAN OBJ#(300586) (object id 300586)
271535 INDEX RANGE SCAN OBJ#(269815) (object id 269815)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
0 SORT (GROUP BY)
118481 NESTED LOOPS
118481 TABLE ACCESS (BY INDEX ROWID) OF 'SOURCE_TABLE'
29968053 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
0 AND-EQUAL
134801 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX02' (NON-UNIQUE)
401316 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
global cache cr request 1198153 0.09 40.66
db file sequential read 161657 0.22 1177.80
db file scattered read 140046 0.33 973.73
latch free 83 0.02 0.23
db file parallel read 2 0.29 0.46
direct path write 4 0.00 0.00
direct path read 4464 0.00 0.02
SQL*Net break/reset to client 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 1.69 1.69
********************************************************************************
10053 Trace :
QUERY
explain plan for
INSERT /*+ INSERT */ INTO TEST_SL
SELECT SOURCE_TABLE.LOT_ID, SOURCE_TABLE.STORE_TIME, SOURCE_TABLE.CLAIM_TIME, SOURCE_TABLE.MOVE_TYPE, SOURCE_TABLE.OPE_CATEGORY, SOURCE_TABLE.OPE_NO, SOURCE_TABLE.PREV_OPE_NO, SOURCE_TABLE.MAINPD_ID,
INSERT STATEMENT 0 SORT GROUP BY 1 NESTED LOOPS 2 1 TABLE ACCESS SOURCE_TABLE BY INDEX ROWID 3 2 INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 4 3 AND-EQUAL 5 2 INDEX SOURCE_TABLE_INDEX02 RANGE SCAN 6 5 INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 7 5 =========================================================================
For the SELECT only :
10046 Trace :
SELECT /*+ Pure SELECT */ SOURCE_TABLE.LOT_ID,
SOURCE_TABLE.STORE_TIME, SOURCE_TABLE.CLAIM_TIME, SOURCE_TABLE.MOVE_TYPE, SOURCE_TABLE.OPE_CATEGORY, SOURCE_TABLE.OPE_NO, SOURCE_TABLE.PREV_OPE_NO, SOURCE_TABLE.MAINPD_ID, SOURCE_TABLE.PREV_MAINPD_ID, SOURCE_TABLE_SUMMARY.EVENT_COUNT,
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 160 0.33 0.32 0 28603 0
2374
total 162 0.33 0.32 0 28603 0
2374
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (HEMANT)
Rows Row Source Operation
------- ---------------------------------------------------
2374 SORT GROUP BY
3494 NESTED LOOPS
2374 TABLE ACCESS BY INDEX ROWID OBJ#(269240)
2374 INDEX RANGE SCAN OBJ#(269815) (object id 269815)
3494 AND-EQUAL
7467 INDEX RANGE SCAN OBJ#(300586) (object id 300586)
7153 INDEX RANGE SCAN OBJ#(269815) (object id 269815)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
2374 SORT (GROUP BY)
2374 TABLE ACCESS (BY INDEX ROWID) OF 'SOURCE_TABLE'
2374 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
3494 AND-EQUAL
7467 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX02' (NON-UNIQUE)
7153 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 160 0.00 0.00
SQL*Net message from client 160 3.57 18.64
********************************************************************************
10053 Trace :
QUERY
explain plan for
SELECT /*+ Pure SELECT */ SOURCE_TABLE.LOT_ID,
SOURCE_TABLE.STORE_TIME, SOURCE_TABLE.CLAIM_TIME, SOURCE_TABLE.MOVE_TYPE, SOURCE_TABLE.OPE_CATEGORY, SOURCE_TABLE.OPE_NO, SOURCE_TABLE.PREV_OPE_NO, SOURCE_TABLE.MAINPD_ID, SOURCE_TABLE.PREV_MAINPD_ID, SOURCE_TABLE_SUMMARY.EVENT_COUNT,
SELECT STATEMENT 0 SORT GROUP BY 1 NESTED LOOPS 2 1 TABLE ACCESS SOURCE_TABLE BY INDEX ROWID 3 2 INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 4 3 AND-EQUAL 5 2 INDEX SOURCE_TABLE_INDEX02 RANGE SCAN 6 5 INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 7 5
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 27 2006 - 11:12:03 CDT
 |
 |