Table partitioning is neat feature introduced in DB2 V9, made life much easier for DBA's. Thought let's compare it with other databases. Here is what my findings, by no mean exhaustive or complete......
Informix:
Informix has both partitioned and non-partitioned index. But there is *no* key word in the CREATE INDEX to say whether it should be partitioned or non-partitioned.
The decision is based on the context. The CREATE INDEX can be successfully creating a partitioned or non-partitioned index or returning a failure.
Whether index is partitioned or not is independent of that of the base table.
Informix index has the 'attached' or 'detached' concepts, which has nothing to do with the table partition attach/detatch operation.
They mean Informix index pages and table data page are in the same object if an index is 'attached'.
When the index object is separate from that of the table, it's considered as 'detatched'.
The attached/detached is independent of whether the base table is partitioned or not.
During table partition attach, existing indexes on the to-be attached table will be dropped unless they match the index definitions
in the target table ( the partitioned table ). Any missing indexes will be automatically created for the newly attached partition during ATTACH.
Everything is done under the cover. ATTACH will not return until the build of all matching indexes on the new partition complete.
Informix doesn't support index reorg syntax.
Index is maintained by a dedicated engine thread , which monitor the index page usage behind the scene and maintenance based on some criteria.
Partition level ( both index and table ) stats are collected and used.
Oracle:
Backup and recovery operations can be performed on individual partitions.
Oracle supports two types of partitioned index:
Local -- All index entries in a single partition will correspond to a single table partition (equi-partitioned).
They are created with the LOCAL keyword and support partition independence. Equi-partitioning allows oracle to be more efficient whilst devising query plans.
Global - Index in a single partition may correspond to multiple table partitions.
They are created with the GLOBAL keyword and do not support partition independence.
Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equi-partitioned,
but Oracle will not take advantage of this structure. Global partioned indexes must be prefixed with the partitioning key.
The user can specify which tablespace each partition of the index is stored in.
SQL Server:
Supports local indexes which can be partitioned different from the table data. Partitioning works as the following:
Create a partition function which maps the rows of a table or index into partitions based on the values of a specified column.
Create a partition scheme which maps the partitions of a partitioned table or index to filegroups.
Create a table using a partition scheme defined (note a default filegroup can be specified, similar to the IN/INDEX IN/etc. clauses of create table for DB2 LUW).
Create an index using a partition scheme defined (does not have to match the table's partitioning scheme).
Uniqueness for UNIQUE indexes is only verified within a single partition.
The default if no partition scheme is specified for create index on a partitioned table is to create partitioned indexes partitioned the same as the data.
All indexes require a partitioning scheme on a partitioned table, but that doesn't exclude global indexes as we know them.
A global index can be created by using a partitioning function which encompasses the entire range for a column and
using a partitioning scheme which puts it in one filegroup.
ALTER PARTITION
Can only split or merge partitions as the means to add/drop partitions. This is an offline operation.
ALTER INDEX
Can be used to rebuild or reorganize indexes offline or online for any of: one index, all indexes on the table, one index partition.
Teradata
Does not support local indexes
Informix:
Informix has both partitioned and non-partitioned index. But there is *no* key word in the CREATE INDEX to say whether it should be partitioned or non-partitioned.
The decision is based on the context. The CREATE INDEX can be successfully creating a partitioned or non-partitioned index or returning a failure.
Whether index is partitioned or not is independent of that of the base table.
Informix index has the 'attached' or 'detached' concepts, which has nothing to do with the table partition attach/detatch operation.
They mean Informix index pages and table data page are in the same object if an index is 'attached'.
When the index object is separate from that of the table, it's considered as 'detatched'.
The attached/detached is independent of whether the base table is partitioned or not.
During table partition attach, existing indexes on the to-be attached table will be dropped unless they match the index definitions
in the target table ( the partitioned table ). Any missing indexes will be automatically created for the newly attached partition during ATTACH.
Everything is done under the cover. ATTACH will not return until the build of all matching indexes on the new partition complete.
Informix doesn't support index reorg syntax.
Index is maintained by a dedicated engine thread , which monitor the index page usage behind the scene and maintenance based on some criteria.
Partition level ( both index and table ) stats are collected and used.
Oracle:
Backup and recovery operations can be performed on individual partitions.
Oracle supports two types of partitioned index:
Local -- All index entries in a single partition will correspond to a single table partition (equi-partitioned).
They are created with the LOCAL keyword and support partition independence. Equi-partitioning allows oracle to be more efficient whilst devising query plans.
Global - Index in a single partition may correspond to multiple table partitions.
They are created with the GLOBAL keyword and do not support partition independence.
Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equi-partitioned,
but Oracle will not take advantage of this structure. Global partioned indexes must be prefixed with the partitioning key.
The user can specify which tablespace each partition of the index is stored in.
SQL Server:
Supports local indexes which can be partitioned different from the table data. Partitioning works as the following:
Create a partition function which maps the rows of a table or index into partitions based on the values of a specified column.
Create a partition scheme which maps the partitions of a partitioned table or index to filegroups.
Create a table using a partition scheme defined (note a default filegroup can be specified, similar to the IN/INDEX IN/etc. clauses of create table for DB2 LUW).
Create an index using a partition scheme defined (does not have to match the table's partitioning scheme).
Uniqueness for UNIQUE indexes is only verified within a single partition.
The default if no partition scheme is specified for create index on a partitioned table is to create partitioned indexes partitioned the same as the data.
All indexes require a partitioning scheme on a partitioned table, but that doesn't exclude global indexes as we know them.
A global index can be created by using a partitioning function which encompasses the entire range for a column and
using a partitioning scheme which puts it in one filegroup.
ALTER PARTITION
Can only split or merge partitions as the means to add/drop partitions. This is an offline operation.
ALTER INDEX
Can be used to rebuild or reorganize indexes offline or online for any of: one index, all indexes on the table, one index partition.
Teradata
Does not support local indexes
