java oracle 分区查询_Oracle的分区表

分区表就是通过分区技术使一张大表拆分成多个表分区 (segment)，就像用一把刀将一个大蛋糕切成各个小份，方便分发和食

用；而分区表技术正是为了提高大表的性能以及更方便运维人员对其进行维护；所以对于分区表来说，

所有独立的表结构都是一样的，例如table1有个 userid varchar(2),那相同的，table2也有相对应的userid varchar(2)；但是

各个表可以处在不同的表空间里，但是表空间默认的block_size必须一致。即使我们对一张大表进行分区，但是对于应用层来

说是感知不到的，也就是说完全不会影响到应用层。

详细来说一下分区表有什么好处：

便于维护：例如一张大表(yxtb)根据时间来进行分区，2018年 每个月都有单独一个表(date);某一天突然接到请求，要查询7月

份业务量超过xx一共有多少天，这样我们便可以单独针对七月份这个表进行操作，无论是添加索引还是导入导出，备份恢复都

很简便；如果某天业务员不小心删掉了7月份的数据，需要我们紧急恢复的时候，这时其他月份的数据还是完好的，不影响使

用，并且再次进行恢复任务量也相对小很多。

像上例所说，yxtb根据时间(date)来分区，这个时间date便叫做 分区键，对此表进行dml操作时，oracle会根据分区键选择相

对应的分区进行操作！

下面对分区表进行简单的实验：

先查询所需要进行分区表的表结构：

sys@FX1TEST>desc fqtbb

Name    Null?    Type

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

ID         NUMBER

YXTIME      DATE

HASH     VARCHAR2(50)

GOODLOOK     VARCHAR2(20)

查询表当前的数据：

sys@FX1TEST>select * from fqtbb;

ID YXTIME       HASH    GOODLOOK

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

1 2011-01-02 00:00:00 java111    no

1 2011-04-02 00:00:00 java121    no

1 2011-05-02 00:00:00 java131    no

1 2012-02-02 00:00:00 java141    no

1 2013-01-02 00:00:00 java1ds    no

1 2012-06-04 00:00:00 java1da    no

1 2013-12-21 00:00:00 java771    no

1 2013-02-02 00:00:00 java771    no

1 2014-06-25 00:00:00 java661    no

1 2014-01-02 00:00:00 java156    no

1 2015-05-22 00:00:00 java132    no

1 2015-06-07 00:00:00 java134    no

1 2016-04-21 00:00:00 java152    yes

1 2016-11-11 00:00:00 java761    yes

1 2016-01-21 00:00:00 java171    yes

1 2017-04-21 00:00:00 java711    yes

1 2017-05-05 00:00:00 java717    yes

1 2017-01-02 00:00:00 java174    yes

1 2018-03-01 00:00:00 java172    yes

1 2018-01-23 00:00:00 java176    yes

1 2018-12-22 00:00:00 java166    yes

21 rows selected.

下面便是一些常用的分区方法

范围分区：利用分区键定义范围，根据实际取值，进行分区选择，这种范围一般以数字形式存储，例如时间，分数，工资等等，所以不可避免的这种分区方法做不到均匀分区。

这里我们用时间范围进行划分 :

create table fqtbb_part (

id number,

yxtime date,

hash varchar(50),

goodlook varchar(20))

PARTITION BY RANGE (yxtime)

(

PARTITION fqtbb_part1 VALUES LESS THAN (TO_DATE('2011-01-01','yyyy-mm-dd'))

TABLESPACE test,

PARTITION fqtbb_part2 VALUES LESS THAN (TO_DATE('2014-01-02','yyyy-mm-dd'))

TABLESPACE test1,

PARTITION fqtbb_part3 VALUES LESS THAN (MAXVALUE)

TABLESPACE test2

);

Table created.

将大表中的数据插到新建表中：

sys@FX1TEST>insert into fqtbb_part select * from fqtbb;

21 rows created.

sys@FX1TEST>commit;

Commit complete.

接下来查询单独分区的数据信息

sys@FX1TEST>select * from fqtbb_part partition(fqtbb_part3);

ID YXTIME       HASH    GOODLOOK

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

1 2014-06-25 00:00:00 java661    no

1 2014-01-02 00:00:00 java156    no

1 2015-05-22 00:00:00 java132    no

1 2015-06-07 00:00:00 java134    no

1 2016-04-21 00:00:00 java152    yes

1 2016-11-11 00:00:00 java761    yes

1 2016-01-21 00:00:00 java171    yes

1 2017-04-21 00:00:00 java711    yes

1 2017-05-05 00:00:00 java717    yes

1 2017-01-02 00:00:00 java174    yes

1 2018-03-01 00:00:00 java172    yes

1 2018-01-23 00:00:00 java176    yes

1 2018-12-22 00:00:00 java166    yes

13 rows selected.

sys@FX1TEST>select * from fqtbb_part partition(fqtbb_part2);

ID YXTIME       HASH    GOODLOOK

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

1 2011-01-02 00:00:00 java111    no

1 2011-04-02 00:00:00 java121    no

1 2011-05-02 00:00:00 java131    no

1 2012-02-02 00:00:00 java141    no

1 2013-01-02 00:00:00 java1ds    no

1 2012-06-04 00:00:00 java1da    no

1 2013-12-21 00:00:00 java771    no

1 2013-02-02 00:00:00 java771    no

8 rows selected.

当然如果查询分区表的信息。也有相对应的表供用户查询。例如：

sys@FX1TEST>select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from dba_part_tables where table_name='FQTBB_PART';

TABLE_NAME       PARTITIONING_TYPE  PARTITION_COUNT STATUS

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

FQTBB_PART       RANGE3 VALID

查询表分区所处的表空间：

sys@FX1TEST>select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART';

PARTITION_NAME       NUM_ROWS TABLESPACE SEGMENT_

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

FQTBB_PART1TEST   YES

FQTBB_PART2TEST1   YES

FQTBB_PART3TEST2   YES

列分区

列分区根据分区键指定的取值列表进行分区，但是这种分区方法适合的比较少，例如部门列表、所属项目组名称等等

这里根据goodlook键进行定义：

create table fqtbb_parta (

id number,

yxtime date,

hash varchar(50),

goodlook varchar(20))

PARTITION BY list(goodlook)

(

PARTITION fqtbb_part1 VALUES ('yes')

TABLESPACE test,

PARTITION fqtbb_part2 VALUES ('no')

TABLESPACE test1,

PARTITION fqtbb_part3 VALUES (default)

TABLESPACE test2

);

Table created.

插入数据

sys@FX1TEST>insert into fqtbb_parta select * from fqtbb;

21 rows created.

sys@FX1TEST>commit;

Commit complete.

分区表创建完成之后进行查询验证：

sys@FX1TEST>select * from fqtbb_parta partition(fqtbb_part2);

ID YXTIME       HASH    GOODLOOK

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

1 2011-01-02 00:00:00 java111    no

1 2011-04-02 00:00:00 java121    no

1 2011-05-02 00:00:00 java131    no

1 2012-02-02 00:00:00 java141    no

1 2013-01-02 00:00:00 java1ds    no

1 2012-06-04 00:00:00 java1da    no

1 2013-12-21 00:00:00 java771    no

1 2013-02-02 00:00:00 java771    no

1 2014-06-25 00:00:00 java661    no

1 2014-01-02 00:00:00 java156    no

1 2015-05-22 00:00:00 java132    no

1 2015-06-07 00:00:00 java134    no

12 rows selected.

sys@FX1TEST>select * from fqtbb_parta partition(fqtbb_part1);

ID YXTIME       HASH    GOODLOOK

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

1 2016-04-21 00:00:00 java152    yes

1 2016-11-11 00:00:00 java761    yes

1 2016-01-21 00:00:00 java171    yes

1 2017-04-21 00:00:00 java711    yes

1 2017-05-05 00:00:00 java717    yes

1 2017-01-02 00:00:00 java174    yes

1 2018-03-01 00:00:00 java172    yes

1 2018-01-23 00:00:00 java176    yes

1 2018-12-22 00:00:00 java166    yes

9 rows selected.

sys@FX1TEST>select * from fqtbb_parta partition(fqtbb_part3);

no rows selected

下面是oracle 分区表常用的管理操作，如下：

由于我们之前再范围分区时，设置了default值，所以无法增加分区，所以这里先演示删除分区

sys@FX1TEST>alter table fqtbb_part drop partition fqtbb_part3;

Table altered.

sys@FX1TEST>select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART';

PARTITION_NAME       NUM_ROWS TABLESPACE_NAME      SEGMENT_

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

FQTBB_PART1      0 TEST     YES

FQTBB_PART2      8 TEST1     YES

添加分区

sys@FX1TEST>alter table fqtbb_part add partition p3 values less than (to_date('2019-09-09','yyyy-mm-dd'));

Table altered.

截断分区：截断分区是指清楚数据，保留分区结构，如下

sys@FX1TEST>alter table fqtbb_part truncate partition p3;

Table truncated.

合并分区：指将相邻的分区合并成一个分区，例如2019、2018 这两个分区，合并之后，分区将采用2019这个界限，并且合并是要合并到2019这个分区里；如下

sys@FX1TEST>alter table fqtbb_part merge partitions fqtbb_part1,fqtbb_part2 into partition fqtbb_part2;

Table altered.

sys@FX1TEST>select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART';

PARTITION_NAME       NUM_ROWS TABLESPACE_NAME      SEGMENT_

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

FQTBB_PART2SYSTEM     YES

P3SYSTEM     YES

拆分分区：有合并当然就有拆分，意思就是将一个分区拆成两个分区，而之前的分区将不存在；如下

sys@FX1TEST>alter table fqtbb_part split partition fqtbb_part2 at(to_date('2014-01-01','yyyy-mm-dd')) into (partition p1,partition p2);

Table altered.

sys@FX1TEST>select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART';

PARTITION_NAME       NUM_ROWS TABLESPACE_NAME      SEGMENT_

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

P1SYSTEM     YES

P2SYSTEM     YES

P3SYSTEM     YES

重命名分区：顾名思义，就是让分区换一个名字

sys@FX1TEST>alter table fqtbb_part rename partition p1 to p11;

Table altered.