- 浏览: 353175 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (176)
- struts2 (4)
- javascript (9)
- database (11)
- hibernate (11)
- java (36)
- CSS (1)
- wap (8)
- html (5)
- spring (3)
- 操作系统 (9)
- xml (0)
- eclipse/myeclipse (6)
- freemarker (2)
- jsp+servlet (4)
- fckeditor (1)
- Linux (12)
- tomcat (4)
- SNMP (2)
- Loadrunner (1)
- php (0)
- 版本控制 (1)
- android (3)
- pki (7)
- jquery (4)
- webservice (4)
- ipad (1)
- 开发工具 (1)
- jslt (1)
- 连接池 (6)
- javaApi (1)
- 学习笔记 (6)
- eclipse (2)
最新评论
-
宋建勇:
nice
keytool详解 -
1122334455aabb:
java int,byte、char之间的相互转化,更多内容参 ...
java中byte 与16进制字符相互转换 -
01jiangwei01:
CA[i >>> 18 & 0x3f ...
使用JAVA数字证书做数字签名认证 -
sjp524617477:
good
keytool详解 -
whlngn:
输出结果我只想取汉字,而不要后面的数字,应该如何实现,求回答
配置paoding analysis
摘要
:在大量业务数据处理的项目中,可以考虑使用分区表来提高应用系统的性能并方便数据管理,本文详细介绍了分区表的
使用。
在大型的企业应用或企业级的数据库应用中,要处理的数据量通常可以达到几十到几百GB,有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快,
但是仍然不能满足用户的需求,为了使用户的大量的数据在读写操作和查询中速度更快,Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性
能。
使用分区的优点:
·增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
·维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
·均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能;
·改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
Oracle数据库提供对表或索引的分区方法有三种:
·范围分区(range)
·Hash分区(散列分区)
·复合分区 (list)
下面将以实例的方式分别对这三种分区方法来说明分区表的使用。为了测试方便,我们先建三个表空间。 create tablespace
dinya_space01
datafile ’/test/demo/oracle/demodata/dinya01.dnf’ size 50M
create tablespace
dinya_space01
datafile ’/test/demo/oracle/demodata/dinya02.dnf’ size 50M
create tablespace dinya_space01
datafile ’/test/demo/oracle/demodata/dinya03.dnf’ size 50M
1.1. 分区表的创建
1.1.1. 范围分区
范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上。如根据序号分区,根据业务记录的创建日期进行分区等。
需求描述:有一个物料交易表,表名:material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这
时候我们可以使用序号分区三个区,每个区中预计存储三千万的数据,也可以使用日期分区,如每五年的数据存储在一个分区上。
根据交易记录的序号分区建表: SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,
5 item_description varchar2(300),
6 transaction_date date not null
7 )
8 partition by range
(transaction_id)
9 (
10 partition part_01 values less than
(30000000) tablespace
dinya_space01
,
11 partition part_02 values less than(60000000) tablespace
dinya_space02,
12 partition part_03 values less than(maxvalue) tablespace dinya_space03
13 );
Table created.
建表成功,根据交易的序号,交易ID在三千万以下的记录将存储在第一个表空间dinya_space01中,分区名为:par_01,在三千万到六千万之
间的记录存储在第二个表空间:
dinya_space02中,分区名为:par_02,而交易ID在六千万以上的记录存储在第三个表空间dinya_space03中,分区名为
par_03.
根据交易日期分区建表: SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,
5 item_description varchar2(300),
6 transaction_date date not null
7 )
8 partition by range (transaction_date)
9 (
10 partition part_01 values less
than(to_date(’2006-01-01’,’yyyy-mm-dd’))
tablespace dinya_space01,
11 partition part_02 values less
than(to_date(’2010-01-01’,’yyyy-mm-dd’))
tablespace dinya_space02,
12 partition part_03 values less than(maxvalue) tablespace dinya_space03
13 );
Table created.
这样我们就分别建了以交易序号和交易日期来分区的分区表。每次插入数据的时候,系统将根据指定的字段的值来自动将记录存储到制定的分区(表空间)中。
当然,我们还可以根据需求,使用两个字段的范围分布来分区,如partition by range ( transaction_id
,transaction_date), 分区条件中的值也做相应的改变,请读者自行测试。
1.1.2. Hash分区(散列分区)
散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。如将物料交易表的数据根据交易
ID散列地存放在指定的三个表空间中: SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,
5 item_description varchar2(300),
6 transaction_date date
7 )
8 partition by hash
(transaction_id)
9 (
10 partition part_01 tablespace dinya_space01,
11 partition part_02 tablespace dinya_space02,
12 partition part_03 tablespace dinya_space03
13 );
Table created.
建表成功,此时插入数据,系统将按transaction_id将记录散列地插入三个分区中,这里也就是三个不同的表空间中。
1.1.3. 复合分区
有时候我们需要根据范围分区后,每个分区内的数据再散列地分布在几个表空间中,这样我们就要使用复合分区。复合分区是先使用范围分区,然后在每个分区内再
使用散列分区的一种分区方法,如将物料交易的记录按时间分区,然后每个分区中的数据分三个子分区,将数据散列地存储在三个指定的表空间中: SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,
5 item_description varchar2(300),
6 transaction_date date
7 )
8 partition by range
(transaction_date)subpartition
by hash
(transaction_id)
9 subpartitions 3 store in
(dinya_space01,dinya_space02,dinya_space03)
10 (
11 partition part_01 values less
than(to_date(’2006-01-01’,’yyyy-mm-dd’)),
12 partition part_02 values less
than(to_date(’2010-01-01’,’yyyy-mm-dd’)),
13 partition part_03 values less than(maxvalue)
14 );
Table created.
该例中,先是根据交易日期进行范围分区,然后根据交易的ID将记录散列地存储在三个表空间中。
1.2. 分区表操作
以上了解了三种分区表的建表方法,下面将使用实际的数据并针对按日期的范围分区来测试分区表的数据记录的操作。
1.2.1. 插入记录: SQL> insert into dinya_test values(1,12,’BOOKS’,sysdate);
1 row created.
SQL> insert into dinya_test values(2,12, ’BOOKS’,sysdate+30);
1 row created.
SQL> insert into dinya_test values(3,12,
’BOOKS’,to_date(’2006-05-30’,’yyyy-mm-dd’));
1 row created.
SQL> insert into dinya_test values(4,12,
’BOOKS’,to_date(’2007-06-23’,’yyyy-mm-dd’));
1 row created.
SQL> insert into dinya_test values(5,12,
’BOOKS’,to_date(’2011-02-26’,’yyyy-mm-dd’));
1 row created.
SQL> insert into dinya_test values(6,12,
’BOOKS’,to_date(’2011-04-30’,’yyyy-mm-dd’));
1 row created.
SQL> commit;
Commit complete.
SQL>
按上面的建表结果,2006年前的数据将存储在第一个分区part_01上,而2006年到2010年的交易数据将存储在第二个分区part_02
上,2010年以后的记录存储在第三个分区part_03上。
1.2.2. 查询分区表记录: SQL> select * from dinya_test
partition(part_01);
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE
--------------------------------------------------------------------------------
1 12 BOOKS 2005-1-14 14:19:
2 12 BOOKS 2005-2-13 14:19:
SQL>
SQL> select * from dinya_test partition(part_02);
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE
--------------------------------------------------------------------------------
3 12 BOOKS 2006-5-30
4 12 BOOKS 2007-6-23
SQL>
SQL> select * from dinya_test partition(part_03);
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE
--------------------------------------------------------------------------------
5 12 BOOKS 2011-2-26
6 12 BOOKS 2011-4-30
SQL>
从查询的结果可以看出,插入的数据已经根据交易时间范围存储在不同的分区中。这里是指定了分区的查询,当然也可以不指定分区,直接执行select
* from dinya_test查询全部记录。
在也检索的数据量很大的时候,指定分区会大大提高检索速度。
1.2.3. 更新分区表的记录: SQL> update
dinya_test partition(part_01)
t set
t.item_description=’DESK’ where
t.transaction_id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL>
这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”,可以看到已经成功更新了一条记录。但是当更新的时
候指定了分区,而根据查询的记录不在该分区中时,将不会更新数据,请看下面的例子: SQL> update dinya_test partition(part_01) t set
t.item_description=’DESK’ where
t.transaction_id=6;
0 rows updated.
SQL> commit;
Commit complete.
SQL>
指定了在第一个分区中更新记录,但是条件中限制交易ID为6,而查询全表,交易ID为6的记录在第三个分区中,这样该条语句将不会更新记录。
1.2.4. 删除分区表记录: SQL> delete from
dinya_test partition(part_02)
t
where t.transaction_id=4;
1 row deleted.
SQL> commit;
Commit complete.
SQL>
上面例子删除了第二个分区part_02中的交易记录ID为4的一条记录,和更新数据相同,如果指定了分区,而条件中的数据又不在该分区中时,将不会删除
任何数据。
1.3. 分区表索引的使用:
分区表和一般表一样可以建立索引,分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。
1.3.1. 局部索引分区的建立: SQL> create index
dinya_idx_t on
dinya_test(item_id)
2 local
3 (
4 partition
idx_1 tablespace
dinya_space01,
5 partition idx_2 tablespace dinya_space02,
6 partition idx_3 tablespace dinya_space03
7 );
Index created.
SQL>
看查询的执行计划,从下面的执行计划可以看出,系统已经使用了索引: SQL> select * from dinya_test partition(part_01) t where
t.item_id=12;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=187)
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF ’DINYA_TEST’ (Cost=
2 Card=1 Bytes=187)
2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1
Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
334 bytes sent via SQL*Net to client
309 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
1.3.2. 全局索引分区的建立
全局索引建立时global 子句允许指定索引的范围值,这个范围值为索引字段的范围值: SQL> create index
dinya_idx_t on
dinya_test
(item_id)
2 global partition by range
(item_id)
3 (
4 partition idx_1 values less than (1000) tablespace dinya_space01,
5 partition idx_2 values less than (10000) tablespace dinya_space02,
6 partition idx_3 values less than (maxvalue) tablespace dinya_space03
7 );
Index created.
SQL>
本例中对表的item_id字段建立索引分区,当然也可以不指定索引分区名直接对整个表建立索引,如: SQL> create index dinya_idx_t on dinya_test(item_id);
Index created.
SQL>
同样的,对全局索引根据执行计划可以看出索引已经可以使用: SQL> select * from dinya_test t where t.item_id=12;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=561)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF ’DINYA_TEST’ (Cost
=2 Card=3 Bytes=561)
2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1
Card=3)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
309 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5 rows processed
SQL>
1.4. 分区表的维护:
了解了分区表的建立、索引的建立、表和索引的使用后,在应用的还要经常对分区进行维护和管理。日常维护和管理的内容包括:增加一个分区,合并一个分区及删
除分区等等。下面以范围分区为例说明增加、合并、删除分区的一般操作:
1.4.1. 增加一个分区: SQL> alter table
dinya_test
2 add partition
part_04 values less
than(to_date(’2012-01-01’,’yyyy-mm-dd’))
tablespace
dinya_spa
ce03;
Table altered.
SQL>
增加一个分区的时候,增加的分区的条件必须大于现有分区的最大值,否则系统将提示ORA-14074 partition bound must
collate higher than that of the last partition 错误。
1.4.2. 合并一个分区: SQL> alter table
dinya_test merge
partitions
part_01,part_02 into partition
part_02;
Table altered.
SQL>
在本例中将原有的表的part_01分区和part_02分区进行了合并,合并后的分区为part_02,如果在合并的时候把合并后的分区定为
part_01的时候,系统将提示ORA-14275 cannot reuse lower-bound partition as
resulting partition 错误。
1.4.3. 删除分区: SQL> alter table
dinya_test drop
partition
part_01;
Table altered.
SQL>
删除分区表的一个分区后,查询该表的数据时显示,该分区中的数据已全部丢失,所以执行删除分区动作时要慎重,确保先备份数据后再执行,或将分区合并。
未来发展方向
自从引入分区技术以来,Oracle
公司在每次推出重要版本时都会增加新的分区方法。Oracle8 引入了范围分区功能,Oracle8i
引入了散列和组合范围散列分区功能,Oracle9i 引入了列表分区功能。在最新版本 Oracle 数据库
10g
中,则增强了用于索引组织表和全局分区索引的分区策略,并且扩展了其用于所有分区维护操作的并发索引维护功能。Oracle
公司致力于不断完善分区技术,确保满足所有的业务需求。
1.5. 总结:
需要说明的是,本文在举例说名分区表事务操作的时候,都指定了分区,因为指定了分区,系统在执行的时候则只操作该分区的记录,提高了数据处理的速度。不要
指定分区直接操作数据也是可以的。在分区表上建索引及多索引的使用和非分区表一样。此外,因为在维护分区的时候可能对分区的索引会产生一定的影响,可能需
要在维护之后重建索引,相关内容请参考分区表索引部分的文档
发表评论
-
group by rollup
2011-05-19 15:10 1249首先引用ITPUB上的总结: rollup(a,b,c)-- ... -
oracle 树状查询
2011-02-15 16:11 1751oracle中的select语句可以用START WITH.. ... -
Oracle使用序列创建自增字段
2011-01-25 11:04 1388在Oracle中sequence就是所谓的序列号,每次取的时候 ... -
【字符集】处理Toad显示乱码及Windows XP下无法插入“某些汉字”问题
2010-11-01 13:14 2652在数据库服务器字符集 ... -
oracle 常用语句
2010-10-27 15:24 699查看 死锁 select username,lockw ... -
关于 db block gets,consistent gets,physical reads的概念
2010-08-17 16:57 1552首先来看下官方文档的解释 db block gets:Num ... -
oralce分区表和索引
2010-08-14 17:39 1328什么时候使用分区: 1、 大数据量的表,比如大于2G ... -
Oracle分区键与分区本地索引
2010-08-14 17:26 1939关于全局索引和本地索引的优缺点,分别应该在什么情况下使用,这方 ... -
oracle表分区详解
2010-08-14 17:19 747此文从以下几个方面来 ... -
mysql 数据类型java类型对照
2009-06-10 21:56 1710类型名称 显示长度 数据库类型 JAVA类型 JD ...
相关推荐
oracle创建分区表 在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。 以system身份登陆数据库,查看 v$option视图,如果其中Partition为TRUE,则支持分区 功能;否则不支持。Partition有...
分享一个自己学习和实践的关于Oracle表自动按月分区知识点,已经在项目上线并且有效的方案。
本文通过图表和实例的阐述在Oracle数据库创建新表时Storage的参数具体含义。
17.创建和使用分区的表 18.基于成本的优化器一般错误概念和问题 19.Delphi 3_0中连接数据库的三种方式 20.远程数据库的访问 21.监控数据库性能的SQL 22.简单实现数据库表空间的备份或迁移 23.简析REDO ...
在Oracle数据库中,如何查找,定位一张表最后一次的DML操作的时间呢? 方式有三种,不过都有一些局限性,下面简单的解析、总结一下。 1:使用ORA_ROWSCN伪列获取表最后的DML时间 ORA_ROWSCN伪列是Oracle 10g开始...
oracle自动建立表分区脚本 适合oracle数据库dba使用 包含详细的操作说明
该教程详细讲解了Oracle数据库中分区表的创建、维护、优化等方面,并提供了大量实例以帮助读者更好地理解和应用这些知识。此外,该教程还包含了丰富的图文资料和视频教程,让读者可以更加直观地了解分区表的相关概念...
虽然存储介质和数据处理技术的发展也很快,但是仍然不能满足用户的需求,为了使用户的大量的数据在读写操作和查询中速度更快,Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性能。
oracle数据库对象(普通表、分区表的创建和删除,索引、约束的创建和删除,表空间的创建、删除和扩展)和触发器、存储过程、job查看、表空间使用情况查询
1.6.1 利用 Oracle 安装程序创建数据库 1.6.2 使用安装程序创建数据库的注意 事项 1.6.3 怎样建立自己的定制数据库创建 脚本 1.6.4 如何从已有数据库克隆数据库 1.6.5 怎样利用 Database Configuration ...
深入学习oracle分区表,oracle10g支持三种分区表创建方式,创建分区表可以一定程度上提高你数据库的访问速度,关键要看你业务上频繁查询的关键字段做为分区关键字.
基于ORACLE数据库上的自动创建分区表存储过程。
该压缩包内包括:Oracle函数大全 .ppt Oracle基本体系结构.ppt oracle命令使用大全.doc oracle数据库安装.ppt ... 锁和表分区.ppt 涉及到oracle数据库的方方面面,实在是不可多得的oracle黄金资料
第3部分:模式对象,内容包括管理模式对象空间、管理表、管理索引、管理分区表和分区索引、管理簇、管理散列、管理视图、序列和同义词、模式对象的常规管理、检测和修复数据块损坏。第4部分:数据库的安全,介绍...
-- 第一次分区尽量在数据库闲时操作,这样更能保证分区表的数据一致性; -- 通常使用的现有大表的分区方法:A.使用RENAME分区 B.使用交换分区 C.使用联机重定义 只有C方案才能保证数据的完全一致性; -- 但是经过测试...
oracle 代码 下载 数据库分区,大家可以下载,谢谢大家支持
Browser对ORACLE数据库提供了大量图形界面,通过各种充实的功能向ORACLE的开发,维护人员提供非常专业的支持.优良的设计,使其特别适合于大型应用系统的开发,维护及性能优化,提高工作效率. 下面将介绍一些Object&...
你或许能看到所有的和数据库文件相关的数据都放在一个路径,或者分区里,所有的索引也是在一个路径下,同样系统和临时表空间也是如此。重做日志能够放在两个位置。你或许看到上百的表空间都有一到两个文件,你能看到...
目前所有使用Oracle作为数据库支撑平台的应用,大部分数据量比较庞大的系统,即表的数据量一般情况下都是在百万级以上的数据量。 当然在Oracle中创建分区是一种不错的选择