分区表 之 Interval分区 和 虚拟列 按星期分区表

最新推荐文章于 2023-03-21 23:05:42 发布
最新推荐文章于 2023-03-21 23:05:42 发布
阅读量810 收藏
点赞数
分类专栏： Oracle 基础知识 文章标签： 2010 oracle11g date table oracle 测试

今天朋友问我, 说想建一个按星期分区的表，就是按星期几分区，没用过，就搜了下，可以用Oracle 11g 中虚拟列来实现。在11g之前 分区表的partition key必须是物理存在的。 11g开始提供了虚拟列，并且可以作为partition key 。

关于分区表，之前也做个整理：

Oracle 分区表 

http://blog.csdn.net/xujinyang/article/details/6832735


Oracle 11g 新特性简介

http://blog.csdn.net/xujinyang/article/details/6829731

 

一．按星期分区表示例：

1. 创建分区表：

CREATE TABLE PT

(

   getdate   date NOT NULL,

   wd        NUMBER GENERATED ALWAYS AS (TO_NUMBER (TO_CHAR (getdate, 'D'))) VIRTUAL

)

PARTITION BY LIST (wd)

   (PARTITION Sun  VALUES (1),

    PARTITION Mon  VALUES (2),

    PARTITION Tue  VALUES (3),

    PARTITION Wed  VALUES (4),

    PARTITION Thu  VALUES (5),

    PARTITION Fri  VALUES (6),

PARTITION Sat   VALUES (7)

);

2. 插入测试数据

SQL> insert into pt(getdate) values(sysdate);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-1);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-2);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-3);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-4);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-5);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-6);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-7);

已创建 1 行。

SQL> commit;

提交完成。

3. 查看数据：

SQL> select * from pt;

GETDATE                WD

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

06-6月 -10              1

07-6月 -10              2

08-6月 -10              3

09-6月 -10              4

10-6月 -10              5

03-6月 -10              5

04-6月 -10              6

05-6月 -10              7

已选择8行。

SQL> alter session set nls_date_format='YYYY-MM-DD';

会话已更改。

SQL> select * from pt;

GETDATE            WD

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

2010-06-06          1

2010-06-07          2

2010-06-08          3

2010-06-09          4

2010-06-10          5

2010-06-03          5

2010-06-04          6

2010-06-05          7

已选择8行。

4. 查看每个分区里的内容：

SQL> select * from pt partition(sun);

GETDATE            WD

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

2010-06-06          1

SQL> select * from pt partition(mon);

GETDATE            WD

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

2010-06-07          2

SQL> select * from pt partition(tue);

GETDATE            WD

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

2010-06-08          3

SQL> select * from pt partition(wed);

GETDATE            WD

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

2010-06-09          4

SQL> select * from pt partition(fri);

GETDATE            WD

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

2010-06-04          6

SQL> select * from pt partition(sat);

GETDATE            WD

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

2010-06-05          7

SQL>

注：如果是10g，就要添加一物理列。

二． Interval分区

通过以上操作，就完成了一个按星期分区的分区表。 在Oracle Database 11g中还可以创建新类型的Interval分区表，Interval类型分区表，可以根据加载数据，自动创建指定间隔的分区。


创建按月分区的分区表：

1. 创建分区表

/* Formatted on 2010/6/10 20:21:12 (QP5 v5.115.810.9015) */

CREATE TABLE intervalpart (c1 NUMBER, c3 DATE)

PARTITION BY RANGE (c3)

   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )

   (PARTITION part1

       VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')),

    PARTITION part2

       VALUES LESS THAN (TO_DATE ('02/12/2010', 'MM/DD/YYYY'))

  )

注意：如果在建Interval分区表是没有把所有的分区写完成，在插入相关数据后会自动生成分区

2. 查看现在表的分区：

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART';

TABLE_NAME                     PARTITION_NAME

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

INTERVALPART                   PART1

INTERVALPART                   PART2

