d_fct是分區(qū)表，設(shè)了并行，字段time_day 按天分區(qū)。下列語(yǔ)句執(zhí)行計(jì)劃上看，確實(shí)走了并行，INDEX FAST FULL SCAN，這個(gè)是全掃了整個(gè)表呢？還是一個(gè)分區(qū)呢？ Pstart和Pstop都是2711，是說(shuō)第2711個(gè)分區(qū)嗎？

select? ?? ?count(*)? ?from d_fct fct

where??fct.time_day = TO_DATE('2014-06-02','YYYY-MM-DD')

執(zhí)行計(jì)劃

----------------------------------------------------------

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'))

統(tǒng)計(jì)信息

----------------------------------------------------------

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

下面這個(gè)加了hint,去掉了并行，執(zhí)行計(jì)劃里有PARTITION RANGE SINGLE ，很明顯只掃了一個(gè)分區(qū)，做了剪裁。這個(gè)正常。但從統(tǒng)計(jì)信息來(lái)看，不走并行的要優(yōu)一些，只有 130??consistent gets，但為什么ORACLE 要選擇走并行呢？不理解。另外是不是Pstart和Pstop一旦有值的話就說(shuō)明做了分區(qū)剪裁呢？那能否用并行的方式做分區(qū)剪裁呢？從來(lái)沒(méi)見過(guò)執(zhí)行計(jì)劃里又有PX又有PARTITION RANGE SINGLE 的情況。

select??/*+ no_parallel */? ? count(*)? ?from d_fct fct

where??fct.time_day = TO_DATE('2014-06-02','YYYY-MM-DD')

執(zhí)行計(jì)劃

----------------------------------------------------------

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'))

統(tǒng)計(jì)信息

----------------------------------------------------------

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

總結(jié)

以上是生活随笔為你收集整理的分区裁剪 oracle,[讨论]分区表并行和剪裁的困惑的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。