oracle --(二) 分區（extent）

標籤：關係   gic   script   obj   less   table   read   drop   header   

基本關係：資料庫---資料表空間---資料區段---分區---資料區塊


一、分區（extent）
分區extent是比資料區塊大一級的儲存結構，是幾個邏輯上相鄰的data block的組合。我們知道，實體儲存體通常是隨機的讀寫過程。即使在同一個檔案裡，我們也不能保證相同的一個資訊是儲存在絕對連續的實體儲存體空間的。Oracle資料存放區同樣如此。

分區extent是磁碟空間分配的最小單位。磁碟按區劃分，每次至少分配一個區。區儲存於段中，它由連續的資料區塊組成。區的分配過程中，每次至分配5個區。如果所剩的空閑空間不夠5個區，就會出現錯誤：ORA-01653。

在進行儲存資料資訊的時候，Oracle將分配資料區塊進行儲存，但是不能保證所有分配的資料區塊都是連續的結構。所以，出現分區extent的概念，表示一系列連續的資料區塊集合。

SQL>  desc dba_extents;
Name           　　 　　　　Type          Nullable 　　  Default Comments                                                  
--------------- ------------- -------- ------- ---------------------------------------------------------
OWNER          　　    VARCHAR2(128)    Y                Owner of the segment associated with the extent           
SEGMENT_NAME     VARCHAR2(128)    Y                Name of the segment associated with the extent            
PARTITION_NAME    VARCHAR2(128)    Y                Partition/Subpartition Name, if any, of the segment       
SEGMENT_TYPE      VARCHAR2(18)     Y                Type of the segment                                       
TABLESPACE_NAME VARCHAR2(30)    Y                Name of the tablespace containing the extent              
EXTENT_ID     　　   NUMBER       　　  Y                Extent number in the segment                              
FILE_ID         　　　　NUMBER      　　  Y                Name of the file containing the extent                    
BLOCK_ID      　　  NUMBER       　　   Y                Starting block number of the extent                       
BYTES         　　      NUMBER      　　   Y                Size of the extent in bytes                               
BLOCKS         　  　 NUMBER       　　  Y                Size of the extent in ORACLE blocks                       
RELATIVE_FNO       NUMBER       　　  Y                Relative number of the file containing the segment header

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

三、表分區有以下優點：
1、改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。
2、增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；
3、維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；
4、均衡i/o：可以把不同的分區映射到磁碟以平衡i/o，改善整個系統效能。



四、表分區的幾種類型及操作方法
 一.定界分割：
定界分割將資料基於範圍映射到每一個分區，這個範圍是你在建立分區時指定的分區鍵決定的。這種分區方式是最為常用的，並且分區鍵經常採用日期。
當使用定界分割時，請考慮以下幾個規則：
1、每一個分區都必須有一個values less then子句，它指定了一個不包括在該分區中的上限值。分區鍵的任何值等於或者大於這個上限值的記錄都會被加入到下一個高一些的分區中。
2、所有分區，除了第一個，都會有一個隱式的下限值，這個值就是此分區的前一個分區的上限值。
3、在最高的分區中，maxvalue被定義。maxvalue代表了一個不確定的值。這個值高於其它分區中的任何分區鍵的值，也可以理解為高於任何分區中指定的value less then的值，同時包括空值。

 

假設有一個customer表，表中有資料200000行，我們將此表進行分區，每個分區儲存100000行，我們將每個分區儲存到單獨的資料表空間中，這樣資料檔案就可以跨越多個物理磁碟。下面是建立表和分區的代碼，如下：
例一：按customer_id進行分區
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
)
例二：按時間劃分
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
)
例三：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
);
 

二.列表分區：
列表分區明確指定了根據某欄位的某個具體值進行分區，而不是像定界分割那樣根據欄位的值範圍來劃分的。基於這樣的特點我們可以採用列表分區。
例一
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
);
)
 

三.散列分區：
這類分區是在列值上使用散列演算法，以確定將行放入哪個分區中。當列的值沒有合適的條件時，建議使用散列分區。
散列分區為通過指定分區編號來均勻分布資料的一種分區類型，因為通過在i/o裝置上進行散列分區，使得這些分區大小一致。
例一：
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分區最主要的機制是根據hash演算法來計算具體某條紀錄應該插入到哪個分區中,hash演算法中最重要的是hash函數，oracle中如果你要使用hash分區，只需指定分區的數量即可。建議分區的數量採用2的n次方，這樣可以使得各個分區間資料分布更加均勻。
 

四.組合範圍散列分區
這種分區是基於定界分割和列表分區，表首先按某列進行定界分割，然後再按某列進行列表分區，分區之中的分區被稱為子分區。
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
          )
)
 

五.複合範圍散列分區：
這種分區是基於定界分割和散列分區，表首先按某列進行定界分割，然後再按某列進行散列分區。
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)
 );

 

六、有關表分區的一些維護性操作：
一、添加分區
以下代碼給sales表添加了一個p3分區
alter table sales add partition p3 values less than(to_date(‘2003-06-01‘,‘yyyy-mm-dd‘));
注意：以上添加的分區界限應該高於最後一個分區界限。
以下代碼給sales表的p3分區添加了一個p3sub1子分區
alter table sales modify partition p3 add subpartition p3sub1 values(‘complete‘);

二、刪除分區
以下代碼刪除了p3表分區：
alter table sales drop partition p3;
在以下代碼刪除了p4sub1子分區：
alter table sales drop subpartition p4sub1;
注意：如果刪除的分區是表中唯一的分區，那麼此分區將不能被刪除，要想刪除此分區，必須刪除表。

三、截斷分區
截斷某個分區是指刪除某個分區中的資料，並不會刪除分區，也不會刪除其它分區中的資料。當表中即使只有一個分區時，也可以截斷該分區。通過以下代碼截斷分區：
alter table sales truncate partition p2;
通過以下代碼截斷子分區：
alter table sales truncate subpartition p2sub2;

四、合并分區
合并分區是將相鄰的分區合并成一個分區，結果分區將採用較高分區的界限，值得注意的是，不能將分區合并到界限較低的分區。以下代碼實現了p1 p2分區的合并：
alter table sales merge partitions p1,p2 into partition p2;

五、拆分分區
拆分分區將一個分區拆分兩個新分區，拆分後原來分區不再存在。注意不能對hash類型的分區進行拆分。
alter table sales sblit partition p2 at(to_date(‘2003-02-01‘,‘yyyy-mm-dd‘)) into (partition p21,partition p22);

六、接合分區(coalesca)
結合分區是將散列分區中的資料接合到其它分區中，當散列分區中的資料比較大時，可以增加散列分區，然後進行接合，值得注意的是，接合分區只能用於散列分區中。通過以下代碼進行接合分區：
alter table sales coalesca partition;

七、重新命名表分區
以下代碼將p21更改為p2
alter table sales rename partition p21 to p2;

八、相關查詢
跨分區查詢
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)
);

查詢表上有多少分區
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
 
--顯示資料庫所有分區表的資訊：
select * from dba_part_tables
 
--顯示目前使用者可訪問的所有分區表資訊:
select * from all_part_tables
 
--顯示目前使用者所有分區表的資訊：
select * from user_part_tables
 
--顯示表分區資訊 顯示資料庫所有分區表的詳細分區資訊：
select * from dba_tab_partitions
 
--顯示目前使用者可訪問的所有分區表的詳細分區資訊：
select * from all_tab_partitions
 
--顯示目前使用者所有分區表的詳細分區資訊：
select * from user_tab_partitions
 
--顯示子分區資訊 顯示資料庫所有組合分區表的子分區資訊：
select * from dba_tab_subpartitions
 
--顯示目前使用者可訪問的所有組合分區表的子分區資訊：
select * from all_tab_subpartitions
 
--顯示目前使用者所有組合分區表的子分區資訊：
select * from user_tab_subpartitions
 
--顯示分區列 顯示資料庫所有分區表的分區列資訊：
select * from dba_part_key_columns
 
--顯示目前使用者可訪問的所有分區表的分區列資訊：
select * from all_part_key_columns
 
--顯示目前使用者所有分區表的分區列資訊：
select * from user_part_key_columns
 
--顯示子分區列 顯示資料庫所有分區表的子分區列資訊：
select * from dba_subpart_key_columns
 
--顯示目前使用者可訪問的所有分區表的子分區列資訊：
select * from all_subpart_key_columns
 
--顯示目前使用者所有分區表的子分區列資訊：
select * from user_subpart_key_columns
 
--怎樣查詢出oracle資料庫中所有的的分區表
select * from user_tables a where a.partitioned=‘yes‘
 
--刪除一個表的資料是
truncate table table_name;
 
--刪除分區表一個分區的資料是
alter table table_name truncate partition p5;

oracle --(二) 分區（extent）