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

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