分区表的特点

最新推荐文章于 2022-09-20 11:07:38 发布
最新推荐文章于 2022-09-20 11:07:38 发布
阅读量334 收藏
点赞数
文章标签： 数据库 操作系统 运维
如果表中的数据量较大而且该表的访问比较频繁，通常考虑使用分区表，不要考虑物化视图。
分区类型：range partition、hash partition、list partition。

高可用性：某分区故障不能使用，表的其他分区仍然可以使用

维护方便：独立管理每个分区比管理单个达标要轻松的多

改善性能：对大表的查询、增加、修改等操作可以分解到表的分区来并行执行


关于分区表的性能问题

create table test1(test_id number(9),id number(9),log_time date)
partition by range(log_time)
(partition t1_01 values less than (to_date('2004-02-01','yyyy-mm-dd')),
partition t1_02 values less than (to_date('2004-03-01','yyyy-mm-dd')),
partition t1_03 values less than (to_date('2004-04-01','yyyy-mm-dd')),
partition t1_04 values less than (to_date('2004-05-01','yyyy-mm-dd')),
partition t1_05 values less than (to_date('2004-06-01','yyyy-mm-dd')),
partition t1_06 values less than (to_date('2004-07-01','yyyy-mm-dd')),
partition t1_07 values less than (to_date('2004-08-01','yyyy-mm-dd')),
partition t1_08 values less than (to_date('2004-09-01','yyyy-mm-dd')),
partition t1_09 values less than (to_date('2004-10-01','yyyy-mm-dd')),
partition t1_10 values less than (to_date('2004-11-01','yyyy-mm-dd')),
partition t1_11 values less than (to_date('2004-12-01','yyyy-mm-dd')),
partition t1_12 values less than (to_date('2005-01-01','yyyy-mm-dd')));

create table test2(test_id number(9),id number(9),log_time date);

create sequence seq_t1;
create sequence seq_t2;

create index idx_test1_logtime on test1(log_time) local;
create index idx_test1_testid on test1(test_id) local;
create index idx_test1_id on test1(id) local;

create index idx_test2_logtime on test2(log_time);
create index idx_test2_testid on test2(test_id);
create index idx_test2_id on test2(id);

关于分区表与非分区表的测试
begin
for i in 1 .. 1000 loop
for j in 1 .. 12 loop
for k in 1 .. 28 loop
insert into test1 values(seq_t1.nextval,i,to_date('2004-'||to_char(j)||'-'||to_char(k),'yyyy-mm-dd'));
end loop;
end loop;
end loop;
end;
/
begin
for i in 1 .. 1000 loop
for j in 1 .. 12 loop
for k in 1 .. 28 loop
insert into test2 values(seq_t2.nextval,i,to_date('2004-'||to_char(j)||'-'||to_char(k),'yyyy-mm-dd'));
end loop;
end loop;
end loop;
end;
/

SQL> select count(*) from test1;

COUNT(*)
----------
336000

Elapsed: 00:00:00.02

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1104 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from test2;

COUNT(*)
----------
336000

Elapsed: 00:00:00.02

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1067 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


统计显示,分区表在全表扫描时将比非分区表需要更多的逻辑读

SQL> select count(*) from test1 where test_id = 1000;

COUNT(*)
----------
1

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
393 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from test2 where test_id = 337000;

COUNT(*)
----------
1

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
393 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

在没有与分区字段组合查询时,分区表需要更多的逻辑读

SQL> select count(*) 
2 from test1 
3 where id = 1000 
4 and log_time between to_date('2004-11-01','yyyy-mm-dd') and to_date('2004-11-30 23:59:59','yyyy-mm-dd hh24:mi:ss');

COUNT(*)
----------
28

Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
393 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

SQL> select count(*) 
2 from test2
3 where id = 1000 
4 and log_time between to_date('2004-11-01','yyyy-mm-dd') and to_date('2004-11-30 23:59:59','yyyy-mm-dd hh24:mi:ss');

COUNT(*)
----------
28

Elapsed: 00:00:00.00

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
393 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

与分区字段组合查询时,分区表比非分区表有更好的性能优势



ORACLE8的分区管理[zt]
--------------------------------------------------------------------------------
摘要：本篇文章介绍了ORACLE数据库的新特性—分区管理，并用例子说明使用方法。
关键词：ORACLE，分区


一、 分区概述：
　　为了简化数据库大表的管理，ORACLE8推出了分区选项。分区将表分离在若干不同的表空间上，用分而治之的方法来支撑无限膨胀的大表，给大表在物理一级的可管理性。将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。针对当前社保及电信行业的大量日常业务数据，可以推荐使用ORACLE8的该选项。


