oracle 表分区设置

小咖张

已于 2022-12-30 11:11:50 修改

阅读量3.8k

点赞数 1

分类专栏：数据库文章标签： oracle 数据库 sql

于 2022-11-16 16:35:43 首次发布

本文链接：https://blog.csdn.net/u010738038/article/details/127090916

版权

数据库专栏收录该内容

18 篇文章 0 订阅

订阅专栏

一基本操作：

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

查看表的分区键：

SELECT * FROM ALL_PART_KEY_COLUMNS where name='EMP';

表的分区查询：SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'EMP'

创建时间范围分区：

CREATE TABLE emp (
	empno NUMBER (10),
	ename VARCHAR2 (30),
	hiredate DATE
	
) 
PARTITION BY RANGE (hiredate)
(
PARTITION p1 VALUES LESS THAN(to_date('2022-04-01','yyyy-mm-dd')),
PARTITION p2 VALUES LESS THAN(to_date('2022-07-01','yyyy-mm-dd')),
PARTITION p3 VALUES LESS THAN(to_date('2022-10-01','yyyy-mm-dd'))
)

如果不创建最大值：

PARTITION p4 VALUES LESS THAN(maxvalue) --其他值去分区4

插入比上面分区范围大的值时会报错：

[Err] ORA-14400: inserted partition key does not map to any partition

否则就要定时去增加新的分区：

ALTER TABLE EMP ADD PARTITION p4 VALUES LESS THAN(to_date('2023-03-01','yyyy-mm-dd'))

创建成功后，可以成功插入数据

二：分区表常用查询

如果查询当前用户下得分区表：
select * from user_tables where partitioned='YES'
如果要查询整个数据库中的分区表：
select * from dba_tables where partitioned='YES'
如果要查询某个用户下得分区表：
select * from dba_tables where partitioned='YES' and owner=' '
查询索引、表上在那些列上创建了分区：
SELECT * FROM USER_PART_KEY_COLUMNS;
看一个表是否是分区表:
select * from user_tables where table_name =''如果这个表里的tablespace_name为空，代表的就是分区表
分区信息存在SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ？

三：分区建立语法

创建表空间

CREATE tablespace op_emp1
datafile '/u01/data/qmtest/op_emp1.dbf'
size 100M
autoextend on next 100M maxsize unlimited;

3.1范围分区:分区字段可以是日期，也可以是数字
CREATE TABLE emp (
   empno NUMBER (10),
   ename VARCHAR2 (30),
   hiredate DATE
)
PARTITION BY RANGE (hiredate)
(
PARTITION p1 VALUES LESS THAN(to_date('2022-04-01','yyyy-mm-dd')) tablespace op_emp1,
PARTITION p2 VALUES LESS THAN(to_date('2022-07-01','yyyy-mm-dd')) tablespace op_emp2,
PARTITION p3 VALUES LESS THAN(to_date('2022-10-01','yyyy-mm-dd')) tablespace op_emp3,
PARTITION p4 VALUES LESS THAN(maxvalue) tablespace op_emp4--其他值去分区4
)

3.2列表分区：针对于该列只有固定几个值的分区：例如省份，部门

CREATE TABLE emp (
    empno NUMBER (10),
    ename VARCHAR2 (30),
    hiredate DATE,
city varchar(10)
)
PARTITION BY list (city)
(
PARTITION p1 VALUES ('江苏','安徽','浙江') ,
PARTITION p2 VALUES ('黑龙江','吉林','辽宁'),
PARTITION p3 VALUES ('新疆') ,
PARTITION p4 VALUES ('其他') --其他值去分区4
)

分区后不加tablespace op_emp1，会自动分到默认的表空间

3.3hash分区：主要是针对于字符串字段的分区
CREATE TABLE emp (
   empno NUMBER (10),
   ename VARCHAR2 (30),
   hiredate DATE
)
PARTITION BY hash (ename)
(
PARTITION p1 ,
PARTITION p2 ,
PARTITION p3 ,
PARTITION p4 --其他值去分区4

)

hash分区很难知道数据具体在哪个分区，sql 查询表后面就不好加 partition(p1)的分区查询

3.4复合分区
范围分区与散列分区或范围分区与列表分区

create table student(
sno number,sname varchar2(10)
)
partition by range(sno)
subpartition by hash(sname)
subpartitions 4
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (maxvalue)
)