當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

[oracle]分区表学习

發布時間：2023/12/13 编程问答 31 豆豆

生活随笔收集整理的這篇文章主要介紹了 [oracle]分区表学习小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

（一）什么是分區

所謂分區，就是將一張巨型表或巨型索引分成若干個獨立的組成部分進行存儲和管理，每一個相對小的，可獨立管理的部分，稱為分區。

（二）分區的優勢

提高數據可管理性。對表進行分區，數據的加載、索引的創建與重建、數據的備份與恢復等操作都可以在分區表上進行，而不必在表級別上進行，提高了數據的可管理性；
增強數據庫的可用性。某個分區出現問題，只影響該分區，其它分區照常運作；
改善查詢性能。將對整個表的查詢轉化為對某個分區表的查詢，提高了查詢速度；
提高數據庫操作的并行性?？蓪Ψ謪^表進行并行操作；
透明性。將一張表分區后，對于用戶而言是無感的，即用戶不會感知到有多個分區表的存在，用戶不需要對SQL語句做處理；

（三）什么時候需要分區

什么時候對表進行分區,我們可以從下面的條件進行考慮：

表大于2G；
對一個表并行進行DML操作；
為了平衡硬盤I/O，需將同一個表分區到不同的表空間，必須對表進行分區；
要將表的一部分設為只讀狀態，另一部分設為讀寫狀態，需要對表進行分區；
要將表一部分設為可用狀態，另一部分設為不可用狀態，需要隊標進行分區；
要將表中數據按照一定規則分散到不同的磁盤中去，需要對表進行分區；

（四）分區方法

oracle數據庫提供了3種分區方法：范圍分區（Range Partitioning）、列表分區（List Partitioning）和散列分區（Hash Partitioning），結合3種方法，又可以進行復合分區。

（1）范圍分區（Range Partitioning）

范圍分區是根據分區列值的范圍對表進行分區，每條記錄根據分區列值的范圍分配到不同的分區表中。常用于按照日期分區的表。

（2）列表分區（List Partitioning）

如果分區列值的并不能劃分范圍（非數值或日期類型），但是分區列的值僅包含少數值，可采用分區列，將特定的值保存到分區中。例如，要統計整個省的人口信息，如果將全部信息放在一張表中，那么表將會非常臃腫，這時候我們可以考慮按照列表分區，將人口信息按照市分配到多個分區表中。

（3）散列分區（Hash Partitioning）

基于分區列的哈希算法，將數據均勻分不到指定的分區中，一個記錄分配到哪個分區中是由Hash函數決定的。

（4）復合分區（Composite Partitioning）

復合分區是結合兩種基本分區方法，先采用一種分區方法進行分區，然后再采用另一種方法進行分區。

（五）創建分區表

（1）創建范圍分區表

CREATE TABLE table_name (...) PARTITION BY RANGE(column1 [,column2,...]) (PARTITION partition1 VALUES LESS THAN(literal | MAXVALUE)[TABLESPACE tablespace]PARTITION partition2 VALUES LESS THAN(literal | MAXVALUE)[TABLESPACE tablespace][PARTITION partition3 VALUES LESS THAN(iteral | MAXVALUE)[TABLESPACE tablespace]] )

參數說明：

PARTITION BY RANGE：采用范圍分區；

column：分區列，可以是單列，也可以是多列；

PARTITION：分區設置；

VALUE LESS THAN：分區上限；

TABLESPACE：分區表存儲的表空間

例子1：創建一個銷售分區表，按照季度進行分區

create table sale_by_range (prod_id number, cust_id number,time_id date,channel_id char(1),promo_id number(6),quantity_sold number(3),amount_sold number ) partition by range(time_id) (partition sales_q1_2012 values less than(to_date('1-4-2012','dd-mm-yyyy')) tablespace TBS1,partition sales_q2_2012 values less than(to_date('1-7-2012','dd-mm-yyyy')) tablespace TBS2,partition sales_q3_2012 values less than(to_date('1-10-2012','dd-mm-yyyy')) tablespace TBS3,partition sales_q4_2012 values less than(to_date('1-1-2013','dd-mm-yyyy')) tablespace TBS4 )

結果如下：

插入數據，發現沒有問題

insert into sale_by_range(prod_id,cust_id,time_id,quantity_sold) values(2,12,to_date('2012-04-01','yyyy-mm-dd'),103);

結果如下：

?? PROD_ID??? CUST_ID TIME_ID?? CHANNEL_ID?? PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
???????? 2???????? 12 01-4月-12????????????????????????????????? 103???????????

（2）創建列表分區表

通過使用帶PARTITION BY LIST子句的CREATE TABLE來創建列表分區表：

CREATE TABLE table_name(...) PARTITION BY LIST(column) (PARTITION partition1 VALUES([literal|NULL]|[DEFAULT])TABLESPACE tablespace_name,[,PARTITION partition2 VALUES([literal|NULL]|[DEFAULT]),TABLESPACE tablespace_name...] );

參數說明：

PARTITION BY LIST:采用列表分區；

DEFAULT:如果列值不符合其它分區記錄的要求，保存在該分區表；

例子2：創建一個銷售分區表，按照地區進行分區

--創建一個銷售分區表，按照地區進行分區 create table sales_by_list (dept number,deptname varchar(20),quarterly_sales number(10,2),province varchar(20) ) partition by list(province) (partition southeast values('guangdong','fujian') tablespace TBS1, --廣東、福建的存放在southeast分區表partition northeast values('heilongjiang','liaoning','jilin') tablespace TBS2, --黑龍江、遼寧、吉林放在northeast分區表partition southwest values('sichuan','yunnan','guizhou') tablespace TBS3, --四川、云南、貴州放在southwest分區表partition otherprovince values(default) tablespace TBS4 --其他省的放在otherprivince分區表 )

插入數據：

--插入數據 insert into sales_by_list(dept,deptname,quarterly_sales,province) values(1,'fa',2000,'sichuan'); insert into sales_by_list(dept,deptname,quarterly_sales,province) values(2,'cim',4000,'guangdong'); insert into sales_by_list(dept,quarterly_sales,province) values(3,6000,'liaoning'); insert into sales_by_list(dept,deptname,quarterly_sales,province) values(4,'cell',8000,'hunan'); insert into sales_by_list(dept,deptname) values(5,'model')

查看數據：

SQL> select * from sales_by_list;DEPT DEPTNAME QUARTERLY_SALES PROVINCE ---------- -------------------- --------------- --------------------2 cim 4000 guangdong3 6000 liaoning1 fa 2000 sichuan4 cell 8000 hunan5 model

（3）創建散列分區表

通過創建PARTITION BY HASH子句的CREATE TABLE語句創建散列分區表：

