Working with Oracle Database 19c. I'm currently trying to take an existing interval range partitioned table (partitioned by day) that was previously only storing partitions in a single tablespace and spread new partitions on to multiple tablespaces instead.
I executed the following to alter the table:
alter table TABLE_NAME set STORE IN(TABLESPACE_1, TABLESPACE_2, TABLESPACE_3)
This worked fine till I re-enabled our clean up script that drops partitions older than N days. Once I started deleting partitions, the Round Robing distribution would stop; new partitions would continue to get created in the same tablespace as the previous one.
I was able to resolve the issue by creating a sort of anchor read only partition that never gets droped from the oldest partition in the table. I'm trying to figure out why this 'anchor' partition is needed and how I can look under the hood to find the cause of this?
Here's an example of what I'm seeing I couldn't get my hands on a demo of this in 19C but ran this in 18c and seeing the same behavor.
create table rr_test (stringCol VARCHAR2(19 BYTE), UP TIMESTAMP(6)) tablespace ROUND_ROBIN_TEST1
partition by range (UP) interval (numtodsinterval(1, 'DAY'))
subpartition by LIST (stringCol) subpartition template
(SUBPARTITION "STR01" VALUES ('01'), SUBPARTITION "STR02" values ('02')) (partition P1 values less than (timestamp '2021-07-21 00:00:00'));
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-21 00:00:00');
INSERT into rr_test VALUES('02', TIMESTAMP '2021-09-22 00:00:00');
INSERT into rr_test VALUES('02', TIMESTAMP '2021-09-23 00:00:00');
alter table rr_test set store in (ROUND_ROBIN_TEST1, ROUND_ROBIN_TEST2, ROUND_ROBIN_TEST3);
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-24 00:00:00');
INSERT into rr_test VALUES('02', TIMESTAMP '2021-09-25 00:00:00');
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-26 00:00:00');
alter table rr_test drop partition P1;
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-27 00:00:00');
alter table rr_test drop partition for (TIMESTAMP'2021-09-20 00:00:00');
INSERT into rr_test VALUES('02', TIMESTAMP '2021-09-28 00:00:00');
alter table rr_test drop partition for (TIMESTAMP'2021-09-21 00:00:00');
INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-29 00:00:00');
select * from user_tab_partitions where table_name = 'RR_TEST';
From the resutls, you can see that the last partitions to get created once we start dropping partitions causes the round robin distribution to stop.
RR_TEST SYS_P9280 TIMESTAMP' 2021-09-24 00:00:00' ROUND_ROBIN_TEST1
RR_TEST SYS_P9283 TIMESTAMP' 2021-09-25 00:00:00' ROUND_ROBIN_TEST1
RR_TEST SYS_P9286 TIMESTAMP' 2021-09-26 00:00:00' ROUND_ROBIN_TEST2
RR_TEST SYS_P9289 TIMESTAMP' 2021-09-27 00:00:00' ROUND_ROBIN_TEST3
RR_TEST SYS_P9292 TIMESTAMP' 2021-09-28 00:00:00' ROUND_ROBIN_TEST1
RR_TEST SYS_P9295 TIMESTAMP' 2021-09-29 00:00:00' ROUND_ROBIN_TEST1
RR_TEST SYS_P9298 TIMESTAMP' 2021-09-30 00:00:00' ROUND_ROBIN_TEST1
CodePudding user response：
Can you build a test case showing what you are observing? Here is mine, and it seems fine
SQL> create table t
2 partition by range( dte )
3 interval( numtodsinterval(1,'DAY') )
4 store in ( users,asktom,largets)
5 ( partition p1 values less than ( date '2020-01-01' ) )
6 as
7 select
8 rownum pk,
9 'name'||rownum data,
10 date '2020-01-01' rownum/1000 dte
11 from dual
12 connect by level <= 1000*10;
Table created.
SQL>
SQL> select count(*) from t;
COUNT(*)
----------
10000
1 row selected.
SQL>
SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'T'
4 order by partition_position;
PARTITION_NAME TABLESPACE_NAME
------------------------------ --------------------
P1 USERS
SYS_P68092 ASKTOM
SYS_P68093 LARGETS
SYS_P68094 USERS
SYS_P68095 ASKTOM
SYS_P68096 LARGETS
SYS_P68097 USERS
SYS_P68098 ASKTOM
SYS_P68099 LARGETS
SYS_P68100 USERS
SYS_P68101 ASKTOM
SYS_P68102 LARGETS
12 rows selected.
SQL> alter table t drop partition P1;
Table altered.
SQL> alter table t drop partition SYS_P68092;
Table altered.
SQL> alter table t drop partition SYS_P68093;
Table altered.
SQL> alter table t drop partition SYS_P68094;
Table altered.
SQL> alter table t drop partition SYS_P68095;
Table altered.
SQL> alter table t drop partition SYS_P68096;
Table altered.
SQL> alter table t drop partition SYS_P68097;
Table altered.
SQL> alter table t drop partition SYS_P68098;
Table altered.
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'T'
4 order by partition_position;
PARTITION_NAME TABLESPACE_NAME
------------------------------ --------------------
SYS_P68099 LARGETS
SYS_P68100 USERS
SYS_P68101 ASKTOM
SYS_P68102 LARGETS
4 rows selected.
SQL> select max(dte) from t;
MAX(DTE)
---------
11-JAN-20
1 row selected.
SQL> insert into t
2 select
3 rownum pk,
4 'name'||rownum data,
5 date '2020-01-12' rownum/1000 dte
6 from dual
7 connect by level <= 1000*10;
10000 rows created.
SQL>
SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'T'
4 order by partition_position;
PARTITION_NAME TABLESPACE_NAME
------------------------------ --------------------
SYS_P68099 LARGETS
SYS_P68100 USERS
SYS_P68101 ASKTOM
SYS_P68102 LARGETS
SYS_P68103 ASKTOM
SYS_P68104 LARGETS
SYS_P68105 USERS
SYS_P68106 ASKTOM
SYS_P68107 LARGETS
SYS_P68108 USERS
SYS_P68109 ASKTOM
SYS_P68110 LARGETS
SYS_P68111 USERS
SYS_P68112 ASKTOM
SYS_P68113 LARGETS
15 rows selected.
CodePudding user response：
Addenda:
You can get into a "not round-robin-ing" issue if you are obeying a strict "add one, drop one" model because once we drop a partition (and you've dropped the anchor), we do a "reset", ie, the following partition starts from the first tablespace listed in the STORE IN clause. Because without the anchor, any given partition that remains could have been any of the tablespaces, so we can't really rely upon it to be an indicator of which should be the "next" tablespace to use.
For example, notice the STORE IN here is TS3, TS1, TS2
SQL> create table rr_test (stringCol VARCHAR2(19 BYTE), UP TIMESTAMP(6))
2 tablespace TS1
3 partition by range (UP) interval (numtodsinterval(1, 'DAY'))
4 store in (TS3, TS1, TS2 )
5 (partition P1 values less than (timestamp '2021-07-21 00:00:00'));
Table created.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-21 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-22 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-23 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-24 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-25 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-26 00:00:00');
1 row created.
SQL>
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 P1 TS1
2 SYS_P68275 TS3
3 SYS_P68276 TS1
4 SYS_P68277 TS2
5 SYS_P68278 TS3
6 SYS_P68279 TS1
7 SYS_P68280 TS2
7 rows selected.
SQL>
SQL> alter table rr_test drop partition P1;
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68275 TS3
2 SYS_P68276 TS1
3 SYS_P68277 TS2
4 SYS_P68278 TS3
5 SYS_P68279 TS1
6 SYS_P68280 TS2
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-27 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68275 TS3
2 SYS_P68276 TS1
3 SYS_P68277 TS2
4 SYS_P68278 TS3
5 SYS_P68279 TS1
6 SYS_P68280 TS2
7 SYS_P68281 TS3
7 rows selected.
SQL>
SQL> alter table rr_test drop partition for (TIMESTAMP'2021-09-20 00:00:00');
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68276 TS1
2 SYS_P68277 TS2
3 SYS_P68278 TS3
4 SYS_P68279 TS1
5 SYS_P68280 TS2
6 SYS_P68281 TS3
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-28 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68276 TS1
2 SYS_P68277 TS2
3 SYS_P68278 TS3
4 SYS_P68279 TS1
5 SYS_P68280 TS2
6 SYS_P68281 TS3
7 SYS_P68282 TS3
7 rows selected.
SQL>
SQL> alter table rr_test drop partition for (TIMESTAMP'2021-09-21 00:00:00');
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68277 TS2
2 SYS_P68278 TS3
3 SYS_P68279 TS1
4 SYS_P68280 TS2
5 SYS_P68281 TS3
6 SYS_P68282 TS3
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-29 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68277 TS2
2 SYS_P68278 TS3
3 SYS_P68279 TS1
4 SYS_P68280 TS2
5 SYS_P68281 TS3
6 SYS_P68282 TS3
7 SYS_P68283 TS3
7 rows selected.
SQL>
SQL>
SQL>
Each time I drop a partition, I reset to the beginning of STORE IN and pick up TS3. Now the same but TS2 is listed as the first partition.
SQL>
SQL> create table rr_test (stringCol VARCHAR2(19 BYTE), UP TIMESTAMP(6))
2 tablespace TS1
3 partition by range (UP) interval (numtodsinterval(1, 'DAY'))
4 store in (TS2, TS3, TS1 )
5 (partition P1 values less than (timestamp '2021-07-21 00:00:00'));
Table created.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-21 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-22 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-23 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-24 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-25 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-26 00:00:00');
1 row created.
SQL>
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 P1 TS1
2 SYS_P68284 TS2
3 SYS_P68285 TS3
4 SYS_P68286 TS1
5 SYS_P68287 TS2
6 SYS_P68288 TS3
7 SYS_P68289 TS1
7 rows selected.
SQL>
SQL> alter table rr_test drop partition P1;
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68284 TS2
2 SYS_P68285 TS3
3 SYS_P68286 TS1
4 SYS_P68287 TS2
5 SYS_P68288 TS3
6 SYS_P68289 TS1
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-27 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68284 TS2
2 SYS_P68285 TS3
3 SYS_P68286 TS1
4 SYS_P68287 TS2
5 SYS_P68288 TS3
6 SYS_P68289 TS1
7 SYS_P68290 TS2
7 rows selected.
SQL>
SQL> alter table rr_test drop partition for (TIMESTAMP'2021-09-20 00:00:00');
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68285 TS3
2 SYS_P68286 TS1
3 SYS_P68287 TS2
4 SYS_P68288 TS3
5 SYS_P68289 TS1
6 SYS_P68290 TS2
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-28 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68285 TS3
2 SYS_P68286 TS1
3 SYS_P68287 TS2
4 SYS_P68288 TS3
5 SYS_P68289 TS1
6 SYS_P68290 TS2
7 SYS_P68291 TS2
7 rows selected.
SQL>
SQL> alter table rr_test drop partition for (TIMESTAMP'2021-09-21 00:00:00');
Table altered.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68286 TS1
2 SYS_P68287 TS2
3 SYS_P68288 TS3
4 SYS_P68289 TS1
5 SYS_P68290 TS2
6 SYS_P68291 TS2
6 rows selected.
SQL>
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-29 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68286 TS1
2 SYS_P68287 TS2
3 SYS_P68288 TS3
4 SYS_P68289 TS1
5 SYS_P68290 TS2
6 SYS_P68291 TS2
7 SYS_P68292 TS2
7 rows selected.
SQL>
SQL>
Then we resume round robin-ing as per normal with more data
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-09-30 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-10-01 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-10-02 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-10-03 00:00:00');
1 row created.
SQL> INSERT into rr_test VALUES('01', TIMESTAMP '2021-10-04 00:00:00');
1 row created.
SQL> select partition_position, partition_name, tablespace_name from user_tab_partitions where table_name = 'RR_TEST';
PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------------ ------------------------
1 SYS_P68295 USERS
2 SYS_P68296 ASKTOM
3 SYS_P68297 LARGETS
4 SYS_P68298 USERS
5 SYS_P68299 ASKTOM
6 SYS_P68300 ASKTOM
7 SYS_P68301 ASKTOM
8 SYS_P68302 LARGETS
9 SYS_P68303 USERS
10 SYS_P68304 ASKTOM
11 SYS_P68305 LARGETS
12 SYS_P68306 USERS
12 rows selected.
I'll log a bug anyway, because I suspect "drop-one-add-one" is a common use case.