Oracle學習筆記八 表空間，oracle學習筆記空間

Oracle學習筆記八 表空間，oracle學習筆記空間

表空間

表空間是一個或多個數據文件的集合，所有的數據對象都存放在指定的表空間中，但主要存放的是表， 所以稱作表空間 。  

分區表

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