d_fct是分区表,设了并行,字段time_day 按天分区。下列语句执行计划上看,确实走了并行,INDEX FAST FULL SCAN,这个是全扫了整个表呢?还是一个分区呢? Pstart和Pstop都是2711,是说第2711个分区吗?
select count(*) from d_fct fct
where fct.time_day = TO_DATE('2014-06-02','YYYY-MM-DD')
执行计划
----------------------------------------------------------
Plan hash value: 3142710511
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 8 | | | | | Q1,00 | P->S | QC (RAND)
| 4 | SORT AGGREGATE | | 1 | 8 | | | | | Q1,00 | PCWP |
| 5 | PX BLOCK ITERATOR | | 15M| 120M| 2 (0)| 00:00:01 | 2711 | 2711 | Q1,00 | PCWC |
| 6 | BITMAP CONVERSION COUNT | | 15M| 120M| 2 (0)| 00:00:01 | | | Q1,00 | PCWP |
|* 7 | BITMAP INDEX FAST FULL SCAN| TIME_DAY_IDX_5 | | | | | 2711 | 2711 | Q1,00 | PCWP |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("FCT"."TIME_DAY"=TO_DATE(' 2014-06-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
192 recursive calls
0 db block gets
866 consistent gets
47 physical reads
0 redo size
239 bytes sent via SQL*Net to client
253 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
下面这个加了hint,去掉了并行,执行计划里有PARTITION RANGE SINGLE ,很明显只扫了一个分区,做了剪裁。这个正常。但从统计信息来看,不走并行的要优一些,只有 130 consistent gets,但为什么ORACLE 要选择走并行呢?不理解。另外是不是Pstart和Pstop一旦有值的话就说明做了分区剪裁呢?那能否用并行的方式做分区剪裁呢?从来没见过执行计划里又有PX又有PARTITION RANGE SINGLE 的情况。
select /*+ no_parallel */ count(*) from d_fct fct
where fct.time_day = TO_DATE('2014-06-02','YYYY-MM-DD')
执行计划
----------------------------------------------------------
Plan hash value: 3759807457
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 84 (0)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE | | 15M| 120M| 84 (0)| 00:00:02 | 2711 | 2711 |
| 3 | BITMAP CONVERSION COUNT | | 15M| 120M| 84 (0)| 00:00:02 | | |
|* 4 | BITMAP INDEX FAST FULL SCAN| TIME_DAY_IDX_5 | | | | | 2711 | 2711 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("FCT"."TIME_DAY"=TO_DATE(' 2014-06-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
130 consistent gets
0 physical reads
0 redo size
239 bytes sent via SQL*Net to client
253 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed