The latest in Oracle Partitioning – Part 1: Read Only Partitions

Now that Oracle Database
12c Release 2 is available on-premise for  Linux x86-64, Solaris Sparc64, and Solaris x86-64 and on the Oracle Cloud for everybody else – the product we had the pleasure to develop and to
play with for quite some time now – it’s also time to introduce some of the new functionality in more detail to the broader audience. This blog post will be the first of
hopefully many over the course of the next months (time permits) to specifically highlight individual features of Oracle Partitioning and why
we actually implemented it. A big thanks at this point to the user community out there: a lot of the features would not be possible without
the continuous feedback from you out there. Yes, we have a lot of ideas – some better than others – but we could never come up with all these
features by ourselves. Keep on going and send us ideas and suggestions! For Partitioning that would be me, hermann.baer@oracle.com, but I am
digressing …

So let’s talk about the first feature to highlight: Read
Only Partitions
.

As the name suggest, it is a rather self-explanatory functionality: With
the new release we now can set an object to read only with the granularity of a partition or subpartition. Prior to this release this was an
all-or-nothing attribute on table level.

And that’s it. Just like other attributes, e.g. compression, tablespace
location, you specify READ ONLY (or READ WRITE, the default) on a table, partition, or subpartition level and voila, you’re done. The most simple
example is probably

CREATE TABLE toto1
PARTITION BY LIST (col1)
(PARTITION p0 VALUES (0) READ ONLY,
 PARTITION p1 VALUES (1) READ WRITE,
 PARTITION p2 VALUES (2))
AS
SELECT mod(rownum,2) col1, mod(rownum,10) col2
FROM DUAL CONNECT BY LEVEL < 100;

Voila. We now have a table with 99 rows and three partitions, one of them
being read only. Let’s now create a second table as composite partitioned table:

CREATE TABLE toto2
PARTITION BY LIST (col1)
SUBPARTITION BY LIST (col2)
SUBPARTITION TEMPLATE
(SUBPARTITION sp1 VALUES (0) READ ONLY,
 SUBPARTITION sp2 VALUES (DEFAULT))
(PARTITION p0 VALUES (0) READ ONLY,
 PARTITION p1 VALUES (1) READ WRITE,
 PARTITION p2 VALUES (2))
AS
SELECT mod(rownum,2) col1, mod(rownum,10) col2
FROM DUAL CONNECT BY LEVEL < 100;

This statement creates a table with two partitions, one of them in read
only mode; each partition has two subpartitions, one of them being set explicitly to READ WRITE.

Let’s now look a little bit closer of how inheritance works here. Note
that we specified READ WRITE only for partition p1 and not for partition p2 in both examples. While on a first glance this seems to be completely
arbitrary from a SQL syntax perspective, there is a subtle difference when it comes to composite partitioned tables (and that difference is true for
other attributes as well). We come to that in a second.

The data dictionary reflects this new attribute in all relevant views: on a table level for the default being chosen (*_PART_TABLES.DEF_READ_ONLY), on a
partition level the attribute setting of a partition or the default for subpartitions (*_TAB_PARTITIONS.READ_ONLY), and on subpartition level the
attribute setting of the subpartition (*_TAB_SUBPARTITIONS.READ_ONLY). So in our case it looks as follows:

SQL> SELECT table_name,
def_read_only FROM user_part_tables WHERE table_name IN (‘TOTO1′,’TOTO2′);

TABLE_NAME                    
DEF_READ_ONLY
—————————— ——————————
TOTO1                          NO
TOTO2                          NO

SQL> SELECT table_name,
partition_name, read_only FROM user_tab_partitions WHERE table_name IN (‘TOTO1′,’TOTO2′) ORDER BY 1,2;

TABLE_NAME                     PARTITION_NAME                
READ_ONLY
—————————— —————————— ——————————
TOTO1                          P0                            
YES
TOTO1                          P1                            
NO
TOTO1                          P2                            
NO
TOTO2                          P0                            
YES
TOTO2                          P1                            
NO
TOTO2                          P2                            
NONE</span>

As mentioned before, the READ ONLY attribute on a partition level is
overloaded and serves a purpose for both partitioned and subpartitioned tables. For partitioned tables it reflects that actual attribute for the
partition segment, so you will see that partitions p1 and p2 of table toto1 are set to READ WRITE and partition p0 is set to READ ONLY. However.
looking at table toto2 you will see that only partition p1 is set to READ WRITE, while partition p2 shows undefined (NONE). That’s because in the
case of a composite partitioned table an attribute on partition level is only set when explicitly specified, which then acts as default for a
partition. If a default is set on partition level then this default will be used for any new subpartition that will be created underneath a
partition if an attribute is not specified explicitly. If none is set then the table level default will be used.

On a subpartition level there are no surprises: subpartitions *_sp1 -
with explicit setting of READ ONLY – are set to read only, supbartitions *_sp2 inherit the attribute from either the partition or table level.

SQL> SELECT table_name,
partition_name, subpartition_name, read_only FROM user_tab_subpartitions WHERE table_name = ‘TOTO2′ ORDER BY 1,2,3;


TABLE_NAME                     PARTITION_NAME      
SUBPARTITION_NAME    READ_ONLY
—————————— ——————– ——————– ——————–
TOTO2                          P0                  
P0_SP1               YES
TOTO2                          P0                  
P0_SP2               YES
TOTO2                          P1                  
P1_SP1               NO
TOTO2                          P1                  
P1_SP2               NO
TOTO2                          P2                  
P2_SP1               YES
TOTO2                          P2                  
P2_SP2               NO