二、分区的优点：
1 、增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用；
2 、减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，故能比整个大表修复花的时间更少；
3 、维护轻松：如果需要重建表，独立管理每个分区比管理单个大表要轻松得多；
4 、均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；
5 、改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快；
6 、分区对用户透明，最终用户感觉不到分区的存在。


三、分区的管理：

1 、分区表的建立：
　　某公司的每年产生巨大的销售记录，DBA向公司建议每季度的数据放在一个分区内，以下示范的是该公司1999年的数据(假设每月产生30M的数据)，操作如下：
STEP1、建立表的各个分区的表空间：
CREATE TABLESPACE ts_sale1999q1
DATAFILE ‘/u1/oradata/sales/sales1999_q1.dat’
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
CREATE TABLESPACE ts_sale1999q2
DATAFILE ‘/u1/oradata/sales/sales1999_q2.dat’
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
CREATE TABLESPACE ts_sale1999q3
DATAFILE ‘/u1/oradata/sales/sales1999_q3.dat’
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
CREATE TABLESPACE ts_sale1999q4
DATAFILE ‘/u1/oradata/sales/sales1999_q4.dat’
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
STEP2、建立基于分区的表：
CREATE TABLE sales
(invoice_no NUMBER,
...
sale_date DATE NOT NULL )
PARTITION BY RANGE (sale_date)
(PARTITION sales1999_q1
VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale1999q1,
PARTITION sales1999_q2
VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale1999q2,
PARTITION sales1999_q3
VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale1999q3,
PARTITION sales1999_q4
VALUES LESS THAN (TO_DATE(‘2000-01-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale1999q4 );


2 、分区表的扩容：

到了1999年年底，DBA应向表中加入2000年的表空间，同样是每季度一个表空间，由于公司业务欣欣向荣，预计每个分区为40M，操作如下。
STEP1、建立表空间：
CREATE TABLESPACE ts_sale2000q1
DATAFILE ‘/u1/oradata/sales/sales2000_q1.dat’
SIZE 130M
DEFAULT STORAGE (INITIAL 40m NEXT 40m MINEXTENTS 3 PCTINCREASE 0)
其他表空间ts_sale2000q2,ts_sale2000q3,ts_sales2000q4如法炮制。
STEP2、为表添加表空间：
ALTER TABLE sales
ADD PARTITION sales2000_q1
VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale2000q1;
其他分区sales2000_q1，sales2000_q1，sales2000_q1如法炮制。


3 、删除不必要的分区：

公司规定：销售的明细数据两年内必须保存在线。到2001年，DBA必须将1999年的数据备份（备份方法见5、EXPORT分区），将1999年的分区删除，将空间供后来的数据使用。如此循环，永远保持两年的销售数据在线。

STEP1、DROP 分区：
ALTER TABLE sales
DROP PARTION sales1999_q1;
ALTER TABLE sales
DROP PARTION sales1999_q2;
ALTER TABLE sales
DROP PARTION sales1999_q3;
ALTER TABLE sales
DROP PARTION sales1999_q4;
STEP2、利用操作系统的工具删除以上表空间占用的文件（表空间基于裸设备无须次步）,UNIX系统为例：
oracle$ rm /u1/oradata/sales/sales1999_q1.dat
oracle$ rm /u1/oradata/sales/sales1999_q2.dat
oracle$ rm /u1/oradata/sales/sales1999_q3.dat
oracle$ rm /u1/oradata/sales/sales1999_q4.dat


4 、分区的其他操作：

分区的其他操作包括截短分区（truncate），将存在的分区划分为多个分区(split)，交换分区(exchange)，重命名（rename）,为分区建立索引等。DBA可以根据适当的情况使用。
以下仅说明分裂分区（split）,例如该公司1999年第四季度销售明细数据急剧增加（因为庆国庆、迎千禧、贺回归），DBA向公司建议将第四季度的分区划分为两个分区，每个分区放两个月份的数据，操作如下：
STEP1、按（1）的方法建立两个分区的表空间ts_sales1999q4p1,
ts_sales1999q4p2；
STEP2、给表添加两个分区sales1999_q4_p1,sales1999_q4_p2;
STEP3、分裂分区：
ALTER TABLE sales
SPLIT PARTITON sales1999_q4
AT TO_DATE (‘1999-11-01’,’YYYY-MM-DD’)
INTO (partition sales1999_q4_p1, partition sales1999_q4_p2)


5 、查看分区信息：

DBA要查看表的分区信息，可查看数据字典USER_EXTENTS,操作如下：
SVRMGRL>SELECT * FROM user_extents WHERE SEGMENT_NAME=’SALES’;
SEGMENT_NA PARTITION_ SEGMENT_TYPE TABLESPACE
---------- ------------ --------------- --------------
SALES SALES1999_Q1 TABLE PARTITION TS_SALES1999Q1
SALES SALES1999_Q2 TABLE PARTITION TS_SALES1999Q2
SALES SALES1999_Q3 TABLE PARTITION TS_SALES1999Q3
SALES SALES1999_Q4 TABLE PARTITION TS_SALES1999Q4
SALES SALES2000_Q1 TABLE PARTITION TS_SALES1999Q1
SALES SALES2000_Q2 TABLE PARTITION TS_SALES1999Q2
SALES SALES2000_Q3 TABLE PARTITION TS_SALES1999Q3
SALES SALES2000_Q4 TABLE PARTITION TS_SALES1999Q4


5 、EXPORT分区：

ORACLE8的EXPORT 工具可在表的分区以及导出数据，例如到2001年，DBA必须将1999年的数据按分区导出，操作如下：
oracle$ exp sales/sales_password tables=sales:sales1999_q1 rows=Y
file=sales1999_q1.dmp
oracle$ exp sales/sales_password tables=sales:sales1999_q2 rows=Y
file=sales1999_q2.dmp
oracle$ exp sales/sales_password tables=sales:sales1999_q3 rows=Y
file=sales1999_q3.dmp
oracle$ exp sales/sales_password tables=sales:sales1999_q4 rows=Y
file=sales1999_q4.dmp


6 、IMPORT分区：

ORACLE8的IMPORT 工具可在表的分区以及导入数据，例如在2001年，用户要查看1999年的数据，DBA必须导入1999年的数据，使之在线，操作如下：
STEP1、建立表的1999年的四个表空间和相应的分区，参照（2）；
STEP2、导入数据：
oracle$ imp sales/sales_password FILE =sales1999_q1.dmp
TABLES = (sales:sales1999_q1) IGNORE=y
oracle$ imp sales/sales_password FILE =sales1999_q2.dmp
TABLES = (sales:sales1999_q2) IGNORE=y
oracle$ imp sales/sales_password FILE =sales1999_q3.dmp
TABLES = (sales:sales1999_q3) IGNORE=y
oracle$ imp sales/sales_password FILE =sales1999_q4.dmp
TABLES = (sales:sales1999_q4) IGNORE=y

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8570952/viewspace-713546/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8570952/viewspace-713546/

cl2516
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
存储管理1-分区管理，伙伴系统
白小羊的博客
06-25 785
本文为笔记总结 多道程序存储管理： 分区程序管理：分为系统区，用户区（再分为若干分区） 一 固定分区存储管理（分区大小个数确定不变）； 管理：内存分配表：记录分区号，起始地址，空间大小，状态及占用进程名 特点：简单，开销小，内部碎片浪费，分区总数固定，缺乏内存保护；程序大小不超过分区大小 二 可变分区管理 从用户区动态创建，不预先分区； 最后会形成占用区与空
操作系统的内存管理---分区，分段，分页，伙伴系统，交换分区
qq_41885826的博客
07-28 1919
1.内存管理方法 内存管理主要包括虚拟地址，地址变换，内存分配和回收，内存扩充，内存共享和保护等功能。 2.分区存储管理方式 为了支持多道程序系统和分时系统，支持多个程序并发执行，引入了分区存储管理方式，分区内存管理是把内存分为一些大小相等或不等的分区，操作系统占用一部分，其余的分区由应用程序使用，每一个应用程序占用一个或多个分区。分区式内存管理虽然可以支持并发，但...
分区表一些特性
minwang593的专栏
10-18 522
(一)创建分区表  create table tmp1 (   DEALER_ID            NUMBER(8) not null,   CO_NO                CHAR(12) not null,   SO_NO                CHAR(12) not null,   COMPANY_ID           NUMBER(8)
分区表的概念、优点以及类型
最新发布
m0_71335202的博客
09-20 937
分区表的概念、优点以及类型
存储管理-分区存储管理
k17041114的博客
11-21 2232
存储管理-分区存储管理 分区管理把内存划分成若干个大小不等的区域，除操作系统占用一个区域之外，其余由多道环境下的各并发进程共享。分区管理是满足多道程序设计的一种最简单的存储管理方法。 固定分区法 固定分区法把内存区固定地划分为若干个大小不等的区域 划分原则 分区划分的原则由系统操作员或操作系统决定。例如可划分为长作业分区和短作业分区 特点 分区一旦划分结束，在整个执行过程中每个分区的长度...
ORACLE 分区表的设计
09-11
Oracle 分区表是一种高效的数据管理技术，它将大型表和索引分解为多个较小且易于管理的部分，称为分区。这种设计旨在解决大数据量带来的查询性能问题和数据管理复杂性。分区表的核心思想是通过将数据按特定规则划分...
分区表医生
07-30
三、《分区表医生》的功能特点 1. 检测分区表：该软件能全面扫描硬盘，发现分区表存在的任何异常，包括丢失的分区、错误的分区大小等。 2. 修复分区表：针对检测到的问题，软件提供自动修复功能，尝试恢复正确的...
PTDD分区表医生
06-20
"PTDD分区表医生"是一款专门用于处理硬盘分区表问题的工具，尤其在数据恢复和磁盘管理领域具有显著的功效。这款软件以其绿色免安装的特点，让用户能够方便快捷地进行操作，无需担心对系统造成不必要的影响。 分区表...
分区表的管理
09-09
### 分区表的管理知识点详解 #### 一、分区表的限制 在Oracle 10g中，分区表的最大分区数量被限定为1024K - 1个分区。这意味着用户可以创建最多1024K - 1个分区来管理和组织数据，这为大型数据库提供了足够的灵活...
ORACLE分区表的创建
08-30
### ORACLE 分区表的创建详解 #### 一、概述 在Oracle数据库中，分区是一种高效的数据管理方式，尤其适用于大型表和索引组织表。通过将数据逻辑地分割成多个独立的部分（即分区），可以显著提高查询性能，并简化表...
表分区的优缺点
hqmln的博客
06-15 1万+
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个“表空间”(物理文件上)，这样查询数据时，不至于每次都扫描整张表而只是从当前的分区查到所要的数据大大提高了数据查询的速度。   Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好...
oracle表分区以及优缺点
热门推荐
panyuxin_的博客
07-06 1万+
  分区表： 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应 该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。      表分区的具体作用   Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分...
oracle表分区详解
weixin_33805992的博客
07-01 3533
此文从以下几个方面来整理关于分区表的概念及操作: 1.表空间及分区表的概念 2.表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5.对表分区的维护性操作. (1.) 表空间及分区表的概念 表空间：　　是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间...
oracle
巩新华
08-20 202
1.表空间及分区表的概念 表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表， 所以称作表空间。 分区表： 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 2...
oracle分区表优点
冰刀(skate)
09-13 3564
分区表的特点,今天再和群里的朋友讨论的时候,突然想到分区表,记得以前面试的时候,有个主考观提出一个问题分区表有什么好处,他说再查询某个分区的数据的时候,完全可以用sql的条件来过滤.其实他只因为提这个问题,是因为他只从功能上来考虑了,我们在能实现同样的功能条件下,就要考虑他们那的有缺点了,当用sql限制条件查询大表时,它是以整表为基础来操作的,消耗的资源要多;但分区表,它是以本身这个小分区表为
[内存管理] 内存五大分区
Damonvv的博客
07-30 6164
IOS 开发中，不少人对内存分区不是很明白，为了搞明白具体分区，花了点时间总结了一下内存的5大分区
mysql 表分区优缺点_mysql分区表的原理和优缺点
weixin_36160690的博客
01-19 4135
1.分区表的原理分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎)，分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。在分区表上的操作按照下面的操作逻辑进行：...
mysql分区表的好处_浅谈MySQL分区表优点和限制
weixin_33694391的博客
01-31 4155
分区是将一个表的数据按照某种方式，逻辑上仍是一个表，也就是所谓的分区表。分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成一些小对象，从而实现对数据的分化管理。作为MySQL数据库中的一个重要机制，MySQL分区表优点和限制也是一目了然的，然而又能够同时实现共存。一、我们先来看MySQL分区...
分区表的使用场景有哪些？
像豆芽一样优秀
07-09 1756
分区表 1. 什么场景的数据需要分区 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均为历史数据。 分区表的数据更加容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。还可以对一个独立分区进行优化、检查、修复等操作。 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。 可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引互斥访问。 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果极好。 2. 分区的限制 一个表最多只
Oracle 表分区技术及其应用
2. 在分区表上建索引及多索引的使用和非分区表一样。 3. 在维护分区的时候可能对分区的索引会产生一定的影响，可能需要在维护之后重建索引。 Oracle 表分区的版本特性： 1. Oracle 8i：引入了分区表的概念，支持...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值