oracle 优化 分区表,Oracle 性能优化——创建分区表

最新推荐文章于 2024-04-08 21:43:59 发布
最新推荐文章于 2024-04-08 21:43:59 发布
阅读量342 收藏
点赞数
文章标签： oracle 优化 分区表

Oracle性能优化——创建分区表[@more@]1、准备工作

创建相应的表空间，使各分区放于不同的表空间上：

SQL> conn / as sysdba

已连接。

SQL> create tablespace test01 datafile 'D:xtdbtest01.dbf' size 50m;

表空间已创建。

SQL> create tablespace test02 datafile 'D:xtdbtest02.dbf' size 50m;

表空间已创建。

SQL> create tablespace test03 datafile 'D:xtdbtest03.dbf' size 50m;

表空间已创建。

SQL> create tablespace test04 datafile 'D:xtdbtest04.dbf' size 100m;

表空间已创建。

为了进行对比，在表空间test04上创建一个未分区的表test_emp01，其结构与scott.emp一样：

SQL> conn scott/tiger

已连接。

SQL>CREATE TABLE test_emp01

2("EMPNO" NUMBER(4,0) not null,

3"ENAME" VARCHAR2(10),

4"JOB" VARCHAR2(9),

5"MGR" NUMBER(4,0),

6"HIREDATE" DATE,

7"SAL" NUMBER(7,2),

8"COMM" NUMBER(7,2),

9"DEPTNO" NUMBER(2,0))

10TABLESPACE test04

11/

表已创建。

2、创建范围分区表

根据表中某个值的范围进行分区，根据某个值的范围，决定将该条数据放在哪个分区。通过sal取值范围创建范围分区：

SQL>CREATE TABLE test01

2("EMPNO" NUMBER(4,0) not null,

3"ENAME" VARCHAR2(10),

4"JOB" VARCHAR2(9),

5"MGR" NUMBER(4,0),

6"HIREDATE" DATE,

7"SAL" NUMBER(7,2),

8"COMM" NUMBER(7,2),

9"DEPTNO" NUMBER(2,0))

10partition by range(sal)

11(partition sal_p1 values less than (1500) tablespace test01,

12partition sal_p2 values less than (3000) tablespace test02,

13partition sal_p3 values less than (maxvalue) tablespace test03);

表已创建。

往test01中插入大量的数据：

SQL> insert into test01 select * from emp;

已创建14行。

SQL> insert into test01 select * from emp;

已创建14行。

SQL> /

已创建14行。

SQL> insert into test01 select * from test01;

已创建42行。

SQL> /

已创建84行。

SQL> /

已创建168行。

SQL> /

已创建336行。

SQL> /

已创建672行。

SQL> /

已创建1344行。

SQL> /

已创建2688行。

SQL> /

已创建5376行。

SQL> /

已创建10752行。

SQL> /

已创建21504行。

SQL> /

已创建43008行。

SQL> /

已创建86016行。

SQL> /

已创建172032行。

SQL> /

已创建344064行。

SQL> commit;

提交完成。

为了对比，在表test_emp01中插入相同的数据量：

SQL> insert into test_emp01

2select * from test01;

已创建688128行。

SQL> commit;

提交完成。

在表test_emp01中查询sal<1500的记录：

select * from test_emp01 where sal < 1500;

此时查看系统I/O

iostat 2 15

如果是windows系统，则可通过任务管理器或其他工具查看。

在表test01中查询分区1的记录：

select * from test01 partition(sal_p1);

查看系统I/O

iostat 2 15

如果是windows系统，则可通过任务管理器或其他工具查看。

通过对比两次I/O，可发现分区表可以很好的提高系统的I/O性能。

3、创建hash分区表

hash分区是通过分区键的hash值来均匀分布数据的一种分区类型，下例通过scott.emp的empno列值进行hash分区：

SQL>CREATE TABLE test02

2("EMPNO" NUMBER(4,0) not null,

3"ENAME" VARCHAR2(10),

4"JOB" VARCHAR2(9),

5"MGR" NUMBER(4,0),

6"HIREDATE" DATE,

7"SAL" NUMBER(7,2),

8"COMM" NUMBER(7,2),

9"DEPTNO" NUMBER(2,0))

10partition by hash(empno)

11(partition test02_p1 tablespace test01,

12partition test02_p2 tablespace test02,

13partition test02_p3 tablespace test03);

表已创建。

往表中插入数据

SQL> insert into test02 select * from emp;

已创建14行。

SQL> select count(*) from test02;

COUNT(*)

----------

14

SQL> insert into test02 select * from emp where sal < 3000;

已创建9行。

SQL> insert into test02 select * from emp where sal < 1500;

已创建6行。

SQL> commit;

提交完成。

SQL> select count(*) from test02;

COUNT(*)

----------

29

查看hash分区结果

SQL> select * from test02 partition(test02_p1);

SQL> select * from test02 partition(test02_p2);

SQL> select * from test02 partition(test02_p3);

从结果可以看出，oracle按empno将记录散列的插入三个分区中，即三个不同的表空间中。

4、创建列表分区

不能严格按照范围分区对表进行分区，也不希望hash分区那样由系统来进行散列的分配，需要自主控制数据自如的插入分区中，列表分区是最好的选择。下例按scott.emp中的deptno将表划分为3个分区。

SQL>CREATE TABLE test03

2("EMPNO" NUMBER(4,0) not null,

3"ENAME" VARCHAR2(10),

4"JOB" VARCHAR2(9),

5"MGR" NUMBER(4,0),

6"HIREDATE" DATE,

7"SAL" NUMBER(7,2),

8"COMM" NUMBER(7,2),

9"DEPTNO" NUMBER(2,0))

10partition by list(deptno)

11(partition test03_p1 values(10) tablespace test01,

12partition test03_p2 values(20) tablespace test02,

13partition test03_p3 values(30) tablespace test03);

表已创建。

向表中插入数据：

SQL> insert into test03 select * from emp;

已创建14行。

SQL> insert into test03 select * from emp where deptno=10;

已创建3行。

SQL> insert into test03 select * from emp where deptno=20;

已创建5行。

SQL> commit;

提交完成。

SQL> select count(*) from test03;

COUNT(*)

----------

22

查询列表分区的结果：

SQL> select * from test03 partition(test03_p1);

SQL> select * from test03 partition(test03_p2);

SQL> select * from test03 partition(test03_p3);

5、创建复合分区表

分为两种：A、先使用范围分区，然后在每个分区内再使用hash分区

B、先使用范围分区，然后在每个分区内再使用列表分区

6、第一种复合分区：

在scott.emp中按sal列的取值范围将表分为三个分区，然后再将每个分区按empno列值用hash分区的方法分为三个子分区。

SQL>CREATE TABLE test04

2("EMPNO" NUMBER(4,0) not null,

3"ENAME" VARCHAR2(10),

4"JOB" VARCHAR2(9),

5"MGR" NUMBER(4,0),

6"HIREDATE" DATE,

7"SAL" NUMBER(7,2),

8"COMM" NUMBER(7,2),

9"DEPTNO" NUMBER(2,0))

10partition by range(sal)

11subpartition by hash(empno)

12subpartitions 3 store in(test01, test02, test03)

13(partition sal_p1 values less than (1500),

14partition sal_p2 values less than (3000),

15partition sal_p3 values less than (maxvalue));

表已创建。

往表中插入数据

SQL> insert into test04 select * from emp;

SQL> insert into test04 select * from emp where deptno=10;

已创建3行。

SQL> insert into test04 select * from emp where sal < 3000;

已创建9行。

SQL> commit;

提交完成。

SQL> select count(*) from test04;

COUNT(*)

----------

26

查看复合分区表的状态：

SQL> col partition_name format a20

SQL> col subpartition_name format a20

SQL> col tablespace_name format a20

SQL> select tablespace_name, partition_name, subpartition_name

2from user_tab_subpartitions

3where table_name='TEST04'

4/

TABLESPACE_NAMEPARTITION_NAMESUBPARTITION_NAME

-------------------- -------------------- --------------------

TEST01SAL_P1SYS_SUBP61

TEST02SAL_P1SYS_SUBP62

TEST03SAL_P1SYS_SUBP63

TEST01SAL_P2SYS_SUBP64

TEST02SAL_P2SYS_SUBP65

TEST03SAL_P2SYS_SUBP66

TEST01SAL_P3SYS_SUBP67

TEST02SAL_P3SYS_SUBP68

TEST03SAL_P3SYS_SUBP69

已选择9行。

7、第二种复合分区：

在scott.emp中按sal列的取值范围将表分为三个分区，然后再将每个分区按deptno列值用列表分区的方法分为三个子分区。

SQL>CREATE TABLE test05

2("EMPNO" NUMBER(4,0) not null,

3"ENAME" VARCHAR2(10),

4"JOB" VARCHAR2(9),

5"MGR" NUMBER(4,0),

6"HIREDATE" DATE,

7"SAL" NUMBER(7,2),

8"COMM" NUMBER(7,2),

9"DEPTNO" NUMBER(2,0))

10partition by range(sal)

11subpartition by list(deptno)

12subpartition template

13(subpartition p1 values(10),

14subpartition p2 values(20),

15subpartition p3 values(30))

16(partition sal_p1 values less than (1500) tablespace test01,

17partition sal_p2 values less than (3000) tablespace test02,

18partition sal_p3 values less than (maxvalue) tablespace test03);

表已创建。

向表中插入数据：

SQL> insert into test05 select * from emp;

已创建14行。

SQL> insert into test05 select * from emp where deptno = 10;

已创建3行。

SQL> insert into test05 select * from emp where sal < 3000;

已创建9行。

SQL> commit;

提交完成。

SQL> select count(*) from test05;

COUNT(*)

----------

26

查看复合分区表的状态：

SQL> edit

已写入 file afiedt.buf

1select tablespace_name, partition_name, subpartition_name

2from user_tab_subpartitions

3* where table_name='TEST05'

SQL> /

TABLESPACE_NAMEPARTITION_NAMESUBPARTITION_NAME

-------------------- -------------------- --------------------

TEST01SAL_P1SAL_P1_P1

TEST01SAL_P1SAL_P1_P2

TEST01SAL_P1SAL_P1_P3

TEST02SAL_P2SAL_P2_P1

TEST02SAL_P2SAL_P2_P2

TEST02SAL_P2SAL_P2_P3

TEST03SAL_P3SAL_P3_P1

TEST03SAL_P3SAL_P3_P2

TEST03SAL_P3SAL_P3_P3

已选择9行。

SQL>select * from test05 subpartition(sal_p2_p2);

EMPNO ENAMEJOBMGR HIREDATESALCOMM

---------- ---------- --------- ---------- -------------- ---------- ----------

DEPTNO

----------

7566 JONESMANAGER7839 02-4月 -812975

20

7566 JONESMANAGER7839 02-4月 -812975

20

插入的记录中，sal<3000，deptno=20，根据分析，此记录是存储在表空间test02中，位于sal_p2分区中p2子分区，查询结果正是如此。

tekiz
关注
oracle自动一年建立表分区.zip
11-05
在Oracle数据库系统中，表分区是一...总的来说，"Oracle自动一年建立表分区"的脚本是数据库性能优化的重要工具，尤其适用于需要处理大量时间序列数据的环境。通过合理地使用和维护，可以显著提升数据库系统的整体效能。
oracle模板分区,设置oracle分区表的子分区模板
weixin_36234738的博客
04-04 450
在将某个分区表删掉并重建之后发现一个问题，就是现在新建分区之后，自动创建的子分区格式与以前不同。以前正常情况下自动生成的子分区格式如下： 重建表之后自动生成的子分区格式如下： 我们可以通过修改表属性更改子分区模板，解决此问题： alter table TESTset subpartition template (subpartition SP_00 values (0),subpartition ...
Oracle分区表详解（Oracle Partitioned Tables）
Vincent的博客
04-26 1万+
当单表数据量随着时间变的越来越大时，会给数据的管理和查询带来不便。我们可以考虑对表进行分区，利用分区表特性将数据分成小块存储，可以大幅提升查询性能，管理便捷性及数据的可用性。目录一、分区表概述1.1 分区表概念1.2 何时使用分区表1.3 分区表的优点1.3.1 提升SQL查询性能1.3.2 提升表可管理性1.3.3 提升数据可用性二、基础分区策略2.1 范围分区（Range Partition）2.2 哈希分区（Hash Partition）2.3 列表分区（List partition）三、扩展分区策略
oracle表分区
weixin_44672916的博客
10-18 5972
在添加主分区时最好新增一个子分区，如果不新增子分区，oracle会自动生成一个默认的子分区；–将worker02和worker03分区中的数据合并到worker03分区中，同时删除worker02分区。当hash分区作为主分区时，oracle中看不到子分区，但是能根据子分区进行查询。3.当列表分区是子分区，hash分区是主分区时；直接添加分区会报错，因为添加的分区要比最大的分区要比最大的分区范围要大。删除主分区，会删除该分区下所有的数据；截断分区是指删除某个分区的中的数据，不会删除分区。
Oracle分区表常用SQL
thinkpadleo的专栏
06-26 3154
 分区表的维护：增加分区： ALTER TABLE sales ADD PARTITION sales2000_q1 VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’) TABLESPACE ts_sale2000q1; 如果已有maxvalue分区，不能增加分区，可以采取分裂分区的办法增加分区！删除分区： ALTER