CREATE TABLE table_name(...) PARTITION BY HASH(column1[,column2,...]) [([PARTITION partition [TABLESPACE tablespace] [,...])] --設置分區表名稱及對應表空間 | [PARTITION hash_parttition_quantity STORE IN([tablespace1[,...])] --設置分區數量及存儲表空間 ]

說明：

HASH分區有2種方式來決定分區的數量，一種是通過設置分區表的名稱來決定分區的數量，一種是直接設置分區的數量。

例子3：通過直接設置分區的數量來創建HASH分區表

--創建一個HASH分區表 create table dept_by_hash (dept number,deptname varchar(20),quarterly_sales number(20),province varchar(20) ) partition by hash(dept) partitions 4 store in(TBS1,TBS2,TBS3,TBS4); --需要注意的是，創建分區時不需要小括號

數據的插入與上面2種方法一樣，這里不做討論。

（4）創建復合分區表

Oracle 11g支持的6種復合分區方法：

Range-Range
Range-List
Range-Hash
List-Range
List-List
List-Hash

為什么不以Hash開頭？通過Hash分區的方式可知，Hash分區是將數據均勻的分配到其分區表中，它不關心數據內容，所以，先以Hash分，然后再用其它方式，顯然沒有意義。

創建符合分區時，首先在CREATE TABLE語句中使用PARTITION BY [RANGE|LIST]子句確定分區方法、分區列，然后再使用SUBPARTITION BY [RANGE|LIST|HASH]子句指定分區的分區方法、分區列、分區數量等信息。

例子4.創建一個分區列表，將2012年的銷售記錄先按季度進行范圍分區，再按區域進行列表分區

--創建一個分區列表，將2012年的銷售記錄先按季度進行范圍分區，再按區域進行列表分區 create table sales_by_range_list (dept number,deptname varchar(20),time_id date,quarterly_sales number(10,2),province varchar(20) ) partition by range(time_id) subpartition by list(province) (partition sales_q1_2012 values less than(to_date('1-4-2012','dd-mm-yyyy')) tablespace TBS1 --第一季度銷售情況(SUBPARTITION sales_q1_2012_southeast VALUES ('guangdong', 'fujian'), --廣東、福建的存放在southeast分區表SUBPARTITION sales_q1_2012_northeast VALUES ('heilongjiang', 'liaoning', 'jilin'), --黑龍江、遼寧、吉林放在northeast分區表SUBPARTITION sales_q1_2012_southwest VALUES ('sichuan', 'yunnan', 'guizhou'), --四川、云南、貴州放在southwest分區表SUBPARTITION sales_q1_2012_otherprovince VALUES ( DEFAULT ) --其他省的放在otherprivince分區表),partition sales_q2_2012 values less than(to_date('1-7-2012','dd-mm-yyyy')) --第二季度銷售情況(subpartition sales_q2_2012_southeast values('guangdong','fujian'),subpartition sales_q2_2012_northeast values('heilongjiang','liaoning','jilin'),subpartition sales_q2_2012_southwest values('sichuan','yunnan','guizhou'),subpartition sales_q2_2012_otherprovince values(default)),partition sales_q3_2012 values less than(to_date('1-10-2012','dd-mm-yyyy')) --第三季度銷售情況(subpartition sales_q3_2012_southeast values('guangdong','fujian'),subpartition sales_q3_2012_northeast values('heilongjiang','liaoning','jilin'),subpartition sales_q3_2012_southwest values('sichuan','yunnan','guizhou'),subpartition sales_q3_2012_otherprovince values(default)),partition sales_q4_2012 values less than(MAXVALUE) --第四季度銷售情況(subpartition sales_q4_2012_southeast values('guangdong','fujian'),subpartition sales_q4_2012_northeast values('heilongjiang','liaoning','jilin'),subpartition sales_q4_2012_southwest values('sichuan','yunnan','guizhou'),subpartition sales_q4_2012_otherprovince values(default)) )

?向表中插入數據，

--向表中插入數據 insert into sales_by_range_list values(1,'a1',to_date('2012-1-2','yyyy-mm-dd'),1000,'guangdong'); --模擬一季度四個地區的數據 insert into sales_by_range_list values(2,'a1',to_date('2012-2-6','yyyy-mm-dd'),1000,'heilongjiang'); insert into sales_by_range_list values(3,'a1',to_date('2012-2-28','yyyy-mm-dd'),1000,'guizhou'); insert into sales_by_range_list values(4,'a1',to_date('2012-3-4','yyyy-mm-dd'),1000,'gansu'); insert into sales_by_range_list values(5,'a1',to_date('2012-4-6','yyyy-mm-dd'),1000,'fujian'); --模擬二季度四個地區的數據 insert into sales_by_range_list values(6,'a1',to_date('2012-5-6','yyyy-mm-dd'),1000,'jilin'); insert into sales_by_range_list values(7,'a1',to_date('2012-5-28','yyyy-mm-dd'),1000,'yunnan'); insert into sales_by_range_list values(8,'a1',to_date('2012-6-6','yyyy-mm-dd'),1000,'xizang'); insert into sales_by_range_list values(9,'a1',to_date('2012-7-6','yyyy-mm-dd'),1000,'guangdong'); --模擬三季度四個地區的數據 insert into sales_by_range_list values(10,'a1',to_date('2012-8-6','yyyy-mm-dd'),1000,'heilongjiang'); insert into sales_by_range_list values(11,'a1',to_date('2012-9-6','yyyy-mm-dd'),1000,'guizhou'); insert into sales_by_range_list values(12,'a1',to_date('2012-9-25','yyyy-mm-dd'),1000,'gansu'); insert into sales_by_range_list values(13,'a1',to_date('2012-10-6','yyyy-mm-dd'),1000,'fujian'); --模擬四季度四個地區的數據 insert into sales_by_range_list values(14,'a1',to_date('2012-11-6','yyyy-mm-dd'),1000,'jilin'); insert into sales_by_range_list values(15,'a1',to_date('2012-12-6','yyyy-mm-dd'),1000,'yunnan'); insert into sales_by_range_list values(16,'a1',to_date('2012-12-26','yyyy-mm-dd'),1000,'xizang');

可以看到結果：

(六)查看分區表

（1）查看分區表信息

可以通過DBA_PART_TABLES、ALL_PART_TABLES、USER_PART_TABLES來查看分區表信息，具體用法如下：

DBA_PART_TABLES：查看數據庫里面的全部分區表信息，需要具有DBA權限，否則會報“ora-00942: 表或視圖不存在”錯誤；
ALL_PART_TABLES：查看當前用戶可見的全部分區表信息；
USER_PART_TABLES：查看當前用戶擁有的全部分區表信息；

例子5：查看當前用戶擁有的的全部分區表信息

select * from user_part_tables;

重要參數說明：

TABLE_NAME:分區表名稱；

PARTITION_TYPE:分區類型；

SUBPARTITION_TYPE:子分區類型；

PARTITION_COUNT:分區數量；

SUBPARTITION_KEY_COUNT：子分區用于分區的列的數量。

通過上面的查詢，我們可以得到：分區表名稱、分區數量、子分區數量等信息，但是并沒有得到分區/子分區的名稱，以及分區/子分區是如何定義等信息，接下來可以使用USER_TAB_PARTITION和USER_TAB_SUBPARTITION查看分區表/子分區表信息：

--查詢分區表信息 SELECT * FROM USER_TAB_PARTITIONS;

--查詢子分區表信息 SELECT * FROM USER_TAB_SUBPARTITIONS;

（2）查看分區表數據

在向分區表插入數據時，我們最關心的就是數據是否按照我們的規劃進入了各個分區表，這時，就需要我們查看分區表的數據，那么，怎么查看分區表的數據，通過sql語句：

SELECT * FROM table_name PARTITION(partition_table_name)

例子6.查看SALES_BY_LIST_RANGE分區表第一季度的信息

select * from SALES_BY_RANGE_LIST partition(sales_q1_2012);

得到結果：

（七）維護分區表

?（1）添加分區

ALTER TABLE table_name ADD PARTITION ... --添加分區ALTER TABLE table_name MODIFY PARTITION partition ADD SUBPARTITION ... --為分區添加子分區

（2）刪除分區

ALTER TABLE table_name DROP PARTITION partition; --刪除分區ALTER TABLE table_name DROP SUBPARTITION subpartition; --刪除子分區

（3）其他

此外，還有聯合分區、交換分區、合并分區等操作，不一一介紹。

（八）與分區相關的數據字典

數據字典	說明
xxx_PART_TABLES	包含分區表的分區信息
xxx_TAB_PARTITIONS	包含分區層次、分區存儲、分區統計等信息
xxx_TAB_SUBPARTITIONS	包含子分區層次、分區存儲、分區統計等信息
xxx_PART_KEY_COLUMNS	包含分區表的分區列信息
xxx_SUBPART_KEY_COLUMNS	包含子分區表的分區列信息
xxx_COL_STATISTICS	包含分區表的分區列的統計信息和柱狀圖信息
…	…
xxx：代表DBA、ALL、USER	?

對分區表有了一定的了解，接下來學習分區索引。

轉載于:https://www.cnblogs.com/lijiaman/p/6495465.html

總結

以上是生活随笔為你收集整理的[oracle]分区表学习的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： Celery框架简单实例
下一篇：抽丝剥茧读源码——Microsoft.E