oracle 10g下范围分区扫描的几种方式
来源:互联网 发布:淘宝客服回复话术 编辑:程序博客网 时间:2024/05/21 22:43
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
3 consistent gets
0 physical reads
0 redo size
327 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
--连续扫描多个分区
SQL> select * from t_range
where test_date <= to_date('2014-04-28', 'yyyy-mm-dd')
and test_date >= to_date('2014-02-28', 'yyyy-mm-dd');
执行计划
----------------------------------------------------------
Plan hash value: 1921532398
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12556 | 147K| 28 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 12556 | 147K| 28 (0)| 00:00:01 | 3 | 5 |
|* 2 | TABLE ACCESS FULL | T_RANGE | 12556 | 147K| 28 (0)| 00:00:01 | 3 | 5 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TEST_DATE">=TO_DATE('2014-02-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"TEST_DATE"<=TO_DATE('2014-04-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
956 consistent gets
0 physical reads
0 redo size
309138 bytes sent via SQL*Net to client
9515 bytes received via SQL*Net from client
832 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12453 rows processed
--不连续扫描多个分区
SQL> select * from t_range
where test_date = to_date('2014-04-28', 'yyyy-mm-dd')
or test_date = to_date('2014-02-28', 'yyyy-mm-dd');
执行计划
----------------------------------------------------------
Plan hash value: 2021067984
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1678 | 20136 | 21 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE INLIST| | 1678 | 20136 | 21 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | T_RANGE | 1678 | 20136 | 21 (0)| 00:00:01 |KEY(I) |KEY(I) |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TEST_DATE"=TO_DATE('2014-02-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"TEST_DATE"=TO_DATE('2014-04-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
175 consistent gets
0 physical reads
0 redo size
22646 bytes sent via SQL*Net to client
1265 bytes received via SQL*Net from client
82 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1211 rows processed
--扫描全分区
SQL> select * from t_range where to_char(test_date,'yyyy-MM-dd')='2014-04-01';
执行计划
----------------------------------------------------------
Plan hash value: 2128486036
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 994 | 11928 | 59 (4)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 994 | 11928 | 59 (4)| 00:00:01 | 1 | 7 |
|* 2 | TABLE ACCESS FULL | T_RANGE | 994 | 11928 | 59 (4)| 00:00:01 | 1 | 7 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_CHAR(INTERNAL_FUNCTION("TEST_DATE"),'yyyy-MM-dd')='2014-04-01')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
272 consistent gets
0 physical reads
0 redo size
327 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0 0
- oracle 10g下范围分区扫描的几种方式
- oracle数据库分区的几种方式
- g++和vc6.0环境下 几种常见数据类型的最大范围问题
- 几种索引扫描方式的比较
- 几种索引扫描方式
- 命令行下Oracle数据库的几种OPEN方式研究
- Oracle 11g下自动创建分区
- 【索引】索引五种扫描方式至索引范围扫描
- Oracle表分区:范围分区
- Oracle 10g几种查找数据的方法
- oracle分区的四种方式
- oracle分区之包含模板的'范围-列表分区'例子
- Oracle表分区分为四种:范围分区,散列分区,列表分区和复合分区
- Oracle表分区分为四种:范围分区,散列分区,列表分区和复合分区
- Oracle表分区分为四种:范围分区,散列分区,列表分区和复合分区
- 10g升级11g OCM必考点:Oracle Database 11g的INTERVAL 分区
- Oracle 10g 有关表分区的问题?
- oracle的数据扫描方式
- 互联网金融-众筹平台的分类
- OpenCV绘图总结
- Dos窗口下的复制与粘贴
- 【Hibernate步步为营】--核心对象+持久对象全析(一)
- 5种实用App导航菜单设计方案
- oracle 10g下范围分区扫描的几种方式
- Dreamweaver CS6破解教程[序列号+破解补丁]
- 黑马程序员_java多线程
- Chrome开发,debug的使用方法。
- 互联网金融-互联网金融分析框架:从支付、投资、融资三个维度理解何谓“互联网金融”
- 用Iterator实现遍历集合
- listView中adapter有不同的click事件的简单写法
- 记__零基础学java的开始
- “注释”部分补充知识点: