Oracle学习笔记八 表空间

表空间

表空间是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表， 所以称作表空间 。

 

分区表

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

注意：已经存在的表没有方法可以直接转化为分区表 

 

 

Oracle允许用户将一个表分成多个分区，用户可以执行查询，只访问表中的特定分区，也将不同的分区存储在不同的磁盘，提高访问性能和安全性。表的每个部分都称为“分区”，用户可以独立地备份和恢复每个分区，可以通过引用逻辑表名进行查询和更新。

 

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具

 

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角 度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程 序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改

 

使用分区的优点：

1、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；

2、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；

3、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；

4、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

什么时候使用分区表：

1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加都新的分区中。

表分区的类型

范围分区

1.以表中的一个列或一组列的值的范围将行映射到分区

2.由表的分区规范进行定义；partition by range(column_list)

3.由每个单独分区的分区规范进行定义：values less than(value_list);

 

范围分区的语法：

PARTITION BY RANGE (column_name)
(
  PARTITION part1 VALUES LESS THAN(range1),
  PARTITION part2 VALUES LESS THAN(range2),
  ...
  [PARTITION partN VALUES LESS THAN(MAXVALUE)]
);

范围分区示例

CREATE TABLE Sales
(
    Product_ID varchar2 (5),
    Sales_Cost number (10)
)
PARTITION BY RANGE (Sales_Cost)  --根据 Sales_Cost 创建分区
(
    PARTITION P1 VALUES LESS THAN (1000),  --包含销售成本低于1000 
的所有产品的值 
    PARTITION P2 VALUES LESS THAN (2000),
    PARTITION P3 VALUES LESS THAN (3000)
);
--P1,P2,P3分区的名称

 

CREATE TABLE SALES2 (
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE NOT NULL,
SALES_COST NUMBER(10))
PARTITION BY RANGE (SALES_DATE)
(
  PARTITION P1 VALUES LESS THAN (DATE '2003-01-01'),
  PARTITION P2 VALUES LESS THAN (DATE '2004-01-01'), 
  PARTITION P3 VALUES LESS THAN (MAXVALUE)
);

 

 

散列分区

1.允许用户对不具有逻辑范围的数据进行分区

2.通过在分区键上执行HASH函数决定存储的分区

3.将数据平均地分布到不同的分区

 

散列分区语法

PARTITION BY HASH (column_name)
PARTITIONS number_of_partitions;
或
PARTITION BY HASH (column_name)
( PARTITION part1 [TABLESPACE tbs1],
  PARTITION part2 [TABLESPACE tbs2],
  ...
  PARTITION partN [TABLESPACE tbsN]);

 散列分区示例

CREATE TABLE Employee
(
    Employee_ID varchar2 (5),
    Employee_Name varchar2(20),
    Department varchar2 (10)
)
PARTITION BY HASH (Department)  --在表 Employee上创建分区键 Department
(    --创建 3 个分区
    Partition D1,
    Partition D2,
    Partition D3
); 
--D1,D2,D3分区的名称

 

CREATE TABLE EMPLOYEE
(
    EMP_ID NUMBER(4),
    EMP_NAME VARCHAR2(14),
    EMP_ADDRESS VARCHAR2(15),
    DEPARTMENT VARCHAR2(10)
)
PARTITION BY HASH (DEPARTMENT)
PARTITIONS 4; 

 

 

列表分区

1.允许用户将不相关的数据组织在一起

 

 列表分区的语法

 

PARTITION BY LIST (column_name)
(
  PARTITION part1 VALUES (values_list1),
  PARTITION part2 VALUES (values_list2),
  ...
  PARTITION partN VALUES (DEFAULT)
);

 列表分区示例

CREATE TABLE Employee
(
    Emp_ID number (4),
    Emp_Name varchar2 (14),
    Emp_Address varchar2 (15)
)
--根据职员住址在表上创建的列表分区
PARTITION BY LIST (Emp_Address) 
(
    Partition north values ('芝加哥'),  --包含住在芝加哥的职员的记录
    Partition west values ('旧金山’, ‘洛杉矶'),
    Partition south values ('亚特兰大', '达拉斯', '休斯顿'),
    Partition east values ('纽约', '波斯顿')
);
--north,west......分区的名称

 

复合分区

1.范围分区与散列分区或列表分区的组合

 

复合分区的语法：

 

PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(
  PARTITION part1 VALUE LESS THAN(range1),
  PARTITION part2 VALUE LESS THAN(range2),
  ...
  PARTITION partN VALUE LESS THAN(MAXVALUE)
);

复合分区示例

 

SQL> CREATE TABLE SALES
(
    PRODUCT_ID VARCHAR2 (5),
    SALES_DATE DATE NOT NULL,
    SALES_COST NUMBER (10)
)
PARTITION BY RANGE (SALES_DATE)  --在表的 Sales_Date 
列中创建范围分区
SUBPARTITION BY HASH (PRODUCT_ID)  --在表的 Product_ID 
列创建散列子分区
SUBPARTITIONS 5    --在每个范围分区中
创建 5 个散列子分区
(
    PARTITION S1 VALUES LESS THAN (TO_DATE('01/4月/2001', 'DD/MON/YYYY')),
    PARTITION S2 VALUES LESS THAN (TO_DATE('01/7月/2001', 'DD/MON/YYYY')),
    PARTITION S3 VALUES LESS THAN (TO_DATE('01/9月/2001', 'DD/MON/YYYY')),
    PARTITION S4 VALUES LESS THAN (MAXVALUE)
);
--S1,S2,S3创建的四个范围分区的名称

 

 

操纵已分区的表

在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区。查询、修改和删除分区表时可以显式指定要操作的分区。

INSERT INTO SALES3 VALUES ('P001', '02-3月-2001', 2000);
INSERT INTO SALES3 VALUES ('P002', '10-5月-2001', 2508);
INSERT INTO SALES3 VALUES ('P003', '05-7月-2001', 780);
INSERT INTO SALES3 VALUES ('P004', '12-9月-2001', 1080);

SELECT * FROM SALES3 PARTITION (P3);

DELETE FROM SALES3 PARTITION (P2);

 

分区维护操作

查询表上有多少分区

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName' 

分区维护操作修改已分区表的分区。

分区维护的类型：

1.计划事件 － 定期删除最旧的分区

2.非计划事件 － 解决应用程序或系统问题

 

分区维护操作有：

1.添加分区

2.删除分区

3.截断分区

4.合并分区

5.拆分分区

 

添加分区 – 在最后一个分区之后添加新分区

ALTER TABLE SALES
     ADD PARTITION P4 VALUES LESS THAN (4000);

 

删除分区 – 删除一个指定的分区，分区的数据也随之删除

ALTER TABLE SALES DROP PARTITION P4;

 

截断分区 – 删除指定分区中的所有记录

ALTER TABLE SALES TRUNCATE PARTITION P3;

 

合并分区 - 将范围分区或复合分区的两个相邻分区连接起来

ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;

 

拆分分区 - 将一个大分区中的记录拆分到两个分区中

 

ALTER TABLE SALES SPLIT PARTITION P2 AT (1500) INTO (PARTITION P21, PARTITION P22);