目錄
一、簡介:
二、分區功能的基本知識 :
三、Oracle 数据库 10g 还提供了三种类型的分区索引:
四、Oracle 提供了一系列丰富的技术,
五、有关表分区的一些维护性操作:
首頁 資料庫 Oracle 完全掌握Oracle分區

完全掌握Oracle分區

Mar 16, 2022 pm 05:41 PM
oracle

這篇文章為大家帶來了關於Oracle的相關知識,其中主要介紹了分區的相關問題,透過分區功能,可以將表、索引和索引組織表進一步細分為段,從而能夠更精確地管理和存取這些資料庫對象,希望對大家有幫助。

完全掌握Oracle分區

推薦教學:《Oracle教學

一、簡介:

Oracle 分割區功能可以提升許多應用程式的可管理性、效能與可用性。透過分區功能,可以將資料表、索引和索引組織表進一步細分為段,從而能夠更精確地管理和存取這些資料庫物件。 Oracle 提供了種類繁多的分區方案以滿足每種業務要求。而且,因為在 SQL 語句中分區是完全透明的,所以該功能幾乎可應用於任何應用程式。

分區功能的優勢:分區功能透過改善可管理性、效能和可用性,從而為各式應用程式帶來了極大的好處。通常,分區可以使某些查詢以及維護操作的效能大大提高。此 外,分區還可以大幅簡化常見的管理任務。透過分區,資料庫設計人員和管理員能夠解決前沿應用程式帶來的一些難題。分割區是建構千兆位元組資料系統或超高可用性系統的關鍵工具。

二、分區功能的基本知識 :

分區功能能夠將表、索引或索引組織表進一步細分為區段。這些資料庫物件的段叫做分區。每個分割區有自己的名稱,也可以選擇自己的儲存特性。從資料庫管理員的角度來看,一個分區後的物件具有多個段,這些段既可進行集體管理,也可單獨管理。這就使資料庫管理員在管理分割區後的物件時有相當大的彈性。但是,從應用程式的角度來看,分區後的表與非分區表完全相同,使用 SQL DML 命令存取分區後的表時,無需任何修改。表的分區是透過「分區鍵」來實現的,分區鍵指的是一些列,這些列決定了某一行所在的分區。 Oracle 資料庫10g 提供了幾項技術用於對錶進行分區:

1、範圍分區(range):每個分區都由一個分區鍵值範圍指定(對於一個以日期列作為分區鍵的表,「2005 年1 月」分區包含分區鍵值為從「2005 年1 月1 日」到「2005 年1 月31 日」的行)。

當使用範圍分割區時,請考慮以下幾個規則:

1)每一個分割區都必須有一個VALUES LESS THEN子句,它指定了一個不包含在該分割區中的上限值。分區鍵的任何值等於或大於這個上限值的記錄都會被加入到下一個高一些的分區。

2)所有分割區,除了第一個,都會有一個隱式的下限值,這個值就是此分割區的前一個分割區的上限值。

3)在最高的分割區中,MAXVALUE被定義。 MAXVALUE代表了一個不確定的值。這個值高於其它分區中的任何分區鍵的值,也可以理解為高於任何分區中指定的VALUE LESS THEN的值,同時包含空值。

例一:

假設有一個CUSTOMER表,表中有資料200000行,我們將此表透過CUSTOMER_ID進行分區,每個分區儲存100000行,我們將每個分割區保存到單獨的表空間中,這樣資料檔案就可以跨越多個實體磁碟。以下是建立表格和分割區的程式碼,如下:

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
);
登入後複製

2、清單分割(list):每個分區都由一個分區鍵值列表指定(對於一個地區列作為分區鍵的表,“北美”分區可能包含值“加拿大”“美國”和“墨西哥”)。這個分區的特徵是某一列的值只有幾個,基於這樣的特點我們可以採用列表分區。

例一

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
);
)
登入後複製

#3、雜湊分割(hash):

將雜湊演算法用於分區鍵來決定指定行所在的分區。這類分區是在列值上使用雜湊演算法,以確定將行放入哪個分區中。當列的值沒有合適的條件時,建議使用雜湊分區。散列分區為透過指定分區編號來均勻分佈資料的一種分區類型,因為透過在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次方,這樣可以使得各分區間資料分佈更加均勻。

4、組合範圍雜湊分區(range-hash):

范围和散列分区技术的组合,通过该组合,首先对表进行范围分区,然后针对每个单独的范围分区再使用散列分区技术进一步细分。索引组织表只能进行范围分区。

这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区,分区之中的分区被称为子分区。

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
)
)
登入後複製

5、组合范围列表分区(range-list):

范围和列表分区技术的组合,通过该组合,首先对表进行范围分区,然后针对每个单独的范围分区再使用列表分区技术进一步细分。索引组织表可以按范围、列表或散列进行分区。

这种分区是基于范围分区和散列分区,表首先按某列进行范围分区,然后再按某列进行散列分区。

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)
);
登入後複製

三、Oracle 数据库 10g 还提供了三种类型的分区索引:

1、本地索引:

本地索引是其分区方式与其所在基础表的分区方式一模一样的索引。本地索引的每个分区仅对应于其所在基础表的一个分区。

2、全局分区索引:

全局分区索引是使用不同于其所在表的分区键进行分区的索引,其所在表可以是分区表或非分区表。全局分区的索引可以使用范围或散列分区进行分区。例如,某个表可以按月份进行范围分区,因此具有十二个分区,而该表上的索引则可以使用不同的分区键进行范围分区,从而具有不同的分区数量。

3、全局非分区索引:

全局非分区索引基本上和非分区表的索引一样。索引结构是不分区的。

四、Oracle 提供了一系列丰富的技术,

可用于对表、索引和索引组织表进行分区,

因此可以针对任何业务环境中的任何应用程序进行最佳的分区 Oracle 还提供一套完整的 SQL 命令,用于管理分区表。其中包括添加新分区、删除分区、分拆分区以及合并分区的命令。

分区的优点:

1、用分区功能提高可管理性

通过 Oracle 分区功能,可将表和索引分成更多、更小的可管理单元,从而使数据库管理员能以“化整为零,个个击破”的方式管理数据。使用分区功能,维护操作可集中于表的特定部分。例如,数据库管理员可以只对表的一部分做备份,而不必对整个表做备份。对整个数据库对象的维护操作,可以在每个分区的基础上进行,从而将维护工作分解成更容易管理的小块。利用分区功能提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程。假设数据库管理员每周向表中加载新数据。可以对该表进行范 围分区,使每个分区包含一周的数据。这样加载进程只是简单地添加新的分区。添加一个分区的操作比修改整个表效率高很多,因为 DBA 不需要修改任何其他分区。

2、用分区功能提高性能

由于限制了所检查或操作的数据数量,同时支持并行任务执行,Oracle 分区功能实现了性能上增益。这些特性包括:

1)分区修整:分区修整是用分区功能提高性能的最简单最有价值的手段。分区修整常常能够将查询性能提高几个数量级。例如,假设某个应用程序包含一个存储订单 历史记录的 Orders 表,并且此表已按周分区。查询一周的订单只需访问该订单表的一个分区。如果该订单表包含两年的历史记录,这个查询只需要访问一个分区而不是一百零四个。该 查询的执行速度因为分区修整而有可能快一百倍。分区修整能与所有其他 Oracle 性能特性协作。Oracle 能将分区修整功能与任何索引技术、联接技术或并行访问方法结合使用。

2)分区智能联接:分区功能可以通过称为分区智能联接的技术提高多表联接的性能。当两个表要联接在一起,而且每个表都用联接键来分区时,就可以使用分区智能联 接。分区智能联接将大型联接分解成较小的发生在各个分区间的联接,从而用较少的时间完成全部联接。这就给串行和并行的执行都能带来显著的性能改善。

3、用分区功能提高可用性

分区的数据库对象具有分区独立性。该分区独立性特点可能是高可用性战略的一个重要部分,例如,如果分区表的一个分区不能用,但该表的所有其他 分区仍然保持在线并可用。那么这个应用可以继续针对该分区表执行查询和事务处理,只要不是访问不可用的分区,数据库操作仍然能够成功运行。数据库管理员可以指定各分区存放在不同的表空间里,从而让管理员隔离其它表分区针对单个分区进行备份与恢复操作。还有,分区功能可以减少计划停机时间。由于分区功能改善了性能,使数据库管理员能用相对较少的时间完成大型数据库对象的维护工作。

五、有关表分区的一些维护性操作:

1、添加分区

以下代码给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');
登入後複製

2、删除分区

以下代码删除了P3表分区:

ALTER TABLE SALES DROP PARTITION P3;
登入後複製

在以下代码删除了P4SUB1子分区:

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
登入後複製

注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。

3、截断分区

截断某个分区是指删除某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。当表中即使只有一个分区时,也可以截断该分区。通过以下代码截断分区:

ALTER TABLE SALES TRUNCATE PARTITION P2;
登入後複製

通过以下代码截断子分区:

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
登入後複製

4、合并分区

合并分区是将相邻的分区合并成一个分区,结果分区将采用较高分区的界限,值得注意的是,不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并:

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
登入後複製

5、拆分分区

拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
登入後複製

6、接合分区(coalesca)

