Oracle建分区表
oracle表分区创建
oracle表分区创建⼀、什么是分区表表分区有以下优点：1、数据查询：数据被存储到多个⽂件上，减少了I/O负载，查询速度提⾼。
2、数据修剪：保存历史数据⾮常的理想。
3、备份：将⼤表的数据分成多个⽂件，⽅便备份和恢复。
4、并⾏性：可以同时向表中进⾏DML操作，并⾏性性能提⾼，均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
5、增强可⽤性：如果表的某个分区出现故障，表在其他分区的数据仍然可⽤；6、维护⽅便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；7、改善查询性能：对分区对象的查询可以仅搜索⾃⼰关⼼的分区，提⾼检索速度。
8、需要注意的是包含LONG、LONGRAW数据类型的表不能分区,如果表格⼤于2G需要考虑分区。
⼆、分区表的种类1、RANGE 范围分区说明：针对记录字段的值在某个范围。
规则：（1）、每⼀个分区都必须有⼀个VALUES LESS THEN⼦句，它指定了⼀个不包括在该分区中的上限值。
分区键的任何值等于或者⼤于这个上限值的记录都会被加⼊到下⼀个⾼⼀些的分区中。
（2）、所有分区，除了第⼀个，都会有⼀个隐式的下限值，这个值就是此分区的前⼀个分区的上限值。
（3）、在最⾼的分区中，MAXVALUE被定义。
MAXVALUE代表了⼀个不确定的值。
这个值⾼于其它分区中的任何分区键的值，也可以理解为⾼于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
若不添加maxvalue的分区插⼊数值⼀旦超过设置的最⼤上限会报错。
例⼀，按date范围创建分区表CREATE TABLE PART_TAB_CUSTOMER_BY_RANGE(CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,FIRST_NAME VARCHAR2(30) NOT NULL,LAST_NAME VARCHAR2(30) NOT NULL,PHONE VARCHAR2(15) NOT NULL,EMAIL VARCHAR2(80),SEX VARCHAR2(10),STATUS VARCHAR2(10),INSERT_DATE DATE)PARTITION BY RANGE (INSERT_DATE) --按时间分区(PARTITION DATE_RANGE1 VALUES LESS THAN (TO_DATE(' 2001-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data1,PARTITION DATE_RANGE2 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data2,PARTITION DATE_RANGE3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3)例⼆、按照number范围分区PARTITION BY RANGE (CUSTOMER_ID) --按id分区(PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE part_Data1,PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE part_Data2,PARTITION CUS_PART2 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3)2、LIST 列表分区说明：该分区的特点是某列的值只有有限个值，基于这样的特点我们可以采⽤列表分区。
oracle创建分区表语法
oracle创建分区表语法在Oracle中，分区表是一个物理数据库对象，它允许您将一个大表的数据分割成较小的、更易于管理的片段，称为分区。
每个分区可以在不同的表空间中存储，并且可以独立地备份和恢复。
这可以提高查询性能，备份和恢复的效率，以及管理大量数据的便利性。
创建分区表的语法如下：```sqlCREATE TABLE table_name (column1 datatype1,column2 datatype2,...)PARTITION BY partition_method (partition_name1 value_column1 value_range,partition_name2 value_column2 value_range,...);```这里是一些常见的分区方法：RANGE Partitioning：根据列的值范围对数据进行分区。
LIST Partitioning：根据列的离散值对数据进行分区。
HASH Partitioning：根据计算列的哈希值对数据进行分区。
KEY Partitioning：类似于 RANGE Partitioning，但使用索引的键值进行分区。
以下是一个创建RANGE分区表的例子：```sqlCREATE TABLE sales (order_id NUMBER,sale_date DATE,amount NUMBER)PARTITION BY RANGE (sale_date) (PARTITION p0 VALUES LESS THAN (TO_DATE('', 'YYYY-MM-DD')), PARTITION p1 VALUES LESS THAN (TO_DATE('', 'YYYY-MM-DD')),PARTITION p2 VALUES LESS THAN (MAXVALUE));```在这个例子中，`sales` 表根据 `sale_date` 列的值被分成了三个分区：p0、p1 和 p2。
oracle创建分区表
oracle创建分区表创建分区create table pt_range_list_test(pid number(10),pname varchar2(30),sex varchar2(10),create_date date) partition by range(create_date)subpartition by list(sex)(partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')) tablespace tetstbs1(subpartition sub1p1 values('MAN') tablespace tetstbs1,subpartition sub2p1 values('WOMAN') tablespace tetstbs1,subpartition sub3p1 values(default) tablespace tetstbs1),partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD')) tablespace tetstbs2(subpartition sub1p2 values('MAN') tablespace tetstbs2,subpartition sub2p2 values('WOMAN') tablespace tetstbs2,subpartition sub3p2 values(default) tablespace tetstbs2),partition p3 values less than(maxvalue) tablespace tetstbs3(subpartition sub1p3 values('MAN') tablespace tetstbs3,subpartition sub2p3 values('WOMAN') tablespace tetstbs3,subpartition sub3p3 values(default) tablespace tetstbs3)) enable row movement;局部索引-- 创建测试分区表create table local_index_example(id number(2),name varchar2(50),sex varchar2(10))partition by range (id)(partition part_1 values less than (5),partition part_2 values less than (10))--创建局部前缀索引；分区键（id）作为索引定义的第⼀列create index local_prefixed_index on local_index_example (id, name) local;--创建局部⾮前缀索引；分区键未作为索引定义的第⼀列create index local_nonprefixed_index on local_index_example (name, id) local;注意：判断局部索引是前缀还是⾮前缀的只需要看分区键是否作为索引定义的第⼀列①： select … from local_index_example where id = :id and name = :name;②： select … from local_index_example where name = :name;对于以上两个查询来说，如果查询第⼀步是⾛索引的话，则：局部前缀索引 local_prefixed_index 只对①有⽤；局部⾮前缀索引 local_nonprefixed_index 则对①和②均有⽤；如果你有多个类似①和②的查询的话，则可以考虑建⽴局部⾮前缀索引；如果平常多使⽤查询①的话，则可以考虑建⽴局部前缀索引；总之，重点是你要尽可能保证查询包含的谓词允许索引分区消除全剧索引对于全局分区索引来说，索引的实际分区数可能不同于表的分区数量；全局索引的分区机制有别于底层表，例如表可以按 done_date 列划分为10个分区，表上的⼀个全局索引可以按 id 列划分为5个分区。
oracle数据库表分区方法
oracle数据库表分区方法【原创实用版4篇】篇1 目录1.Oracle 数据库表分区的概念和理解2.Oracle 数据库表分区的操作方法3.Oracle 数据库表分区的优势和应用场景4.总结篇1正文一、Oracle 数据库表分区的概念和理解Oracle 数据库表分区是一种将大表按照一定规则划分为多个小表的方法，这样可以提高查询效率和数据管理方便性。
在 Oracle 数据库中，表空间是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中。
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。
二、Oracle 数据库表分区的操作方法1.范围分区：根据数据范围进行分区，例如按照年份、季度等时间属性进行分区。
2.列表分区：根据数据中的某一列的值进行分区，例如按照地区、产品类型等进行分区。
3.复合分区：结合范围分区和列表分区，根据多个属性进行分区。
具体的分区操作方法如下：1.创建分区表：使用 CREATE TABLE 语句创建一个分区表，例如：```CREATE TABLE sales (invoiceno NUMBER,number NUMBER,saledate DATE,not NULL)PARTITION BY RANGE (saledate);```2.添加分区：如果需要对已有的分区表添加新的分区，可以使用ALTER TABLE 语句，例如：```ALTER TABLE salesADD PARTITION (PARTITION sales_p2 VALUES LESS THAN(TO_DATE("2000-01-01", "YYYY-MM-DD")));```3.删除分区：如果需要删除分区表中的某个分区，可以使用 ALTER TABLE 语句，例如：```ALTER TABLE salesDROP PARTITION sales_p1;```三、Oracle 数据库表分区的优势和应用场景1.提高查询效率：分区表可以将大表划分为多个小表，这样可以减少查询时的 I/O 操作，提高查询效率。
Oracle分区表和索引的创建与管理
Oracle分区表和索引的创建与管理今天用到了Oracle表的分区，就顺便写几个例子把这个表的分区说一说：一、创建分区表1、范围分区根据数据表字段值的范围进行分区举个例子，根据学生的不同分数对分数表进行分区，创建一个分区表如下：create table range_fraction（id number（8），name varchar2（20），fraction number（3），grade number（2））partition by range（fraction）（partition fraction_60 values less than（60）， --不及格partition fraction_80 values less than（85）， --及格partition fraction_100 values less than（maxvalue） --优秀）创建完分区表后向表中添加一些数据：declarename varchar2（10）；fraction number（5）；grade number（5）；i number（8）：=1;beginfor i in 1100000 LOOPSELECT CHR （ROUND （DBMS_RANDOM.VALUE （97, 122））） INTO NAME FROM DUAL;SELECT ABS（MOD（DBMS_RANDOM.RANDOM,101）） into fraction FROM DUAL;SELECT ABS（MOD（DBMS_RANDOM.RANDOM,10））+1 into grade FROM DUAL;insert into range_fraction values（seq_range_fraction.nextval ,name,fraction,grade）；END LOOP;end;查询分区表：--分别查询所有的，不及格的，中等的，优秀的成绩select * from range_fraction;select * from range_fraction partition（fraction_60） ;select * from range_fraction partition（fraction_80） ;select * from range_fraction partition（fraction_100） ;当我们的查询语句不指定分区的时候，如果分区字段出现在where条件之后，Oracle会自动根据字段值的范围扫描响应的分区：select * from range_fraction where fraction<30; 这句SQL执行的时候只会扫描不及格的分区select * from range_fraction where fraction<80; 这句SQL执行的时候会扫描不及格和中等两个分区2、散列分区在范围分区中，分区字段的连续值通常出现在一个分区内，而在散列分区中，连续的字段值不一定存储在相同的分区中。
oracle普通表转化为分区表的方法
一、引言在数据库管理系统中，分区表是一种将表中数据按照特定规则分割存储的技术，可以提高数据的查询和管理效率。
而对于现有的普通表，如何将其转化为分区表是数据库管理员经常面临的问题之一。
在Oracle数据库中，有多种方法可以实现将普通表转化为分区表，本文将对其中的一些常用方法进行介绍和分析。
二、创建分区表的基本步骤1. 设计分区键：分区键是决定表数据如何分割存储的关键因素，可以是按照时间、地区、业务类型等规则进行分割。
在设计分区键时，需要考虑到数据的查询频率、增长趋势以及分区之间的平衡性。
2. 创建分区表：通过创建新的表结构，并按照设计好的分区键进行分割，可以将普通表转化为分区表。
在创建分区表时，需要考虑到分区类型（范围分区、列表分区、哈希分区等）、分区键的数据类型和约束条件等因素。
3. 数据迁移：将原有普通表中的数据迁移至新创建的分区表中，在数据迁移过程中需要考虑到数据的一致性和完整性，可以通过Oracle内置的数据迁移工具或者自定义的数据迁移脚本来实现。
4. 更新应用程序：由于原有的普通表与新创建的分区表结构不同，需要对应用程序进行相应的更新和调整，以适配新的数据存储结构。
三、利用ALTER TABLE语句进行分区表转化1. 使用ALTER TABLE ... MOVE PARTITION语句：该语句可以将整个分区的数据移动至新创建的分区表中，并可以同时对数据进行重分布和整理。
这种方法适用于数据量较小的表，操作简单方便。
2. 使用ALTER TABLE ... SPLIT PARTITION语句：如果原有的普通表结构已经满足分区表的要求，可以通过该语句将原有表中的数据按照分区键进行分割，并将其转化为分区表。
四、利用DBMS_REDEFINITION包进行分区表转化1. 使用DBMS_REDEFINITION.START_REDEF_TABLE过程：通过该过程可以启动对指定表的在线重定义操作，包括表结构、数据进行迁移等。
创建ORACLE列表分区表
partition sale3 values (2001) tablespace ts3,
partition sale4 values (2002) tablespace ts4,
partition sale5 values (null) tablespace ts5,
PARTITION iSALE4 tablespace tsi4,
PARTITION iSALE5 tablespace tsi5)
;
insert into sales2 values(1999,01,01,1);
--ORA-14400: 插入的分区关键字未映射到任何分区
insert into sales2 values(2004,01,01,1);
partition sale5 values less than(MAXVALUE) tablespace tsi5
);
--第三种索引
drop index idx_sales2;
--ORA-14024: LOCAL 索引的分区数必须等于基本表的分区数(索引分区名可以与表分区名不一致，不过索引数据怎么存储呢？）
partition sale6 values (default) tablespace ts6
);
--第一种索引
drop index idx_sales2;
create index idx_sales2 on sales2(year);
--第二种索引
drop index idx_sales2;
drop table sales2;
oracle按时间创建分区表
oracle 按时间创建分区表首先明确分区表和表分区的区别：表分区是一种思想，分区表示一种技术实现。
当表的大小过G 的时候可以考虑进行表分区，提高查询效率，均衡IO 。
oracle 分区表是oracle 数据库提供的一种表分区的实现形式。
表进行分区后，逻辑上仍然是一张表，原来的查询SQL 同样生效，同时可以采用使用分区查询来优化SQL 查询效率，不至于每次都扫描整个表一、分区表基本操作1、按时间分区表创建：1 2 3 4 5 6 7 8 9 10 11 create table t_test (pk_id number(30) not null,add_date_time DATE,constraintPK_T_TEST primary key (pk_id))PARTITION BY RANGE (add_date_time)(PARTITIONt_test_2013_less VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,PARTITIONt_test_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,PARTITION t_test_2014VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))TABLESPACE TS_MISPS)其中add_date_time 为分区字段，每一年一个分区。
插入100W 数据1 2 3 4 5 6 7 declarei int := 1;yearVARCHAR2(20);beginloopyear := CASEmod(i, 3)WHEN 0 THEN8 9 10 11 12 13 14 15 16 17 18 '2012-01-14 12:00:00'WHEN 1 THEN'2013-01-14 12:00:00'ELSE'2014-01-14 12:00:00'END;insert into t_test values(i, to_date(year, 'yyyy-mm-dd hh24:mi:ss'));exit when i= 1000000;i := i + 1;end loop;end;查看分区表的分区的详细信息1 Select table_name,partition_name,high_value fromdba_tab_partitions where table_name='T_TEST';2、分区表修改2.1增加一个分区分两种情况：1.没有maxvalue 分区。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。
以system身份登陆数据库，查看v$option视图，如果其中Partition为TRUE，则支持分区功能；否则不支持。
Partition有基于范围、哈希、综和三种类型。
我们用的比较多的是按范围分区的表。
在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。
以system身份登陆数据库，查看v$option视图，如果其中Partition为TRUE，则支持分区功能；否则不支持。
Partition有基于范围、哈希、综和三种类型。
我们用的比较多的是按范围分区的表。
我们以一个2001年开始使用的留言版做例子讲述分区表的创建和使用：
1 、以system 身份创建独立的表空间(大小可以根据数据量的多少而定) create tablespace g_2000q4 datafile
'/home/oradata/oradata/test/g_2000q4.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);
create tablespace g_2001q1 datafile
'/home/oradata/oradata/test/g_2001q1.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);
create tablespace g_2001q2 datafile
'/home/oradata/oradata/test/g_2001q2.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);
2 、用EXPORT工具把旧数据备份在guestbook.dmp中
把原来的guestbook表改名
alter table guestbook rename to guestbookold;
以guestbook 身份创建分区的表
create table guestbook(
id number(16) primary key,
username varchar2(64),
sex varchar2(2),
email varchar2(256),
expression varchar2(128),
content varchar2(4000),
time date,
ip varchar2(64)
)
partition by range (time)
(partition g_2000q4 values less than
(to_date('2001-01-01','yyyy-mm-dd'))
tablespace g_2000q4
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
partition g_2001q1 values less than
(to_date('2001-04-01','yyyy-mm-dd'))
tablespace g_2001q1
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
partition g_2001q2 values less than
(to_date('2001-07-01','yyyy-mm-dd'))
tablespace g_2001q2
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0)
);
(说明：分区的名称可以和表空间的名称不一致。
这里是每个季度做一个分区，当然也可以每个月做一个分区)
3、IMPORT导入数据，参数ignore=y
4、分区表的扩容：
到了2001 年下半年，建立新的表空间：
create tablespace g_2001q3 datafile
'/home/oradata/oradata/test/g_2001q3.dbf' size 50m default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);
为表添加新分区和表空间：
alter table guestbook add partition g_2001q3
values less than (to_date('2001-10-01','yyyy-mm-dd')
tablespace g_2001q3
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);
5、删除不必要的分区
将2000年的数据备份（备份方法见6、EXPORT 分区），将2000年的分区删除。
alter table guestbook drop partion g_2000q4;
删除物理文件
%rm /home/oradata/oradata/test/g_2000q4.dbf
6、EXPORT 分区：
% exp guestbook/guestbook_password tables=guestbook:g_2000q4 rows=Y file=g_2000q4.dmp
7、IMPORT分区：
例如在2001 年，用户要查看2000 年的数据，先创建表空间
create tablespace g_2000q4 datafile
'/home/oradata/oradata/test/g_2000q4.dbf' size 50m default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);
为表添加新分区和表空间：
alter table guestbook add partition g_2000q4
values less than (to_date('2001-01-01','yyyy-mm-dd')
tablespace g_2001q3
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);
导入数据
%imp guestbook/guestbook_password file=g_2000q4.dmp
tables=(guestbook:g_2000q4) ignore=y
(说明：如果不指明导入的分区，imp会自动按分区定义的范围装载数据)。