Introduction

Welcome to the Oracle Partitioning Tutorial. You can find tons of basic core examples for Oracle Partitioning in LiveSQL, so we want to focus on somewhat more enhanced topics in this tutorial in individual modules. You can go through all the modules in order, you can do it in random order, or you can only look at individual ones.

All you should do is run the first module to clean up any remnants you might have lingering around. It's easier to do the cleanup once and be done with it rather than "littering" the individual modules with DROP TABLE or other cleanup commands.

This tutorial is only a first starting point. There is always more to functionality than we can possibly introduce in an overview tutorial, so we encourage you to consult the documentation for details. Especially the "VLDB and Partitioning Guide" and the "SQL Language Reference Manual" are your best friends here.

We also tried hard to conceptualize the examples and focus on introducing the technology and some related important details. So please bear with us if you see meaningless table and column names. Business scenarios and use cases are manifold and are or will be discussed in blogs.

Oracle hopes you enjoy this short tutorial for Oracle Partitioning. For suggestions and comments, please contact us. We love to hear from you.

Cleanup

To not litter the individual tutorials with DROP TABLE commands, let's remove all the objects that will be created. Simply execute the following code snippet and be done with it .. . You can safely ignore any 'table does not exist' error message. If a table does not exist there is nothing wrong with not being able to drop it ..

rem cleanup of all objects
drop table mc purge;
drop table alp purge;
drop table soon2bpart purge;
drop table part4filter purge;
drop table ropt purge;
drop table part4xchange purge;
drop table np4xchange purge;
drop table compart4xchange purge;
drop table p4xchange purge;

That's all you needed to do. You're ready to go.

Multi-column list Partitioning

Partitioning allows the break down of tables and indexes into smaller physical pieces while keeping the logical view of a single object. Partitioning improves the performance, manageability, and availability of large objects in the database by enabling processing and data management to happen on the smaller physical pieces called partitions.

List Partitioning allows to specify lists of discrete values of the partitioning columns that qualify rows to be included in a partition. You can create a list partitioned table not only on one, but multiple columns, which we will demonstrate in this example.

To create a simple multi-column list partitioned table you can issue the following command:

rem simple multi-column list partitioned table
create table mc (col1 number, col2 number)
partition by list (col1, col2)
(partition p1 values ((1,2),(3,4)),
 partition p2 values ((4,4)),
 partition p3 values (default));

The metadata of this table is as follows. You can also identify multi-column list partitioning by looking at the number of partition keys in the table metadata

rem table metadata - number of partition keys
select table_name, partitioning_type, partitioning_key_count 
from user_part_tables where table_name='MC';

Looking at the partition level you will see how the multi-column key values are represented. You will see that the high value of the partitioning metadata indicates that we have multiple columns as partitioning key. Value pairs for the multi-column key are enclosed with parenthesis and multiple value pairs are separated through a comma.

rem metadata of individual partitions
select partition_name, high_value
from user_tab_partitions where table_name='MC';

rem insert some sample data
insert into mc values (1,2);
insert into mc values (1,3);
insert into mc values (99,99);
commit;

Let's now check where the data ended up. We will use first the partition extended syntax to point specifically to partition p1. The only valid records we expect to see are records that either have (1,2) or (3,4) as partition key.

rem content of partition p1 using partition extended syntax
select * from mc partition (p1);

With multi-column partitioning you can also use the partition extended syntax with the FOR () clause. Simply point to a fully qualified record, meaning you have to specify a complete partitioning key criteria, which is a value pair in our case. Using the FOR () clause will show you the complete content of the partition that contains the specified partition keys. In the following example we chose a value pair that is not explicitly defined for any of the partitions, so it points to the DEFAULT partition.

rem content of DEFAULT partition using the partitioned extended syntax PARTITION FOR ()
select * from mc partition for (1,3);

Note that DEFAULT is not a value, so if you were to try to use it as "value" with the partitioned extended syntax you will get an error:

rem wrong usage of partition extended syntax: DEFAULT is not a valid partition key value
select * from mc partition for (default);

After having introduced the basic working of a multi-column partitioned table, let's do a standard partition maintenance operation. You will see that it behaves exactly as it does for any other partitioning method; the only difference is that a fully qualified partition key now obviously consists of value pairs with values for all partition k ey columns.

