??? 今天國慶節，愿大家玩得開心，好好放松！
??? 我也想好好放松，到各地旅游，到世界每個地方...
??? 但...
??? ...繼續努力！！！
???
不多說題外話了，進入今天主題，如題，今天對Oracle中表分區機制作個總結。
?? Oracle中提供了對表進行分區的機制，通過表分區，可以將表空間中數據按照某種方式分別存放到特定的分區中。
表分區的作用：平衡
IO
操作，分區均勻，提高效率。
?? Oracle中表分區方法有：范圍分區法、散列分區法、復合分區法、列表分區法。
?范圍分區:
語法
Partition? by? range
();
適合數值型或日期型
?
示例：
?1?create?table
?Student
?2
(
?3?????Studentid?integer?not?null
,
?4?????Studentname?varchar2(20
),
?5?????Score?integer
?6
)
?7?Partition?by
?range(Score)
?8
(
?9?????Partition?p1?values?less?than(60
),
10?????Partition?p2?values?less?than(75
),
11?????Partition?p3?values?less?than(85
),
12?????Partition?p4?values
?less?than(maxvalue)
13 );
? 散列分區法：
根據Oracle內部散列算法存儲，語法 Partition by hash();
?
實例：
?1?create?table
?department
?2?
(
?3?????Deptno?int
,
?4?????Deptname?varchar2(24
)
?5?
)
?6?Partition?by
?hash(deptno)
?7?
(
?8?
????Partition?p1,
?9?
????Partition?p2
10?);
? 復合分區法：由上面兩種方法復合而成
? 示例：
?1?create?table
?salgrade
?2?
(
?3?????grade?number
,
?4?????losal?number
,
?5?????hisal?number
?6?
)
?7?Partition?by
?range(grade)
?8?Subpartition?by
?hash(losal,hisal)
?9?
(
10?????Partition?p1?values?less?than(10
),
11?
??????(subpartition?sp1,subpartition?sp2),
12?????Partition?p2?values?less?than(20
),
13?
??????(subpartition?sp3,subpartition?sp4)
14?)
?
列表分區法：
適合字符型 語法
Partition?
by
?list()
? 實例：
?1?create?table
?customer
?2?
(
?3?????custNo?int
,
?4?????custname?varchar(20
),
?5?????custState?varchar(20
)
?6?
)
?7?Partition?by
?list(custState)
?8?
(
?9?????Partition?saia?values('中國','韓國','日本'
),
10?????Partition?Europe?values('英國','俄國','法國'
),
11?????Partition?ameria?values('美國','加拿大','墨西哥'
),
12?
);
13?????
表分區維護：
添加分區:alter table student add partition p5 values less than(120);
刪除分區:alter table student drop partition p4;
截斷分區:alter table student truncate partition p5;
合并分區:alter table student merge partitions p3,p4 into partition p6;
? (轉載請注明出處)?
?
鳳凰涅槃/浴火重生/馬不停蹄/只爭朝夕
???? 隱姓埋名/低調華麗/簡單生活/完美人生