文章目錄
- 分區
-
- 1 範圍分區(range)
- 2 HASH列分區
- 3 清單分區
- 分區表添加子分區
- 4 interval分區
- 5 添加分區
- 6 并入表分區
- 7 建立本地索引分區
- 8 索引分區維護
- 9 索引分區重命名
通過PL/SQL developer工具檢視表空間的情況
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuUDO4ATNzcTMxADNwAjMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
建立兩個表空間,參考 《Oracle建立表空間和表》
SQL> create TABLESPACE TBSP_1 DATAFILE 'D:\app\Administrator\oradata\oracle\TBSP_1.dbf' SIZE 10M;
表空間已建立。
SQL> CREATE TABLESPACE TBSP_2 DATAFILE 'D:\app\Administrator\oradata\oracle\TBSP_2.dbf' SIZE 10M;
表空間已建立。
檢視建立好的表空間
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 TBSP_1 YES NO YES
7 TBSP_2 YES NO YES
分區
- 減少維護工作量
- 增強資料庫可用性
- 均衡I/O
- 分區對使用者透明,對其存在無感覺
- 提高查詢速度
1 範圍分區(range)
- 資料根據分區鍵的範圍進行分布
例如:日期分區鍵–“08-2019”就會包括從“01-08-2019”到“31-08-2019”之間的所有分區鍵值。
執行個體:
建立一個商品零售表,根據銷售日期建立四個範圍分區。
create table ware_retail_part(
id integer primary key,
retail_date date,
ware_name varchar2(50)
)
partition by range(retail_date)
(
partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBSP_1,
partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBSP_1,
partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBSP_2,
partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBSP_2
)
建立好後,插入若幹條記錄:
SQL> insert into ware_retail_part values(1,to_date('2011-01-20','yyyy-mm-dd'),'平闆電 腦');
SQL> insert into ware_retail_part values(2,to_date('2011-04-15','yyyy-mm-dd'),'智能手 機');
SQL> insert into ware_retail_part values(3,to_date('2011-07-25','yyyy-mm-dd'),'MP5');
檢視分區和表格:
SQL> select * from ware_retail_part partition(par_02);
ID RETAIL_DATE WARE_NAME
---------- -------------- --------------------------------------------------
2 15-4月 -11 智能手 機
SQL> select * from ware_retail_part;
ID RETAIL_DATE WARE_NAME
---------- -------------- --------------------------------------------------
1 20-1月 -11 平闆電 腦
2 15-4月 -11 智能手 機
3 25-7月 -11 MP5
如果是多範圍分區,比如根據銷售編号和銷售日期的組合建立三個分區:
參考《oracle 建立分區表以及自動添加分區》
SQL> create table ware_retail_part2(
2 id integer primary key,
3 retail_no integer,
4 retail_date date,
5 ware_name varchar2(50)
6 )
7 partition by range(retail_date,retail_no)
8 (
9 partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd'),5) tablespace TBSP_1,
10 partition par_02 values less than(to_date('2011-08-01','yyyy-mm-dd'),10) tablespace TBSP_1,
11 partition par_03 values less than(to_date('2011-12-01','yyyy-mm-dd'),15) tablespace TBSP_2
12 )
13 /
SQL> insert into ware_retail_part2 values(2,12,to_date('2011-10-25','yyyy-mm-dd'),'MP5');
SQL> select * from ware_retail_part2 partition(par_03);
ID RETAIL_NO RETAIL_DATE WARE_NAME
---------- ---------- -------------- ----------------------
2 12 25-10月-11 MP5
2 HASH列分區
即散列分區,列值無法确定時采用。
執行個體:
建立一個person表,為該表建立hash分區(分區列為id)
SQL> create table person
2 (
3 id number,
4 personname varchar2(50)
5 )
6 storage(initial 2084k)
7 partition by hash(id)
8 (
9 partition part1 tablespace tbsp_1,
10 partition part2 tablespace tbsp_2
11 );
表已建立。
SQL> insert into person values(55,'wj');
已建立 1 行。
SQL> select * from person partition(part1);
ID PERSONNAME
---------- --------------------------------------------------
55 wj
3 清單分區
建立一個用于儲存客戶資訊的clients,然後以PROVINCE列為分區鍵建立列分區;
SQL> l
1 create table clients
2 (
3 id integer primary key,
4 name varchar2(50),
5 province varchar2(20)
6 )
7 partition by list(province)
8 (
9 partition shandong values('山東省'),
10 partition guangdong values('廣東省'),
11 partition yunnan values('雲南省')
12* )
SQL> /
表已建立。
SQL> insert into clients values (19,'東方','雲南省');
已建立 1 行。
SQL> select * from clients partition(yunnan);
ID NAME PROVINCE
---------- -------------------------------------------------- --------------------
19 東方 雲南省
清單分區這裡有一個小測試可以自我簡單測試下:連結🔗
分區表添加子分區
參考《對分區表添加子分區》
建立一個儲存人員資訊的資料表PERSON2,然後建立三個範圍分區,每個分區包含兩個子分區,子分區沒有名字,有系統自動生成,并要求其分布在2個制定的表空間中。
create table persons2(
id integer primary key,
personname varchar2(50)
)
partition by range(id)
subpartition by hash(personname)
SUBPARTITIONS 2
(
partition part_1 values less than(5) tablespace tbsp_1,
partition part_2 values less than(10) tablespace tbsp_1,
partition part_3 values less than(15) tablespace tbsp_2
);
4 interval分區
建立的分區作為中繼資料,隻有最開始的分區是永久分區,随着資料增加會配置設定更多,并自動建立新的分區和本地索引。
執行個體:
建立一個表saleRecord,然後為該表建立一個Interval分區
SQL> create table saleRecord
2 (
3 id number primary key,
4 goodsname varchar2(50),
5 saledate date,
6 quantity number
7 )
8 partition by range(saledate)
9 interval (numtoyminterval(1,'year'))
10 (
11 partition par_first values less than (to_date('2020-01-01','yyyy-mm-dd'))
12 );
表已建立。
SQL> insert into saleRecord values(1,'MP5',sysdate,123);
SQL> select TABLE_NAME, PARTITION_NAME from user_tab_partitions where table_name='SALERECORD';
#檢視表中包含的分區
TABLE_NAME PARTITION_NAME
----------- ------------------------------
SALERECORD PAR_FIRST
SALERECORD SYS_P47
SQL> select * from saleRecord partition(par_first);
未標明行
SQL> select * from saleRecord partition(SYS_P47);
ID GOODSNAME SALEDATE QUANTITY
---------- -------------------------------------------------- -------------- ----------
1 MP5 02-4月 -20 123
5 添加分區
執行個體:
為cilents添加一個省分為“河北省”的表分區
SQL> alter table clients
2 add partition hebei values('河北省')
3 storage(initial 10K next 20k)tablespace tbsp_1
4 nologging;
表已更改。
SQL> select partition_name from user_tab_partitions where table_name ='CLIENTS';
PARTITION_NAME
------------------------------
SHANDONG
GUANGDONG
YUNNAN
HEBEI
6 并入表分區
- MERGE PARTITION語句,将相鄰的範圍分區合并在一起,變成新的分區;
- 合并分區為空,則辨別為UNSABLE;
- 不能對HASH分區表執行MERGE PARTITION語句;
- 并入範圍分區是将兩個以上的分區合并到一個存在的分區中,合并後索引需重建
執行個體:
建立一個銷售記錄表sales,然後歲該表的記錄按照銷售日期分為四個分區;再建立局部索引
SQL> create table sales
2 (
3 id number primary key,
4 goodsname varchar2(10),
5 saledate date
6 )
7 partition by range(saledate)
8 (
9 partition part_seal values less than (to_date('2011-04-01','yyyy-mm-dd')) tablespace tbsp_1,
10 partition part_sea2 values less than (to_date('2011-07-01','yyyy-mm-dd')) tablespace tbsp_2,
11 partition part_sea3 values less than (to_date('2011-10-01','yyyy-mm-dd')) tablespace tbsp_1,
12 partition part_sea4 values less than (to_date('2012-01-01','yyyy-mm-dd')) tablespace tbsp_2
13 )
14 /
表已建立。
SQL> create index index_3_4 on sales(saledate)
2 local
3 (
4 partition part_sea1 tablespace tbsp_1,
5 partition part_sea2 tablespace tbsp_2,
6 partition part_sea3 tablespace tbsp_1,
7 partition part_sea4 tablespace tbsp_2
8 );
索引已建立。
将第三個分區并入到第四個分區中,并重建局部索引:
# 這裡的merge partitions記得加s
SQL> alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;
表已更改。
SQL> alter table sales modify partition part_sea4 rebuild unusable local indexes;
表已更改。
SQL> select partition_name from user_tab_partitions where table_name ='SALES';
PARTITION_NAME
------------------------------
PART_SEAL
PART_SEA2
PART_SEA4
如果是将第二個和第三個分區并入到第四個分區中,可以合并兩次:
SQL> alter table sales2 merge partitions part_sea2,part_sea3 into partition part_sea3;
表已更改。
SQL> alter table sales2 merge partitions part_sea3,part_sea4 into partition part_sea4;
表已更改。
SQL> select partition_name from user_tab_partitions where table_name ='SALES2';
PARTITION_NAME
------------------------------
PART_SEAL
PART_SEA4
7 建立本地索引分區
本地索引分區與該表分區所采用的列是相同的。
1 準備表空間:
SQL> create TABLESPACE ts_1 DATAFILE 'D:\app\Administrator\oradata\oracle\ts_1.dbf' SIZE 10M extent management local autoallocate;
表空間已建立。
SQL> create TABLESPACE ts_2 DATAFILE 'D:\app\Administrator\oradata\oracle\ts_2.dbf' SIZE 10M extent management local autoallocate;
表空間已建立。
SQL> create TABLESPACE ts_3 DATAFILE 'D:\app\Administrator\oradata\oracle\ts_3.dbf' SIZE 10M extent management local autoallocate;
表空間已建立。
2 建立一個存儲學生成績的分區表studentgrade,該表有三個分區,分别位于三個不同表空間:
SQL> create table studentgrade
2 (
3 id number primary key,
4 name varchar2(10),
5 subject varchar2(10),
6 grade number
7 )
8 partition by range(grade)
9 (
#小于60分,不及格
10 partition par_nopass values less than(60) tablespace ts_1,
#小于70分,及格
11 partition par_pass values less than(70) tablespace ts_2,
#大于等于70分,優秀
12 partition par_good values less than(maxvalue) tablespace ts_3
13 );
表已建立。
3 根據表分區建立本地索引分區
SQL> create index grade_index on studentgrade(grade)
2 local
3 (
4 partition p1 tablespace ts_1,
5 partition p2 tablespace ts_2,
6 partition p3 tablespace ts_3
7 );
索引已建立。
4 根據dba_ind_partitions檢視索引分區資訊
SQL> select partition_name,tablespace_name from dba_ind_partitions where index_name ='GRADE_INDEX';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P3 TS_3
P2 TS_2
P1 TS_1
8 索引分區維護
SQL> create table books2(
2 BooksNo number(4) not null,
3 BookName varchar2(20),
4 Author varchar2(10),
5 SalePrice number(9,2),
6 PublisherNo varchar2(4) not null,
7 PublisherDate date,
8 ISBN varchar2(20) not null
9 );
表已建立。
SQL>
SQL> create index index_salesprice on Books2(SalePrice)
2 global partition by range(SalePrice)
3 (
4 partition p1 values less than (30),
5 partition p2 values less than (50),
6 partition p3 values less than (maxvalue)
7 );
索引已建立。
SQL> alter index index_salesprice drop partition p2;
索引已更改。
SQL> alter index index_salesprice drop partition p1;
索引已更改。
全局索引分區,不能删除索引的最高分區,否則系統報錯
SQL> alter index index_salesprice drop partition p3;
alter index index_salesprice drop partition p3
*
第 1 行出現錯誤:
ORA-14078: 您不能删除 GLOBAL 索引的最高分區