Oracle分区.docx
- 文档编号:6496778
- 上传时间:2023-01-07
- 格式:DOCX
- 页数:16
- 大小:22.80KB
Oracle分区.docx
《Oracle分区.docx》由会员分享,可在线阅读,更多相关《Oracle分区.docx(16页珍藏版)》请在冰豆网上搜索。
Oracle分区
Oracle中分区表的使用
前提:
查询分区:
Select*Fromuser_extentsWHEREpartition_name='分区名';
1)创建表空间
createtablespaceHRPM0
datafile'/oradata/misdb/HRPM0.DBF'size5mautoextendonnext10mmaxsizeunlimited
2)删除表空间(同时把数据文件也删除)
DROPTABLESPACEdata01INCLUDINGCONTENTSANDDATAFILES;
如果不想删除数据文件:
Droptablespacetablespace_name;
3)修改表空间大小
alterdatabasedatafile'/path/NADDate05.dbf'resize100M
4)添加数据文件(在建立表空间时,若是约束了表空间的大小,那么一段时间后,这个表空间就会被装满,无法再添加其他对象。
则需要给表空间添加数据文件):
Altertablespacetablespace_nameadddatafile’'/path/NADDate06.dbf’size100M;
4)备注:
4.1).--.禁止undotablespace自动增长
alterdatabasedatafile'full_path\undotbs01.dbf'autoextendoff;
4.2).--创建一个新的小空间的undotablespace
createundotablespaceundotBS2datafile'full_path\UNDOTBS02.DBF'size100m;
4.3).--设置新的表空间为系统undo_tablespace
altersystemsetundo_tablespace=undotBS2;
4.4).--Drop旧的表空间
droptablespaceundotbs1includingcontents;
4.5).--查看所有表空间的情况
select*fromdba_tablespaces
5)查到一个最好用的表:
dict
5.1)select*fromdictwheretable_namelike'%PART%'
5.2)ALL_TAB_PARTITIONS:
可以查出表所对应的分区内容;
5.3)dab_tab_partitons:
与上2);
5.4)dba_ind_partitons:
查询分区的索引;
5.5)子分区也是一样的(dba_tab_subpartitons,dba_ind_partitons)
一、使用分区的优点:
1、增强可用性:
如果表的某个分区出现故障,表在其他分区的数据仍然可用;
2、维护方便:
如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
3、均衡I/O:
可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能;
4、改善查询性能:
对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
二、Oracle数据库提供对表或索引的分区方法有几种(收集到四种):
1、范围分区
2、列表分区
3、Hash分区(散列分区)
4、复合分区
三、详描述分区实例:
1)下面将以实例的方式分别对这三种分区方法来说明分区表的使用。
为了测试方便,我们先建三个表空间。
createtablespacedinya_space01datafile'C:
\表空间\dinya01.dbf'size5M;createtablespacedinya_space02datafile'C:
\表空间\dinya02.dbf'SIZE5M;createtablespacedinya_space03datafile'C:
\表空间\dinya03.dbf'SIZE5M;
select*fromuser_tablespaces
<表空间->三个>
1.1)范围分区
范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上。
如根据序号分区,根据业务记录的创建日期进行分区等。
需求描述:
有一个物料交易表,表名:
material_transactions。
该表将来可能有千万级的数据记录数。
要求在建该表的时候使用分区表。
这时候我们可以使用序号分区三个区,每个区中预计存储三千万的数据,也可以使用日期分区,如每五年的数据存储在一个分区上。
根据交易记录的序号分区建表:
----为了测试需要做以下修改;
createtabledinya_test
(
transaction_idnumberprimarykey,
item_idnumber(8)notnull,
item_descriptionvarchar2(300),
transaction_datedatenotnull
)
partitionbyrange(transaction_id)
(
partitionpart_01valueslessthan
(2)tablespacedinya_space01,-----2条以下的交易在此分区上:
part_01
partitionpart_02valueslessthan(3)tablespacedinya_space02,-----等于+大于2而小于3的交易在此分区:
part_02
partitionpart_03valueslessthan(maxvalue)tablespacedinya_space03----大于3的交易在此分区:
part_03
-----------------以上在pl/sql测试成功;
----------------以下没有在pl/sql测试!
根据交易日期分区建表:
SQL>createtabledinya_test
(
transaction_idnumberprimarykey,
item_idnumber(8)notnull,
item_descriptionvarchar2(300),
transaction_datedatenotnull
)
partitionbyrange(transaction_date)
(
partitionpart_01valueslessthan(to_date('2006-01-01','yyyy-mm-dd'))tablespacedinya_space01,
partitionpart_02valueslessthan(to_date('2010-01-01','yyyy-mm-dd'))tablespacedinya_space02,
partitionpart_03valueslessthan(maxvalue)tablespacedinya_space03
);
这样我们就分别建了以交易序号和交易日期来分区的分区表。
每次插入数据的时候,系统将根据指定的字段的值来自动将记录存储到制定的分区(表空间)中。
当然,我们还可以根据需求,使用两个字段的范围分布来分区,如partitionbyrange(transaction_id,transaction_date),分区条件中的值也做相应的改变,请读者自行测试。
---------------------------------以上没有在pl/sql测试!
1.2)范围分区创建成功之后的相关操作测试;
a)向表添加测试数据:
insertintodinya_testvalues(1,12,'BOOKS',sysdate);
insertintodinya_testvalues(2,12,'BOOKS',sysdate+30);
insertintodinya_testvalues(3,12,'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));
insertintodinya_testvalues(4,12,'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));
insertintodinya_testvalues(5,12,'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));
insertintodinya_testvalues(6,12,'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));
b)查询
b.1)如果查询全表数据
select*fromdinya_test;如下图:
<全表数据>
select*fromdinya_testpartition(part_01);如下图:
select*fromdinya_testpartition(part_02);如下图:
select*fromdinya_testpartition(part_03);如下图:
updatedinya_testpartition(part_01)tsett.item_description='DESK'wheret.transaction_id=1;
select*fromdinya_testpartition(part_01);BOOKS->DESK(发生变化)
select*fromdinya_test(此结果就不用查看了,肯定变了);
---删除part_03分区中transaction_id=4的记录:
deletefromdinya_testpartition(part_03)twheret.transaction_id=4;
select*fromdinya_testpartition(part_03)
少了transaction_id=4的记录(与上图对比)
c)索引的创建:
c.1)局部索引的创建:
createindexdinya_idx_tondinya_test(item_id)
local
(
partitionidx_1tablespacedinya_space01,---分区名为:
idx_1
partitionidx_2tablespacedinya_space02,---分区名为:
idx_2
partitionidx_3tablespacedinya_space03---分区名为:
idx_3
); ---pl/sql测试成功
注:
select*fromALL_TAB_PARTITIONSwheretable_name='DINYA_TEST'
select*Fromdba_ind_partitionswherepartition_name='IDX_1'
c.2)全局索引的创建:
全局索引建立时global子句允许指定索引的范围值,这个范围值为索引字段的范围值:
createindexdinya_idx_tondinya_test(item_id)
globalpartitionbyrange(item_id)
(
partitionidx_1valueslessthan(1000)tablespacedinya_space01,
partitionidx_2valueslessthan(10000)tablespacedinya_space02,
partitionidx_3valueslessthan(maxvalue)tablespacedinya_space03
);----PL/SQL末测试[参照以上local];
整个表创建索引:
Createindexdinya_idx_tondinya_test(item_id);
备注:
select*fromall_indexes(dba_indexes、all_ind_columns、user_ind_columns、dba_ind_columns)
1.3)Hash分区(散列分区)
——————————以下没有在机器上测试
散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。
如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中:
createtabledinya_test
(
transaction_idnumberprimarykey,
item_idnumber(8)notnull,
item_descriptionvarchar2(300),
transaction_datedate
)
partitionbyhash(transaction_id)
(
partitionpart_01tablespacedinya_space01,
partitionpart_02tablespacedinya_space02,
partitionpart_03tablespacedinya_space03
);
建表成功,此时插入数据,系统将按transaction_id将记录散列地插入三个分区中,这里也就是三个不同的表空间中。
——————————以上没有在机器上测试;
1.4)列表分区:
该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。
示例1:
CREATETABLEPROBLEM_TICKETS
(
PROBLEM_IDNUMBER(7)NOTNULLPRIMARYKEY,
DESCRIPTIONVARCHAR2(2000),
CUSTOMER_IDNUMBER(7)NOTNULL,
DATE_ENTEREDDATENOTNULL,
STATUSVARCHAR2(20)
)
PARTITIONBYLIST(STATUS)
(
PARTITIONPROB_ACTIVEVALUES('ACTIVE')TABLESPACEPROB_TS01,
PARTITIONPROB_INACTIVEVALUES('INACTIVE')TABLESPACEPROB_TS02
)
备注:
active和inactive是列status的值!
谨记与range和hash分区的区别;
1.4.1)测试如下:
insertintoPROBLEM_TICKETSvalues(1,'BOOKS',1,sysdate,'ACTIVE');
insertintoPROBLEM_TICKETSvalues(2,'son',2,sysdate+30,'INACTIVE');
insertintoPROBLEM_TICKETSvalues(3,'son',3,to_date('2006-05-30','yyyy-mm-dd'),'INACTIVE');
insertintoPROBLEM_TICKETSvalues(4,'BOOKS',4,to_date('2007-06-23','yyyy-mm-dd'),'INACTIVE');
insertintoPROBLEM_TICKETSvalues(5,'old',5,to_date('2011-02-26','yyyy-mm-dd'),'ACTIVE');
insertintoPROBLEM_TICKETSvalues(6,'test',6,to_date('2011-04-30','yyyy-mm-dd'),'INACTIVE');
select*fromPROBLEM_TICKETS
<查询全表>
1.4.2)
select*fromPROBLEM_TICKETSpartition(PROB_ACTIVE)
1.4.2)
select*fromPROBLEM_TICKETSpartition(PROB_INACTIVE)
在测试中遇到这样的情况。
如果表创建了分区,如果要删除数据文件(表空间文件),则要先删除分区,然后才能删除数据文件(但是在删除数据文件时,必须要保留一个分区才能最终删除数据文件>表空间文件,)
当然,也可以直接就删除表也行,刚所有的全删除,但是表空间文件还在!
1.5)复合分区
有时候我们需要根据范围分区后,每个分区内的数据再散列地分布在几个表空间中,这样我们就要使用复合分区。
复合分区是先使用范围分区,然后在每个分区内再使用散列分区的一种分区方法,如将物料交易的记录按时间分区,然后每个分区中的数据分三个子分区,将数据散列地存储在三个指定的表空间中:
createtabledinya_test
(
transaction_idnumberprimarykey,
item_idnumber(8)notnull,
item_descriptionvarchar2(300),
transaction_datedate
)
partitionbyrange(transaction_date)subpartitionbyhash(transaction_id)
subpartitions3storein(dinya_space07,dinya_space08,dinya_space09)
(
partitionpart_07valueslessthan(to_date('2006-01-01','yyyy-mm-dd')),
partitionpart_08valueslessthan(to_date('2010-01-01','yyyy-mm-dd')),
partitionpart_09valueslessthan(maxvalue)
);
---测试如下:
select*Fromuser_tab_partitionswheretable_name=upper('dinya_test')
selec*Fromuser_tab_subpartitionswheretable_name=upper('dinya_test')
《图1》
插入如下数据:
insertintodinya_testvalues(1,12,'BOOKS',sysdate);
insertintodinya_testvalues(2,12,'BOOKS',sysdate+30);
insertintodinya_testvalues(3,12,'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));
insertintodinya_testvalues(7,12,'BOOKS',to_date('2005-05-30','yyyy-mm-dd'));
insertintodinya_testvalues(4,12,'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));
insertintodinya_testvalues(5,12,'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));
insertintodinya_testvalues(6,12,'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));
select*Fromdinya_test:
如下图
select*Fromdinya_testpartition(part_07)如下图:
select*Fromdinya_testpartition(part_09)
参照下图,按所显的子分区名,看能否查出数据:
select*Fromuser_tab_subpartitionswheretable_name=upper('dinya_test')
select*Fromdinya_testsubpartition(SYS_SUBP62):
如下图:
其它的查询一样。
-----测试成功;
备注:
该例中,先是根据交易日期进行范围分区,然后根据交易的ID将记录散列地存储在三个表空间中。
1.6)复合范围列表分区:
这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区,分区之中的分区被称为子分区。
示例1:
Createtablesales
(
Product_idvarchar2(5),
Sales_datedate,
Sales_costnumber(10),
Statusvarchar2(20)
)
Partitionbyrange(Sales_cost)
Subpartitionbylist(status)
(
Partitionp1valueslessthan
(1)tablespacedinya_space01
(
Subpartitionp1sub1values('ACTIVE')tablespacedinya_space03,
Subpartitionp1sub2values('INACTIVE')tablespacedinya_space04
),
Partitionp2valueslessthan(3)tablespacedinya_space02
(
Subpartitionp1sub3values('ACTIVE')tablespacedinya_space05,
Subpartitionp1sub4values('INACTIVE')tablespacedinya_space06
)
)测试如下:
insertintosalesvalues(1,sysdate,0.1,'ACTIVE');
insertintosalesvalues(2,sysdate+30,1,'INACTIVE');
insertintosales
values(3,to_date('2006-05-30','yyyy-mm-dd'),2,'INACTIVE');
select*Fromsales:
Select*fromsalespartition(p2)
SELECT*FROMSALESSUBPARTITIO
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 分区