We have 6
methods to partition a table
• Range partitioning: You may specify ranges of data that should
be stored together.
For example, everything that has a timestamp within the month of Jan-2010
will be stored in partition 1, everything with a timestamp within Feb-2010 in
partition 2, and so on. This is probably the most commonly used partitioning
mechanism in Oracle.
• Hash partitioning: You saw this in the first example in this
chapter. A column (or columns) has a hash function applied to it, and the row
will be placed into a partition according to the value of this hash.
• List partitioning: You specify a discrete set of values, which
determines the data that should be stored together. For example, you could
specify that rows with a STATUS column value in ( 'A', 'M', 'Z' ) go into
partition 1, those with a STATUS value in ( 'D', 'P', 'Q' ) go into partition
2, and so on.
• Interval partitioning: This is very similar to range
partitioning with the exception that the database itself can create new
partitions as data arrives. With traditional range partitioning, the DBA was
tasked with pre-creating partitions to hold every possible data value, for now
and into the future. This typically meant that a DBA was tasked with creating
partitions on a schedule—to hold next months’ or next weeks’ data. With
interval partitioning, the database itself will create partitions as new data
arrives that doesn’t fit into any existing partition based on a rule specified
by the DBA.
• Reference partitioning: This allows a child table in a
parent/child relationship enforced by a foreign key to inherit the partitioning
scheme of the parent table. This makes it possible to equi-partition a child
table with its parent table without having to de-normalize the data model. In
the past, a table could only be partitioned based on attributes it physically
stored; reference partitioning in effect allows you to partition a table based
on attributes from its parent table.
• Composite partitioning: This is a combination of range,
hash, and list partitioning. It allows you to first apply one partitioning
scheme to some data, and then within each resulting partition have that
partition subdivided into sub partitions using some partitioning scheme.
Range Partition:
create table inv (invoice_id, invoice_date, invoice_code, customer_no) partition by range (invoice_date) (partition part_1 values less than (to_date('01/01/1990','dd/mm/yyyy')),
partition part_2 values less than (to_date('01/01/1995','dd/mm/yyyy')),
partition part_3 values less than (to_date('01/01/2000','dd/mm/yyyy')),
partition part_4 values less than (to_date('01/01/2005','dd/mm/yyyy')),
partition part_5 values less than (to_date('01/01/2010','dd/mm/yyyy')),
partition part_6 values less than (to_date('01/01/2014','dd/mm/yyyy'))
)
as
select
invoice_id, invoice_date,
invoice_code, customer_no from invoice_headers_hist_fgd
select sum(cnt)
from (
select count(*) cnt
from inv partition (part_4)
union
select count(*)
from inv partition (part_3) -- To find the sum of records in all
partitions
union
select count(*)
from inv partition (part_5)
union
select count(*)
from inv partition (part_6)
)
Now, try to insert the following record into above table
insert into inv values ('abc', sysdate+500,'SALE','ABC')
ORA-14400:
inserted partition key does not map to any partition
The above error is because we are inserting data with date which is not
matching any of the partitions.
To fix this error the table needs to create with partition which uses
MAXVALUE clause.
Modified Table Structure with MAXVALUE partition
create table inv (invoice_id, invoice_date, invoice_code, customer_no) partition by range (invoice_date) (partition part_1 values less than (to_date('01/01/1990','dd/mm/yyyy')),
partition part_2 values less than (to_date('01/01/1995','dd/mm/yyyy')),
partition part_3 values less than (to_date('01/01/2000','dd/mm/yyyy')),
partition part_4 values less than (to_date('01/01/2005','dd/mm/yyyy')),
partition part_5 values less than (to_date('01/01/2010','dd/mm/yyyy')),
partition part_6 values less than (to_date('01/01/2014','dd/mm/yyyy')),
partition part_7 values less than (MAXVALUE)
)
as
select
invoice_id, invoice_date,
invoice_code, customer_no from invoice_headers_hist_fgd
Now, the following insert works fine.
insert into inv values ('abc', sysdate+500,'SALE','ABC')
Hash Partitioning
When hash partitioning a table, Oracle will apply a hash function to the
partition key to determine in which of the N partitions the data should be
placed. Oracle recommends that N be a number that is a power of 2 (2, 4, 8, 16,
and so on) to achieve the best overall distribution, and we’ll see shortly that
this is absolutely good advice.
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date
DATE NOT NULL,
comments
VARCHAR2(500))
PARTITION BY HASH (invoice_no)
PARTITIONS 4
STORE IN (users, users, users, users);
OR
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date
DATE NOT NULL,
comments
VARCHAR2(500))
PARTITION BY HASH (invoice_no)
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION
invoices_q4 TABLESPACE users);
select * from
ALL_TAB_PARTITIONS
drop table invoices purge statement drops table along with
its partitions.
Composite Partitioning
Tables
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date
DATE NOT NULL,
comments
VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
SUBPARTITION BY HASH (invoice_no)
SUBPARTITIONS 2
(PARTITION
invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
PARTITION
invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')))
The above create table statements creates 4 main partitions, 2 sub
partitions for each main partition which means in total 4*2 = 8 sub partitions
will be created.
select * from
ALL_TAB_SUBPARTITIONS
insert into invoices (select invoice_id, invoice_date,customer_no from
invoice_headers_hist_fgd
where trunc(invoice_date) < TO_DATE('01/04/2001', 'DD/MM/YYYY')
and rownum
<201 )
select * from
invoices subpartition (SYS_SUBP74)
To select the data
from partition
select * from
invoices partition (invoices_q1)
Naming the sub partitions with our own names
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date
DATE NOT NULL,
comments
VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
SUBPARTITION BY HASH (invoice_no)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) ( subpartition invoices_q1_1, subpartition invoices_q1_2) ,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) ( subpartition invoices_q2_1, subpartition invoices_q2_2),
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) ( subpartition invoices_q31_1, subpartition invoices_q3_2),
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) ( subpartition invoices_q4_1, subpartition invoices_q4_2))
select * from
ALL_TAB_SUBPARTITIONS
List Partitioning
List partitioning was a new
feature of Oracle9i Release 1. It provides the ability to specify in which partition
a row will reside, based on discrete lists of values. It is often useful to be
able to partition by some code, such as a state or region code. For example, we
might want to pull together in a single partition all records for people in the
states of Maine (ME), New Hampshire (NH), Vermont (VT), and Massachusetts (MA),
since those states are located next to or near each other and our application queries
data by geographic region. Similarly, we might want to group together
Connecticut (CT), Rhode Island (RI), and New York (NY).
We can’t use a range
partition, since the range for the first partition would be ME through VT, and the
second range would be CT through RI. Those ranges overlap. We can’t use hash
partitioning since we can’t control which partition any given row goes into;
the built-in hash function provided by Oracle does that.
create table list_example
(state_cd varchar2(2),
data varchar2(20)
)
partition by list(state_cd)
(partition part_1 values('ME','NH','VT','MA'),
partition part_2 values('CT','RI','NY')
)
insert into list_example values ('CT', 'application
data...');
insert into list_example values ('MA', 'application
data...');
insert into list_example values ('ME', 'application
data...');
insert into list_example values ('NH', 'application
data...');
insert into list_example values ('NY', 'application
data...');
insert into list_example values ('RI', 'application
data...');
insert into list_example values ('VT', 'application
data...');
select * from
list_example partition (part_1)
select * from
list_example partition (part_2)
insert into list_example values ( 'VA', 'data' );
ORA-14400: inserted partition key does not map to any partition
alter table list_example add partition part_3 values ( DEFAULT );
After adding the default partition now the insert
statement works fine
alter table list_example add partition part_4 values( 'CA', 'NM' );
ORA-14323: cannot add partition when DEFAULT partition exists
We would have to remove the DEFAULT
partition, and then add PART_4, and then put the DEFAULT partition back. The
reason behind this is that the DEFAULT partition could have had rows with the
list partition key value of CA or NM—they would not belong in the DEFAULT
partition after adding PART_4.
Interval Partitioning
Interval partitioning is a
new feature available in Oracle Database 11g Release 1 and above. It is very similar
to range partitioning described previously—in fact, it starts with a range
partitioned table but adds a rule (the interval) to the definition so the
database knows how to add partitions in the future. The goal of interval
partitioning is to create new partitions for data—if, and only if, data exists
for a given partition and only when that data arrives in the database. In other
words, to remove the need to pre-create partitions for data, to allow the data
itself to create the partition as it is inserted. To use interval partitioning,
you start with a range partitioned table without a MAXVALUE partition and
specify an interval to add to the upper bound, the highest value of that
partitioned table to create a new range. You need to have a table that is range
partitioned on a single column that permits adding a NUMBER or INTERVAL type to
it (e.g. a table partitioned by a VARCHAR2 field cannot be interval
partitioned; there is nothing you can add to a VARCHAR2). You can use interval
partitioning with any suitable existing range partitioned table; that is, you
can ALTER an existing range table to be interval partitioned, or you can create
one with the CREATE TABLE command.
CREATE TABLE interval_tab (
id NUMBER,
code
VARCHAR2(10),
description
VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);
Reference Partitioning:
Reference partitioning is a
new feature of Oracle Database 11g 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 year’s 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.
Refer page 581 expert_oracle_database_architecture_2nd_edition.pdf
and add the examples by testing them in your laptop 11g instance
Row Movement
You might wonder what would
happen if the column used to determine the partition is modified in any of the
preceding partitioning schemes. There are two cases to consider:
• The modification would not cause a different
partition to be used; the row would still belong in this partition. This is
supported in all cases.
• The modification would cause the row to move across
partitions. This is supported if row movement is enabled for the table;
otherwise, an error will be raised.
CREATE TABLE range_example
( range_key_column date ,
data varchar2(20)
)
PARTITION BY RANGE (range_key_column)
( PARTITION part_1 VALUES LESS THAN
(to_date('01/01/2010','dd/mm/yyyy')),
PARTITION part_2 VALUES LESS THAN
(to_date('01/01/2011','dd/mm/yyyy'))
)
insert into range_example ( range_key_column, data ) values
( to_date( '15-dec-2009
00:00:00', 'dd-mon-yyyy hh24:mi:ss' ), 'application
data...' );
insert into range_example ( range_key_column, data ) values ( to_date( '01-jan-2010
00:00:00', 'dd-mon-yyyy
hh24:mi:ss' )-1/24/60/60, 'application
data...' );
We take one of the rows and update the value
in its RANGE_KEY_COLUMN such that it can remain in PART_1:
update range_example
set range_key_column = trunc(range_key_column)
where range_key_column =
to_date( '31-dec-2009
23:59:59', 'dd-mon-yyyy hh24:mi:ss' )
As expected, this succeeds: the row remains
in partition PART_1. Next, we update the
RANGE_KEY_COLUMN to a value that would cause
it to belong in PART_2:
update range_example
set range_key_column = to_date('01-jan-2010','dd-mon-yyyy')
where range_key_column = to_date('31-dec-2009','dd-mon-yyyy');
ORA-14402: updating partition key column would cause a partition change
This immediately raises an error since we did
not explicitly enable row movement. In Oracle8i and later releases, we can
enable row movement on this table to allow the row to move from partition to
partition.
alter table range_example enable row movement;
After the above alter statement, if we try re
execute the above update statement, it works this time without any error.
Note:
ROW Movement causes change
in ROWID of the given row. There are other cases where a ROWID can change as a
result of an update. It can happen as a result of an update to the primary key
of an IOT. The universal ROWID will change for that row, too. The Oracle 10g
and above FLASHBACK TABLE command may also change the ROWID of rows, as might
the Oracle 10g and above ALTER TABLE SHRINK command.
Using hash or list partitions within range
partitions is advisable when the resulting range partitions are too large to
manage or when you want to use all PDML capabilities or parallel index scanning
against a single range partition.
Partitioning Indexes
Two Types,
• Equi partition the index with the table:
This is also known as a local index. For every table partition, there
will be an index partition that indexes just that table partition. All of the
entries in a given index partition point to a single table partition, and all
of the rows in a single table partition are represented in a single index
partition.
• Partition the index by range or hash:
This is also known as a globally partitioned index. Here the index is
partitioned by range, or optionally in Oracle 10g and above by hash, and a
single index partition may point to any (and all) table partitions.
In the case of a globally partitioned index,
note that the number of index partitions may be different from the number of
table partitions.
Since global indexes may be
partitioned by range or hash only, you must use local indexes if you wish to
have a list or composite partitioned index. The local index will be partitioned
using the same scheme as the underlying table.
Local indexes are more for data warehouse
implementations and global indexes are for OLTP.
Oracle makes a distinction between the
following two types of local indexes:
• Local prefixed indexes: These are indexes whereby
the partition keys are on the leading edge of the index definition. For
example, if a table is range partitioned on a column named LOAD_DATE, a local
prefixed index on that table would have LOAD_DATE as the first column in its
column list.
• Local non-prefixed indexes: These
indexes do not have the partition key on the leading edge of their column list.
The index may or may not contain the partition key columns.
Global Indexes:
CREATE TABLE partitioned
( timestamp date,
id int
)
PARTITION BY RANGE (timestamp)
(
PARTITION part_1 VALUES LESS THAN
( to_date('01-jan-2000','dd-mon-yyyy') ) ,
PARTITION part_2 VALUES LESS THAN
( to_date('01-jan-2001','dd-mon-yyyy') )
)
create index partitioned_index
on partitioned(id)
GLOBAL
partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
)
MAXVALUE represents an infinite upper
bound on the range.
alter table partitioned add constraint partitioned_pk primary key(id)
drop index partitioned_index;
ORA-02429: cannot drop index used for enforcement of unique/primary key
create index partitioned_index2
on partitioned(timestamp,id)
GLOBAL
partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
)
ORA-14038: GLOBAL partitioned index must be prefixed
Use UPDATE GLOBAL INDEXES clause along with
ALTER table command to maintain partition.
No comments:
Post a Comment