本帖最后由 yanwang0120 于 2015-8-12 15:05 编辑
比如
select r.a,t.b from r, t where t.c = r.d;查a,b两个字段不走索引。
select r.afrom r, t where t.c = r.d;只查a一个字段走索引。
a,b字段都不存在函数和索引。字段c和字段d分别建b-tree索引。
SQL> set autotrace traceonly;
SQL> select t.task_def_key_
2from t_h_recieve r, act_ru_task t
3 where t.proc_inst_id_ = r.piid
4 and r.piid is not null;
已选择2607行。
执行计划
----------------------------------------------------------
Plan hash value: 3918855481
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."PROC_INST_ID_"=SYS_OP_C2C("R"."PIID"))
3 - filter("R"."PIID" IS NOT NULL)
统计信息
----------------------------------------------------------
1recursive calls
0db block gets
1270consistent gets
0physical reads
0redo size
60337bytes sent via SQL*Net to client
2422bytes received via SQL*Net from client
175SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
2607rows processed
查询两个字段时的执行计划:
SQL> set autotrace traceonly;
SQL> select t.task_def_key_, r.HOTLINE_NUM
2from t_h_recieve r, act_ru_task t
3 where t.proc_inst_id_ = r.piid
4 and r.piid is not null;
已选择2993行。
执行计划
----------------------------------------------------------
Plan hash value: 1009754173