8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Partitioning Enhancements In Oracle 9i
Related articles.
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 11g Release 1
- Partitioning an Existing Table using DBMS_REDEFINITION
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;
or
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:
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 11g Release 1
- Partitioning an Existing Table using DBMS_REDEFINITION
Hope this helps. Regards Tim...