Oracle學習筆記八 資料表空間

標籤：

資料表空間資料表空間是一個或多個資料檔案的集合，所有的資料對象都存放在指定的資料表空間中，但主要存放的是表， 所以稱作資料表空間 。  分區表當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時就應該考慮對錶進行分區。表進行分區後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個資料表空間(物理檔案上)，這樣查詢資料時，不至於每次都掃描整張表。注意：已經存在的表沒有方法可以直接轉化為分區表   Oracle允許使用者將一個表分成多個分區，使用者可以執行查詢，只訪問表中的特定分區，也將不同的分區儲存在不同的磁碟，提高訪問效能和安全性。表的每個部分都稱為“分區”，使用者可以獨立地備份和恢複每個分區，可以通過引用邏輯表名進行查詢和更新。 Oracle的表資料分割函數通過改善可管理性、效能和可用性，從而為各式應用程式帶來了極大的好處。通常，分區可以使某些查詢以及維護操作的效能大大提高。此外,分區還可以極大簡化常見的管理工作，分區是構建千MB資料系統或超高可用性系統的關鍵工具 資料分割函數能夠將表、索引或索引組織表進一步細分為段，這些資料庫物件的段叫做分區。每個分區有自己的名稱，還可以選擇自己的儲存特性。從資料庫管理員的角 度來看，一個分區後的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理員在管理分區後的對象時有相當大的靈活性。但是，從應用程 序的角度來看，分區後的表與非分區表完全相同，使用 SQL DML 命令訪問分區後的表時，無需任何修改 使用分區的優點：

1、增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；

2、維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；

3、均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能；

4、改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。

什麼時候使用分區表：1、表的大小超過2GB。
2、表中包含曆史資料，新的資料被增加都新的分區中。表分區的類型

定界分割1.以表中的一個列或一組列的值的範圍將行映射到分區2.由表的分區規範進行定義；partition by range(column_list)3.由每個單獨分區的分區規範進行定義：values less than(value_list); 定界分割的文法：

PARTITION BY RANGE (column_name)(  PARTITION part1 VALUES LESS THAN(range1),  PARTITION part2 VALUES LESS THAN(range2),  ...  [PARTITION partN VALUES LESS THAN(MAXVALUE)]);

定界分割樣本

CREATE TABLE Sales(    Product_ID varchar2 (5),    Sales_Cost number (10))PARTITION BY RANGE (Sales_Cost)  --根據 Sales_Cost 建立分區(    PARTITION P1 VALUES LESS THAN (1000),  --包含銷售成本低於1000 的所有產品的值     PARTITION P2 VALUES LESS THAN (2000),    PARTITION P3 VALUES LESS THAN (3000));--P1,P2,P3分區的名稱

 

CREATE TABLE SALES2 (PRODUCT_ID VARCHAR2(5),SALES_DATE DATE NOT NULL,SALES_COST NUMBER(10))PARTITION BY RANGE (SALES_DATE)(  PARTITION P1 VALUES LESS THAN (DATE ‘2003-01-01‘),  PARTITION P2 VALUES LESS THAN (DATE ‘2004-01-01‘),   PARTITION P3 VALUES LESS THAN (MAXVALUE));

 

 散列分區1.允許使用者對不具有邏輯範圍的資料進行分區2.通過在分區鍵上執行HASH函數決定儲存的分區3.將資料平均地分布到不同的分區 散列分區文法

PARTITION BY HASH (column_name)PARTITIONS number_of_partitions;或PARTITION BY HASH (column_name)( PARTITION part1 [TABLESPACE tbs1],  PARTITION part2 [TABLESPACE tbs2],  ...  PARTITION partN [TABLESPACE tbsN]);

 散列分區樣本

CREATE TABLE Employee(    Employee_ID varchar2 (5),    Employee_Name varchar2(20),    Department varchar2 (10))PARTITION BY HASH (Department)  --在表 Employee上建立分區鍵 Department(    --建立 3 個分區    Partition D1,    Partition D2,    Partition D3); --D1,D2,D3分區的名稱

 

CREATE TABLE EMPLOYEE(    EMP_ID NUMBER(4),    EMP_NAME VARCHAR2(14),    EMP_ADDRESS VARCHAR2(15),    DEPARTMENT VARCHAR2(10))PARTITION BY HASH (DEPARTMENT)PARTITIONS 4; 

 

 列表分區1.允許使用者將不相關的資料群組織在一起  列表分區的文法 