rem simple partition maintenance operation, demonstrating split
alter table mc split partition p3 into (partition p3 values (1,3),partition p4) online;

After the split we expect all records for the newly split partition p3 to contain records with partition key (1,3), and partition p4 to contain the rest .. since this partition is now the new DEFAULT partition. Just like with a single column partitioned table, you can only add a partition to a list partitioned table with a DEFAULT partition by splitting the DEFAULT partition. Oracle cannot simply create a new partition in this case since conceptually all possible partition keys are contained in this catch-it-all partition.

Let's check the content of our "new" partition that we created by splitting the DEFAULT partition:

rem content of partition p3 after split
select * from mc partition (p3);

rem partition information for our table
select partition_name, high_value
from user_tab_partitions
where table_name='MC'
order by partition_position;

The new DEFAULT partition has all the rest of the records:

rem content of partition p4 after the split, our new DEFAULT partition
select * from mc partition (p4);

You successfully made it to the end of module 'multi-column list partitioning'. For further details please consult the documentation. For suggestions and comments, please contact us. We love to hear from you.

Auto-list Partitioning

Another enhanced technique with list partitioned tables is auto-list partitioning. Similar to interval partitioning, auto-list automatically creates a new partition as soon as a new partitioning key value is seen. As data is loaded into the table, the database automatically creates a new partition if the loaded partitioning key value does not correspond to any of the existing partitions.

Let's create a simple auto-list partitioned table:

rem create a simple auto-list partitioned table
create table alp (col1 number, col2 number)
partition by list (col1) automatic
(partition p1 values (1,2,3,4,100),
 partition p2 values (5));

Just by using the new keyword AUTOMATIC you created an auto-list partitioned table. Note that you always have to specify at minimum one partition. Oracle does not allow the creation of a table with at least one partition. That was easy, wasn't it?

There are some subtle differences between the way an interval partitioned table works versus an auto-list partitioned table. The first difference can be seen when looking at the data dictionary information of our newly created auto-list partitioned table:

rem metadata information for an auto-list partitioned table
select table_name, partitioning_type, autolist, partition_count 
from user_part_tables where table_name='ALP';

