Penguasaan lengkap pembahagian Oracle

Artikel ini membawakan anda pengetahuan yang berkaitan tentang Oracle, yang terutamanya memperkenalkan isu yang berkaitan dengan pembahagian, jadual, indeks dan jadual tersusun indeks boleh dibahagikan lagi kepada segmen , supaya anda boleh mengurus dan mengakses objek pangkalan data ini dengan lebih tepat saya harap ia akan membantu semua orang.

Tutorial yang disyorkan: "Tutorial Oracle"

1. Pengenalan:

Fungsi pembahagian Oracle boleh dipertingkatkan banyak Kebolehurusan aplikasi, prestasi dan ketersediaan. Pemisahan membolehkan jadual, indeks dan jadual tersusun indeks dibahagikan lagi kepada segmen, membenarkan pengurusan dan akses yang lebih tepat bagi objek pangkalan data ini. Oracle menawarkan pelbagai jenis skim pembahagian untuk memenuhi setiap keperluan perniagaan. Dan, kerana pembahagian adalah telus sepenuhnya dalam pernyataan SQL, ciri ini boleh digunakan pada hampir mana-mana aplikasi.

Faedah Pembahagian: Pembahagian membawa manfaat besar kepada pelbagai aplikasi dengan meningkatkan kebolehurusan, prestasi dan ketersediaan. Secara umum, pembahagian boleh meningkatkan prestasi pertanyaan dan operasi penyelenggaraan tertentu. Di samping itu, pembahagian boleh sangat memudahkan tugas pengurusan biasa. Melalui pembahagian, pereka bentuk pangkalan data dan pentadbir boleh menyelesaikan beberapa cabaran yang ditimbulkan oleh aplikasi canggih. Pembahagian ialah alat utama dalam membina sistem data gigabait atau sistem ketersediaan ultra tinggi.

2. Pengetahuan asas fungsi partition:

Fungsi partition boleh membahagikan lagi jadual, indeks atau jadual tersusun indeks kepada segmen. Segmen objek pangkalan data ini dipanggil partition. Setiap partition mempunyai nama sendiri dan boleh memilih ciri storannya sendiri. Dari perspektif pentadbir pangkalan data, objek yang dipisahkan mempunyai berbilang segmen, yang boleh diuruskan secara kolektif atau individu. Ini memberikan pentadbir pangkalan data fleksibiliti yang besar dalam menguruskan objek yang dipisahkan. Walau bagaimanapun, dari perspektif aplikasi, jadual yang dipisahkan adalah sama dengan jadual yang tidak dipisahkan dan tiada pengubahsuaian diperlukan apabila mengakses jadual yang dipisahkan menggunakan arahan SQL DML. Pembahagian jadual dicapai melalui "kunci partition", yang merujuk kepada lajur yang menentukan partition di mana satu baris terletak. Pangkalan Data Oracle 10g menyediakan beberapa teknologi untuk pembahagian jadual:

1 Pembahagian julat (julat): Setiap partition ditentukan oleh julat nilai kunci partition (untuk lajur tarikh sebagai Jadual kunci partition, partition "Januari 2005". mengandungi baris dengan nilai kunci partition dari "1 Januari 2005" hingga "31 Januari 2005").

Apabila menggunakan pembahagian julat, sila pertimbangkan peraturan berikut:

1) Setiap partition mesti mempunyai klausa NILAI KURANG MAKA, yang menentukan nilai yang tidak termasuk dalam nilai had atas partition. Sebarang rekod dengan nilai kunci partition sama atau lebih besar daripada had atas ini akan ditambahkan pada partition seterusnya yang lebih tinggi.

2) Semua partition, kecuali yang pertama, akan mempunyai nilai had bawah tersirat, iaitu nilai had atas partition sebelumnya bagi partition ini.

3) Dalam partition tertinggi, MAXVALUE ditakrifkan. MAXVALUE mewakili nilai yang tidak pasti. Nilai ini lebih tinggi daripada nilai mana-mana kunci partition dalam partition lain, dan juga boleh difahami sebagai lebih tinggi daripada nilai VALUE LESS THEN yang dinyatakan dalam mana-mana partition, termasuk nilai null.

Contoh 1:

Andaikan terdapat jadual PELANGGAN dengan 200,000 baris data Kami membahagikan jadual ini mengikut CUSTOMER_ID, dan setiap partition menyimpan 100,000 setiap baris partition ke ruang jadual yang berasingan supaya fail data boleh merentangi berbilang cakera fizikal. Berikut ialah kod untuk mencipta jadual dan partition, seperti berikut:

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
)

Contoh 2: Bahagikan mengikut masa

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
)

Contoh 3: 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. Pembahagian senarai (senarai): Setiap partition ditentukan oleh senarai nilai kunci partition (untuk jadual dengan lajur wilayah sebagai kunci partition, partition "Amerika Utara" mungkin mengandungi nilai " Kanada", "Amerika Syarikat" dan "Mexico"). Ciri partition ini ialah terdapat hanya beberapa nilai dalam lajur tertentu Berdasarkan ciri ini, kita boleh menggunakan pembahagian senarai.

Contoh 1

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

Contoh 2

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. Pembahagian cincang (cincang):

Pencincangan algoritma digunakan pada kekunci partition untuk menentukan partition di mana baris tertentu berada. Pembahagian jenis ini menggunakan algoritma pencincangan pada nilai lajur untuk menentukan partition mana satu baris harus diletakkan. Pembahagian cincang disyorkan apabila nilai lajur tidak mempunyai keadaan yang sesuai. Pembahagian cincang ialah sejenis pembahagian yang mengedarkan data secara sama rata dengan menentukan nombor pemisahan kerana pembahagian cincang dilakukan pada peranti I/O untuk menjadikan pembahagian saiz yang sama.

Contoh 1:

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
)

Singkatan:

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);

Mekanisme utama partition hash adalah untuk mengira partition mana rekod tertentu harus dimasukkan berdasarkan algoritma hash Antaranya, perkara yang paling penting dalam algoritma hash ialah fungsi hash Jika anda ingin menggunakan pembahagian cincang dalam Oracle, anda hanya perlu menentukan bilangan partition. Adalah disyorkan bahawa bilangan partition adalah 2 hingga kuasa ke-n, yang boleh menjadikan pengagihan data antara setiap partition lebih sekata.

4. Pembahagian cincang julat gabungan (cincang julat):

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

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

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学习教程》

Atas ialah kandungan terperinci Penguasaan lengkap pembahagian Oracle. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!