Database Reference
In-Depth Information
So, what we've done is take the
HIGH_VALUE
and subtract one second from it. We know that the
HIGH_VALUE
represents the
strictly less than
value, so one second before its value would be a value in the range. Once we have that,
we applied the format
"PART_"yyyy_mm
to the resulting
TIMESTAMP
and get a string such as
PART_2014_03
for
March 2014. We use that string in a rename command and now our data dictionary looks like this:
EODA@ORA12CR1> select a.partition_name, a.tablespace_name, a.high_value,
2 decode( a.interval, 'YES', b.interval ) interval
3 from user_tab_partitions a, user_part_tables b
4 where a.table_name = 'AUDIT_TRAIL'
5 and a.table_name = b.table_name
6 order by a.partition_position;
PARTITION_NAME TABLESPACE HIGH_VALUE INTERVAL
-------------- ---------- ------------------------------- ------------------------------
P0 USERS TIMESTAMP' 1900-01-01 00:00:00'
PART_2014_02 USERS TIMESTAMP' 2014-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
PART_2014_03 EXAMPLE TIMESTAMP' 2014-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
PART_2014_06 USERS TIMESTAMP' 2014-07-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
We would just run that script every now and then to rename any newly added partitions to keep the nice naming
convention in place. Bear in mind, to avoid any SQL Injection issues (we are using string concatenation, not bind
variables; we cannot use bind variables in DDL) we would want to keep this script as an anonymous block or as an
invoker's rights routine if we decide to make it a stored procedure. That will prevent others from running SQL in our
schema as if they were us, which could be a disaster.
Reference Partitioning
Reference partitioning is a feature of Oracle Database 11
g
Release 1 and above. It addresses the issue of parent/child
equi-partitioning; that is, when you need the child table to be partitioned in such a manner that each child table partition
has a one-to-one relationship with a parent table partition. This is important in situations such as a data warehouse
where you want to keep a specific amount of data online (say the last five years' worth of
ORDER
information) and need
to ensure the related child data (the
ORDER_LINE_ITEMS
data) is online as well. In this classic example, the
ORDERS
table
would typically have a column
ORDER_DATE
, making it easy to partition by month and thus facilitate keeping the last five
years of data online easily. As time advances, you would just have next month's partition available for loading and you
would drop the oldest partition. However, when you consider the
ORDER_LINE_ITEMS
table, you can see you would have
a problem. It does not have the
ORDER_DATE
column, there is nothing in the
ORDER_LINE_ITEMS
table to partition it by;
therefore, it's not facilitating the purging of old information or loading of new information.
In the past, prior to reference partitioning, developers would have to denormalize the data, in effect copying the
ORDER_DATE
attribute from the parent table
ORDERS
into the child
ORDER_LINE_ITEMS
table. This presented the typical
problems of data redundancy, that of increased storage overhead, increased data loading resources, cascading update
issues (if you modify the parent, you have to ensure you update all copies of the parent data) and so on. Additionally, if you
enabled foreign key constraints in the database (as you should), you would discover that you lost the ability to truncate
or drop old partitions in the parent table. For example, let's set up the conventional
ORDERS
and
ORDER_LINE_ITEMS
tables
starting with the
ORDERS
table:
EODA@ORA12CR1> create table orders
2 (
3 order# number primary key,
4 order_date date,
5 data varchar2(30)
6 )