查询字段是否影响执行计划的索引

显示全部楼层 · 2015-8-12 11:29:00

我遇到一个问题：
有两个表t_h_recieve r, act_ru_task t，r表16w+条数据，t表3000+数据。
字段t.proc_inst_id_ 和 r.piid分别建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
-------------------------------------------------------------------------------------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT|
| 401K|17M| 315 (2)| 00:00:04 |
|*1 |HASH JOIN
|
| 401K|17M| 315 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL | ACT_RU_TASK |3462 | 138K| 204 (0)| 00:00:03 |
|*3 | INDEX FAST FULL SCAN| INDEX_RPI | 156K| 917K| 107 (2)| 00:00:02 |
-------------------------------------------------------------------------------------
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
----------------------------------------------------------------------------------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 401K|22M|3788 (1)| 00:00:46 |
|*1 |HASH JOIN |
| 401K|22M|3788 (1)| 00:00:46 |
| 2 | TABLE ACCESS FULL| ACT_RU_TASK |3462 | 138K| 204 (0)| 00:00:03 |
|*3 | TABLE ACCESS FULL| T_H_RECIEVE | 156K|2905K|3580 (1)| 00:00:43 |
----------------------------------------------------------------------------------
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
13612consistent gets

0physical reads

0redo size
89890bytes sent via SQL*Net to client
2708bytes received via SQL*Net from client
201SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
2993rows processed

千问 · 2015-8-12 11:29:00

SQL> desc t_h_recieve;
名称
是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
ID
NOT NULL VARCHAR2(36)
HOTLINE_NUM
VARCHAR2(40)
RECIEVE_TYPE
VARCHAR2(10)
RECIEVE_FROM
NOT NULL VARCHAR2(40)
RECIEVE_NUM
VARCHAR2(80)
PHONE_MAN
VARCHAR2(80)
PHONE_NUM
VARCHAR2(40)
RESPOND_NUM
VARCHAR2(200)
PHONE_TIME
DATE
PHONE_PERIOD
DATE
REQUEST_VALID
VARCHAR2(10)
RESPOND_MUST
VARCHAR2(10)
PROBLEM_CLASS
NUMBER(10)
PROBLEM_NAME
VARCHAR2(200)
MSG_TITLE
VARCHAR2(300)
LINK_UNIT
VARCHAR2(40)
SIGN_MAN_ID
VARCHAR2(40)
SIGN_MAN
VARCHAR2(80)
SIGN_DATE
DATE
IS_REDUPLICATE_CALL
VARCHAR2(10)
PRO_STATUS
VARCHAR2(40)
PIID
VARCHAR2(40)
REPAIR_DATE
DATE
CRISIS_LEVEL
VARCHAR2(40)
PHONE_REQUEST
DATE
IS_DELETE
VARCHAR2(10)
END_DATE
DATE
IS_PUBLIC
VARCHAR2(40)
CHANGE_DATE
DATE
ADDRESS
VARCHAR2(300)
IS_REPAIR
VARCHAR2(10)
UNDERTAKER
VARCHAR2(40)
DISPATCH_ADVICE
VARCHAR2(4000)
DISPATCH_DATE
DATE
IS_CONTACT_COMPLAINANT
VARCHAR2(10)
COMPLAINANT
VARCHAR2(40)
CONTACT_REMARK
VARCHAR2(400)
DESCRIPTION
VARCHAR2(4000)
ACCEPT_DATE
DATE
BACK_DATE
DATE
REPLY_DATE
DATE
HANDLE_DURATION
NUMBER
IS_ALLOW_BACK
VARCHAR2(10)
A5ID
VARCHAR2(50)
IS_SECRET
VARCHAR2(10)
SQL> desc act_ru_task
名称
是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
ID_
NOT NULL NVARCHAR2(64)
REV_
NUMBER(38)
EXECUTION_ID_
NVARCHAR2(64)
PROC_INST_ID_
NVARCHAR2(64)
PROC_DEF_ID_
NVARCHAR2(64)
NAME_
NVARCHAR2(255)
PARENT_TASK_ID_
NVARCHAR2(64)
DESCRIPTION_
NVARCHAR2(2000)
TASK_DEF_KEY_
NVARCHAR2(255)
OWNER_
NVARCHAR2(255)
ASSIGNEE_
NVARCHAR2(255)
DELEGATION_
NVARCHAR2(64)
PRIORITY_
NUMBER(38)
CREATE_TIME_
TIMESTAMP(6)
DUE_DATE_
TIMESTAMP(6)
SUSPENSION_STATE_
NUMBER(38)