如何更改驱动表

JeanWan

SELECT /*+ index_ss(a I_EQP_PROCESS_HISTORY_EQUIP) */  UNIQUE equipment,processstate,TIMESTAMP
FROM  SMIC_EQP_PROCESS_HISTORY@mesfabdb a
WHERE equipment IN (SELECT UNIQUE eqpid FROM sdb_tb_qtime_config b)
AND TIMESTAMP >= :mytime
现在的执行计划是：
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT UNIQUE (cr=3 pr=150 pw=1695 time=54619884 us)
      0   HASH JOIN  (cr=3 pr=150 pw=1695 time=54619836 us)
294310    REMOTE  SMIC_EQP_PROCESS_HISTORY (cr=0 pr=0 pw=0 time=1178660 us)
      0    TABLE ACCESS FULL SDB_TB_QTIME_CONFIG (cr=3 pr=0 pw=0 time=147 us)
希望把SDB_TB_QTIME_CONFIG作为驱动表，不知道各位有什么办法？先谢了。

dream_land

用exist子句

JeanWan

回复 2# dream_land


    exists和in的查询结果似乎有可能不完全一致吧

dingning239

两表内连接，然后加上/*+order*/ 提示，这样试试看

JeanWan

回复 4# dingning239

确实可行 谢了
SELECT /*+ ordered index_ss(a I_EQP_PROCESS_HISTORY_EQUIP) */ UNIQUE equipment,processstate,TIMESTAMP
FROM  sdb_tb_qtime_config b, SMIC_EQP_PROCESS_HISTORY@mesfabdb a
WHERE a.equipment = b.eqpid
AND TIMESTAMP >= :mytime

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT UNIQUE (cr=3 pr=0 pw=0 time=612 us)
      0   HASH JOIN  (cr=3 pr=0 pw=0 time=576 us)
      0    TABLE ACCESS FULL SDB_TB_QTIME_CONFIG (cr=3 pr=0 pw=0 time=93 us)
      0    REMOTE  SMIC_EQP_PROCESS_HISTORY (cr=0 pr=0 pw=0 time=0 us)

dingning239