Oracle Partitioning is a separately licensed option of the Oracle
database that is only
available with the Enterprise Edition of the database. Partitioning allows DBAs to split large tables into more manageable "sub-tables",
called partitions, to improve database performance, manageability and
availability.
Partitioning is basically a divide-and-conquer
approach to scale to very large database sizes. It improves manageability by
allowing partitions to be added, loaded, indexed and removed while the database
is on-line and users are working against these tables. Partitioning can
potentially make the database faster the bigger it gets.
Oracle 10g R2
allowed more than 1 million partitions per table. It also introduced
multidimensional pruning and fast table drops.
Starting with Oracle 11g,
Oracle provides a partition advisor to assist customers to choose the best
partitioning method for their data.
Some of the advantages offered:
§ Partitions can be stored in different tablespaces.
§ Partitions can be added/ removed while users are working.
DBAs can perform maintenance without having to bring down an entire table (import/
export/ load/ etc).
§ Data can be selected from targeted partitions without having
to scan all partitions for rows (partition pruning). The SQL optimizer will
bypass partitions that don't contain data pertinent to the query being solved.
Available partitioning types:
§ Range partitioning (introduced in Oracle 8)
§ Hash partitioning (introduced in Oracle 8i)
§ Composite partitioning (introduced in Oracle 8i)
§ List partitioning (introduced in Oracle 9i)
§ Interval partitioning (introduced in Oracle 11g)
§ System partitioning (introduced in Oracle 11g)
§ Reference partitioning (introduced in Oracle 11g)
Partition compression
Starting from Oracle 11g individual
partitions can be compressed:
§ Compression as high as 3.5 to 1 is possible
§ Compressed tables now support: DML Statements, Add
and Drop Column, Partition level COMPRESS or NOCOMPRESS.
§ ALTER TABLE... COMPRESS;
§ ALTER TABLE... NOCOMPRESS;
§ Table compression now supported for OLTP
As we know that in 10g we were able to compress the whole
table neither it was apply to individual partition but in 11g we can use
compress on partition level. Let's check the compression feature for
partitions.
SQL>CREATE
TABLE test
(sno NUMBER(6),
last_name VARCHAR2(30),
salary NUMBER(6))
PARTITION BY RANGE (salary)
INTERVAL (5000)
(
PARTITION p1 VALUES LESS THAN (5000) COMPRESS,
PARTITION p2 VALUES LESS THAN (10000) NOCOMPRESS,
PARTITION p3 VALUES LESS THAN (15000) COMPRESS,
PARTITION p4 VALUES LESS THAN (20000)) NOCOMPRESS;
SQL>
SELECT table_name, partition_name, compression
2 FROM
user_tab_partitions
3 WHERE
table_name='TEST';
TABLE_NAME PARTITION_NAME COMPRESS
---------------
--------------------------- --------------
TEST P1 ENABLED
TEST P2 DISABLED
TEST P3 ENABLED
TEST P4 DISABLED
We can define compression for whole partition by using
single keyword like this.
SQL>
CREATE TABLE test
2
(sno NUMBER(6),
3
last_name VARCHAR2(30),
4
salary NUMBER(6))
5
COMPRESS
6
PARTITION BY RANGE (salary)
7
INTERVAL (5000)
8
(
9
PARTITION p1 VALUES LESS THAN (5000) ,
10 PARTITION p2 VALUES LESS THAN (10000),
11 PARTITION p3 VALUES LESS THAN (15000),
12 PARTITION p4 VALUES LESS THAN (20000));
Table
created.
SQL>
SELECT table_name, partition_name, compression
2 FROM
user_tab_partitions
3 WHERE
table_name='TEST';
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------
----------------------------- -------------
TEST P1 ENABLED
TEST P2 ENABLED
TEST P3 ENABLED
TEST P4 ENABLED
How
does one enable partition support for a database?
Partitioning is only available with Oracle Enterprise
Edition as a cost option (you need to buy licenses before you can use it).
Start the Oracle installer and check if "Oracle
Partitioning" is installed. If it is, you can just start using it.
If not, you will get error ORA-00439: feature not
enabled: Partitioning. If you get this error, upgrade to Enterprise Edition
and/or install the partitioning option.
What
partitioning types does Oracle support?
Oracle support the following partitioning methods:
§ Range partitioning - data is mapped to
partitions based on a range of column values (usually a date column)
§ Hash partitioning - data is mapped to
partitions based on a hashing algorithm, evenly distributing data between the
partitions.
§ List partitioning - data is mapped to
partitions based on a list of discrete values.
§ Interval partitioning - data is
mapped to partitions based on an interval
§ Composite partitioning - combinations
of the above methods
§ Range-Hash partitioning - data is partitioned by range, then
hashed to sub-partitions.
§ Range-List partitioning - data is partitioned by range, then
to sub-partitions based on a value list.
§ Range-range partitioning
§ List-range partitioning
§ List-hash partitioning
§ List-list partitioning
§ Interval partitioning, an extension of
range partitioning. New partitions are automatically added.
§ System partitioning, application controlled
partitioning.
§ Reference partitioning, partitioned like a
parent table (referenced in a referential constraint).
How do I know if an object is
partitioned or not?
§ A simple query against the data dictionary will show if a
given table or index is partitioned or not:
§ SELECT * FROM dba_part_tables;
§ SELECT * FROM dba_part_indexes;
How does one list the partitions of a partitioned object?
One can list the partitions of a table with:
SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';
One can show the partitions of an given index with:
SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';
No comments:
Post a Comment