oracle 10g下范畴分区扫描的几种方式
日期：2014-05-16 浏览次数：20573 次
oracle 10g下范围分区扫描的几种方式
(
partition p_1 values less than (to_date('2013-12-01', 'yyyy-mm-dd')),
partition p_2 values less than (to_date('2014-02-01', 'yyyy-mm-dd')),
partition p_3 values less than (to_date('2014-03-01', 'yyyy-mm-dd')),
partition p_4 values less than (to_date('2014-04-01', 'yyyy-mm-dd')),
partition p_5 values less than (to_date('2014-05-01', 'yyyy-mm-dd')),
partition p_6 values less than (to_date('2014-06-01', 'yyyy-mm-dd')),
partition p_max values less than (MAXVALUE)
) nologging;
SQL> insert /*+append */ into t_range select rownum,
to_date(to_char(sysdate - 140, 'J') +
trunc(dbms_random.value(0, 80)),
'J')
from dual
connect by rownum <= 100000;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats(user,'T_RANGE');
SQL> select to_char(t.test_date,'yyyy-MM'),count(1) from t_range t
group by to_char(t.test_date,'yyyy-MM');
TO_CHAR COUNT(1)
------- ----------
2014-01 38803
2014-03 11242
2013-12 15107
2014-02 34848
SQL> set autotrace traceonly
--扫描单个分区
SQL> select * from t_range where test_date = to_date('2014-04-28', 'yyyy-mm-dd');
执行计划
----------------------------------------------------------
Plan hash value: 3010141842
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 22 | 2 (0)| 00:00:01 | 5 | 5 |
|* 2 | TABLE ACCESS FULL | T_RANGE | 1 | 22 | 2 (0)| 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TEST_DATE"=TO_DATE('2014-04-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
oracle 10g下有几种扫描方式，注意最后一种扫描方式，当对分区的列进行计算时，会不走分区，这跟对索引列进行计算会导致无法用索引一样。
--扫描单个分区 PARTITION RANGE SINGLE
--连续扫描多个分区 PARTITION RANGE ITERATOR
--不连续扫描多个分区 PARTITION RANGE INLIST
--扫描全分区 PARTITION RANGE ALL
SQL> drop table t_range purge;
SQL> create table t_range (id number not null PRIMARY KEY, test_date date) partition by range (test_date)(
partition p_1 values less than (to_date('2013-12-01', 'yyyy-mm-dd')),
partition p_2 values less than (to_date('2014-02-01', 'yyyy-mm-dd')),
partition p_3 values less than (to_date('2014-03-01', 'yyyy-mm-dd')),
partition p_4 values less than (to_date('2014-04-01', 'yyyy-mm-dd')),
partition p_5 values less than (to_date('2014-05-01', 'yyyy-mm-dd')),
partition p_6 values less than (to_date('2014-06-01', 'yyyy-mm-dd')),
partition p_max values less than (MAXVALUE)
) nologging;
SQL> insert /*+append */ into t_range select rownum,
to_date(to_char(sysdate - 140, 'J') +
trunc(dbms_random.value(0, 80)),
'J')
from dual
connect by rownum <= 100000;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats(user,'T_RANGE');
SQL> select to_char(t.test_date,'yyyy-MM'),count(1) from t_range t
group by to_char(t.test_date,'yyyy-MM');
TO_CHAR COUNT(1)
------- ----------
2014-01 38803
2014-03 11242
2013-12 15107
2014-02 34848
SQL> set autotrace traceonly
--扫描单个分区
SQL> select * from t_range where test_date = to_date('2014-04-28', 'yyyy-mm-dd');
执行计划
----------------------------------------------------------
Plan hash value: 3010141842
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 22 | 2 (0)| 00:00:01 | 5 | 5 |
|* 2 | TABLE ACCESS FULL | T_RANGE | 1 | 22 | 2 (0)| 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TEST_DATE"=TO_DATE('2014-04-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