结合分区是将散列分区中的数据接合到其它分区中,当散列分区中的数据比较大时,可以增加散列分区,然后进行接合,值得注意的是,接合分区只能用于散列分区中。通过以下代码进行接合分区:

ALTER TABLE SALES COALESCA PARTITION;
登入後複製

7、重命名表分区

以下代码将P21更改为P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;
登入後複製

8、相关查询

跨分区查询

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 11g中,组合分区功能这块有所增强,又增加了range-range,list-range,list-list,list-hash,并且 11g里面还支持Interval分区和虚拟列分

推荐教程:《Oracle学习教程

以上是完全掌握Oracle分區的詳細內容。更多資訊請關注PHP中文網其他相關文章!

本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

oracle打不開怎麼辦 oracle打不開怎麼辦 Apr 11, 2025 pm 10:06 PM

Oracle 打不開的解決辦法包括:1. 啟動數據庫服務;2. 啟動監聽器;3. 檢查端口衝突;4. 正確設置環境變量;5. 確保防火牆或防病毒軟件未阻止連接;6. 檢查服務器是否已關閉;7. 使用 RMAN 恢復損壞的文件;8. 檢查 TNS 服務名稱是否正確;9. 檢查網絡連接;10. 重新安裝 Oracle 軟件。

oracle游標關閉怎麼解決 oracle游標關閉怎麼解決 Apr 11, 2025 pm 10:18 PM

解決 Oracle 游標關閉問題的方法包括:使用 CLOSE 語句顯式關閉游標。在 FOR UPDATE 子句中聲明游標,使其在作用域結束後自動關閉。在 USING 子句中聲明游標,使其在關聯的 PL/SQL 變量關閉時自動關閉。使用異常處理確保在任何異常情況下關閉游標。使用連接池自動關閉游標。禁用自動提交,延遲游標關閉。

oracle怎麼循環創建游標 oracle怎麼循環創建游標 Apr 12, 2025 am 06:18 AM

Oracle 中,FOR LOOP 循環可動態創建游標, 步驟為:1. 定義游標類型;2. 創建循環;3. 動態創建游標;4. 執行游標;5. 關閉游標。示例:可循環創建游標,顯示前 10 名員工姓名和工資。

oracle數據庫如何分頁 oracle數據庫如何分頁 Apr 11, 2025 pm 08:42 PM

Oracle 數據庫分頁利用 ROWNUM 偽列或 FETCH 語句實現:ROWNUM 偽列用於通過行號過濾結果,適用於復雜查詢。 FETCH 語句用於獲取指定數量的第一行,適用於簡單查詢。

oracle數據庫怎麼停止 oracle數據庫怎麼停止 Apr 12, 2025 am 06:12 AM

要停止 Oracle 數據庫,請執行以下步驟:1. 連接到數據庫;2. 優雅關機數據庫(shutdown immediate);3. 完全關機數據庫(shutdown abort)。

HDFS配置CentOS需要哪些步驟 HDFS配置CentOS需要哪些步驟 Apr 14, 2025 pm 06:42 PM

在CentOS系統上搭建Hadoop分佈式文件系統(HDFS)需要多個步驟,本文提供一個簡要的配置指南。一、前期準備安裝JDK:在所有節點上安裝JavaDevelopmentKit(JDK),版本需與Hadoop兼容。可從Oracle官網下載安裝包。環境變量配置:編輯/etc/profile文件,設置Java和Hadoop的環境變量,使系統能夠找到JDK和Hadoop的安裝路徑。二、安全配置:SSH免密登錄生成SSH密鑰:在每個節點上使用ssh-keygen命令

oracle動態sql怎麼創建 oracle動態sql怎麼創建 Apr 12, 2025 am 06:06 AM

可以通過使用 Oracle 的動態 SQL 來根據運行時輸入創建和執行 SQL 語句。步驟包括:準備一個空字符串變量來存儲動態生成的 SQL 語句。使用 EXECUTE IMMEDIATE 或 PREPARE 語句編譯和執行動態 SQL 語句。使用 bind 變量傳遞用戶輸入或其他動態值給動態 SQL。使用 EXECUTE IMMEDIATE 或 EXECUTE 執行動態 SQL 語句。

oracle中亂碼怎麼解決 oracle中亂碼怎麼解決 Apr 11, 2025 pm 10:09 PM

Oracle 亂碼問題可以通過以下步驟解決:檢查數據庫字符集以確保與數據相匹配。設置客戶端字符集以與數據庫相匹配。轉換數據或修改列字符集以匹配數據庫字符集。使用 Unicode 字符集,並避免多字節字符集。檢查數據庫和客戶端的語言設置是否正確。

See all articles