------ 能用到索引：
SQL > select count(*) from dt_bar64;

  COUNT(*)
----------
223413

SQL > explain plan for select * from dt_bar64 t where t.taskid = 3651 and t.trnn='000087';

Explained.

select * from table(dbms_xplan.display());
SQL >
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3845150141

-------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |          |    1 | 113 |    4 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| DT_BAR64 |    1 | 113 |    4 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | PK_DT_BAR64 |    1 |    |    3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

2 - access("T"."TASKID"=3651 AND "T"."TRNN"='000087')

14 rows selected.

------ 不能用到索引：
SQL > select count(*) from dt_bar65;

  COUNT(*)
----------
234088

SQL > explain plan for select * from dt_bar65 t where t.taskid = 3651 and t.trnn='000087';

Explained.

SQL > select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2601456579

--------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time    | TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |       |    1 | 65 |    3 (0)| 00:00:01 |       |    |          |
| 1 |  PX COORDINATOR    |       |    |    |          |       |       |    |          |
| 2 | PX SEND QC (RANDOM)| :TQ10000 |    1 | 65 |    3 (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
| 3 | PX BLOCK ITERATOR |       |    1 | 65 |    3 (0)| 00:00:01 |  Q1,00 | PCWC |          |
|*  4 |    TABLE ACCESS FULL| DT_BAR65 |    1 | 65 |    3 (0)| 00:00:01 |  Q1,00 | PCWP |          |
--------------------------------------------------------------------------------------------------------------

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

4 - filter("T"."TASKID"=3651 AND "T"."TRNN"='000087')

16 rows selected.

两个表都是并行度为300的表，同时在在两张表上创建相同的联合索引，索引的字段都是(TASKID,TRNN)
问题：
1.并行的表是怎样影响执行计划的？
2.请解释一下PX COORDINATOR，我查到资料是表示并行查询的执行计划会包含这个内容，但是另外一个用到索引的就不好解释了
3.在使用强制索引后执行计划显示是可以使用到索引的，select /*+ index  (t,PK_DT_BAR65) */ * from dt_bar65 t where t.taskid = 3651 and t.trnn='000087';，这两种执行效果哪个更高效一些？

ITPub博客

【问题处理】同样的并行表，同样的索引结构，不同的执行计划

最新文章