一、使用分区的优点:
1、增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
2、维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
3、均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能;
4、改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
二、Oracle数据库提供对表或索引的分区方法有几种(收集到四种):
1、范围分区
2、列表分区
3、Hash分区(散列分区)
4、复合分区
三、详描述分区实例:
1)下面将以实例的方式分别对这三种分区方法来说明分区表的使用。为了测试方便,我们先建三个表空间。
- create tablespace dinya_space01 datafile 'C:\表空间\dinya01.dbf' SIZE 5M;
- create tablespace dinya_space02 datafile 'C:\表空间\dinya02.dbf' SIZE 5M;
- create tablespace dinya_space03 datafile 'C:\表空间\dinya03.dbf' SIZE 5M;
- select * from user_tablespaces
------------------------------------------范围分区
范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上。
如根据序号分区,根据业务记录的创建日期进行分区等。
需求描述:有一个物料交易表,表名:material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。
这时候我们可以使用序号分区三个区,每个区中预计存储三千万的数据,也可以使用日期分区,如每五年的数据存储在一个分区上。
根据交易记录的序号分区建表:
- create table dinya_test
- (
- transaction_id number primary key,
- item_id number(8) not null,
- item_description varchar2(300),
- transaction_date date not null
- )
- partition by range (transaction_id)
- (
- partition part_01 values less than(2) tablespace dinya_space01,-----2条以下的交易在此分区上:part_01
- partition part_02 values less than(3) tablespace dinya_space02,-----等于+大于2而小于3的交易在此分区:part_02
- partition part_03 values less than(maxvalue) tablespace dinya_space03----大于3的交易在此分区:part_03
- )
根据交易日期分区建表:
- create table dinya_test
- (
- transaction_id number primary key,
- item_id number(8) not null,
- item_description varchar2(300),
- transaction_date date not null
- )
- partition by range (transaction_date)
- (
- partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dinya_space01,
- partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace dinya_space02,
- partition part_03 values less than(maxvalue) tablespace dinya_space03
- )
这样我们就分别建了以交易序号和交易日期来分区的分区表。
每次插入数据的时候,系统将根据指定的字段的值来自动将记录存储到制定的分区(表空间)中。
当然,我们还可以根据需求,使用两个字段的范围分布来分区,如partition by range ( transaction_id ,transaction_date),分区条件中的值也做相应的改变,请读者自行测试。
向表添加测试数据:
insert into dinya_test values(1,12,'BOOKS',sysdate);
insert into dinya_test values(2,12, 'BOOKS',sysdate+30);
insert into dinya_test values(3,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));
insert into dinya_test values(4,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));
insert into dinya_test values(5,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));
insert into dinya_test values(6,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));
查询全表数据
- select * from dinya_test;
查询分区的数据
- select * from dinya_test partition(part_01);
- select * from dinya_test partition(part_02);
- select * from dinya_test partition(part_03);
修改part_01分区的数据
- update dinya_test partition(part_01) t set t.item_description='DESK' where t.transaction_id=1;
删除part_03分区的数据
- delete from dinya_test partition(part_03) t where t.transaction_id=4;
创建索引
1)局部本地分区索引的创建:
- create index dinya_idx_t on dinya_test(item_id)
- local
- (
- partition idx_1 tablespace dinya_space01,---分区名为:idx_1
- partition idx_2 tablespace dinya_space02, ---分区名为:idx_2
- partition idx_3 tablespace dinya_space03---分区名为:idx_3
- );
注:
- select *from ALL_TAB_PARTITIONS where table_name ='DINYA_TEST'
- select *From dba_ind_partitions where partition_name='IDX_1'
2)全局分区索引的创建:
全局索引建立时global 子句允许指定索引的范围值,这个范围值为索引字段的范围值:
- create index dinya_idx_t on dinya_test(item_id)
- global partition by range(item_id)
- (
- partition idx_1 values less than (1000) tablespace dinya_space01,
- partition idx_2 values less than (10000) tablespace dinya_space02,
- partition idx_3 values less than (maxvalue) tablespace dinya_space03
- );
3)整个表创建索引:
- Create index dinya_idx_t on dinya_test(item_id);
- 备注: select *from all_indexes(dba_indexes、all_ind_columns 、user_ind_columns 、 dba_ind_columns)
------------------------------------------Hash分区(散列分区)
散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中:
- create table dinya_test
- (
- transaction_id number primary key,
- item_id number(8) not null,
- item_description varchar2(300),
- transaction_date date
- )
- partition by hash(transaction_id)
- (
- partition part_01 tablespace dinya_space01,
- partition part_02 tablespace dinya_space02,
- partition part_03 tablespace dinya_space03
- );
建表成功,此时插入数据,系统将按transaction_id将记录散列地插入三个分区中,这里也就是三个不同的表空间中。
------------------------------------------列表分区:
该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。
示例1:
- CREATE TABLE PROBLEM_TICKETS
- (
- PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY,
- DESCRIPTION VARCHAR2(2000),
- CUSTOMER_ID NUMBER(7) NOT NULL,
- DATE_ENTERED DATE NOT NULL,
- STATUS VARCHAR2(20)
- )
- PARTITION BY LIST (STATUS)
- (
- PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01,
- PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02
- )
备注:active和inactive是列status的值!谨记与range和hash分区的区别;
测试如下:
- insert into PROBLEM_TICKETS values(1,'BOOKS',1,sysdate,'ACTIVE');
- insert into PROBLEM_TICKETS values(2,'son',2,sysdate+30,'INACTIVE');
- insert into PROBLEM_TICKETS values(3,'son',3,to_date('2006-05-30','yyyy-mm-dd'),'INACTIVE');
- insert into PROBLEM_TICKETS values(4,'BOOKS',4,to_date('2007-06-23','yyyy-mm-dd'),'INACTIVE');
- insert into PROBLEM_TICKETS values(5,'old',5,to_date('2011-02-26','yyyy-mm-dd'),'ACTIVE');
- insert intoPROBLEM_TICKETSvalues(6,'test',6,to_date('2011-04-30','yyyy-mm-dd'),'INACTIVE');
- select * from PROBLEM_TICKETS
当然,也可以直接就删除表也行,刚所有的全删除,但是表空间文件还在!
------------------------------------------复合分区
有时候我们需要根据范围分区后,每个分区内的数据再散列地分布在几个表空间中,这样我们就要使用复合分区。复合分区是先使用范围分区,然后在每个分区内再使用散列分区的一种分区方法,如将物料交易的记录按时间分区,然后每个分区中的数据分三个子分区,将数据散列地存储在三个指定的表空间中:
- create table dinya_test
- (
- transaction_id number primary key,
- item_id number(8) not null,
- item_description varchar2(300),
- transaction_date date
- )
- partition by range(transaction_date)subpartition by hash(transaction_id)
- subpartitions 3 store in (dinya_space07,dinya_space08,dinya_space09)
- (
- partition part_07 values less than(to_date('2006-01-01','yyyy-mm-dd')),
- partition part_08 values less than(to_date('2010-01-01','yyyy-mm-dd')),
- partition part_09 values less than(maxvalue)
- );
测试如下:
- select *From user_tab_partitions where table_name=upper('dinya_test')
- selec *From user_tab_subpartitions where table_name=upper('dinya_test')
插入如下数据:
- insert into dinya_test values(1,12,'BOOKS',sysdate);
- insert into dinya_test values(2,12, 'BOOKS',sysdate+30);
- insert into dinya_test values(3,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));
- insert into dinya_test values(7,12, 'BOOKS',to_date('2005-05-30','yyyy-mm-dd'));
- insert into dinya_test values(4,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));
- insert into dinya_test values(5,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));
- insert into dinya_test values(6,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));
select *From dinya_test
select *From dinya_test partition(part_07)
select *From dinya_test partition(part_09)
参照下图,按所显的子分区名,看能否查出数据:
select*Fromuser_tab_subpartitions where table_name=upper('dinya_test')
select *From dinya_test subpartition(SYS_SUBP62)
备注: 该例中,先是根据交易日期进行范围分区,然后根据交易的ID将记录散列地存储在三个表空间中。
------------------------------------------复合范围列表分区
这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区,分区之中的分区被称为子分区。
示例1:
- Create table sales
- (
- Product_id varchar2(5),
- Sales_date date,
- Sales_cost number(10),
- Status varchar2(20)
- )
- Partition by range(Sales_cost)
- Subpartition by list(status)
- (
- Partition p1 values less than (1) tablespace dinya_space01
- (
- Subpartition p1sub1 values('ACTIVE') tablespace dinya_space03,
- Subpartition p1sub2 values('INACTIVE') tablespace dinya_space04
- ),
- Partition p2 values less than (3) tablespace dinya_space02
- (
- Subpartition p1sub3 values('ACTIVE') tablespace dinya_space05,
- Subpartition p1sub4 values('INACTIVE') tablespace dinya_space06
- )
- )
- insert into sales values(1,sysdate,0.1,'ACTIVE');
- insert into sales values(2,sysdate+30,1,'INACTIVE');
- insert into sales values(3,to_date('2006-05-30','yyyy-mm-dd'),2,'INACTIVE');
- select * From sales:
- Select * from sales partition(p2)
- SELECT * FROM SALES SUBPARTITION(p1sub4)
- SELECT * FROM SALES SUBPARTITION(p1sub3)
- select * From dba_tab_subpartitions where table_name='SALES'
有关表分区的一些维护性操作:
一、添加分区
- 以下代码给SALES表添加了一个P3分区
- ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
- 注意:以上添加的分区界限应该高于最后一个分区界限。
- 以下代码给SALES表的P3分区添加了一个P3SUB1子分区
- ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
二、删除分区
- 以下代码删除了P3表分区:
- ALTER TABLE SALES DROP PARTITION P3;
- 在测试中遇到这样的情况。如果表创建了分区,如果要删除数据文件(表空间文件),则要先删除分区,然后才能删除数据文件(但是在删除数据文件时,必须要保留一个分区才能最终删除数据文件>表空间文件,)
- 当然,也可以直接就删除表也行,刚所有的全删除,但是表空间文件还在!
- 在以下代码删除了P4SUB1子分区:
- ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
- 注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。
三、截断分区
- 截断某个分区是指删除某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。当表中即使只有一个分区时,也可以截断该分区。通过以下代码截断分区:
- ALTER TABLE SALES TRUNCATE PARTITION P2;
- 通过以下代码截断子分区:
- ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
四、合并分区
- 合并分区是将相邻的分区合并成一个分区,结果分区将采用较高分区的界限,值得注意的是,不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并:
- ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
五、拆分分区
- 拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
- ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD'))
- INTO (PARTITION P21,PARTITION P22);
六、接合分区(coalesca)
- 结合分区是将散列分区中的数据接合到其它分区中,当散列分区中的数据比较大时,可以增加散列分区,然后进行接合,值得注意的是,接合分区只能用于散列分区中。通过以下代码进行接合分区:
- ALTER TABLE SALES COALESCA PARTITION;
七、重命名表分区
- 以下代码将P21更改为P2
- ALTER TABLE SALES RENAME PARTITION P21 TO P2;
九、跨分区查询
- select sum( *) from (
- (select count(*) cn from t_table_SS PARTITION (P200709_1)
- union all
- select count(*) cn from t_table_SS PARTITION (P200709_2));
十、查询表上有多少分区
- SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'
- --显示数据库所有分区表的信息:
- select * from DBA_PART_TABLES where table_name=upper('dinya_test')
- --显示当前用户可访问的所有分区表信息:
- select * from ALL_PART_TABLES
- --显示当前用户所有分区表的信息:
- select * from USER_PART_TABLES
- --显示表分区信息 显示数据库所有分区表的详细分区信息:
- select * from DBA_TAB_PARTITIONS
- --显示当前用户可访问的所有分区表的详细分区信息:
- select * from ALL_TAB_PARTITIONS
- --显示当前用户所有分区表的详细分区信息:
- select * from USER_TAB_PARTITIONS
- --显示子分区信息 显示数据库所有组合分区表的子分区信息:
- select * from DBA_TAB_SUBPARTITIONS
- --显示当前用户可访问的所有组合分区表的子分区信息:
- select * from ALL_TAB_SUBPARTITIONS
- --显示当前用户所有组合分区表的子分区信息:
- select * from USER_TAB_SUBPARTITIONS
- --显示分区列 显示数据库所有分区表的分区列信息:
- select * from DBA_PART_KEY_COLUMNS
- --显示当前用户可访问的所有分区表的分区列信息:
- select * from ALL_PART_KEY_COLUMNS
- --显示当前用户所有分区表的分区列信息:
- select * from USER_PART_KEY_COLUMNS
- --显示子分区列 显示数据库所有分区表的子分区列信息:
- select * from DBA_SUBPART_KEY_COLUMNS
- --显示当前用户可访问的所有分区表的子分区列信息:
- select * from ALL_SUBPART_KEY_COLUMNS
- --显示当前用户所有分区表的子分区列信息:
- select * from USER_SUBPART_KEY_COLUMNS
- --怎样查询出oracle数据库中所有的的分区表
- select * from user_tables a where a.partitioned='YES'
- --删除一个表的数据是
- truncate table table_name;
- --删除分区表一个分区的数据是
- alter table table_name truncate partition p5;
注:分区根据具体情况选择。
表分区有以下优点:
1、数据查询:数据被存储到多个文件上,减少了I/O负载,查询速度提高。
2、数据修剪:保存历史数据非常的理想。
3、备份:将大表的数据分成多个文件,方便备份和恢复。
4、并行性:可以同时向表中进行DML操作,并行性性能提高。
================================================
索引:
1、一般索引:
- create index index_name on table(col_name);
2、Oracle 分区索引详解
语法:Table Index
- CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
- ON [schema.]table_name [tbl_alias]
- (col [ASC | DESC]) index_clause index_attribs
index_clauses:
分以下两种情况
1. Local Index
就是索引信息的存放位置依赖于父表的Partition信息,换句话说创建这样的索引必须保证父表是Partition
1.1 索引信息存放在父表的分区所在的表空间。但是仅可以创建在父表为HashTable或者composite分区表的。
- LOCAL STORE IN (tablespace)
1.2 仅可以创建在父表为HashTable或者composite分区表的。并且指定的分区数目要与父表的分区数目要一致
- LOCAL STORE IN (tablespace) (PARTITION [partition [LOGGING|NOLOGGING] [TABLESPACE {tablespace|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] [STORE IN {tablespace_name|DEFAULT] [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])
1.3 索引信息存放在父表的分区所在的表空间,这种语法最简单,也是最常用的分区索引创建方式。
Local
1.4 并且指定的Partition 数目要与父表的Partition要一致
- LOCAL (PARTITION [partition
- [LOGGING|NOLOGGING]
- [TABLESPACE {tablespace|DEFAULT}]
- [PCTFREE int]
- [PCTUSED int]
- [INITRANS int]
- [MAXTRANS int]
- [STORAGE storage_clause]
- [STORE IN {tablespace_name|DEFAULT]
- [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])
Global Index
索引信息的存放位置与父表的Partition信息完全不相干。甚至父表是不是分区表都无所谓的。
语法如下:
- GLOBAL PARTITION BY RANGE (col_list)
- ( PARTITION partition VALUES LESS THAN (value_list)
- [LOGGING|NOLOGGING]
- [TABLESPACE {tablespace|DEFAULT}]
- [PCTFREE int]
- [PCTUSED int]
- [INITRANS int]
- [MAXTRANS int]
- [STORAGE storage_clause] )
- 但是在这种情况下,如果父表是分区表,要删除父表的一个分区都必须要更新Global Index ,否则索引信息不正确
- ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes
--查询索引
- select object_name,object_type,tablespace_name,sum(value)
- from v$segment_statistics
- where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
- group by object_name,object_type,tablespace_name
- order by 4 desc
http://keepwork.iteye.com/blog/1949489
相关推荐
Hash分区是Oracle实现表分区的三种基本分区方式之一。对于那些无法有效划分分区范围的大表,或者出于某些特殊考虑的设计,需要使用Hash分区,下面介绍使用方法
总结描述Oracle 11g分区表的种类及分区索引的类型。范围分区,列表分区,散列分区,组合分区,哈希分区,全局索引,分区索引
包括表分区方法、索引分区方法。 范围分区(range partitioning); 哈希分区(hash partitioning); 列表分区(list partitioning); 范围-哈希组合分区(composite range-hash partitioning); 范围-列表组合...
Range分区:Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中。 如按照时间划分,2010年1月的数据放到a分区,2月的数据放到b分区,在创建的...
表可以按range、hash、list分区,表分区后,其上的索引和普通表上的索引有所不同,oracle对于分区表上的索引分为2类,即局部索引和全局索引,下面分别对这2种索引的特点和局限性做个总结。局部索引local index1.局部...
主要是对oracle分区技术的介绍,有范围分区、hash分区 列表分区、复合分区。
日期类型 date 7字节 用于存储表中的日期和时间数据,取值范围是公元前4712年1月1日至公元9999年12月31日,7个字节分别表示世纪、年、月、日、时、分和秒 二进制数据类型 row 1~2000字节 可变长二进制数据,在具体...
第一章:oracle体系结构 ORACLE 10G的卸载与安装 客户端连接工具 Oracle 10g体系结构 ...HASH分区(散列分区) 列表分区 复合分区 分区表操作 分区表的管理 第十二章:数据库优化 概述 SQL的优化
文章目录优化索引为索引列选择合适的数据类型一般原则建立索引,但是不走索引的情况表分区表分区的功能范围分区(Range Partition列表分区(List Partition)哈希分区(Hash Partition)复合分区 优化索引 MySQL中,...
针对这种情况,Oracle在连接键利用一个hash函数将build input和probe input分割成多个不相连的分区(分别记作Si和Bi),这个阶段叫做分区阶段;然后各自相应的分区,即Si和Bi再做Hash join,这个阶段叫做join阶段。
sub = 对应表分区 (可选) mode = 导入操作 INSERT/APPEND/REPLACE/TRUNCATE (默认INSERT) buffer = 转换缓冲区大小 (UNIT:MB 默认16) reclen = 记录最大长度,8-2048 (默认2048) parallel = 并行导入 (默认FALSE...
24.2. 创建分区表 161 24.3. 范围分区(Range) 161 24.4. 列表分区(List) 164 24.5. 散列分区(Hash) 165 24.6. 组合范围散列分区 167 24.7. 复合范围散列分区 168 24.8. 维护表分区 169 二十四、 PL/SQL基础 173 ...
1.3.5 Oracle 11g 中的分区表............... 74 1.3.5.1 11g 中的分区表新特性........................74 1.3.5.1.1 Interval Partitioning ......................74 1.3.5.1.2 System Partitioning .....
第一部分 ORACLE系统优化基本知识 23 第1章 ORACLE结构回顾 23 §1.1 Oracle数据库结构 23 §1.1.1 Oracle数据字典 23 §1.1.2 表空间与数据文件 24 §1.1.3 Oracle实例(Instance) 24 §1.2 Oracle文件 26 §1.2.1...
对于静态表,则采用Hash分区或列表分区;在范围分区中,如果数据按某关键字段均衡分布,则采用子分区的复合分区方法。 每个表均创建类型为Sequence的主键字段。 每个表中需含有如下几个基本字段:一个表的SEQ号,4个...
型数据库都支持分区 ,但是 ORACLEORACLE ORACLE ORACLE在不同 的版本不断 的版本不断 完善,提供 完善,提供 完善,提供 了更加方便强大的分区 加方便强大的分区 加方便强大的分区 加方便强大的分区 表特性 如Range...
在optimizer_mode=choose时,如果表有统计信息(分区表外),优化器将选择CBO,否则选RBO。RBO遵循简单的分级方法学,使用15种级别要点,当接收到查询,优化器将评估使用到的要点数目, 然后选择最佳级别(最少的数量)的...
27、查看放在ORACLE的内存区里的表 SQL>select table_name,cache from user_tables where instr(cache,\'Y\')>0; 28、约束条件 create table employee (empno number(10) primary key, name varchar2(40) ...
值范围: Oracle8i National Language Support Guide 中指定的任何有效的10 字节字符串。 默认值: 从 NLS_TERRITORY 中获得 nls_date_language: 说明: 指定拼写日期名, 月名和日期缩写词 (AM, PM, AD, BC) 的语言。...