OK, so far I should not have
told you anything new here .. just should have shown that the standard inheritance of attribute for partitioned tables works exactly the same way
for the new READ ONLY attribute as it does for other ones. So let’s have a quick look into what it means to have a partition set to read only. We
will use table toto1 only from now on.

The first thing is probably the most-intuitive behavior: you cannot
update data in a read only partition. So when you try to update partition p0 and partition p1 you will see that DML works on the read write
partition but throws an error for the read only partition.

SQL> UPDATE toto1 PARTITION (p0)
SET col2=col2+1;

Error starting at line : 1 in
command -
update toto1 partition (p0) set col2=col2+1
Error report -
ORA-14466: Data in a read-only partition or subpartition cannot be modified.

SQL> UPDATE toto1 PARTITION (p1) SET col2=col2+1;

50 rows updated.

You can change the attribute for a partition, so if you wanted to update
partition p0 in table toto1, you would have to set it to read write:

SQL> ALTER TABLE toto1 MODIFY
PARTITION p0 READ WRITE;

Table toto1 altered.

SQL> UPDATE toto1 PARTITION (p0) SET col2=col2+1;

49 rows updated.

But let’s ignore this one and skip that step, so partition p0 is
still read only.

The question begs now what operations are allowed? The conceptual rule for read only partitions is that Oracle must not allow any change to the data
that would change the data at the time a partition was set to read only. Oracle provides data immutability for the data as it existed when a
partition is set to read only. Or in other more SQL-like words, the SELECT
FROM

[SUB]PARTITION
must not change under any circumstances.

Let’s set our partition back to read only again:

SQL> ALTER TABLE toto1 MODIFY
PARTITION p0 READ ONLY;

Table toto1 altered.

The first logical consequence is that you
cannot drop a read only partition
. Conceptually a drop partition
removes a subset of data from a table, equivalent to a DELETE FROM

[sub]partition> WHERE , so this violates
the above-mentioned ‘must-not-change-date’ rule:

SQL> ALTER TABLE toto1 DROP
PARTITION p0;

Error starting at line : 1 in command -
alter table toto1 drop partition p0
Error report -
ORA-14466: Data in a read-only partition or subpartition cannot be modified.

Note, however, that you can drop a read only table as well as a partitioned table with one or multiple read only partitions. Ooops, a bug? Nope, works
as designed. Removing an object is different than removing data. The read only attribute is not meant to protect an object; that’s what privileges are
good for or, if you really want to protect a dedicated object, the locking capabilities of a table: you can disable a table lock.

Second, you cannot exchange a read only
partition
. That changes data in the table as well.

SQL> CREATE TABLE xtoto FOR
EXCHANGE WITH TABLE toto1;

Table xtoto created.

SQL> ALTER TABLE toto1 EXCHANGE PARTITION p0 WITH TABLE xtoto;

Error starting at line : 1 in command -
alter table toto1 exchange partition p0 with table xtoto
Error report -
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
You cannot drop a column of a table
with read only partitions or set columns
to unused
. Kind of obvious, isn’t it? That removes data partially.
However, you can set a column to invisible.
No data is removed, it is just not part of the * notation of a table anymore.

After knowing the basics of what you can’t do, what is possible?

In short, anything that does not violate the above-mentioned rules. So you
can split
a read only partition, you
can merge
partitions where one or multiple partitions are read
only, you can move partitions.
Pretty much everything else is doable from a partition maintenance operation (there is some subtleties, like not allowing online partition maintenance
operations when read only partitions are involved, or not allowing filtered partition maintenance operations, another new and exciting functionality in
12.2.0.1 and topic of a later blog, but that’s beyond this blog post). A simple example with our table toto1:

SQL> ALTER TABLE toto1 MERGE PARTITIONS p0, p1 INTO PARTITION p0_1 READ ONLY;

Table toto1 altered.

The most important thing you can do is that you
can add columns
to a table with read only partitions, irrespective
of whether they have default values or not, whether they’re nullable or not, or whether the addition of a column might require touching the blocks on
disk. All this is allowed and does not violate the above-mentioned rule of data immutability. The output of the select list at read-only-setting time
does not change.

SQL> ALTER TABLE toto1 ADD col_new
varchar2(100) DEFAULT ‘i am not empty';

Table toto1 altered.
Why do we allow this? The answer is simple.

Partitioned tables are ‘living beasts’ and fundamental components of tens of thousands of applications. These tables never go away (unless an application
goes away) and are growing. Another given is that applications change over time.

If we had decided to disallow the most basic schema evolution for a partitioned table – adding columns – and defined data immutability blindly
as “select * must not change”, then we had made this new functionality way too limited to benefit our broad customer base. Any application change
involving partitioned tables with read only partitions would have been ruled out unless all read only partitions were set to read write for doing the add
column to then set the formerly read only partitions back to their original state … plus ensuring that the data of these partitions must not be
changed throughout this process. Way too complicated and convoluted. Our decision is based on our customer’s interest and feedback that we got
throughout the early process of our beta program.

That’s about it for now for read only partitions. I am sure I have forgotten some little details here and there, but there’s always soo many things to
talk (write) about that you will never catch it all.

But stay tuned. There’s more blog posts to come. And please, if you have any comments about this specific one or suggestions for future ones, then please
let me know. You can always reach me at hermann.baer@oracle.com.

Cheers, over and out.

About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Recent Posts

Categories


« March 2017
Sun Mon Tue Wed Thu Fri Sat
     

3

4

5

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

 
             
Today

Let’s block ads! (Why?)

The Data Warehouse Insider