CODEWITHSUNDEEP
sqloracle-databasepartitioningpartitiondatabase-partitioning
Shweta
Shweta

Reputation: 239

Oracle - How the rows are handled in partitioned table, if the respective interval is already dropped?

I have a existing table which is partitioned based on range partitioning Schema :

create table History(
hid number(19,0),
type varchar2(255 char),
lastupdated timestamp (6) not null enable,
name varchar2(255 char),
primary key (hid))
partition by range (lastupdated) interval (numtodsinterval(1,'day'))
(partition retailhistory values less than (to_timestamp('12/01/2020','DD/MM/YYYY')));

My goal was to create a procedure which will drops old partitions, say before 15 days. As the requirement I had developed a procedure which drops the last partition also, i. e. retailhistory, with the system generated partitions. Now the question came to my mind, since the lowest bound of the the interval partitioning is now changed, what will happen if I inserted the data for dropped partitions again,

Say, in above case, partition for retail history is now dropped and then I want to insert a data with timestamp (11/01/2020) as the start date for partition was 12 jan.

How this data would be now allocated to a partition. I can see the row has been now inserted in table, but I cant see a system generated partition created for this date. Any help would be appreciated. Thanks. :)

Edit : I dropped the partition using below technique :

Alter table History set interval (numtodsinterval(1,'day'));

Then

Alter table History drop partition retail history update indexes;

retailhistory partition stores data before 12th Jan. After dropping this partition, the next system generated partition becomes lower bound.

Now I want to insert data with timestamp say 1 jan. So how it would be handled.

Upvotes: 0

Views: 551

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You can see which partition each row of data it assigned to with a query like:

select h.lastupdated, uo.subobject_name
from history h
join user_objects uo on uo.data_object_id = dbms_rowid.rowid_object(h.rowid);

The row you insert will go into whichever partition has the lowest high_value that is greater than the date you're inserting, 2020-01-11. If you have retained partitions for , say, dates in February and dropped all of those in January, then inserting 2020-01-11 will go in the same partition as timestamps on 2020-02-01. The high_value for that will be 2020-02-02 00:00:00; your data is before that, so it will go in that one.

db<>fiddle demo

After dropping this partition, the next system generated partition becomes lower bound.

No, it isn't the lower bound.

From the documentation:

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.

The next system generated partition becomes lower lowest upper bound

After you've dropped old partitions, the new 'first' partition still has no lower bound, so any earlier values will go in that bucket.

In your original table with just the retailhistory partition, if you had inserted a value before that - e.g. timestamp 1999-12-31 - then that would also still have gone in that partition, because that value is less than 2020-01-12.

All other system-generated partitions cover exactly one day, but the first partition can go back over any number of days.

Upvotes: 2

Related Questions