PARTITION BY LIST (column_name)(  PARTITION part1 VALUES (values_list1),  PARTITION part2 VALUES (values_list2),  ...  PARTITION partN VALUES (DEFAULT));

 列表分區樣本

CREATE TABLE Employee(    Emp_ID number (4),    Emp_Name varchar2 (14),    Emp_Address varchar2 (15))--根據職員住址在表上建立的列表分區PARTITION BY LIST (Emp_Address) (    Partition north values (‘芝加哥‘),  --包含住在芝加哥的職員的記錄    Partition west values (‘舊金山’, ‘洛杉磯‘),    Partition south values (‘亞特蘭大‘, ‘達拉斯‘, ‘休斯頓‘),    Partition east values (‘紐約‘, ‘波斯頓‘));--north,west......分區的名稱

 

複合分區1.定界分割與散列分區或列表分區的組合 複合分區的文法： 

PARTITION BY RANGE (column_name1)SUBPARTITION BY HASH (column_name2)SUBPARTITIONS number_of_partitions(  PARTITION part1 VALUE LESS THAN(range1),  PARTITION part2 VALUE LESS THAN(range2),  ...  PARTITION partN VALUE LESS THAN(MAXVALUE));

複合分區樣本 

SQL> CREATE TABLE SALES(    PRODUCT_ID VARCHAR2 (5),    SALES_DATE DATE NOT NULL,    SALES_COST NUMBER (10))PARTITION BY RANGE (SALES_DATE)  --在表的 Sales_Date 列中建立定界分割SUBPARTITION BY HASH (PRODUCT_ID)  --在表的 Product_ID 列建立散列子分區SUBPARTITIONS 5    --在每個定界分割中建立 5 個散列子分區(    PARTITION S1 VALUES LESS THAN (TO_DATE(‘01/4月/2001‘, ‘DD/MON/YYYY‘)),    PARTITION S2 VALUES LESS THAN (TO_DATE(‘01/7月/2001‘, ‘DD/MON/YYYY‘)),    PARTITION S3 VALUES LESS THAN (TO_DATE(‘01/9月/2001‘, ‘DD/MON/YYYY‘)),    PARTITION S4 VALUES LESS THAN (MAXVALUE));--S1,S2,S3建立的四個定界分割的名稱

 

 

操縱已分區的表在已分區的表中插入資料與操作普通表完全相同，Oracle會自動將資料儲存到對應的分區。查詢、修改和刪除分區表時可以顯式指定要操作的分區。

INSERT INTO SALES3 VALUES (‘P001‘, ‘02-3月-2001‘, 2000);INSERT INTO SALES3 VALUES (‘P002‘, ‘10-5月-2001‘, 2508);INSERT INTO SALES3 VALUES (‘P003‘, ‘05-7月-2001‘, 780);INSERT INTO SALES3 VALUES (‘P004‘, ‘12-9月-2001‘, 1080);SELECT * FROM SALES3 PARTITION (P3);DELETE FROM SALES3 PARTITION (P2);

 

分區維護操作查詢表上有多少分區

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME=‘tableName‘ 

分區維護操作修改資料分割資料表的分區。分區維護的類型：1.計劃事件 － 定期刪除最舊的分區2.非計劃事件 － 解決應用程式或系統問題 分區維護操作有：1.添加分區2.刪除分區3.截斷分區4.合并分區5.拆分分區 添加分區 – 在最後一個分區之後添加新分區

ALTER TABLE SALES     ADD PARTITION P4 VALUES LESS THAN (4000);

 

刪除分區 – 刪除一個指定的分區，分區的資料也隨之刪除

ALTER TABLE SALES DROP PARTITION P4;

 

截斷分區 – 刪除指定分區中的所有記錄

ALTER TABLE SALES TRUNCATE PARTITION P3;

 

合并分區 - 將定界分割或複合分區的兩個相鄰分區串連起來

ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;

 

拆分分區 - 將一個大分區中的記錄拆分到兩個分區中 

ALTER TABLE SALES SPLIT PARTITION P2 AT (1500) INTO (PARTITION P21, PARTITION P22);

 

 

Oracle學習筆記八 資料表空間