oracle表分割槽詳解(按天、按月、按年等)
分割槽表的概念:
當表中的資料量不斷增大,查詢資料的速度就會變慢,應用程式的效能就會下降,這時就應該考慮對錶進行分割槽。表進行分割槽後,邏輯上表仍然是一張完整的表,只是將表中的資料在物理上存放到多個表空間(物理檔案上),這樣查詢資料時,不至於每次都掃描整張表。
分割槽表的優點:
1) 改善查詢效能:對分割槽物件的查詢可以僅搜尋自己關心的分割槽,提高檢索速度。
2) 增強可用性:如果表的某個分割槽出現故障,表在其他分割槽的資料仍然可用。
3) 維護方便:如果表的某個分割槽出現故障,需要修復資料,只修復該分割槽即可;
4) 均衡I/O:可以把不同的分割槽對映到磁碟以平衡I/O,改善整個系統效能。
分割槽表的種類:
1.範圍分割槽
概念: 範圍分割槽將資料基於範圍對映到每一個分割槽,這個範圍是你在建立分割槽時指定的分割槽鍵決定的。這種分割槽方式是最為常用的,並且分割槽鍵經常採用日期。舉個例子:你可能會將銷售資料按照月份進行分割槽。
-- 按行分割槽
SQL> CREATE TABLE part_andy1
2 (
3 andy_ID NUMBER NOT NULL PRIMARY KEY,
4 FIRST_NAME VARCHAR2(30) NOT NULL,
5 LAST_NAME VARCHAR2(30) NOT NULL,
6 PHONE VARCHAR2(15) NOT NULL,
7 EMAIL VARCHAR2(80),
8 STATUS CHAR(1)
9 )
10 PARTITION BY RANGE (andy_ID)
11 (
12 PARTITION PART1 VALUES LESS THAN (10000) ,
13 PARTITION PART2 VALUES LESS THAN (20000)
14 );
Table created.
-- 按時間分割槽
SQL> CREATE TABLE part_andy2
2 (
3 ORDER_ID NUMBER(7) NOT NULL,
4 ORDER_DATE DATE,
5 OTAL_AMOUNT NUMBER,
6 CUSTOTMER_ID NUMBER(7),
7 PAID CHAR(1)
8 )
9 PARTITION BY RANGE (ORDER_DATE)
10 (
11 PARTITION p1 VALUES LESS THAN (TO_DATE('2014-10-1', 'yyyy-mm-dd')) ,
12 PARTITION p2 VALUES LESS THAN (TO_DATE('2015-10-1', 'yyyy-mm-dd')) ,
13 PARTITION p3 VALUES LESS THAN (TO_DATE('2016-10-1', 'yyyy-mm-dd')) ,
14 partition p4 values less than (maxvalue)
15 );
Table created.
2. Hash分割槽概念:
對於那些無法有效劃分範圍的表,可以使用hash分割槽,這樣對於提高效能還是會有一定的幫助。hash分割槽會將表中的資料平均分配到你指定的幾個分割槽中,列所在分割槽是依據分割槽列的hash值自動分配,因此你並不能控制也不知道哪條記錄會被放到哪個分割槽中,hash分割槽也可以支援多個依賴列。
注意:
hash分割槽最主要的機制是根據hash演算法來計算具體某條紀錄應該插入到哪個分割槽中,hash演算法中最重要的是hash函式,Oracle中如果你要使用hash分割槽,只需指定分割槽的數量即可。建議分割槽的數量採用2的n次方,這樣可以使得各個分割槽間資料分佈更加均勻。
--按hash分割槽
SQL> create table part_andy3
2 (
3 transaction_id number primary key,
4 item_id number(8) not null
5 )
6 partition by hash(transaction_id)
7 (
8 partition part_01 ,
9 partition part_02 ,
10 partition part_03
11 );
Table created.
3. List分割槽
概念:
List分割槽也需要指定列的值,其分割槽值必須明確指定,該分割槽列只能有一個,不能像range或者hash分割槽那樣同時指定多個列做為分割槽依賴列,但它的單個分割槽對應值可以是多個。
注意:
在分割槽時必須確定分割槽列可能存在的值,一旦插入的列值不在分割槽範圍內,則插入/更新就會失敗,因此通常建議使用list分割槽時,要建立一個default分割槽儲存那些不在指定範圍內的記錄,類似range分割槽中的maxvalue分割槽。
-- 按list分割槽
SQL> create table part_andy4
2 (
3 id varchar2(15 byte) not null,
4 city varchar2(20)
5 )
6 partition by list (city)
7 (
8 partition t_list025 values ('beijing'),
9 partition t_list372 values ('shanghai') ,
10 partition t_list510 values ('changsha'),
11 partition p_other values (default)
12 );
Table created.
4. 組合分割槽
Oracle10g提供兩種分割槽組合
– Range-hash
SQL> create table part_andy5
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,
5 item_description varchar2(300),
6 transaction_date date
7 )
8 partition by range(transaction_date)subpartition by hash(transaction_id)
9 (
10 partition part_01 values less than(TO_DATE('2014-10-1', 'yyyy-mm-dd')),
11 partition part_02 values less than(TO_DATE('2015-10-1', 'yyyy-mm-dd')),
12 partition part_03 values less than(maxvalue)
13 );
Table created.
– Range-list
SQL> CREATE TABLE SALES
2 (
3 PRODUCT_ID VARCHAR2(5),
4 SALES_DATE DATE,
5 SALES_COST NUMBER(10),
6 STATUS VARCHAR2(20)
7 )
8 PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
9 (
10 PARTITION P1 VALUES LESS THAN(TO_DATE('2014-10-1', 'yyyy-mm-dd'))
11 (SUBPARTITION P1SUB1 VALUES ('ACTIVE') ,SUBPARTITION P1SUB2 VALUES ('INACTIVE')
12 ),PARTITION P2 VALUES LESS THAN (TO_DATE('2015-10-1', 'yyyy-mm-dd'))
13 (
14 SUBPARTITION P2SUB1 VALUES ('ACTIVE') ,
15 SUBPARTITION P2SUB2 VALUES ('INACTIVE')
16 )
17 );
Table created.
Oracle11g增加了四種組合– RANGE-RANGE
– LIST-RANGE
– LIST-HASH
– LIST-LIST
Oracle 11g 中虛擬列來實現。在11g之前 分割槽表的partition key必須是物理存在的。11g開始提供了虛擬列,並且可以作為partition key 。
--按星期分割槽
SQL> CREATE TABLE part_andy6
2 (
3 getdate date NOT NULL,
4 wd NUMBER GENERATED ALWAYS AS (TO_NUMBER (TO_CHAR (getdate, 'D'))) VIRTUAL
5 )
6 PARTITION BY LIST (wd)
7 (
8 PARTITION Mon VALUES (1),
9 PARTITION Tue VALUES (2),
10 PARTITION Wed VALUES (3),
11 PARTITION Thu VALUES (4),
12 PARTITION Fri VALUES (5),
13 PARTITION Sat VALUES (6),
14 PARTITION Sun VALUES (7)
15 );
Table created.
SQL>
SQL> insert into part_andy6(getdate) values(sysdate);
1 row created.
SQL> insert into part_andy6(getdate) values(sysdate-1);
1 row created.
SQL> insert into part_andy6(getdate) values(sysdate-2);
1 row created.
SQL> insert into part_andy6(getdate) values(sysdate-3);
1 row created.
SQL> insert into part_andy6(getdate) values(sysdate-4);
1 row created.
SQL> insert into part_andy6(getdate) values(sysdate-5);
1 row created.
SQL> insert into part_andy6(getdate) values(sysdate-6);
1 row created.
SQL> insert into part_andy6(getdate) values(sysdate-7);
1 row created.
-- 檢查測試成功
SQL> select * from part_andy6;
GETDATE WD
------------------- ----------
2014-11-23 16:35:07 1
2014-11-24 16:35:07 2
2014-11-25 16:35:07 3
2014-11-26 16:35:07 4
2014-11-27 16:35:07 5
2014-11-28 16:35:07 6
2014-11-29 16:35:07 7
2014-11-22 16:35:08 7
8 rows selected.
Oracle Database 11g,Interval型別分割槽表,可以根據載入資料,自動建立指定間隔的分割槽。
建立按月分割槽的分割槽表:
a. 建立分割槽表
SQL> CREATE TABLE interval_andy7 (a1 NUMBER, a2 DATE)
2 PARTITION BY RANGE (a2)
3 INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
4 (PARTITION part1
5 VALUES LESS THAN (TO_DATE('2014-11-1', 'yyyy-mm-dd')),
6 PARTITION part2
7 VALUES LESS THAN (TO_DATE('2014-12-1', 'yyyy-mm-dd'))
8 );
Table created.
注意:如果在建Interval分割槽表是沒有把所有的分割槽寫完成,在插入相關資料後會自動生成分割槽
b. 檢視現在表的分割槽:
SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_ANDY7';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INTERVALPART PART1
INTERVALPART PART2
c. 插入測試資料:
SQL> begin
2 for i in 0 .. 11 loop
3 insert into interval_andy7 values(i,add_months(to_date('2014-11-1','yyyy-mm-dd'),i));
4 end loop ;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
PL/SQL 過程已成功完成。
補充:add_months()函式獲取前一個月或者下一個月的月份, 引數中 負數 代表 往前, 正數 代表 往後。
--上一個月
select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;
--下一個月
select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;
d. 觀察自動建立的分割槽:
SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_ANDY7';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INTERVAL_ANDY7 PART1
INTERVAL_ANDY7 PART2
INTERVAL_ANDY7 SYS_P24
INTERVAL_ANDY7 SYS_P25
INTERVAL_ANDY7 SYS_P26
INTERVAL_ANDY7 SYS_P27
INTERVAL_ANDY7 SYS_P28
INTERVAL_ANDY7 SYS_P29
INTERVAL_ANDY7 SYS_P30
INTERVAL_ANDY7 SYS_P31
INTERVAL_ANDY7 SYS_P32
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INTERVAL_ANDY7 SYS_P33
INTERVAL_ANDY7 SYS_P34
13 rows selected.
下面建立一個以天為間隔的分割槽表:
1. 建立分割槽表:
SQL> create table interval_andy8
2 (
3 id number,
4 dt date
5 )
6 partition by range (dt)
7 INTERVAL (NUMTODSINTERVAL(1,'day'))
8 (
9 partition p20141101 values less than (to_date('2014-11-1','yyyy-mm-dd'))
10 );
Table created.
2. 檢視錶分割槽:
SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_ANDY8';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INTERVAL_ANDY8 P20141101
3. 插入測試資料:
begin
for i in 1 .. 12 loop
insert into INTERVAL_ANDY8 values(i,trunc(to_date('2014-11-1','yyyy-mm-dd')+i));
end loop;
commit;
end;
/
PL/SQL 過程已成功完成。
4. 觀察自動建立的分割槽:
SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_ANDY8';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INTERVAL_ANDY8 P20141101
INTERVAL_ANDY8 SYS_P35
INTERVAL_ANDY8 SYS_P36
INTERVAL_ANDY8 SYS_P37
INTERVAL_ANDY8 SYS_P38
INTERVAL_ANDY8 SYS_P39
INTERVAL_ANDY8 SYS_P40
INTERVAL_ANDY8 SYS_P41
INTERVAL_ANDY8 SYS_P42
INTERVAL_ANDY8 SYS_P43
INTERVAL_ANDY8 SYS_P44
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INTERVAL_ANDY8 SYS_P45
INTERVAL_ANDY8 SYS_P46
13 rows selected.
說明:個人總結+網路博文
參考: http://blog.csdn.net/tianlesoftware/article/details/5662337
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31383567/viewspace-2131689/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 分割槽表詳解Oracle
- oracle表分割槽詳解Oracle
- oracle分割槽表詳解Oracle
- Oracle 表分割槽詳解Oracle
- Oracle表分割槽詳解(優缺點)Oracle
- 詳解ORACLE資料庫的分割槽表Oracle資料庫
- mysql 5.1新功能 -- 按日期分割槽MySql
- SQL Server表分割槽詳解SQLServer
- 硬碟分割槽表詳解(轉)硬碟
- oracle分割槽表和分割槽表exchangeOracle
- oracle 線上重新定義,普通表改變分割槽表,分割槽表可以更改型別、分割槽欄位等Oracle型別
- oracle索引詳解 分割槽索引Oracle索引
- Oracle分割槽表及分割槽索引Oracle索引
- oracle分割槽表和非分割槽表exchangeOracle
- SQL Server表分割槽操作詳解SQLServer
- 普通表自動轉化為按月分割槽表的指令碼指令碼
- Oracle 表分割槽Oracle
- oracle分割槽表Oracle
- oracle表分割槽Oracle
- Oracle 分割槽表Oracle
- efcore如何優雅的實現按年分庫按月分表
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- MySQL 分割槽表原理及使用詳解MySql
- nxlog4go 按天或按檔案大小分割日誌Go
- Oracle 建立分割槽表Oracle
- ORACLE分割槽表管理Oracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 2萬字詳解Oracle分割槽表技術,太頂了Oracle
- 詳細講解Oracle表分割槽相關概念及優點Oracle
- Oracle資料庫中分割槽表的操作方法詳解Oracle資料庫
- 【INDEX】Oracle分割槽索引技術詳解IndexOracle索引
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- ORACLE分割槽表梳理系列Oracle
- Oracle分割槽表遷移Oracle