11 of the oracle handbook Series: Partition)

Partitioning enables you to decompose very large tables and indexes into smaller and more manageable pieces called partitions. Each partition is an independent object with its own name and optionally its own storage characteristics.

Oracle allows you to split large tables and large indexes into small blocks. Each block is a separate object called a partition, partitioning technology can be used to improve query and DML performance, and facilitate data management.

 

1.Partition Table

1.1.Partition tables mainly include three types:

Range Partitioning

List Partitioning

Hash partitioning

 

1.2.Partition Table creation syntax example (RangePartition ):

CREATE TABLE tab_part_0309(val DATE, val2 VARCHAR2(200))
PARTITION BY RANGE(val)
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('20120308','yyyymmdd'))
);

 

1.3.ORA-14400Error

If Oracle cannot find the appropriate partition when inserting data, a ORA-14400 error occurs, "inserted partition key does not map to any partition ". For example (assume the current value is ):

INSERT INTO tab_part_0309 VALUES(SYSDATE,'abrownfox');

 

1.4.How to add partitions

ALTER TABLE tab_part_0309 ADD PARTITION p2 VALUES LESS THAN (TO_DATE('20120309','yyyymmdd'));
ALTER TABLE tab_part_0309 ADD PARTITION p3 VALUES LESS THAN (TO_DATE('20120310','yyyymmdd'));

 

1.5.Query

SELECT * FROM tab_part_0309 PARTITION(p3);

You can specify partitions during query. However, Oracle's automatic Partition Pruning is more common. That is, if the Where clause contains columns used for partitioning (the val column in the example) during query, Oracle will automatically locate the Partition, instead of specifying partitions manually.

 

1.6.Delete Partition

ALTER TABLE tab_part_0309 DROP PARTITION p3;

 

1.7.MAXVALUE

In some cases, we will create partitions on a regular basis through the scheduled task (Scheduler). At this time, we need to consider a problem. If the scheduled task fails and the partition is not created, then the data insertion in the rear will encounter a ORA-14400 error. There is a way to avoid this error, using MAXVALUE:

CREATE TABLE tab_part_0309_2(val DATE, val2 VARCHAR2(200))
PARTITION BY RANGE(val)
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('20120308','yyyymmdd')),
  PARTITION pm VALUES LESS THAN (MAXVALUE)
);

After MAXVALUE is used, you cannot add new partitions:

ALTER TABLE tab_part_0309_2 ADD PARTITION p2 VALUES LESS THAN (TO_DATE('20120310','yyyymmdd'))
ORA-14074, partition bound must be collate higher than that of the last partition

However, we can split the data from the pm partition:

ALTER TABLE tab_part_0309_2
  SPLIT PARTITION pm AT (TO_DATE('20120310','yyyymmdd'))
INTO (PARTITION p2, PARTITION pm)

The advantage is that even if the scheduled task for splitting fails, data can still be inserted normally (only in the pm partition ). At the same time, after an error is found, we can remedy it and manually split a partition. In this way, data that meets the new partition conditions in the pm will be automatically partitioned.

 

1.8.Interval partitioning

The 11G introduces a more powerful partitioning mechanism: Interval partitioning, which can automatically create partitions according to certain conditions. The creation syntax is as follows:

CREATE TABLE tab_part_0309_3(val DATE, val2 VARCHAR2(200))
PARTITION BY RANGE(val)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('20120308','yyyymmdd'))
);

The Interval Partition Table must specify a root partition (p1 in the previous example) and use Interval to specify the partition Interval. It uses the NumToYMInterval function. The first parameter of this function is number, and the last parameter is unit, including 'month' and 'Year'. The previous example indicates that the interval is one MONTH. Currently, the interval of one MONTH is the minimum. You can specify 0.5 or 0.3, however, the final effect is one month.

If the current time is, run the following statement:

INSERT INTO tab_part_0309_3 VALUES(SYSDATE, 'abrownfox');

1) As a result, a new partition is automatically created.

2) The partition field value is, which is one month after the root partition.

3) The partition name is automatically generated, for example, sys_p21.

4) if the inserted time is two months later or longer, Oracle only generates the required partitions and does not generate continuous partitions. For example, if the insert time is, only the 2012-06-08 partition is generated. The and partitions are not generated.

 

2.Partition Index

Like a table, indexes can also be divided into common indexes and literary indexes (when partition indexes are more literary ). You can create a partition index for a common table (but you cannot create a Local partition index, which will be explained later). You can also create a common index for a partitioned table.

2.1.There are two types of partition indexes:

Global partition index)

Local partition index)

 

2.2.Global partition Index

It has nothing to do with the table partition. When creating an index, you can specify any column as the Key of the index partition. The creation syntax is as follows:

CREATE TABLE tab_part_0321_2(val DATE, val2 NUMBER)
PARTITION BY RANGE(val)
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('20120308','yyyymmdd'))
);


CREATE INDEX tab_part_0321_2_idx ON tab_part_0321_2 (val2)
   GLOBAL PARTITION BY RANGE (val2)
      (PARTITION p1 VALUES LESS THAN (2),
       PARTITION p2 VALUES LESS THAN (3),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));

Note that the global keyword is used here, and the Key used by the index partition is the val2 field, rather than the val field used by the table partition.

Can I create an index for the field val2 but use the val field as the partition key?

CREATE INDEX tab_part_0321_2_idx2 ON tab_part_0321_2 (val2)
   GLOBAL PARTITION BY RANGE (val)
      (PARTITION pa VALUES LESS THAN (to_date('20120308','yyyymmdd')),
       PARTITION pb VALUES LESS THAN (to_date('20120408','yyyymmdd')),
       PARTITION pc VALUES LESS THAN (MAXVALUE));
ORA-14038 global partitioned index must be prefixed.

Prefixed refers to "prefix Index", which means that the index key is the same as the partition key, and Oracle cannot create a non-Prefix global partition index.

 

2.3.Local partition Index

Only the partition table can create a local partition index. The local partition index always uses the same fields as the partition table for partitioning (the partition by clause is not required ), therefore, the index partitions correspond to the table partitions one by one. Let's look at the Syntax:

CREATE INDEX tab_part_0321_2_idx3 ON tab_part_0321_2 (val) LOCAL

Try:

CREATE INDEX tab_part_0321_2_idx4 ON tab_part_0321_2 (val2) LOCAL
ORA-01408 such column list already indexed

This is because we have created an index in this column in the previous section. We will go back and remove tab_part_0321_2_idx and re-execute the preceding statement to create the index successfully. This indicates that it is different from the global partition index, local partitioned indexes allow the creation of "non-Prefix indexes ".

 

Note: If you are interested in the concepts of local prefixed index and local non-prefixed index mentioned above, you can refer to some posts on OTN, for example: https://forums.oracle.com/forums/thread.jspa? ThreadID = 2150455 & start = 0 & tstart = 0