ITPub博客

首页 > 数据库 > Oracle > oracle 根据虚拟列创建以周几为单位的分区表

oracle 根据虚拟列创建以周几为单位的分区表

原创 Oracle 作者:raysuen 时间:2021-01-28 17:13:04 1 删除 编辑
SQL> create table t2(rid number,rname varchar2(20),rdate date,week_num number generated always as (to_char(rdate,'d')))
  2  partition by list(week_num)
  3  (
  4  partition rdate1 values (1),
  5  partition rdate2 values (2),
  6  partition rdate3 values (3),
  7  partition rdate4 values (4),
  8  partition rdate5 values (5),
  9  partition rdate6 values (6),
10  partition rdate7 values (7)
11  );
Table created.
SQL> insert into t2(rid,rname,rdate) values(5,'thursday',sysdate);
1 row created.
SQL> insert into t2(rid,rname,rdate) values(6,'friday',sysdate+1);
1 row created.
SQL> insert into t2(rid,rname,rdate) values(7,'saturday',sysdate+2);
1 row created.
SQL> insert into t2(rid,rname,rdate) values(1,'sunday',sysdate+3);
1 row created.
SQL> insert into t2(rid,rname,rdate) values(2,'monday',sysdate+4);
1 row created.
SQL> insert into t2(rid,rname,rdate) values(3,'tuesday',sysdate+5);
1 row created.
SQL> insert into t2(rid,rname,rdate) values(4,'wednesday',sysdate+6);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t2;
       RID RNAME                RDATE          WEEK_NUM
---------- -------------------- ------------ ----------
         1 sunday               31-JAN-21             1
         2 monday               01-FEB-21             2
         3 tuesday              02-FEB-21             3
         4 wednesday            03-FEB-21             4
         5 thursday             28-JAN-21             5
         6 friday               29-JAN-21             6
         7 saturday             30-JAN-21             7
         
7 rows selected.
SQL> select * from t2 partition(rdate1);
       RID RNAME                RDATE          WEEK_NUM
---------- -------------------- ------------ ----------
         1 sunday               31-JAN-21             1
         
SQL> select * from t2 partition(rdate2);
       RID RNAME                RDATE          WEEK_NUM
---------- -------------------- ------------ ----------
         2 monday               01-FEB-21             2
         
SQL> select * from t2 partition(rdate3);
       RID RNAME                RDATE          WEEK_NUM
---------- -------------------- ------------ ----------
         3 tuesday              02-FEB-21             3
         
SQL> select * from t2 partition(rdate4);
       RID RNAME                RDATE          WEEK_NUM
---------- -------------------- ------------ ----------
         4 wednesday            03-FEB-21             4
         
SQL> select * from t2 partition(rdate5);
       RID RNAME                RDATE          WEEK_NUM
---------- -------------------- ------------ ----------
         5 thursday             28-JAN-21             5
         
SQL> select * from t2 partition(rdate6);
       RID RNAME                RDATE          WEEK_NUM
---------- -------------------- ------------ ----------
         6 friday               29-JAN-21             6
         
SQL> select * from t2 partition(rdate7);
       RID RNAME                RDATE          WEEK_NUM
---------- -------------------- ------------ ----------
         7 saturday             30-JAN-21             7



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

请登录后发表评论 登录
全部评论
擅长oracle,Linux。精通shell,python。

注册时间:2016-08-23

  • 博文量
    138
  • 访问量
    179038