Partitioning

Partitioning Schemes

  • Range-based: Data is separated into partitions using ranges of values.

CREATE TABLE time_range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
);

  • Hash-based: A hash function is applied to a column in the table. The more distinct this column is, the better. The hast function works the best when number of partitions is power of 2.

CREATE TABLE hash_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY HASH (prod_id)
PARTITIONS 2;

  • List-based: You set up list of values to tell the database in which partition the data belongs. For example, you could use a CODE field and dictate that records with the codes A, X and Y go into partition P1, while records with the codes B, C and Z go into partition P2. List partitions are limited to single columns only as opposed to range based or hash partitions that may work on multiple columns.

CREATE TABLE list_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY LIST (channel_id)
(PARTITION even_channels VALUES (2,4),
PARTITION odd_channels VALUES (3,9)
);

  • Composite: This is a hybrid partitioning scheme. The data will be partitioned initially by range. Then each range partition may be further subpartitioned by either a hash or a list.

There are two primary ways to partition indexes in Oracle

  • Locally partitioned index: For each and every table partition created, there will be an index partition. The data in each index partition points to data in exactly one table partition by definition. You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Like other indexes, you can create a bitmap index on partitioned tables. The only restriction is that bitmap indexes must be local to the partitioned table—they cannot be global indexes. Global bitmap indexes are supported only on nonpartitioned tables.
CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;
  • Globally partitioned index: The index is partitioned by its own scheme. There is no parity between the index partitions and the table partitions. A single index can point to data in any number of table partitions, unlike the locally partitioned index. In general, global indexes are useful for OLTP applications, where rapid access, data integrity, and availability are important. In an OLTP system, a table may be partitioned by one key, for example, the employees.department_id column, but an application may need to access the data with many different keys, for example, by employee_id or job_id. Global indexes can be useful in this scenario.
CREATE INDEX time_channel_sales_idx ON time_range_sales (channel_id)
   GLOBAL PARTITION BY RANGE (channel_id)
      (PARTITION p1 VALUES LESS THAN (3),
       PARTITION p2 VALUES LESS THAN (4),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));

Partitioning should be used for increased availability, easier administration and increased performance.

Increased availability is achieved by placing tableparts in different tablespaces, so that when one datafile goes corrupted, users can still access the rest of the data that is not in that datafile.

Easier administration is achieved by the fact that we have to work with smaller objects, which is easier, faster and less resource-intensive.

Increased performance is the hardest thing to achieve from all three, because it requires most thinking. It must be applied in order to solve a real problem we have. It should not be just applied without knowing what do we have in mind with it. It can happen that we make partitions based on one column, but mostly we query the table on some other column etc.

 

SQL ACCESS ADVISOR

To get recommendations on which partitions to make based on the previous queries on the database, you should run the SQL ACCESS ADVISOR.

LINKS:

http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CFAGCECI

http://docs.oracle.com/cd/E11882_01/server.112/e25555/tdpdw_perform.htm#CIHJDAHD

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: