Oracle分区表基础运维-02范围分区

IT小Chen 2021-04-13

425

Range Partitioning

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition.

Range partitioning is the most common type of partitioning and is often used with dates. For a table with a date column as the partitioning key, the January-2017 partition would contain rows with partitioning key values from 01-Jan-2017 to 31-Jan-2017.

Each partition has a VALUES LESS THAN clause, that specifies a non-inclusive upper bound for the partitions. Any values of the partitioning key equal to or higher than this

literal are added to the next higher partition. All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause of the previous partition.

A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partitioning key, including the NULL value.

创建范围分区表range_part_tab

create table range_part_tab (id number,deal_date date,area_code number,contents varchar2(4000))
partition by range (deal_date)
(
partition p1 values less than (TO_DATE('2019-06-01', 'YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2019-07-01', 'YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-08-01', 'YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-09-01', 'YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-10-01', 'YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-11-01', 'YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2019-12-01', 'YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2020-01-01', 'YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2020-02-01', 'YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2020-03-01', 'YYYY-MM-DD')),
partition p11 values less than (TO_DATE('2020-04-01', 'YYYY-MM-DD')),
partition p12 values less than (TO_DATE('2020-05-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
);
复制

插入数据：

---插入一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：

insert into range_part_tab
  (id, deal_date, area_code, contents)
  select rownum,
         to_date(to_char(sysdate - 365, 'J') +
                 TRUNC(DBMS_RANDOM.VALUE(0, 365)),
                 'J'),
         ceil(dbms_random.value(590, 599)),
         rpad('*', 400, '*')
    from dual
  connect by rownum <= 100000;
commit;
复制

函数说明：

ceil(n) 取大于等于数值n的最小整数；floor(n)取小于等于数值n的最大整数；

select ceil(9.1), floor(9.1), ceil(9.9), floor(9.9) from dual;
复制

lpad( string1, padded_length, [ pad_string ] )

rpad( string1, padded_length, [ pad_string ] )

select rpad('CJC', 10, '*'),lpad('CJC', 10, '*') from dual;
复制

查看分区表信息

---查看当前用户下有哪些分区表

select TABLE_NAME from user_tables a where a.partitioned='YES';
复制

---查看分区表名，分区名，表空间等信息

select table_name, partition_name, tablespace_name, high_value
  from user_tab_partitions
 where table_name = 'RANGE_PART_TAB';
复制

---查看high_value

with xs as
 (select x.*,
         to_date(substr(high_value, 11, 19), 'YYYY-MM-DD HH24:MI:SS') dates,
         replace(high_value, 'TIMESTAMP''') datestr
    from xmltable('/ROWSET/ROW' passing (select dbms_xmlgen.getxmltype('
select t.table_owner, t.table_name, t.partition_name, t.high_value
  from dba_tab_partitions t')
                      from dual) columns table_owner varchar2(30) path
                   'TABLE_OWNER',
                   table_name varchar2(30) path 'TABLE_NAME',
                   partition_name varchar2(30) path 'PARTITION_NAME',
                   high_value varchar2(30) path 'HIGH_VALUE') x)
select xs.datestr, p.table_name, p.partition_name, p.tablespace_name
  from xs, dba_tab_partitions p
 where xs.table_owner = 'CJC'
   and p.table_owner = xs.table_owner
   and p.table_name = xs.table_name
   and p.partition_name = xs.partition_name
 order by p.table_owner, p.table_name, p.partition_position;
复制

SQL> SELECT P.TABLE_NAME, P.PARTITION_NAME, P.HIGH_VALUE FROM USER_TAB_PARTITIONS P;
复制

---查看时间分布

select count(*) from RANGE_PART_TAB partition(P1);
select count(*) from RANGE_PART_TAB partition(P2);
select count(*) from RANGE_PART_TAB partition(P3);
select count(*), to_char(deal_date, 'yyyy-mm')
  from RANGE_PART_TAB
 group by to_char(deal_date, 'yyyy-mm')
 order by 2;
复制

---通过object_id查看每个分区数据分布情况

select * from dba_segments where segment_name = 'RANGE_PART_TAB';
select * from dba_objects where object_name='RANGE_PART_TAB';
select dbms_rowid.rowid_object(rowid) obj_id, count(*)
  from RANGE_PART_TAB
 group by dbms_rowid.rowid_object(rowid)
 order by 1;
复制

更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：

http://blog.itpub.net/29785807/

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle分区表基础运维-02范围分区

评论

相关阅读