SQL> Select /*+ index(a,wip_info_fwseq) */
2 Wafer_no, Wait_time, In_time, Out_time, Sysdate, Status, Proc_id
3from Wip_info a
4 where Proc_Flow = 'P06C14'
5 order by Wafer_no, Proc_Seq_hist;
已选择341637行。
已用时间:00: 00: 50.06
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=33710 Card=39172 Byt
es=1606052)
10 TABLE ACCESS (BY INDEX ROWID) OF 'WIP_INFO' (Cost=33710 Ca
rd=39172 Bytes=1606052)
21 INDEX (RANGE SCAN) OF 'WIP_INFO_FWSEQ' (NON-UNIQUE) (Cos
t=256 Card=39172)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
300146consistent gets
14352physical reads
0redo size
14457205bytes sent via SQL*Net to client
251028bytes received via SQL*Net from client
22777SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
341637rows processed
SQL> SelectWafer_no, Wait_time, In_time, Out_time, Sysdate, Status, Proc_id
2from Wip_info a
3 where Proc_Flow = 'P06C14'
4 order by Wafer_no, Proc_Seq_hist;
已选择341637行。
已用时间:00: 00: 38.01
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=9097 Card=39172 Byte
s=1606052)
10 SORT (ORDER BY) (Cost=9097 Card=39172 Bytes=1606052)
21 TABLE ACCESS (FULL) OF 'WIP_INFO' (Cost=8808 Card=39172
Bytes=1606052)
Statistics
----------------------------------------------------------
0recursive calls
10db block gets
92864consistent gets
68347physical reads
0redo size
14457205bytes sent via SQL*Net to client
251028bytes received via SQL*Net from client
22777SQL*Net roundtrips to/from client
0sorts (memory)
1sorts (disk)
341637rows processed
SQL>
谁能帮我解释一下。当我不加提示执行SQL。为什么Oracle选全表
扫描。明明物理读很多。SQL执行是physical reads重要还是consistent gets重要。
虽然从整个执行时间还看。第二个执行总时间上要比第一个快。但是在PL/SQL等一些表格工具查询时。第一个SQL的响应速度只要0.1秒。数据库的optimizer_mode是choose
在不改成FIRST_ROWS，不改动SQL的情况下。怎么让Oracle选择索引。
|
