Welcome to the Oracle Partitioning Tutorial for Oracle Database 12c Release 2. We will introduce new and exciting functionality 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 to run the first module to cleanup any remnants that you might have still lingering around. It's easier to do the cleanup once and be done with it rather than "littering" the individual modules with DROP TABLE Commands.
This tutorial is obviously only a first starting point. There is always more to a 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 important details related to it. So please bear with us if you see meaningless table and columns 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.
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<br> 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.
Partitioning allows the break down of tables and inde xes 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. With Oracle Database 12c Release 2, the partition key is no longer limited to one column only and can be one or multiple columns from the table.
To create a simple multi-column list partitioned table you can issue the
rem simple multi-column list partitioned table<br> 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<br> 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.
Let us now insert some data into our previously created table and see where the data is actually stored, We intentionally insert some data that is explicitly aligned with the partition key values and some other data that isn't. We expect all records that do not match a specific partition to end up in the DEFAULT partition of the table.
rem metadata of individual partitions<br> select partition_name, high_value from user_tab_partitions where table_name='MC';
rem insert some sample data<br> 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
rem content of partition p1 using partition extended syntax<br> select * from mc partition (p1);
rem content of DEFAULT partition using the partitioned extended syntax PARTITION FOR ()<br> 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<br> select * from mc partition for (default);
rem simple partition maintenance operation, demonstrating split<br> 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:
Let's quickly check the metadata of the table again to see what the split did:
rem content of partition p3 after split<br> select * from mc partition (p3);
rem partition information for our table<br> 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<br> 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.
Another new exciting functionality in Oracle Database 12c Release 2 is the introduction of 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<br> 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<br> 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
rem insert some data w/o matching partition<br> 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<br> 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.
Another subtle difference between an auto-list partitioned table and an interval partitioned table is that you can do all partition maintenance operations without limitation. Since no metadata is needed other than "create new partition for new partition key value", you do not have any limitation when it comes to merging, splitting, or even manually adding partitions.
rem change auto-generated name<br> alter table alp rename partition for (999) to p999;
rem standard PMOP that would not be allowed on interval partitioned table<br> 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<br> 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:
One pre-requirement to evolve a list partitioned table to an auto-list one is that you must not have a DEFAULT partition. Kind of makes sense when you think of it: a DEFAULT partition is the catch-it-all partition for a list partitioned table, meaning that all partition key values that are not explicitly defined as partition key for other partitions will be stored in it. Guess how many new partitions you would create with auto-list if Oracle was to allow a DEFAULT partition with it .. exactly, not a single one.
rem simple multi-column list partitioned table<br> 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 p3 values (default));
rem remove the default partition<br> 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<br > 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<br> alter table mc set automatic;
Now we are trying the insert of the same record again:
rem try to insert again .. will work<br> 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<br> 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.
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<br> 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);
rem indexes in general<br> select index_name, index_type, uniqueness, partitioned, status from user_indexes where table_name='SOON2BPART' order by 1;<br> <br> rem partitioned index<br> 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
rem do an online conversion rem - only one index will not use default conversion<br> 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
rem partitioning metadata select table_name, partitioning_type, partition_count from user_part_tables where table_name='SOON2BPART';<br> 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:
So let's check the index shape and their status:
rem indexes general<br> 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<br> 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<br> 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.
Beginning with Oracle Database 12c Release 2 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
rem simple interval partitioned table with one read only partition<br> 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
rem metadata<br> select table_name, def_read_only from user_part_tables where table_name='ROPT';<br> 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:
You can change the read only/read write attribute for existing
rem change the status of a partition to read only<br> 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?The answer is simple: Oracle made the conscious design decision that we do not allow the combination of an online partition maintenance operations and a scenario where either one (or multiple) of the origin partitions are read only or where one (or multiple) of the target partitions (after the PMOP) are set to read only.
rem online PMOP will not work when one of the target partitions is read only<br> 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<br> 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<br> 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<br> 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<br> 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<br> 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<br> 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<br> 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
rem no drop or truncate partition<br> alter table ropt drop partition for (56);
Dropping a column (or setting a column to unused):
rem drop column is not allowed<br> 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<br> 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.
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.
To address this challenge, Oracle Database 12c Release 2 introduces a SQL command to specifically 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
rem partitioned table as target for exchange<br> 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<br> alter table part4xchange add (colnew1 number default 99);<br> rem set col unused<br> alter table part4xchange set unused (col2);
rem create table 4 xchange<br> create table np4xchange for exchange with table part4xchange;
rem exchange<br> 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<br> 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 l
subpartitioning layout of partition p1 as list partitioning layout for our
table to be exchanged:
rem table for exchange with composite partitioned table<br> 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 becaus
e the layout does not match):
rem exchange<br> 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.