Partitioning Enhancements In Oracle9i

List Partitioning
Maintaining Global Indexes
Parallel Direct-Load Inserts

List Partitioning

List partitioning allows creater flexibility in the mapping of rows to partitions than range or hash partitioning. Since the data is distributed based on discrete column values, unordered and unrelated sets of data can be grouped together with no relationship between the partitions:

CREATE TABLE employees
(
  id                      NUMBER(10),
  first_name              VARCHAR2(20),
  last_name               VARCHAR2(20),
  country                 VARCHAR2(30)
)
PARTITION BY LIST (country)
(
  PARTITION europe VALUES ('ENGLAND', 'FRANCE', 'ITALY', 'SWITZERLAND'),
  PARTITION america VALUES ('AMERICA'),
  PARTITION unknown VALUES (NULL)
);

There are several usage issues with respect to list partitioning:

Only available to heap organized tables.
Multicolumn partitioning not supported.
All literals in the value lists must be unique.
NULL is a valid literal value.
There is no equivalent of MAXVALUE.
All lists must have at lease one literal.
Lists of literals cannot exceed 4KB
Partition pruning, partition wise joins and parallelism are supported.
Local indexes and global range partitioned indexes are supported.

Maintaining Global Indexes

The UPDATE GLOBAL INDEXES clause can be added to several partition DDL operations (ADD, DROP, MOVE, TRUNCATE, SPLIT, MERGE, EXCHANGE and COALESCE PARTITION) that would normally leave the global indexes associated with the partition in an UNUSABLE state:

TRUNCATE TABLE employees
  UPDATE GLOBAL INDEXES;

ALTER TABLE employees
  DROP SUBPARTITON emp1
  UPDATE GLOBAL INDEXES
  PARALLEL (DEGREE 4);

This makes maintenance of global indexes much easier than previous versions, thus increasing availability. Only indexes that are currently valid will be affected by this clause. This clause is not available for IOTs.

A number of operations on partitons are extremely fast since they only require a few data dictionary updates. If the UPDATE GLOBAL INDEXES clause is added the performance is reduced since the index rebuild is part of the issued DDL. The index updates are logged and it should only be used when the number of rows is low and data must stay available. For larger numbers of rows index rebuilds are more efficient and allow index reorganization.

Parallel Direct-Load Inserts

In Oracle8i only one slave process could act on each partition. In Oracle9i multiple slaves can act on each partition increasing performance where there is data skew between partitions.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.