现象如下:
先看下两者的执行计划:
从第二个执行计划的Predicate Information处看出,filter里边的T表给解析成只有一个字段"RN"(也应该有结果出来?)。
尝试加hint让with as的部分放在temp表,执行结果以及执行计划如下:
结果已经执行出来了。
另外该语句在10g是可以不用加hint就能正常执行:
这里尝试将11g里边的参数optimizer_features_enable修改成10g的,在看看执行计划以及结果:
Predicate Information的filter已经没错了,但是还是出不来结果。
这里不是很懂了。
- SYS@proc> select * from v$version where rownum=1;
-
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
-
- SYS@proc> drop table test purge;
-
- Table dropped.
-
- SYS@proc> with t as (select a.*,rownum rn from dba_objects a)
- 2 select count(*) from t where t.rn=(select max(rn) from t);
-
- COUNT(*)
- ----------
- 1
-
- SYS@proc> create table test as select * from dba_objects;
-
- Table created.
-
- SYS@proc> with t as (select a.*,rownum rn from test a)
- 2 select * from t where t.rn=(select max(rn) from t);
-
- no rows selected
先看下两者的执行计划:
- SYS@proc> set long 9999 pagesize 9999 lines 500
- SYS@proc> set autotrace traceonly
- SYS@proc> with t as (select a.*,rownum rn from dba_objects a)
- 2 select count(*) from t where t.rn=(select max(rn) from t);
-
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1413014202
-
- --------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 1336 (1)| 00:00:17 |
- | 1 | TEMP TABLE TRANSFORMATION | | | | | |
- | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6622_16F270 | | | | |
- | 3 | COUNT | | | | | |
- | 4 | VIEW | DBA_OBJECTS | 86955 | 13M| 301 (1)| 00:00:04 |
- | 5 | UNION-ALL | | | | | |
- |* 6 | TABLE ACCESS BY INDEX ROWID| SUM$ | 1 | 9 | 1 (0)| 00:00:01 |
- |* 7 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
- | 8 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 30 | 3 (0)| 00:00:01 |
- |* 9 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
- |* 10 | FILTER | | | | | |
- |* 11 | HASH JOIN | | 86954 | 10M| 299 (2)| 00:00:04 |
- | 12 | TABLE ACCESS FULL | USER$ | 94 | 1598 | 3 (0)| 00:00:01 |
- |* 13 | HASH JOIN | | 86954 | 9001K| 296 (2)| 00:00:04 |
- | 14 | INDEX FULL SCAN | I_USER2 | 94 | 2068 | 1 (0)| 00:00:01 |
- |* 15 | TABLE ACCESS FULL | OBJ$ | 86954 | 7132K| 294 (1)| 00:00:04 |
- | 16 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
- |* 17 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
- |* 18 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
- | 19 | NESTED LOOPS | | 1 | 105 | 2 (0)| 00:00:01 |
- | 20 | TABLE ACCESS FULL | LINK$ | 1 | 88 | 2 (0)| 00:00:01 |
- | 21 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 0 (0)| 00:00:01 |
- |* 22 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
- | 23 | SORT AGGREGATE | | 1 | 13 | | |
- |* 24 | VIEW | | 86955 | 1103K| 517 (1)| 00:00:07 |
- | 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6622_16F270 | 86955 | 13M| 517 (1)| 00:00:07 |
- | 26 | SORT AGGREGATE | | 1 | 13 | | |
- | 27 | VIEW | | 86955 | 1103K| 517 (1)| 00:00:07 |
- | 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6622_16F270 | 86955 | 13M| 517 (1)| 00:00:07 |
- --------------------------------------------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 6 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
- 7 - access("S"."OBJ#"=:B1)
- 9 - access("EO"."OBJ#"=:B1)
- 10 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
- "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
- "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR
- ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10
- OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR
- "O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
- "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS
- (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88
- AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
- edition_id')))))
- 11 - access("O"."SPARE3"="U"."USER#")
- 13 - access("O"."OWNER#"="U"."USER#")
- 15 - filter("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
- "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
- 17 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
- filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
- 18 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
- 22 - access("L"."OWNER#"="U"."USER#")
- 24 - filter("T"."RN"= (SELECT MAX("RN") FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
- "OWNER","C1" "OBJECT_NAME","C2" "SUBOBJECT_NAME","C3" "OBJECT_ID","C4" "DATA_OBJECT_ID","C5"
- "OBJECT_TYPE","C6" "CREATED","C7" "LAST_DDL_TIME","C8" "TIMESTAMP","C9" "STATUS","C10"
- "TEMPORARY","C11" "GENERATED","C12" "SECONDARY","C13" "NAMESPACE","C14" "EDITION_NAME","C15" "RN"
- FROM "SYS"."SYS_TEMP_0FD9D6622_16F270" "T1") "T"))
-
-
- Statistics
- ----------------------------------------------------------
- 241 recursive calls
- 1342 db block gets
- 3814 consistent gets
- 2628 physical reads
- 1216 redo size
- 526 bytes sent via SQL*Net to client
- 523 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 15 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
-
- SYS@proc> with t as (select a.*,rownum rn from test a)
- 2 select * from t where t.rn=(select max(rn) from t);
-
- no rows selected
-
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1063871704
-
- ------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 71052 | 14M| 678 (1)| 00:00:09 |
- |* 1 | VIEW | | 71052 | 14M| 339 (1)| 00:00:05 |
- | 2 | COUNT | | | | | |
- | 3 | TABLE ACCESS FULL | TEST | 71052 | 14M| 339 (1)| 00:00:05 |
- | 4 | SORT AGGREGATE | | 1 | 13 | | |
- | 5 | VIEW | | 71052 | 902K| 339 (1)| 00:00:05 |
- | 6 | COUNT | | | | | |
- | 7 | TABLE ACCESS FULL| TEST | 71052 | | 339 (1)| 00:00:05 |
- ------------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 1 - filter("T"."RN"= (SELECT MAX("RN") FROM (SELECT ROWNUM "RN"
- FROM "TEST" "A") "T"))
-
- Note
- -----
- - dynamic sampling used for this statement (level=2)
-
-
- Statistics
- ----------------------------------------------------------
- 45 recursive calls
- 0 db block gets
- 2756 consistent gets
- 2482 physical reads
- 0 redo size
- 1407 bytes sent via SQL*Net to client
- 512 bytes received via SQL*Net from client
- 1 SQL*Net roundtrips to/from client
- 2 sorts (memory)
- 0 sorts (disk)
- 0 rows processed
尝试加hint让with as的部分放在temp表,执行结果以及执行计划如下:
- SYS@proc> set autotrace off
- SYS@proc> with t as (select /*+MATERIALIZE*/ a.*,rownum rn from test a)
- 2 select count(*) from t where t.rn=(select max(rn) from t);
-
- COUNT(*)
- ----------
- 1
-
- SYS@proc> set autotrace traceonlu
- Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
- SYS@proc> set autotrace traceonly
- SYS@proc> l
- 1 with t as (select /*+MATERIALIZE*/ a.*,rownum rn from test a)
- 2* select count(*) from t where t.rn=(select max(rn) from t)
- SYS@proc> /
-
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 340454420
-
- --------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 1426 (1)| 00:00:18 |
- | 1 | TEMP TABLE TRANSFORMATION | | | | | |
- | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6624_16F270 | | | | |
- | 3 | COUNT | | | | | |
- | 4 | TABLE ACCESS FULL | TEST | 71052 | 14M| 339 (1)| 00:00:05 |
- | 5 | SORT AGGREGATE | | 1 | 13 | | |
- |* 6 | VIEW | | 71052 | 902K| 543 (1)| 00:00:07 |
- | 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_16F270 | 71052 | 14M| 543 (1)| 00:00:07 |
- | 8 | SORT AGGREGATE | | 1 | 13 | | |
- | 9 | VIEW | | 71052 | 902K| 543 (1)| 00:00:07 |
- | 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_16F270 | 71052 | 14M| 543 (1)| 00:00:07 |
- --------------------------------------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 6 - filter("T"."RN"= (SELECT MAX("RN") FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
- "OWNER","C1" "OBJECT_NAME","C2" "SUBOBJECT_NAME","C3" "OBJECT_ID","C4" "DATA_OBJECT_ID","C5"
- "OBJECT_TYPE","C6" "CREATED","C7" "LAST_DDL_TIME","C8" "TIMESTAMP","C9" "STATUS","C10"
- "TEMPORARY","C11" "GENERATED","C12" "SECONDARY","C13" "NAMESPACE","C14" "EDITION_NAME","C15"
- "RN" FROM "SYS"."SYS_TEMP_0FD9D6624_16F270" "T1") "T"))
-
- Note
- -----
- - dynamic sampling used for this statement (level=2)
-
-
- Statistics
- ----------------------------------------------------------
- 12 recursive calls
- 1340 db block gets
- 3876 consistent gets
- 3869 physical reads
- 764 redo size
- 526 bytes sent via SQL*Net to client
- 523 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
另外该语句在10g是可以不用加hint就能正常执行:
- SYS@proc> select * from v$version where rownum=1;
-
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
-
- SYS@proc> with t as (select a.*,rownum rn from test a)
- 2 select count(*) from t where t.rn=(select max(rn) from t);
-
- COUNT(*)
- ----------
- 1
这里尝试将11g里边的参数optimizer_features_enable修改成10g的,在看看执行计划以及结果:
- SYS@proc> alter system set optimizer_features_enable='10.2.0.4';
-
- System altered.
-
- SYS@proc> show parameter feature
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- optimizer_features_enable string 10.2.0.4
- SYS@proc> set pagesize 9999 long 9999 lines 500
- SYS@proc> with t as (select a.*,rownum rn from test a)
- 2 select count(*) from t where t.rn=(select max(rn) from t);
-
- COUNT(*)
- ----------
- 0
-
- SYS@proc> set autotrace traceonly
- SYS@proc> l
- 1 with t as (select a.*,rownum rn from test a)
- 2* select count(*) from t where t.rn=(select max(rn) from t)
- SYS@proc> /
-
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 4022736097
-
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 678 (1)| 00:00:09 |
- | 1 | SORT AGGREGATE | | 1 | 13 | | |
- |* 2 | VIEW | | 71052 | 902K| 339 (1)| 00:00:05 |
- | 3 | COUNT | | | | | |
- | 4 | TABLE ACCESS FULL | TEST | 71052 | | 339 (1)| 00:00:05 |
- | 5 | SORT AGGREGATE | | 1 | 13 | | |
- | 6 | VIEW | | 71052 | 902K| 339 (1)| 00:00:05 |
- | 7 | COUNT | | | | | |
- | 8 | TABLE ACCESS FULL| TEST | 71052 | | 339 (1)| 00:00:05 |
- -------------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - filter("T"."RN"= (SELECT MAX("RN") FROM (SELECT "A"."OWNER"
- "OWNER","A"."OBJECT_NAME" "OBJECT_NAME","A"."SUBOBJECT_NAME"
- "SUBOBJECT_NAME","A"."OBJECT_ID" "OBJECT_ID","A"."DATA_OBJECT_ID"
- "DATA_OBJECT_ID","A"."OBJECT_TYPE" "OBJECT_TYPE","A"."CREATED"
- "CREATED","A"."LAST_DDL_TIME" "LAST_DDL_TIME","A"."TIMESTAMP"
- "TIMESTAMP","A"."STATUS" "STATUS","A"."TEMPORARY"
- "TEMPORARY","A"."GENERATED" "GENERATED","A"."SECONDARY"
- "SECONDARY","A"."NAMESPACE" "NAMESPACE","A"."EDITION_NAME"
- "EDITION_NAME",ROWNUM "RN" FROM "TEST" "A") "T"))
-
- Note
- -----
- - dynamic sampling used for this statement (level=2)
-
-
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 2488 consistent gets
- 2482 physical reads
- 0 redo size
- 525 bytes sent via SQL*Net to client
- 523 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
这里不是很懂了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30174570/viewspace-2152578/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30174570/viewspace-2152578/