| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Help: Optimizer hints and CREATE TABLE AS SELECT
I was wondering if anyone could explain why a well-optimized query (when run
as a stand-alone SELECT statement) isn't so when run as a CREATE TABLE AS
SELECT statement. Specifically, all my nice efficient index RANGE SCANs and
UNIQUE SCANs (as revealed by the EXPLAIN PLAN command) are all replaced with
FULL SCANs on the indexes, and the execution time goes from a "second" to 20
seconds. (Three of the tables involved have statistics, three others don't,
yet even the ones with statistics don't use a UNIQUE SCAN. I'm using INDEX()
hints to force the use of indexes.)
Version 7.3.3.4
As a SELECT statement:
OPERATION OPTIONS OBJECT_NAME
------------------------------ ------------ ------------------------------
NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER TABLE ACCESS FULL TEST_DATA TABLE ACCESS BY ROWID QC$TMP_75265$31954 INDEX RANGE SCAN QC$TMP_75265$31955 TABLE ACCESS BY ROWID QC$TMP_75265$31956 INDEX RANGE SCAN QC$TMP_75265$31957 TABLE ACCESS BY ROWID QC$TMP_75265$31958 INDEX RANGE SCAN QC$TMP_75265$31959 TABLE ACCESS BY ROWID ASSESSMENT INDEX UNIQUE SCAN PK_ASSESSMENT TABLE ACCESS BY ROWID ASSESSMENT INDEX UNIQUE SCAN PK_ASSESSMENT TABLE ACCESS BY ROWID ASSESSMENT INDEX UNIQUE SCAN PK_ASSESSMENT As a CREATE TABLE AS SELECT statement: OPERATION OPTIONS OBJECT_NAME
------------------------------ ------------ ------------------------------
CREATE AS SELECT HASH JOIN OUTER HASH JOIN OUTER HASH JOIN OUTER HASH JOIN OUTER HASH JOIN OUTER HASH JOIN OUTER TABLE ACCESS FULL TEST_DATA TABLE ACCESS BY ROWID QC$TMP_75265$31954 INDEX FULL SCAN QC$TMP_75265$31955 TABLE ACCESS BY ROWID QC$TMP_75265$31956 INDEX FULL SCAN QC$TMP_75265$31957 TABLE ACCESS BY ROWID QC$TMP_75265$31958 INDEX FULL SCAN QC$TMP_75265$31959 TABLE ACCESS BY ROWID ASSESSMENT INDEX FULL SCAN PK_ASSESSMENT TABLE ACCESS BY ROWID ASSESSMENT INDEX FULL SCAN PK_ASSESSMENT TABLE ACCESS BY ROWID ASSESSMENT INDEX FULL SCAN PK_ASSESSMENT
Any ideas would be greatly appreciated. Gerard
 |
 |