(updated 3.12.2009 to deal with UPDATE GLOBAL INDEXES option)
When I decide to partition a table, I also need to consider whether to partition the indexes, and if so how. The easiest option is to locally partition the indexes. I try to avoid globally partitioned indexes because they can become invalid when you do partition maintenance. However, where an index leads on a column other than the partitioning key, then you might have to scan all partitions of a locally partitioned index if you do not query by the partitioning column. Sometimes, it is necessary to partition an index differently to the table, or not at all. However, I have found that there are some partition management operations that do not invalidate global indexes.
In the case of PeopleSoft Global Payroll I range partition tables to match the Payroll 'stream' definitions. Thus each concurrent payroll process only updates one partition, and more importantly, each partition is updated by one, and only one, payroll process. Thus eliminating consistent read on these objects. The number of payroll processes is usually a function of the hardware configuration. This doesn't change often, so I don't expect to do partition maintenance. If I need to change the payroll stream definitions, then I will completely rebuild the partitioned tables.
In the case of Time & Labor, I create periodic partitions (weekly, fortnightly, or monthly) for the reported time table, partitioning on the 'date under reporting' column. This column is used by many of the queries in T&L processing, resulting in partition elimination. However, it is also necessary to periodically add new partitions.
The Oracle documentation says this about
"Management of Global Partitioned Indexes
Global partitioned indexes are harder to manage than local indexes:
When the data in an underlying table partition is moved or removed (SPLIT, MOVE, DROP, or TRUNCATE), all partitions of a global index are affected. Consequently global indexes do not support partition independence."
The key point is that the global index is invalidated if rows of data in a partition table are affected by DDL on a partition. Here is a little demonstration. I will create a table with a locally partitioned unique index, and a second non-partitioned index.
Now I will add and populate a third partition
And now I will check on the indexes.
Note that the global index IB is still usable
The interesting thing is that if I delete all the rows from a partition then I can drop it, and the index does not go unusable.
but if I didn't delete all rows from partition t3 before I drop it, then the global index goes unusable.
If I truncate the table such that I remove rows, then the index immediately becomes unusable
However, if I had deleted those rows first the truncate would not have invalidated the index.
Addendum As Randolf points out in his comment, it is possible to prevent the global index from being invalidated by specifying the UPDATE GLOBAL INDEXES clauses.
However, this comes at a cost. Oracle now full scans the partition being dropped and removes the entries from the index. This recursive SQL statement can be found in an Oracle trace.
Conclusion
There are some things that I can do without invalidating global indexes
When I decide to partition a table, I also need to consider whether to partition the indexes, and if so how. The easiest option is to locally partition the indexes. I try to avoid globally partitioned indexes because they can become invalid when you do partition maintenance. However, where an index leads on a column other than the partitioning key, then you might have to scan all partitions of a locally partitioned index if you do not query by the partitioning column. Sometimes, it is necessary to partition an index differently to the table, or not at all. However, I have found that there are some partition management operations that do not invalidate global indexes.
In the case of PeopleSoft Global Payroll I range partition tables to match the Payroll 'stream' definitions. Thus each concurrent payroll process only updates one partition, and more importantly, each partition is updated by one, and only one, payroll process. Thus eliminating consistent read on these objects. The number of payroll processes is usually a function of the hardware configuration. This doesn't change often, so I don't expect to do partition maintenance. If I need to change the payroll stream definitions, then I will completely rebuild the partitioned tables.
In the case of Time & Labor, I create periodic partitions (weekly, fortnightly, or monthly) for the reported time table, partitioning on the 'date under reporting' column. This column is used by many of the queries in T&L processing, resulting in partition elimination. However, it is also necessary to periodically add new partitions.
The Oracle documentation says this about
"Management of Global Partitioned Indexes
Global partitioned indexes are harder to manage than local indexes:
When the data in an underlying table partition is moved or removed (SPLIT, MOVE, DROP, or TRUNCATE), all partitions of a global index are affected. Consequently global indexes do not support partition independence."
The key point is that the global index is invalidated if rows of data in a partition table are affected by DDL on a partition. Here is a little demonstration. I will create a table with a locally partitioned unique index, and a second non-partitioned index.
DROP TABLE t PURGE;
CREATE TABLE t
(a NUMBER NOT NULL
,b VARCHAR2(100) NOT NULL
)
PARTITION BY RANGE(a)
(PARTITION T1 VALUES LESS THAN (100)
,PARTITION T2 VALUES LESS THAN (200)
);
CREATE UNIQUE INDEX ia ON t (a)
LOCAL
(PARTITION i1
,PARTITION i2
);
CREATE INDEX ib ON t (b);
INSERT INTO t
SELECT ROWNUM, TO_CHAR(TO_DATE(ROWNUM,'j'),'Jsp')
FROM dba_objects
WHERE ROWNUM < 200;
COMMIT;
CREATE TABLE t
(a NUMBER NOT NULL
,b VARCHAR2(100) NOT NULL
)
PARTITION BY RANGE(a)
(PARTITION T1 VALUES LESS THAN (100)
,PARTITION T2 VALUES LESS THAN (200)
);
CREATE UNIQUE INDEX ia ON t (a)
LOCAL
(PARTITION i1
,PARTITION i2
);
CREATE INDEX ib ON t (b);
INSERT INTO t
SELECT ROWNUM, TO_CHAR(TO_DATE(ROWNUM,'j'),'Jsp')
FROM dba_objects
WHERE ROWNUM < 200;
COMMIT;
Now I will add and populate a third partition
ALTER TABLE t
ADD PARTITION t3 VALUES LESS THAN (300);
INSERT INTO t
SELECT ROWNUM+199, TO_CHAR(TO_DATE(ROWNUM+199,'j'),'Jsp')
FROM dba_objects
WHERE ROWNUM+199 < 300 ; COMMIT;
ADD PARTITION t3 VALUES LESS THAN (300);
INSERT INTO t
SELECT ROWNUM+199, TO_CHAR(TO_DATE(ROWNUM+199,'j'),'Jsp')
FROM dba_objects
WHERE ROWNUM+199 < 300 ; COMMIT;
And now I will check on the indexes.
column table_name format a5 heading 'Table|Name'
column index_name format a5 heading 'Index|Name'
column partition_name format a4 heading 'Part|Name'
column partitioned heading 'Part?#'
column status heading 'Index|Status'
SELECT table_name, index_name, partitioned, status
FROM user_indexes where table_name = 'T' ORDER BY 1,2;
SELECT index_name, partition_name, status
FROM user_ind_partitions WHERE index_name IN (SELECT index_name FROM user_indexes WHERE table_name = 'T') order by 1,2,3;
column index_name format a5 heading 'Index|Name'
column partition_name format a4 heading 'Part|Name'
column partitioned heading 'Part?#'
column status heading 'Index|Status'
SELECT table_name, index_name, partitioned, status
FROM user_indexes where table_name = 'T' ORDER BY 1,2;
SELECT index_name, partition_name, status
FROM user_ind_partitions WHERE index_name IN (SELECT index_name FROM user_indexes WHERE table_name = 'T') order by 1,2,3;
Note that the global index IB is still usable
Table Index Index
Name Name Part?# Status
----- ----- ------ ------
T IA YES N/A
T IB NO VALID
Index Part Index
Name Name Status
----- ---- ------
IA I1 USABLE
IA I2 USABLE
IA T3 USABLE
Name Name Part?# Status
----- ----- ------ ------
T IA YES N/A
T IB NO VALID
Index Part Index
Name Name Status
----- ---- ------
IA I1 USABLE
IA I2 USABLE
IA T3 USABLE
The interesting thing is that if I delete all the rows from a partition then I can drop it, and the index does not go unusable.
DELETE FROM t WHERE a>=200;
ALTER TABLE t DROP PARTITION t3;
Table Index Index
Name Name Part?# Status
----- ----- ------ ------
T IA YES N/A
T IB NO VALID
Index Part Index
Name Name Status
----- ---- ------
IA I1 USABLE
IA I2 USABLE
ALTER TABLE t DROP PARTITION t3;
Table Index Index
Name Name Part?# Status
----- ----- ------ ------
T IA YES N/A
T IB NO VALID
Index Part Index
Name Name Status
----- ---- ------
IA I1 USABLE
IA I2 USABLE
but if I didn't delete all rows from partition t3 before I drop it, then the global index goes unusable.
ALTER TABLE t DROP PARTITION t3;
Table Index Index
Name Name Part?# Status
----- ----- ------ --------
T IA YES N/A
T IB NO UNUSABLE
Index Part Index
Name Name Status
----- ---- --------
IA I1 USABLE
IA I2 USABLE
Table Index Index
Name Name Part?# Status
----- ----- ------ --------
T IA YES N/A
T IB NO UNUSABLE
Index Part Index
Name Name Status
----- ---- --------
IA I1 USABLE
IA I2 USABLE
If I truncate the table such that I remove rows, then the index immediately becomes unusable
ALTER TABLE t TRUNCATE PARTITION t3;
Table Index Index
Name Name Part?# Status
----- ----- ------ --------
T IA YES N/A
T IB NO UNUSABLE
Index Part Index
Name Name Status
----- ---- --------
IA I1 USABLE
IA I2 USABLE
Table Index Index
Name Name Part?# Status
----- ----- ------ --------
T IA YES N/A
T IB NO UNUSABLE
Index Part Index
Name Name Status
----- ---- --------
IA I1 USABLE
IA I2 USABLE
However, if I had deleted those rows first the truncate would not have invalidated the index.
Addendum As Randolf points out in his comment, it is possible to prevent the global index from being invalidated by specifying the UPDATE GLOBAL INDEXES clauses.
ALTER TABLE t DROP PARTITION t1 UPDATE GLOBAL INDEXES;
However, this comes at a cost. Oracle now full scans the partition being dropped and removes the entries from the index. This recursive SQL statement can be found in an Oracle trace.
insert /*+ RELATIONAL(T) PARALLEL(T,1) APPEND */ into "SYSADM"."T" partition ("T1") select /*+ RELATIONAL(T) PARALLEL(T,1) */ * from "SYSADM"."T" partition ("T1") delete global indexes
Conclusion
There are some things that I can do without invalidating global indexes
- I can add a new partition
- I can delete all rows from an existing partition and drop it.
- I can truncate a partition from which all the rows have been deleted.
- I can drop a partition with rows if I specify the UPDATE GLOBAL INDEXES clause, but this could take time.
- If I truncate a partition that does contain rows then I will immediately invalidate the index.
- If I do any DDL that removes rows from a partitioned table, I will invalidate global indexes because they contain rowids that are invalid.