如果把一张大表的数据分散到各个磁盘上做一个分区表是能增强它的i/o;
但是现在我们往往将几个磁盘做成一个raid,表已经分散到各个磁盘上了。从操作系统上来看,它是一整块磁盘,这个时候如果我们在上面将表做成分区表还有意义么?如果有,那么他将怎样提升哪一块儿的性能?
但是现在我们往往将几个磁盘做成一个raid,表已经分散到各个磁盘上了。从操作系统上来看,它是一整块磁盘,这个时候如果我们在上面将表做成分区表还有意义么?如果有,那么他将怎样提升哪一块儿的性能?
解决方案 »
- 请问上传数据的具体步骤//写下代码,谢谢
- ora-00904: 'right' invalid identifier
- 问一个group by 的问题
- 关于Oracle存储过程直接调用Corba组件的设想
- 救命!!SqlServer的Image类型与Oracle的Blob互相写入的问题
- 各位看看这道题该怎么做??
- windows2003安装ORacle10g失败~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- 怎样生成大量数据的问题?
- 各位,如何在PB中引用ORACLE的序列
- D2000与ORACLE问题!!!!中秋没有休息了!
- 诚聘网络远程家教
- 求linux下安装 oracle10g出错的解决方法
在你决定在哪个分区前,还需要判断数据是否在[a,b]之内。这和在索引的B树上走两个节点不会有太多的时间差别吧。
dvd_list_new:一张普通的表
dvd_list_part:一张按月分区的表(索引未分区)
两张表数据都一样,都是使用insert into .. select * from ....,这样防止碎片。
执行
select count(*) from dvd_list_part where time>'20060601000000' and time <'20060631000000';
结果 COUNT(*)
----------
2149656Elapsed: 00:00:01.11Execution Plan
----------------------------------------------------------
Plan hash value: 3756034816-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 7822 (2)| 00:01:34 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX FAST FULL SCAN| INDEX_TIME_PART | 2141K| 30M| 7822 (2)| 00:01:34 |
-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("TIME"<'20060631000000' AND "TIME">'20060601000000')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
35238 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed执行
select count(*) from dvd_list_new where time>'20060601000000' and time <'20060631000000';
结果
COUNT(*)
----------
2149656Elapsed: 00:00:00.82Execution Plan
----------------------------------------------------------
Plan hash value: 2838380510----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 5100 (2)| 00:01:02 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX FAST FULL SCAN| INDEX_TIME_NEW | 2126K| 30M| 5100 (2)| 00:01:02 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("TIME">'20060601000000' AND "TIME"<'20060631000000')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
24143 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到分区表的逻辑读('consistent gets' + 'db block gets')比未分区的多得多。这样只能得出一个结论,就是分区后要慢。
为什么?