oracle 建立分区表
lzy_persist的专栏
10-21 873
最近所数据统计报表用到了表分区，所以最近就看了看，拿来研究研究。  一.范围分区 范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。 --例一取值范围： CREATE TABLE CUSTOMER (     CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
oracle分区表之hash分区表的使用及扩展
09-10
Oracle分区表中的Hash分区是一种基于哈希算法的分区策略，适用于处理无法清晰定义分区范围的大型数据表。这种分区方式通过计算分区键的哈希值来决定数据存储在哪个分区，以此达到数据分散和负载均衡的目的。Hash分区...
oracle普通表转化为分区表的方法
09-09
Oracle数据库中的分区表是一种高级的表组织形式，它将大表分成多个较小的部分，每个部分称为一个分区，以提高查询性能和管理效率。当表的大小超过2GB时，官方推荐使用分区表，因为它们在处理大量数据时具有显著优势...
Oracle分区表(Partition Table)使用详解
06-10
Oracle分区表是Oracle数据库系统中一个强大的特性，用于提高数据管理效率和查询性能。通过将大表分成逻辑上独立的小块，每个块对应一个特定的数据范围或键值，可以实现数据的并行处理，简化管理和优化查询。在本文中...
oracle表中已经有数据还能创建分区吗
06-20
oracle创建分区表
Oracle查询优化改写技巧与案例总结四
weixiaohuai的博客
01-09 959
一、简介 本文是总结Oracle查询优化方法与改写技巧的第四篇文章，接着第三篇文章，继续。。。 二、优化技巧 【1】将结果集反向转置为一列 案例：将以下结果集反向转置为一列进行展示，并且每个员工信息之后空一行。 思路：利用之前说的unpivot（ for in( )）语句实现即可，至于空一行，我们可以使用空值null作为占位占一行，空出来。 --将结果集反向转置为一列 --sel...
oracle关于分区,Oracle 关于分区表
weixin_35060251的博客
04-05 450
在表建立的开始就需要定下是采用分区还是不分区，表不可以直接ALTER转化成分区表或者分区表也不能直接转化为无分区的表。建立分区表要首先确定分区关键字，关键字的类型只能是标准类型。关键字最多可以有16列组成。lessthan 用来指定每一个分区的上限。最后一个可以指定一个具体的上限，也可以指定maxvalue(无穷大)，如果以后要建立globalindex的话这里请指定maxvalue，以确定所建立...
达梦分区表
你学废了么的博客
03-19 1633
二级分区 LIST-RANG CREATE TABLE SALES( SALES_ID INT, SALEMAN CHAR(20), SALEDATE DATETIME, CITY CHAR(10) ) PARTITION BY LIST(CITY) SUBPARTITION BY RANGE(SALEDATE) SUBPARTITION TEMPLATE( SUBPARTITION P11 VALUES LESS THAN (‘2012-04-01’), SUBPARTITION P12 VALUE
Oracle-性能优化篇-分区表
JAVA & HTML
09-16 1988
分区表一般创建本地索引，本地索引在分区表中就是分区索引，各个分区之间的索引相互独立，可以避免因为删除某个分区而导致索引失效的问题。根据多个字段进行分区（用于单个字段分区后每个分区的数据依然很多，使用组合分区可以对分区进行再分区）适合于有明确的时间线或者表里有能表示范围的字段的大批量数据。引用分区是给子表分区，适合与已存在的分区表有关联关系的表。适合于表里存在固定值的字段，如性别、年级等。适合于表里没有任何规律的字段。
Oracle表分区和分区索引的优化
互联网知识分享
09-14 377
Oracle表分区和分区索引的优化是一种提高数据库性能的技术。通过将表数据分割成更小的逻辑和物理单元，可以实现更高效的数据访问和查询。参数介绍：PARTITION BY: 指定分区方式，可以是范围、列表或哈希。PARTITIONS: 指定分区的数量。SUBPARTITION BY: 指定子分区方式，可以是范围、列表或哈希。SUBPARTITIONS: 指定子分区的数量。完整代码案例：--创建分区表CREATETABLEsales (id NUMBER,sale_date。
【数据库】Oracle数据库优化经验总结（一）
最新发布
go9898的专栏
04-08 478
Oracle数据库优化经验总结：分区表
Oracle Interval-Partition 解决Range分区大难题
hellojoy的博客
11-23 1552
在实际运用Range分区时，遇到了这样的难题： createtableTMP_LXQ_1 (  PROPOSALNO             VARCHAR2(22),  STARTDATE              DATE ) partitionbyrange(STARTDATE) ( partitionpart_t01values less than(to_date('2008
oracle分区详解
热门推荐
huangbaokang的博客
07-01 2万+
分区技术有如下优点： 1、分区技术使数据库的可管理性变得更加容易，如：用户可以往一个单独的分区中装载数据，而对其他分区没有任何影响；用户可以在单独的分区上创建索引等。 2、分区可以提高表的查询性能，SQL语句的where子句会过滤掉不需要的分区，oracle不会再扫描那些不需要的分区。 3、分区技术减少数据的不可用时间，用户可以单独维护一个分区中的数据，而不影响其他分区中数据的使用。 4、分区技术...
oracle的partition,Oracle 之Partition By 函数的使用
weixin_39861627的博客
04-03 512
原标题：Oracle 之Partition By 函数的使用Begin：现在许多系统对页面的显示效果有很高的标准，系统做的丑客户爸爸不买单。当然系统做的美不美是前端的锅，和我们关系不是很大，我们只需把前端所需要的数据传给他们就行了，这些是题外话。 图1.系统排名图进入正题，使用 Partition By XX列 Order By XX列 进行实现分组排序，还是使用我们万能的Person表。。。这次...
优化Oracle分区表策略：性能提升与管理指南
Oracle甲骨文原厂分区表最佳实践是一份针对Oracle数据库管理员和开发人员的重要参考资料，它深入探讨了在...通过遵循其中的最佳实践，数据库管理员和开发人员能够更有效地利用Oracle分区表技术，应对复杂的业务需求。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值