▼▼▼
create table t as select * from dba_objects;
--表t的object_id列有索引，其實(shí)這里的last_ddl_time也是有索引的，而且可以走索引，為了簡(jiǎn)化，不進(jìn)行模擬
create index idx_t on t(object_id);
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>t,no_invalidate=>false);
SQL> select count(*) from t;
  COUNT(*)
----------
    261898
原始語(yǔ)句：
select *
from(
select rowid,t.*
from t where t.object_id in
(
 select object_id
 from(
 select object_id
 from t
 where mod(object_id,10)=0 
 and status=VALID
 and last_ddl_time > trunc(sysdate-200)
 order by timestamp,last_ddl_time
    ) where rownum<=100
)   and t.status=VALID
 and t.last_ddl_time > trunc(sysdate-200)
 order by last_ddl_time
) where rownum<=100;

▼▼▼
--執(zhí)行12分鐘還沒(méi)有出現(xiàn)結(jié)果
SQL> set autotrace traceonly
SQL> select *
  2  from(
  3  select rowid,t.*
  4  from t where t.object_id in
  5  (
  6     select object_id
  7     from(
  8             select object_id
  9             from t
 10             where mod(object_id,10)=0 
 11             and status=VALID
 12             and last_ddl_time > trunc(sysdate-200)
                order by last_ddl_time
 13   14      ) where rownum<=100
 15  )   and t.status=VALID
 16             and t.last_ddl_time > trunc(sysdate-200)
 17             order by last_ddl_time
 18  ) where rownum<=100;
^Cselect *
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:12:05.73

--問(wèn)題執(zhí)行計(jì)劃如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 3028954274
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   219 |  2100K  (2)| 07:00:08 |
|*  1 |  COUNT STOPKEY               |      |       |       |            |          |
|   2 |   VIEW                       |      |     1 |   219 |  2100K  (2)| 07:00:08 |
|*  3 |    SORT ORDER BY STOPKEY     |      |     1 |   100 |  2100K  (2)| 07:00:08 |
|*  4 |     FILTER                   |      |       |       |            |          |
|*  5 |      TABLE ACCESS FULL       | T    |  4936 |   482K|   855   (3)| 00:00:11 |
|*  6 |      FILTER                  |      |       |       |            |          |
|*  7 |       COUNT STOPKEY          |      |       |       |            |          |
|   8 |        VIEW                  |      |    49 |   637 |   851   (2)| 00:00:11 |
|*  9 |         SORT ORDER BY STOPKEY|      |    49 |  1960 |   851   (2)| 00:00:11 |
|*  10 |          TABLE ACCESS FULL   | T    |    49 |  1960 |   850   (2)| 00:00:11 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)
   4 - filter( EXISTS ()
   5 - filter("T"."STATUS"=VALID AND
              "T"."LAST_DDL_TIME">TRUNC(SYSDATE@!-200))
   6 - filter("OBJECT_ID"=:B1)
   7 - filter(ROWNUM<=100)
   9 - filter(ROWNUM<=100)
  10 - filter("STATUS"=VALID AND MOD("OBJECT_ID",10)=0 AND
              "LAST_DDL_TIME">TRUNC(SYSDATE@!-200))

▼▼▼
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery removal for query block SEL$3 (#3)
RSW: Not valid for subquery removal SEL$3 (#3)
Subquery unchanged.
Subquery Unnesting on query block SEL$2 (#2)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$2 (#2).
SU:   Checking validity of unnesting subquery SEL$3 (#3)
SU:     SU bypassed: Subquery in a view with rowid reference.                   
--含有ROWID的subquery unnest失敗
SU:   Validity checks failed.

▼▼▼
A query referencing a rowid from a subquery with a join fails with the following error:
SQL> select rowid
from
  (
    select e.empno
    from emp e left outer join (select deptno from dept) d
      on (e.deptno = d.deptno)
  );

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

▼▼▼
The error is observed in query with ANSI joins because the way the query is written.
A rowid is only defined for individual rows in a table, so it is not legal to select a rowid from a subquery unless each row from that subquery can be guaranteed to be uniquely associated with exactly one row in one table.  Therefore, the subquery may have only one item in its FROM list; that item must also have a rowid; and the subquery must not use DISTINCT, GROUP BY, or anything else that might gather multiple rows into one.

▼▼▼
Reference the rowid when it is valid as an explicit select list item:
SQL> select rid as "ROWID"
from
  ( select e.empno, e.rowid as rid
    from emp e left outer join (select deptno from dept) d
      on (e.deptno = d.deptno)
  );

▼▼▼
——改寫方案       
select rd as "ROWID",object_id,object_name,last_ddl_time
from(
select rowid rd,t.*
from t where t.object_id in
(
 select object_id
 from(
 select object_id
 from t
 where mod(object_id,10)=0 
 and status=VALID
 and last_ddl_time > trunc(sysdate-200)
 order by timestamp,last_ddl_time
    ) where rownum<=100
)   and t.status=VALID
 and t.last_ddl_time > trunc(sysdate-200)
 order by last_ddl_time
) where rownum<=100;

▼▼▼
——改寫方案       
selectExecution Plan
----------------------------------------------------------
Plan hash value: 16082276
-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |    49 |  4900 |   931   (2)| 00:00:12 |
|*  1 |  COUNT STOPKEY                 |          |       |       |            |          |
|   2 |   VIEW                         |          |    49 |  4900 |   931   (2)| 00:00:12 |
|*  3 |    SORT ORDER BY STOPKEY       |          |    49 |  5145 |   931   (2)| 00:00:12 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T        |     1 |   100 |     2   (0)| 00:00:01 |
|   5 |      NESTED LOOPS              |          |    49 |  5145 |   930   (2)| 00:00:12 |
|   6 |       VIEW                     | VW_NSO_1 |    49 |   245 |   851   (2)| 00:00:11 |
|   7 |        HASH UNIQUE             |          |    49 |   245 |            |          |
|*  8 |         COUNT STOPKEY          |          |       |       |            |          |
|   9 |          VIEW                  |          |    49 |   245 |   851   (2)| 00:00:11 |
|* 10 |           SORT ORDER BY STOPKEY|          |    49 |  1960 |   851   (2)| 00:00:11 |
|* 11 |            TABLE ACCESS FULL   | T        |    49 |  1960 |   850   (2)| 00:00:11 |
|* 12 |       INDEX RANGE SCAN         | IDX_T    |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)
   4 - filter("T"."STATUS"=VALID AND "T"."LAST_DDL_TIME">TRUNC(SYSDATE@!-200))
   8 - filter(ROWNUM<=100)
  10 - filter(ROWNUM<=100)
  11 - filter("STATUS"=VALID AND MOD("OBJECT_ID",10)=0 AND
              "LAST_DDL_TIME">TRUNC(SYSDATE@!-200))
  12 - access("T"."OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4026  consistent gets
          0  physical reads
          0  redo size
       7402  bytes sent via SQL*Net to client
        586  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        100  rows processed
) where rownum<=100;

▼▼▼
--其實(shí)原來(lái)的業(yè)務(wù)就是這么簡(jiǎn)單
select rowid,object_id,object_name,last_ddl_time
from (
 select object_id
 from t
 where mod(object_id,10)=0 
 and status=VALID
 and last_ddl_time > trunc(sysdate-200)
 order by timestamp,last_ddl_time
 )
) where rownum<=100;