ORACLE范围分区如何创建 TR 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 基本概念:Oracle有两种表分区技术,即范围分区和HASH分区。范围分区主要用于把业务系统的历史数据和实时数据分开,并按照某个字段的值的范围,划分为物理上可以单独管理的小块。它有两个好处:第一,可以大大提高和保持数据库的运行性能,我们知道,一般的业务系统,随着时间的增长,数据不断增多,而系统的响应速度也不断下降,而采用范围分区技术,可以把业务系统的当前处理数据范围限定在一个固定的时间段里,如按年度划分,则每一年的业务经常处理数据不会随着时间的增长而积累增多,系统处理的数据量相对固定,因此,可以有效解决超大数据量的问题,另外,单独某个表分区如果数据已经是历史数据,则可以设置成只读表分区,可以大大提高查询效率,对于以前的系统,只有把数据分割存放在不同的表里面,才可以达到类似的效果,但不同的表给系统的设计带来很大的麻烦,特别是系统有可能会用到历史数据查询的情况;第二,可以提高数据的整体完整性,单独某个表分区对应的硬件故障被限制在该表分区,不会影响表的其它分区部分,系统管理员可以对某个分区进行单独地备份和恢复操作,表的其它分区还可以不受影响地供用户使用。HASH分区纯粹是用于提高访问性能的技术,按照散列算法,把数据分片到不同的表空间或数据区段,这不仅可以有效地同磁盘的散列技术结合到一起,从而平均I/O之外,最主要的就是可以充分利用多CPU的分区间和分区内并行,来提高性能。一般情况下,可以综合利用这两种分区技术。二 实现技术: 在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。 以system身份登陆数据库,查看 v$option视图,如果其中Partition为TRUE,则支持分区功能;否则不支持。Partition有基于范围、哈希、综和三种类型。我们用的比较多的是按范围分区的表。 我们以一个2001年开始使用的185的数据做例子讲述分区表的创建和使用:1 、以system 身份创建独立的表空间(大小可以根据数据量的多少而定)create tablespace cc_2000 datafile '/home/oradata/oradata/test/cc_2000.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);create tablespace cc_2001 datafile '/home/oradata/oradata/test/cc_2001.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);create tablespace cc_2002 datafile '/home/oradata/oradata/test/cc_2002.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);2 、用EXPORT工具把旧数据备份在t_call.dmp中把原来的t_call表改名alter table t_call rename to t_call_old;以callcenter 身份创建分区的表create table t_call(callidd number(16) primary key, sid varchar2(8), cid varchar2(8), …. stime date) partition by range (stime) (partition cc_2000 values less than (to_date('2001-01-01','yyyy-mm-dd')) tablespace cc_2000 storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0), partition cc_2001 values less than (to_date('2001-06-01','yyyy-mm-dd')) tablespace cc_2001 storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0), partition cc_2002 values less than (to_date('2002-12-31','yyyy-mm-dd')) tablespace cc_2002 storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) );(说明:分区的名称可以和表空间的名称不一致。这里是每半年做一个分区,当然也可以每个月做一个分区)3、IMPORT导入数据,参数ignore=y4、分区表的扩容:到了2002 年,建立新的表空间:create tablespace cc_2002_1 datafile '/home/oradata/oradata/test/cc_2002_1.dbf' size 50m default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);为表添加新分区和表空间:alter table t_call add partition cc_2002_1values less than (to_date('2002-06-31','yyyy-mm-dd')tablespace cc_2002_1storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);5、删除不必要的分区将2000年的数据备份(备份方法见 6、EXPORT 分区),将2000年的分区删除。alter table t_call drop partion cc_2000;删除物理文件%rm /home/oradata/oradata/test/cc_2000.dbf6、EXPORT 分区:% exp callcenter/callcenter_password tables=t_call:cc_2000 rows=Y file=cc_2000.dmp7、IMPORT分区:例如在2001 年,用户要查看2000 年的数据,先创建表空间create tablespace cc_2000 datafile '/home/oradata/oradata/test/cc_2000.dbf' size 50m default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);为表添加新分区和表空间:alter table t_call add partition cc_2000values less than (to_date('2001-01-01','yyyy-mm-dd')tablespace cc_2000storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);导入数据%imp callcenter/callcenter_password file=cc_2000.dmp tables=(guestbook:g_2000q4) ignore=y(说明:如果不指明导入的分区,imp会自动按分区定义的范围装载数据) CREATE TABLE ORDER_TRANSACTION ( ORD_NUMBER NUMBER(12), ORD_DATE DATE, PROD_ID VARCHAR2 (15), QUANTITY NUMBER (15,3))PARTITION BY RANGE (ORD_DATE) (PARTITION FY1999Q4 VALUES LESS THAN (TO_DATE(‘01012000’,‘MMDDYYYY’))TABLESPACE ORD_1999Q4,PARTITION FY2000Q1 VALUES LESS THAN (TO_DATE(‘04012000’,‘MMDDYYYY’))TABLESPACE ORD_2000Q1 STORAGE (INITIAL 500M NEXT 500M)INITRANS 2 PCTFREE 0,PARTITION FY2000Q2 VALUES LESS THAN (TO_DATE(‘07012000’,‘MMDDYYYY’))TABLESPACE ORD_2000Q2,PARTITION FY2000Q3 VALUES LESS THAN (TO_DATE(‘10012000’,‘MMDDYYYY’))TABLESPACE ORD_2000Q3 STORAGE (INITIAL 10M NEXT 10M))STORAGE (INITIAL 200M NEXT 200M PCTINCREASE 0 MAXEXTENTS 4096)NOLOGGING; 在oracle需要添加一个字段而且要是自动增长的 请求大家帮助解决oracle817的问题 怎么在存储过程里并行调用其他的存储过程? 请高手帮我写条SQL 在10g中怎么找JSP主页面? plsql中minus的問題 查询一个日期字段的SQL问题 java.lang.ClassCastException: oracle.sql.CLOB 固定时间统计产量的语句 如何将一个文本文件中的内容插入到数据表中? !!!高手求救:如何写根据时间触发的触发器。 oracle回滚段该如何实现?
Oracle有两种表分区技术,即范围分区和HASH分区。
范围分区主要用于把业务系统的历史数据和实时数据分开,并按照某个字段的值的范围,划分为物理上可以单独管理的小块。它有两个好处:第一,可以大大提高和保持数据库的运行性能,我们知道,一般的业务系统,随着时间的增长,数据不断增多,而系统的响应速度也不断下降,而采用范围分区技术,可以把业务系统的当前处理数据范围限定在一个固定的时间段里,如按年度划分,则每一年的业务经常处理数据不会随着时间的增长而积累增多,系统处理的数据量相对固定,因此,可以有效解决超大数据量的问题,另外,单独某个表分区如果数据已经是历史数据,则可以设置成只读表分区,可以大大提高查询效率,对于以前的系统,只有把数据分割存放在不同的表里面,才可以达到类似的效果,但不同的表给系统的设计带来很大的麻烦,特别是系统有可能会用到历史数据查询的情况;第二,可以提高数据的整体完整性,单独某个表分区对应的硬件故障被限制在该表分区,不会影响表的其它分区部分,系统管理员可以对某个分区进行单独地备份和恢复操作,表的其它分区还可以不受影响地供用户使用。
HASH分区纯粹是用于提高访问性能的技术,按照散列算法,把数据分片到不同的表空间或数据区段,这不仅可以有效地同磁盘的散列技术结合到一起,从而平均I/O之外,最主要的就是可以充分利用多CPU的分区间和分区内并行,来提高性能。
一般情况下,可以综合利用这两种分区技术。
二 实现技术:
在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。
以system身份登陆数据库,查看 v$option视图,如果其中Partition为TRUE,则支持分区功能;否则不支持。Partition有基于范围、哈希、综和三种类型。我们用的比较多的是按范围分区的表。
我们以一个2001年开始使用的185的数据做例子讲述分区表的创建和使用:
1 、以system 身份创建独立的表空间(大小可以根据数据量的多少而定)create tablespace cc_2000 datafile '/home/oradata/oradata/test/cc_2000.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);create tablespace cc_2001 datafile '/home/oradata/oradata/test/cc_2001.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);create tablespace cc_2002 datafile '/home/oradata/oradata/test/cc_2002.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);2 、用EXPORT工具把旧数据备份在t_call.dmp中
把原来的t_call表改名
alter table t_call rename to t_call_old;以callcenter 身份创建分区的表create table t_call(
callidd number(16) primary key,
sid varchar2(8),
cid varchar2(8),
….
stime date
)
partition by range (stime)
(partition cc_2000 values less than (to_date('2001-01-01','yyyy-mm-dd'))
tablespace cc_2000
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
partition cc_2001 values less than (to_date('2001-06-01','yyyy-mm-dd'))
tablespace cc_2001
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
partition cc_2002 values less than (to_date('2002-12-31','yyyy-mm-dd'))
tablespace cc_2002
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0)
);(说明:分区的名称可以和表空间的名称不一致。这里是每半年做一个分区,当然也可以每个月做一个分区)3、IMPORT导入数据,参数ignore=y4、分区表的扩容:到了2002 年,建立新的表空间:create tablespace cc_2002_1 datafile '/home/oradata/oradata/test/cc_2002_1.dbf' size 50m default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);为表添加新分区和表空间:alter table t_call add partition cc_2002_1
values less than (to_date('2002-06-31','yyyy-mm-dd')
tablespace cc_2002_1
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);5、删除不必要的分区将2000年的数据备份(备份方法见 6、EXPORT 分区),将2000年的分区删除。alter table t_call drop partion cc_2000;删除物理文件%rm /home/oradata/oradata/test/cc_2000.dbf6、EXPORT 分区:% exp callcenter/callcenter_password tables=t_call:cc_2000 rows=Y file=cc_2000.dmp7、IMPORT分区:例如在2001 年,用户要查看2000 年的数据,先创建表空间create tablespace cc_2000 datafile '/home/oradata/oradata/test/cc_2000.dbf' size 50m default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);为表添加新分区和表空间:alter table t_call add partition cc_2000
values less than (to_date('2001-01-01','yyyy-mm-dd')
tablespace cc_2000
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);导入数据%imp callcenter/callcenter_password file=cc_2000.dmp tables=(guestbook:g_2000q4) ignore=y(说明:如果不指明导入的分区,imp会自动按分区定义的范围装载数据)
ORD_NUMBER NUMBER(12),
ORD_DATE DATE,
PROD_ID VARCHAR2 (15),
QUANTITY NUMBER (15,3))
PARTITION BY RANGE (ORD_DATE)
(PARTITION FY1999Q4 VALUES LESS THAN
(TO_DATE(‘01012000’,‘MMDDYYYY’))
TABLESPACE ORD_1999Q4,
PARTITION FY2000Q1 VALUES LESS THAN
(TO_DATE(‘04012000’,‘MMDDYYYY’))
TABLESPACE ORD_2000Q1 STORAGE (INITIAL 500M NEXT 500M)
INITRANS 2 PCTFREE 0,
PARTITION FY2000Q2 VALUES LESS THAN
(TO_DATE(‘07012000’,‘MMDDYYYY’))
TABLESPACE ORD_2000Q2,
PARTITION FY2000Q3 VALUES LESS THAN
(TO_DATE(‘10012000’,‘MMDDYYYY’))
TABLESPACE ORD_2000Q3 STORAGE (INITIAL 10M NEXT 10M))
STORAGE (INITIAL 200M NEXT 200M PCTINCREASE 0 MAXEXTENTS 4096)
NOLOGGING;