3.  插入测试数据：

SQL> begin

  2  for i in 0 .. 11 loop

  3  insert into intervalpart values(i,add_months(to_date('2010-1-1','yyyy-mm-dd'),i));

  4  end loop ;

  5  commit;

  6  end;

  7  /

PL/SQL 过程已成功完成。

补充：add_months()函数获取前一个月或者下一个月的月份， 参数中 负数 代表 往前， 正数 代表 往后。

--上一个月

select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;

--下一个月 

select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;

4. 观察自动创建的分区：

SQL>  select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART';

TABLE_NAME                     PARTITION_NAME

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

INTERVALPART                   PART1

INTERVALPART                   PART2

INTERVALPART                   SYS_P22

INTERVALPART                   SYS_P23

INTERVALPART                   SYS_P24

INTERVALPART                   SYS_P25

INTERVALPART                   SYS_P26

INTERVALPART                   SYS_P27

INTERVALPART                   SYS_P28

INTERVALPART                   SYS_P29

INTERVALPART                   SYS_P30

INTERVALPART                   SYS_P31

已选择12行。

5. 查看分区内容：

SQL> select * from INTERVALPART;

        C1 C3

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

         1 2010-01-01

         0 2010-01-01

         1 2010-02-01

         2 2010-03-01

         3 2010-04-01

         4 2010-05-01

         5 2010-06-01

         6 2010-07-01

         7 2010-08-01

         8 2010-09-01

         9 2010-10-01

        10 2010-11-01

        11 2010-12-01

已选择13行。

SQL> select * from INTERVALPART partition(part1);

        C1 C3

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

         1 2010-01-01

         0 2010-01-01

SQL> select * from INTERVALPART partition(part2);

        C1 C3

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

         1 2010-02-01

下面创建一个以天为间隔的分区表：

1. 创建分区表：

SQL> create table dave
  2  (
  3  id    number,
  4  dt    date
  5  )
  6  partition by range (dt)
  7  INTERVAL (NUMTODSINTERVAL(1,'day'))
  8  (
  9  partition p100101 values less than (to_date('2010-01-01','yyyy-mm-dd'))
10  );

2. 查看表分区：
SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE';

TABLE_NAME                     PARTITION_NAME

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

DAVE                           P100101

3. 插入测试数据：

SQL> begin

  2  for i in 1 .. 12 loop

  3  insert into dave values(i,trunc(to_date('2010-1-1','yyyy-mm-dd')+i));

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL 过程已成功完成。

4. 观察自动创建的分区：

SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE';

TABLE_NAME                     PARTITION_NAME

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

DAVE                           P100101

DAVE                           SYS_P32

DAVE                           SYS_P33

DAVE                           SYS_P34

DAVE                           SYS_P35

DAVE                           SYS_P36

DAVE                           SYS_P37

DAVE                           SYS_P38

DAVE                           SYS_P39

DAVE                           SYS_P40

DAVE                           SYS_P41

DAVE                           SYS_P42

DAVE                           SYS_P43

已选择13行。

5. 查看分区内容：

SQL> select * from dave partition(SYS_P32);

        ID DT

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

         1 2010-01-02

SQL> select * from dave partition(SYS_P33);

        ID DT

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

         2 2010-01-03

SQL> select * from dave partition(SYS_P34);

        ID DT

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

         3 2010-01-04

SQL> select * from dave;

        ID DT

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

         1 2010-01-02

         2 2010-01-03

         3 2010-01-04

         4 2010-01-05

         5 2010-01-06

         6 2010-01-07

         7 2010-01-08

         8 2010-01-09

         9 2010-01-10

        10 2010-01-11

        11 2010-01-12

        12 2010-01-13

已选择12行。

我们对Interval分区 进行一个总结，通过上面的2个例子我们发现一个现象，就是在创建分区的时候，如果选择了Interval分区，那么在该分区表中，没有创建的分区会自动生成。 这样有什么好处呢？

我们假设一下，由于数据量的巨大，所以表设计为每天一个分区，数据库管理员日常要做的一件重复而无聊的工作就是每隔一天要生成新的分区，用以存储第二天的数据。如果在Oracle 11g，我们就可以用Interval分区很好的实现这个功能，Oracle会自动完成剩下的分区，是不是很方便呢？

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

小宝老豆
关注
专栏目录
分区表之Interval分区和虚拟列按星期分区表
塞尔达的传说
04-07 185
原 分区表 之 Interval分区 和 虚拟列 按星期分区表https://blog.csdn.net/tianlesoftware/article/details/5662337版权声明： https://blog.csdn.net/tianlesoftware/article/details/5662337 今天朋友问我, 说想建一个按星期分区的表，就是按星期几分区，没用过，就搜了下，可以用...
oracle创建range分区表,Oracle分区表详解，分区表创建，分区表按日期划分
weixin_30286727的博客
04-12 2058
前言(絮叨絮叨)最近接手一个项目，要求与Oracle数据库做对接，分区表还是要做的，可是网上根本涉及内容很少，没办法，用一下午才弄明白，截至发稿，饭都没吃呢。。。。。。。。分区表概念：正常增删改查都行，SQL语句正常写，但是查询可以通过分区表进行区分查询，可以限定范围什么的，特别方便，而且快，不絮叨，直接说主题。分区表创建先说下，里面有两个地方需要注意，一个是分区字段名，这里用的是：WORKDAT...
11g分布表新特性——Interval分区
jianhai1229的博客
10-29 1965
作为一个成熟的DBMS，在每个新的版本中Oracle都会推出一些有益的技术尝试和新特性。熟悉掌握这些新特性，有助于我们更快的适应新版本Oracle软件，掌握其发展动态脉络，及时优化我们的工作方式。 分区表是我们经常使用的一种堆表优化手段。借助分区表，我们可以将一个数据量巨大的表根据业务需求水平切分为不同的分区块。将数据访问处理和运维管理集中在特定的数据块内部，以期减少全表大面积搜索。 相对于普通数...
KingbaseES interval 分区表介绍
lyu1026的博客
06-02 1397
KingbaseES从V008R006C005B0041版本开始支持Oracle的Interval分区表功能。 Interval分区表是一种特殊的范围分区表。当执行INSERT或者UPDATE时，若数据超过所有现有范围分区时由数据库根据定义的间隔参数自动创建新的分区。 Interval分区只能指定一个分区键列，并且数据类型必须为NUMBER或者DATE类型。 语法 CREATE TABLE ta...
oracle 根据日期自动生成分区表
最新发布
monkey00001的博客
03-21 2044
如果执行含有函数的sql时报错：”INTERVAL YEAR TO MONTH literal“。interval_unit: ‘YEAR’, ‘MONTH’ ，或其他可以转换成这两个值之一的表达式。常用的单位有 (‘day’,‘hour’,‘minute’,‘second’)NumToYMInterval(1, ‘YEAR’) ：一年后的间隔。NumToYMInterval(-1, ‘MONTH’): 一个月前。常用的单位有（‘year’,‘month’）,x是一个数字,c是一个字符串,
Oracle 虚拟列 子分区 virtual column partition
weixin_34326558的博客
05-23 289
Oracle 11g 虚拟列子分区实验以下两个create 语句均为创建成功；需求描述：项目要求对3个字段进行分区，如时间，小时，还有个区域ID，便联想到虚拟列。droptabletest_partpurge; createtabletest_part (t_timedate, hournumber, city_idnumber, hour_ci...
ORACLE表自动按月分区步骤
08-22
**步骤3：创建按月自动分区表** 使用如下SQL语句创建一个按月自动分区的新表`USER_ORDER`，该表仅包含一个初始分区`p_month_1`。需要注意的是，分区的起始时间（例如`2014-01-01`）应该选择为现有表中最早的日期月份...
Oracle分区表(Partition Table)使用详解
06-10
Oracle分区表是Oracle数据库系统中一个强大的特性，用于提高数据管理效率和查询性能。通过将大表分成逻辑上独立的小块，每个块对应一个特定的数据范围或键值，可以实现数据的并行处理，简化管理和优化查询。在本文中...
11g分布表新特性——Interval分区（上）
星星_月亮的光芒
11-28 802
作为一个成熟的DBMS，在每个新的版本中Oracle都会推出一些有益的技术尝试和新特性。熟悉掌握这些新特性，有助于我们更快的适应新版本Oracle软件，掌握其发展动态脉络，及时优化我们的工作方式。   分区表是我们经常使用的一种堆表优化手段。借助分区表，我们可以将一个数据量巨大的表根据业务需求水平切分为不同的分区块。将数据访问处理和运维管理集中在特定的数据块内部，以期减少全表大面积搜索。  
分区表，对表进行按照年、月、周、日进行分区
文档搬运工
10-07 2730
RDBMS 11.2.0.4  之前在学习分区表的是，对表分区，按照年，月分区的多一些。 对于按照周，日进行分区，做的少一些。最近因为业务需要，需要对表进行按照天或者周进行分区。通过了解学习。 发现主要是使用这两个函数：NUMTODSINTERVAL和NUMTOYMINTERVAL 关于这两个函数，官方网站有说明： NUMTODSINTERVAL Purpose NUMTODSINT...
分区表的概念与创建
鲨鱼胃的博客
10-11 5165
文章目录概念 概念 oracle 11g 支持自动分区，不过得在创建表时就设置好分区。 　　如果已经存在的表需要改分区表，就需要将当前表 rename后，再创建新表，然后复制数据到新表，然后删除旧表就可以了。 　　分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。 　　Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同...
oracle 11g分区表新特性---interval分区 的坑
cizhilan8323的博客
07-18 571
oracle 11g分区表新特性---interval分区 的坑 oracle 11g的范围分区表中新增的interval分区特性，此种范围分区不需要定义MAXVALUE，Oracle会根据分区定义的步长来动态的分配...
分区表之间隔分区（interval）
crcz16618的博客
03-13 936
When to Use Range or Interval Partitioning Range partitioning is a convenient method for partitioning historica...
Oracle 11g创建Interval分区表
欧辰的专栏
07-09 5042
1.    interval使用之后，就不用手工增加分区，来适应数据的增长。 2.    根据年月 INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))             INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))    根据天   INTERVAL(NUMTODSINTERVAL(1,'DAY')) 3.  CREA
oracle 11g的interval partition 如何按天\周\月\年分区
热门推荐
sdtysdu2000的专栏
06-09 1万+
ORACLE11g的表分区功能中可以建立新类型的Interval分区表，它可以根据加载数据，自动创建指定间隔的分区，也就是说建立了Interval分区 ，就不必像ORACLE10以前的表分区那样需要提前为新数据建立分区，它会自动创建，以下是创建的语句。注意其中INTERVAL后面指定的关键词，对于月与天是不同的，具体已经标注出来。 按年分区： CREATE TABLE TEST
11g分布表新特性——Interval分区（下）
星星_月亮的光芒
11-28 789
Oracle 11g中推出的Interval-Partition特性，是针对Range类型分区的一种功能拓展。对连续数据类型的Range分区，如果插入的新数据值与当前分区均不匹配，Interval-Partition特性可以实现自动的分区创建。   目前的Interval-Partition支持的Range分区键类型只有number和date两种类型。在上面的示例中，我们已经演示了数字numb
Oracle分区表详解：存储管理与优化策略
这可以基于多种标准进行，如范围分区（按特定范围划分）、扩展分区（包含子分区）、Interval分区（按时间间隔划分）、外键分区（根据关联表的某个字段）、模拟列分区（根据表中的某个值）等。Oracle从8.0.5版本开始...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值