As you can see, there is a new metadata column that classifies a table as an auto-list partitioned table. You also see that, unlike an interval partitioned table, an auto-list partitioned table does not show a partition count of one million. The reason is that an auto-list partitioned table does not require any metadata to be auto-list partitioned. It just has to know that whenever a new partition key value arrives it has to create a new partition. Interval partitioned table must have an INTERVAL specified; with this specification all partitions of an interval partitioned table are know in advance, and are semantically created (albeit not persistent on disk or as individual partition metadata.

Let's now insert some data that has a partition key that is not defined yet.

rem insert some data w/o matching partition
insert into alp values (999,999);
commit;

Since we do not have a partition yet that has 999 as partition key value we expect a new partition to being created:

rem metadata, newly created partition
select partition_name, high_value 
from user_tab_partitions where table_name='ALP' order by partition_position;

Voila, here we go. We have a new partition with a system generated name. Let's quickly change the name to something more meaningful using the partition extended syntax (this is deterministic all the time, so if you want to have your own names all the time, just do this programmatically.

rem change auto-generated name
alter table alp rename partition for (999) to p999;

rem standard PMOP that would not be allowed on interval partitioned table
alter table alp add partition pnew values (10,11,12);

Note that the partition position of an partition in an auto-list partitioned table is derived by the order of the creation of a partition, not the order of the partition key values .. if that was even possible. The same is true for a normal list partitione d table as well.

rem see the newly created partition
select partition_name, high_value 
from user_tab_partitions where table_name='ALP' order by partition_position;

One thing that auto-list partitioned tables have in common with interval partitioning is that auto-list is extension to list partitioning as interval is an extension to range partitioning. Consequently you can evolve a list partitioned table into an auto-list partitioned one.

Let's quickly do this for our earlier created multi-column list partitioned table MC. In case you have not gone through the module multi-column list partitioning, here is the DDL for the table again:

rem simple multi-column list partitioned table
create table mc (col1 number, col2 number)
partition by list (col1, col2)
(partition p1 values ((1,2),(3,4)),
 partition p2 values ((4,4)),
 partition p3 values ((9,9)),
 partition p4 values (default));

rem remove the default partition
alter table mc drop partition p4;

To ensure that we are having a normal list partitioned table without a DEFAULT partition, let's try to insert a record that does not have a matching partition. It will fail.

rem try to insert out--of-bound value, will fail
insert into mc values (1234,5678);

Let's now evolve the table to an auto-list partitioned table. Whoever did the similar operation to evolve a range partitioned table to an interval one will see the similarity:

rem evolve table
alter table mc set automatic;

Now we are trying the insert of the same record again:

rem try to insert again .. will work
insert into mc values (1234,5678);
commit;

As expected, it succeeded, and we now have a newly created partition in our evolved multi-column list partitioned table.

rem metadata after creation of first automated list partition
select partition_name, high_value 
from user_tab_partitions where table_name='MC';

You successfully made it to the end of module 'auto-list partitioning'. For further details please consult the documentation. For suggestions and comments, please contact us. We love to hear from you.

Conversion of a nonpartitioned table to a partitioned table

create table soon2bpart (col1 number primary key, col2 number, col3 number not null, col4 number);
insert /*+ append */ into soon2bpart 
select rownum, mod(rownum,100), mod(rownum,1000), dbms_random.normal from dual connect by level <=10;
commit;

Our sample table needs a couple of indexes, so let's create them:

rem create a bunch of different indexes on it
rem some indexes, different shape and type
create index i1_prefix_soon2bpart on soon2bpart(col2);
create index i2_nonprefix_soon2bpart on soon2bpart(col4);
create index i3_prefix_but_ovrd_soon2bpart on soon2bpart(col3, col2);
create index i4_global_part_soon2bpart on soon2bpart(col3) global partition by hash(col3) partitions 4;
create bitmap index i5_bix_soon2bpart on soon2bpart (col2,col3);

Let's see the index metadata for our table as it exists prior to the conversion:

rem indexes in general
select index_name, index_type, uniqueness, partitioned, status 
from user_indexes
where table_name='SOON2BPART'
order by 1;

rem partitioned index
select index_name, partitioning_type, partition_count, locality 
from user_part_indexes
where table_name='SOON2BPART'
order by 1;

The conversion is not an in-place conversion: one of the key concepts of Oracle Partitioning is that data of individual partitions is, well, stored in individual physical segments. The nonpartitioned table has data stored "wherever" in the table. So for the duration of the conversion you will need the extra space for the new table partition and index segments. After the successful conversion the space for the old nonpart itioned table and its indexes will be freed.

Let's kick off the conversion of the table. Note that we are doing an online conversion, so if you were able to spawn a second session that does DML against our table while the conversion is in place you'd experience that all your DML will go through without being blocked. We will also rely on the default index conversion rules that are defined, with the exception of one index. This helps to demonstrate the default behavior and to give you a glimpse insight into what you can do for indexes as part of the online conversion:

rem do an online conversion
rem - only one index will not use default conversion
alter table soon2bpart modify
partition by list (col2) automatic
(partition p1 values (1)) online
update indexes (i3_prefix_but_ovrd_soon2bpart global);

OK, the table is successfully converted. Let' see the table partitioning metadata:

rem partitioning metadata
select table_name, partitioning_type, partition_count 
from user_part_tables where table_name='SOON2BPART';
select partition_name, high_value 
from user_tab_partitions where table_name='SOON2BPART'
order by partition_position asc;

What happened to the indexes? Oracle is smart enough to have a couple of default index conversion rules, that can be overwritten as we demonstrate with one index. The rules are:

• Global partitioned indexes are untouched and re tain their shape.
• Non-prefixed indexes will become global nonpartitioned tables.
• Prefixed indexes will become local partitioned indexes.
• Bitmap indexes will become local partitioned indexes.

So let's check the index shape and their status:

rem indexes general
select index_name, index_type, uniqueness, partitioned, status 
from user_indexes
where table_name='SOON2BPART'
order by 1;

You see that the conversion rules were applied as discussed, with the exception of index I1B_SOON2BPART which was defined as becoming a global nonpartitioned index as part of the conversion.

rem partitioned indexes
select index_name, partitioning_type, partition_count, locality 
from user_part_indexes
where table_name='SOON2BPART'
order by 1;

All the index partitions are also in a valid state:

rem status of partitioned index
select ip.index_name, ip.status, count(*) cnt
from user_ind_partitions ip, user_indexes i
where i.index_name=ip.index_name and table_name='SOON2BPART'
group by ip.index_name, ip.status
order by 1;

You successfully made it to the end of module 'conversion to partitioned table'. For further details please consult the documentation. For suggestions and comments, please contact us.We love to hear from you.

Read only partitions and subpartitions

You can set not only tables but partitions and subpartitions to read-only status to protect data from unintentional DML operations by any user or trigger. Any attempt to update data in a partition or subpartition that is set to read only results in an error, while updating data in partitions or subpartitions that are set to read write succeeds.

We will demonstrate this functionality using a single level range partitioned table.

rem simple interval partitioned table with one read only partition
create table ropt (col1, col2, col3) nocompress
partition by range (col1) interval (10)
(partition p1 values less than (1) read only,
 partition p2 values less than (11))
as select rownum, rownum*10, rpad('a',rownum,'b') 
from dual connect by level <= 100;

As you can see, we did specify read only for partition P1 but nowhere else, neither on table nor partition level. Let's see what we ended up with:

rem metadata
select table_name, def_read_only from user_part_tables where table_name='ROPT';
rem currently existent partitions<br>
select partition_name, high_value, read_only
from user_tab_partitions
where table_name='ROPT';

As probably expected, we only have one partition that is set to read only in this example. That means that:

• The table level default is (and will stay) read write.
• Only partition p1 is defined as read only where it was explicitly defined.

You can change the read only/read write attribute for existing partitions.

rem change the status of a partition to read only
alter table ropt modify partition for (5) read only;

As partition level attribute, read only can obviously be used in conjunction with other partition maintenance operations. The question now begs what does it really mean for partition maintenance operations, and especially when these PMOPs are executed in an online mode?

rem online PMOP will not work when one of the target partitions is read only
alter table ropt split partition for (5) into 
(partition pa values less than (7), partition pb read only) online;

Read only is considered a guaranteed state for the time when a PMOP is started. It would be also ambiguous when to change the state if a change from read write to read only is taking place or vice versa. So for the statement to work you have to run it in offline mode, meaning that no data changes are allowed as soon as the PMOP starts:

rem offline PMOP
alter table ropt split partition for (5) into 
(partition pa values less than (7), partition pb read only);

You can also set a whole table to read only. This will change the state for all existing partitions as well as the default of the table.Note that this is in line with other attributes.

rem set everything read only, including the default property
alter table ropt read only;

Let's now have a closer look what it means to have a partition set to read only and how Oracle describes data immutability in this context. The fundamental data immutability rule for read only tables and partitions is that only operations are allowed that must not change the data content of the partition at the point in time when a partition was set to read only. Or, in more sql-like words, the result of SELECT <column list at read only setting time> FROM <table> PARTITION <partition set to read only> within the partitioned tables must not change.

So what works?

Any operation that does not change the content, but only the physical representation on disk. A classical example is moving a partition to introduce compression. Let's demonstrate this using a partition of our now fully read only table:

rem partition pb
select partition_name, high_value, read_only, compression
from user_tab_partitions
where table_name='ROPT' and partition_name='PB';

Let's move and compress this partition:

rem do the move and compress
alter table ropt move partition pb compress for oltp;

The partition move on the read only partition succeeded without raising any error. Checking the partition attributes again you now will see that the partition is compressed.

rem partition pb
select partition_name, high_value, read_only, compression
from user_tab_partitions
where table_name='ROPT' and partition_name='PB';

Another operation that works on a table with read only partitions is adding a column. Such an operation works irrespective of whether the new column is nullable or not and whether the column has a default value.

rem add a column to the table
alter table ropt add (newcol number default 99);

This might be surprising to you at first glance, but if you go back to how we defined data immutability you will see that adding a column does not violate our rule. Why did Oracle choose this approach? The answer is simple: if we had decided to use a SELECT * FROM <read only partition> as data immutability we had ruled out any schema evolution like adding a column for a partitioned table as soon as one partition was set to read only. This was considered an unacceptable limitation for partitioned tables.

Now, what operations are not allowed? Anything that is considered changing the data immutability as defined earlier. Examples are

Any form of DML on a read only partition:

rem no DML on read only partitions
update ropt set col2=col2 where col1=88;

Dropping or truncating a read only partition - since this is semantically equivalent to a DELETE FROM <table> WHERE <partitioning criteria>:

rem no drop or truncate partition
alter table ropt drop partition for (56);

Dropping a column (or setting a column to unused):

rem drop column is not allowed
alter table ropt drop column col2;

Last but not least, and this is no different to existing read only tables, you can drop a table with one, multiple or all partitions in a read only state:

rem drop everything succeeds
drop table ropt purge;

Semantically you are not violating any data immutability when you remove a complete object. If you want to preserve this case you should address this with proper privilege management or, under some circumstances, by disabling the table level lock. The latter one prevents a drop table, but also all other operations that require an exclusive table level lock.

You successfully made it to the end of module 'read only partitions and subpartitions'. For further details please consult the documentation. For suggestions and comments, please contact us.We love to hear from you.

Create a table for Exchange

Partition exchange is a common operation to load or 'unload' data from a partitioned table by exchanging a standalone table the exactly matches the shape of a partitioned table. However, over the years, matching the exact shape of a partitioned table became harder and more complicated. In fact, under some circumstances it is not even possible to create a table for a successful exchange without knowing exactly what DDL operations have taken place on the partitioned table and in what order.

Oracle offers an explicit DDL command to address the creation of table for an exchange: tables can be created with the FOR EXCHANGE WITH clause to exactly match the shape of a partitioned table and be eligible for a partition exchange command.

Let's first create a partitioned table that we are going to exchange with:

rem partitioned table as target for exchange
create table part4xchange (col1, col2, col3) nocompress
partition by range (col1) interval (1000)
(partition p1 values less than (1),
 partition p2 values less than (11))
as select rownum, rownum*10, rpad('a',rownum,'b') 
from dual connect by level <=100;

To add a little bit to the fun, let's do some DDL on this table that changes the shape of it:

rem add a column
alter table part4xchange add (colnew1 number default 99);
rem set col unused
alter table part4xchange set unused (col2);

rem create table 4 xchange
create table np4xchange for exchange with table part4xchange; 

You can specify all table level attributes as you like, e.g. the location, compression, cache attributes, etc. Note that auxiliary data structures, such as indexes are not created as part of this command. This newly created table for exchange is empty and could be populated with data if the exchange operation is used for fast data loading.

We now use this newly created table for exchange and exchange it with a partition of my partitioned table.

rem exchange
alter table part4xchange exchange partition for (99) with table np4xchange;

We can also use this new functionality to exchange a partition of a composite partitioned table.

rem composite partitioned table as target for exchange
create table compart4xchange (col1, col2, col3) nocompress
partition by range (col1) interval (100)
subpartition by list (col2)
(partition p1 values less than (1)
  (subpartition sp1_1 values (0), subpartition sp1_2 values (1)),
 partition p2 values less than (11)
  (subpartition sp2_d values (DEFAULT)))
as select rownum, mod(rownum,2), rpad('a',rownum,'b') 
from dual connect
by level <=100;

An exchange of a partition of a composite table is slightly different than the exchange of a partition of a single-level partitioned table. In our example, a partition of interval-list composite partitioned table compart4xchange is further subpartitioned by list. We therefore need a list-partitioned table to exchange any partition of this table with and the table to exchange has to match exactly the subpartitioning setup of the partition.

As you see in this example, the subpartitioning layout is different for partitions p1 and p2, so it makes a difference which partition you are exchanging with. Currently there is no syntax support for the new CREATE TABLE FOR EXCHANGE command to copy the metadata of exactly one target partition so when we now create a table for exchange we have to specify the list partitioning strategy of the target partition we want to exchange with.

We want to exchange with partition p1, so we add the identical first subpartitioning layout of partition p1 as list partitioning layout for our table to be exchanged:

rem table for exchange with composite partitioned table
create table p4xchange
  partition by list (col2)
  (partition p1 values (0), partition p2 values (1))
for exchange with table compart4xchange;

We now can exchange this table with partition p1 of our composite partitioned table (if you were to try an exchange with partition p2 you would get an error because the layout does not match):

rem exchange
alter table compart4xchange exchange partition p1 with table p4xchange;

You successfully made it to the end of module 'create a table for exchange'. For further details please consult the documentation. For suggestions and comments, please contact us. We love to hear from you.