(1) 表空間及分區(qū)表的概念
表空間：
是一個或多個數(shù)據(jù)文件的集合，所有的數(shù)據(jù)對象都存放在指定的表空間中，但主要存放的是表， 所以稱作表空間。
分區(qū)表：?
當(dāng)表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會變慢，應(yīng)用程序的性能就會下降，這時就應(yīng)該考慮對表進行分區(qū)。表進行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個表空間(物理文件上)，這樣查詢數(shù)據(jù)時，不至于每次都掃描整張表。
表分區(qū)的幾種類型及操作方法
?一.范圍分區(qū)：
當(dāng)使用范圍分區(qū)時，請考慮以下幾個規(guī)則：
1、每一個分區(qū)都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分區(qū)中的上限值。分區(qū)鍵的任何值等于或者大于這個上限值的記錄都會被加入到下一個高一些的分區(qū)中。
2、所有分區(qū)，除了第一個，都會有一個隱式的下限值，這個值就是此分區(qū)的前一個分區(qū)的上限值。
3、在最高的分區(qū)中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高于其它分區(qū)中的任何分區(qū)鍵的值，也可以理解為高于任何分區(qū)中指定的VALUE LESS THEN的值，同時包括空值
? A.具體字段
假設(shè)有一個CUSTOMER表，表中有數(shù)據(jù)200000行，我們將此表通過CUSTOMER_ID進行分區(qū)，每個分區(qū)存儲100000行，我們將每個分區(qū)保存到單獨的表空間中，這樣數(shù)據(jù)文件就可以跨越多個物理磁盤。下面是創(chuàng)建表和分區(qū)的代碼，如下：
CREATE TABLE CUSTOMER?
(?
??? 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),?
??? STATUS?????? CHAR(1)?
)?
PARTITION BY RANGE (CUSTOMER_ID)?
(?
??? PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,?
??? PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02?
)
B.按時間劃分
CREATE TABLE ORDER_ACTIVITIES?
(?
??? ORDER_ID????? NUMBER(7) NOT NULL,?
??? ORDER_DATE??? DATE,?
??? TOTAL_AMOUNT NUMBER,?
??? CUSTOTMER_ID NUMBER(7),?
??? PAID?????????? CHAR(1)?
)?
PARTITION BY RANGE (ORDER_DATE)?
(
? PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,
? PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
? PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
)
C：MAXVALUE
CREATE TABLE RangeTable
(?
? idd?? INT PRIMARY KEY ,?
? iNAME VARCHAR(10),?
? grade INT??
)?
PARTITION? BY? RANGE (grade)?
(?
????? PARTITION? part1 VALUES? LESS? THEN (1000) TABLESPACE? Part1_tb,?
????? PARTITION? part2 VALUES? LESS? THEN (MAXVALUE) TABLESPACE? Part2_tb?
);
二.列表分區(qū)：
該分區(qū)的特點是某列的值只有幾個，基于這樣的特點我們可以采用列表分區(qū)。
例一
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
例二
CREATE? TABLE? ListTable
(?
??? id??? INT? PRIMARY? KEY ,?
??? name? VARCHAR (20),?
??? area? VARCHAR (10)?
)?
PARTITION? BY? LIST (area)?
(?
??? PARTITION? part1 VALUES ('guangdong','beijing') TABLESPACE? Part1_tb,?
??? PARTITION? part2 VALUES ('shanghai','nanjing')? TABLESPACE? Part2_tb?
);
)
三.散列分區(qū)：
這類分區(qū)是在列值上使用散列算法，以確定將行放入哪個分區(qū)中。當(dāng)列的值沒有合適的條件時，建議使用散列分區(qū)。
散列分區(qū)為通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型，因為通過在I/O設(shè)備上進行散列分區(qū)，使得這些分區(qū)大小一致。
例一：
CREATE TABLE HASH_TABLE?
(?
? COL NUMBER(8),?
? INF VARCHAR2(100)?
)?
PARTITION BY HASH (COL)?
(?
? PARTITION PART01 TABLESPACE HASH_TS01,?
? PARTITION PART02 TABLESPACE HASH_TS02,?
? PARTITION PART03 TABLESPACE HASH_TS03?
)
簡寫：
CREATE TABLE emp
(
??? empno NUMBER (4),
??? ename VARCHAR2 (30),
??? sal?? NUMBER?
)
PARTITION BY? HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
hash分區(qū)最主要的機制是根據(jù)hash算法來計算具體某條紀(jì)錄應(yīng)該插入到哪個分區(qū)中,hash算法中最重要的是hash函數(shù)，Oracle中如果你要使用hash分區(qū)，只需指定分區(qū)的數(shù)量即可。建議分區(qū)的數(shù)量采用2的n次方，這樣可以使得各個分區(qū)間數(shù)據(jù)分布更加均勻。
四.組合范圍散列分區(qū)
這種分區(qū)是基于范圍分區(qū)和列表分區(qū)，表首先按某列進行范圍分區(qū)，然后再按某列進行列表分區(qū)，分區(qū)之中的分區(qū)被稱為子分區(qū)。
CREATE TABLE SALES?
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
?? PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009?
????????? (?
????????????? SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,?
????????????? SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009?
????????? ),?
?? PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009?
????????? (?
????????????? SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,?
????????????? SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009?
????????? )?
)
五.復(fù)合范圍散列分區(qū)：
這種分區(qū)是基于范圍分區(qū)和散列分區(qū)，表首先按某列進行范圍分區(qū)，然后再按某列進行散列分區(qū)。
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_space01,dinya_space02,dinya_space03)?
?(?
???? partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),?
???? partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),?
???? partition part_03 values less than(maxvalue)?
?);
.有關(guān)表分區(qū)的一些維護性操作：?
一、添加分區(qū)?
以下代碼給SALES表添加了一個P3分區(qū)
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
注意：以上添加的分區(qū)界限應(yīng)該高于最后一個分區(qū)界限。
以下代碼給SALES表的P3分區(qū)添加了一個P3SUB1子分區(qū)
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
二、刪除分區(qū)?
以下代碼刪除了P3表分區(qū)：
ALTER TABLE SALES DROP PARTITION P3;
在以下代碼刪除了P4SUB1子分區(qū)：
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
注意：如果刪除的分區(qū)是表中唯一的分區(qū)，那么此分區(qū)將不能被刪除，要想刪除此分區(qū)，必須刪除表。
三、截斷分區(qū)?
截斷某個分區(qū)是指刪除某個分區(qū)中的數(shù)據(jù)，并不會刪除分區(qū)，也不會刪除其它分區(qū)中的數(shù)據(jù)。當(dāng)表中即使只有一個分區(qū)時，也可以截斷該分區(qū)。通過以下代碼截斷分區(qū)：
ALTER TABLE SALES TRUNCATE PARTITION P2;
通過以下代碼截斷子分區(qū)：
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
四、合并分區(qū)?
合并分區(qū)是將相鄰的分區(qū)合并成一個分區(qū)，結(jié)果分區(qū)將采用較高分區(qū)的界限，值得注意的是，不能將分區(qū)合并到界限較低的分區(qū)。以下代碼實現(xiàn)了P1 P2分區(qū)的合并：
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
五、拆分分區(qū)?
拆分分區(qū)將一個分區(qū)拆分兩個新分區(qū)，拆分后原來分區(qū)不再存在。注意不能對HASH類型的分區(qū)進行拆分。
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
六、接合分區(qū)(coalesca)?
結(jié)合分區(qū)是將散列分區(qū)中的數(shù)據(jù)接合到其它分區(qū)中，當(dāng)散列分區(qū)中的數(shù)據(jù)比較大時，可以增加散列分區(qū)，然后進行接合，值得注意的是，接合分區(qū)只能用于散列分區(qū)中。通過以下代碼進行接合分區(qū)：
ALTER TABLE SALES COALESCA PARTITION;
七、重命名表分區(qū)?
以下代碼將P21更改為P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
八、相關(guān)查詢
跨分區(qū)查詢
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)
);
查詢表上有多少分區(qū)
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'
查詢索引信息
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
--顯示數(shù)據(jù)庫所有分區(qū)表的信息：
select * from DBA_PART_TABLES
--顯示當(dāng)前用戶可訪問的所有分區(qū)表信息:
select * from ALL_PART_TABLES
--顯示當(dāng)前用戶所有分區(qū)表的信息：
select * from USER_PART_TABLES
--顯示表分區(qū)信息 顯示數(shù)據(jù)庫所有分區(qū)表的詳細(xì)分區(qū)信息：
select * from DBA_TAB_PARTITIONS
--顯示當(dāng)前用戶可訪問的所有分區(qū)表的詳細(xì)分區(qū)信息：
select * from ALL_TAB_PARTITIONS
--顯示當(dāng)前用戶所有分區(qū)表的詳細(xì)分區(qū)信息：
select * from USER_TAB_PARTITIONS
--顯示子分區(qū)信息 顯示數(shù)據(jù)庫所有組合分區(qū)表的子分區(qū)信息：
select * from DBA_TAB_SUBPARTITIONS
--顯示當(dāng)前用戶可訪問的所有組合分區(qū)表的子分區(qū)信息：
select * from ALL_TAB_SUBPARTITIONS
--顯示當(dāng)前用戶所有組合分區(qū)表的子分區(qū)信息：
select * from USER_TAB_SUBPARTITIONS
--顯示分區(qū)列 顯示數(shù)據(jù)庫所有分區(qū)表的分區(qū)列信息：
select * from DBA_PART_KEY_COLUMNS
--顯示當(dāng)前用戶可訪問的所有分區(qū)表的分區(qū)列信息：
select * from ALL_PART_KEY_COLUMNS
--顯示當(dāng)前用戶所有分區(qū)表的分區(qū)列信息：
select * from USER_PART_KEY_COLUMNS
--顯示子分區(qū)列 顯示數(shù)據(jù)庫所有分區(qū)表的子分區(qū)列信息：
select * from DBA_SUBPART_KEY_COLUMNS
--顯示當(dāng)前用戶可訪問的所有分區(qū)表的子分區(qū)列信息：
select * from ALL_SUBPART_KEY_COLUMNS
--顯示當(dāng)前用戶所有分區(qū)表的子分區(qū)列信息：
select * from USER_SUBPART_KEY_COLUMNS
--怎樣查詢出oracle數(shù)據(jù)庫中所有的的分區(qū)表
select * from user_tables a where a.partitioned='YES'
--刪除一個表的數(shù)據(jù)是
truncate table table_name;
--刪除分區(qū)表一個分區(qū)的數(shù)據(jù)是
alter table table_name truncate partition p5;
原文章見 https://www.cnblogs.com/congcidaishangjiamianju/p/8045804.html
