Home / Oracle DBA / Partitioned Tables and Indexes

Partitioned Tables and Indexes

Partitioned Tables And Indexes

Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways.

  • Partition independence means backup and recovery operations can be performed on individual partitions, whilst leaving the other partitions available.
  • Query performance can be improved as access can be limited to relevant partitions only.
  • There is a greater ability for parallelism with more partitions.

All the examples shown here use the users table space for all partitions. In a real situation it is likely that these partitions would be assigned to different table spaces to reduce device contention.

Range Partitioning Tables

Partitioned Tables and Indexes-1

Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition. Once historical data is no longer needed the whole partition can be removed. If the table is indexed correctly search criteria can limit the search to the partitions that hold data of a correct age.

Example

CREATE TABLE invoices
(invoice_no   NUMBER NOT NULL,
invoice_date DATE   NOT NULL,
comments     VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) 
TABLESPACE users,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) 
TABLESPACE users,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) 
TABLESPACE users,
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) 
TABLESPACE users);

Hash Partitioning Tables

Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16…) and can be specified by the PARTITIONS…STORE IN clause.

Example

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 specified individually.

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);

Composite Partitioning Tables

Partitioned Tables and Indexes-2

Composite partitioning allows range partitions to be hash subpartitioned on a different key. The greater number of partitions increases the possiblities for parallelism and reduces the chances of contention. The following example will range partition the table on invoice_date and subpartitioned these on the invoice_no giving a total of 32 subpartitions.

Example:

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 8
(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'));
DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe