Oracle 11g筆記——分區表

標籤：

一、分區表
分區技術，Oracle允許把一個大表分成幾部分，每部分叫一個分區，然後把每個部分放在不同的物理磁碟，以提高整個資料庫的效能。
每個分區還可以再分成幾份，這樣產生的分區叫子分區（Subpartition）。分區表邏輯上還是一個整體。
1、優點：
  （1）分區技術使資料庫的可管理性變得更加容易，
       如：使用者可以往一個單獨的分區中裝載資料，而對其它分區沒有任何影響；使用者可以在一個單獨的分區上建立索引。
  （2）分區可以提高表的查詢效能，SQL語句的WHERE子句會過濾掉不需要的分區，Oracle不會再掃描那些不需要的分區。
  （3）分區技術減少資料的不可用時間，使用者可以單獨地維護一個分區中的資料，而不影響其它分區中資料的使用。
  （4）分區技術在資料庫級完成，幾乎不需要對應用程式做任何修改。

2、分類：
  （1）定界分割
       根據表中列值的範圍將整個表分成不同的部分，如：按照時間進行定界分割
  （2）列表分區
       使用列值將表劃分成幾部分
  （3）雜湊分割：作用雜湊函數把表分成幾部分
  （4）複合分區：同時使用兩種分區方法對錶進行分區

3、建立樣本
  （1）定界分割表
       SQL>create table sales(invoice_no number,sale_year int not null,sale_month int not null,sale_day int not null)
           partition by range (sale_year)
           (partition p1 values less than (2000) tablespace USERS,
            partition p2 values less than (2001) tablespace LMTBSB,
            partition p3 values less than (2002) tablespace BIGTBS_01,
            partition p4 values less than (2003) tablespace TSSEG_MANUAL);

  （2）雜湊分割（根據雜湊值把表分成幾個分區。法一：指定分區數量；法二、指定分區名字）
       a.指定分區數量，關鍵字partitions指定分區的數量
         SQL>create table dept(deptno number,deptname varchar(32))
             partition by hash(deptno) partitions 4;
             指定分區數為4，所有分區都放在相同的資料表空間中。
       b.按指定分區的名字進行分區
         SQL>create table dept(deptno number,deptname varchar(32))
             partition by hash(deptno)
             (partition p1 tablespace USERS,
              partition p2 tablespace LMTBSB,
              partition p3 tablespace BIGTBS_01,
              partition p4 tablespace TSSEG_MANUAL);
     
  （3）列表分區
       SQL>create table sales(item integer,qty integer,store_name varchar(30),city varchar2(20),sale_date date)
           partition by list (city)
           (partition region_east values(‘杭州‘,‘上海‘) tablespace USERS,
            partition region_west values(‘成都‘,‘重慶‘) tablespace LMTBSB,
            partition region_south values(‘廣州‘,‘桂林‘) tablespace BIGTBS_01);

4、其它樣本
  （1）得到一個分區表的所有子分區
       SQL>select table_name,partition_name,subpartition_name,tablespace_name
           from dba_tab_subpartitions
           where table_name=‘COMPOSITE_SALES‘
           order by table_name,partition_name
  （2）將表的一個分區從一個資料表空間移動至另外一個資料表空間
       a.確認分區SP1在哪個資料表空間
           SQL>select table_name,partition_name,subpartition_name,tablespace_name
           from dba_tab_subpartitions
           where table_name=‘sales‘ and table_owner=‘test‘
           查詢得知：SP1在P1資料表空間中
       b.將分區SP1移至資料表空間P
           SQL>alter table sales move partition sp1 tablespace TP;
       c.移動分區，會使索引無效，需要重建索引，如：
           SQL>alter index sales3_pk rebuild;
  （3）查看一個表是不是資料表空間
       SQL>select owner,table_name,partitioned from dba_tables where owner=‘test‘ and table_name=‘sales3‘;
       查看使用者test的表sales3是不是分區表。
       若partitioned=yes表示這個表是分區表
  （4）得到建立分區的SQL語句
       SQL>select dbms_metadata.get_ddl (‘TABLE‘,‘sales3‘,‘test‘) from dual;
       新增一個定界分割，註：新增的分區邊界值必須大於已存大的分區邊界
       SQL>alter table sales3 add partition p1999 values less then (1999);
  （5）合并分區，註：合并以後的分區的名字不能是邊界值較低的那個分區的名字，但可以是另外一個分區的名字
       SQL>alter table sales3 merge partitions sp1,sp3 into partitions sp3    
  （6）分區交換
       SQL>alter table sales3 exchange partition sp1 with table tar_sales;
       或：
       SQL>alter table sales3 exchange partition sp3 with table tar_sales including indexes without validation;
       註：進行分區交換時，分區表上有索引，目標表上也應有索引
       查看索引IND_MAP由哪些列組成：
       SQL>select index_owner,index_name,table_name,column_name from dba_ind_columns where index_owner=‘test‘ and index_name=‘IND_MAP‘; 

 

Oracle 11g筆記——分區表