一哥们问我，怎么才能让子查询作为驱动表？ SQL如下：

select  rowid rid
   from its_car_pass7 v
  where 1 = 1
    and pass_datetime >=
        to_date('2013-07-06 :17:46:04', 'yyyy-mm-dd hh24:mi:ss')
    and pass_datetime <=
        to_date('2013-07-06 :18:46:06', 'yyyy-mm-dd hh24:mi:ss')
    and v.pass_device_unid in
        (select unid
           from its_base_device
          where dev_bay_unid in ('01685EFE4658C19D59C4DDAAEDD37393')
            and dev_type = '1'
            and dev_chk_flag = '1'
            and dev_delete_flag = 'N')
  order by v.pass_datetime asc 
 /

执行计划如下：

Execution Plan
----------------------
Plan hash value: 3634433140

--------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------
|   0 | SELECT STATEMENT              |                    |     1 |   111 |     2  (50)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                |                    |     1 |   111 |     2  (50)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |                    |       |       |            |          |       |       |
|   3 |    NESTED LOOPS               |                    |     1 |   111 |     1   (0)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE SINGLE    |                    |     1 |    39 |     1   (0)| 00:00:01 |  1284 |  1284 |
|*  5 |      INDEX SKIP SCAN          | IDX_VT7_DEVICEID   |     1 |    39 |     1   (0)| 00:00:01 |  1284 |  1284 |
|*  6 |     INDEX UNIQUE SCAN         | PK_ITS_BASE_DEVICE |     1 |       |     0   (0)| 00:00:01 |       |       |
|*  7 |    TABLE ACCESS BY INDEX ROWID| ITS_BASE_DEVICE    |     1 |    72 |     0   (0)| 00:00:01 |       |       |
--------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("PASS_DATETIME">=TO_DATE(' 2013-07-06 17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND
              "PASS_DATETIME"<=TO_DATE(' 2013-07-06 18:46:06', 'syyyy-mm-dd hh24:mi:ss'))
       filter("PASS_DATETIME">=TO_DATE(' 2013-07-06 17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND
              "PASS_DATETIME"<=TO_DATE(' 2013-07-06 18:46:06', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("V"."PASS_DEVICE_UNID"="UNID")
   7 - filter("DEV_BAY_UNID"='01685EFE4658C19D59C4DDAAEDD37393' AND "DEV_TYPE"='1' AND
              "DEV_DELETE_FLAG"='N' AND "DEV_CHK_FLAG"='1')


Statistics
----------------------
          1  recursive calls
          0  db block gets
     110973  consistent gets
          0  physical reads
          0  redo size
      47861  bytes sent via SQL*Net to client
       1656  bytes received via SQL*Net from client
        105  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1560  rows processed

这里我们就不管统计信息是否准确了，也不管SQL优化的问题，就单单讨论哥们问的问题吧。

那哥们说，怎么才能让子查询作为驱动表呢？他自己试了很多方法就是搞不定。 那我们来亲自搞搞吧

explain plan for   select  rowid rid
   from its_car_pass7 v
  where 1 = 1
    and pass_datetime >=
        to_date('2013-07-06 :17:46:04', 'yyyy-mm-dd hh24:mi:ss')
    and pass_datetime <=
        to_date('2013-07-06 :18:46:06', 'yyyy-mm-dd hh24:mi:ss')
    and v.pass_device_unid in
        (select unid
           from its_base_device
          where dev_bay_unid in ('01685EFE4658C19D59C4DDAAEDD37393')
            and dev_type = '1'
            and dev_chk_flag = '1'
            and dev_delete_flag = 'N')
  order by v.pass_datetime asc 
 /

执行计划如下

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));

-----------------------
Plan hash value: 2191740724
------