If you want to reuse the indexes on the source table as index partitions on a target table when attaching data partitions, all index key columns or expressions for the indexes on the source table must match the index key columns or expressions for the partitioned indexes on the target table.
If the source table has an expression-based index, the system-generated statistical view and package that are associated with the index are dropped as part of the process to attach the partition. If the target table has a partitioned expression-based index, the expression is used when determining whether the source table has a matching index. If all other properties of the two indexes are the same, the index on the source table is considered to match the partitioned index on the target table. That is, the index on the source table can be used as an index on the target table.
The following table applies only when the target index is partitioned. The target index property is assumed by the source index in all cases where they are considered to be a match.
Rule number | Target index property | Source index property | Does the source index match? |
---|---|---|---|
1. | Non-unique | Unique | Yes, if the index is not an XML index. |
2. | Unique | Non-unique | No. |
3. | Column X is descending | Column X is ascending | No. |
4. | Column X is ascending | Column X is descending | No. |
5. | Column X is random | Column X is ascending | No. |
6. | Column X is ascending | Column X is random | No. |
7. | Column X is random | Column X is descending | No. |
8. | Column X is descending | Column X is random | No. |
9. | Partitioned | Nonpartitioned | No. It is assumed that the source table is partitioned. |
10. | pctfree n1 | pctfree n2 | Yes |
11. | level2pctfree n1 | level2pctfree n2 | Yes. |
12. | minpctused n1 | minpctused n2 | Yes. |
13. | Disallow reverse scans | Allow reverse scans | Yes, the physical index structure is the same irrespective of whether reverse scans are allowed. |
14. | Allow reverse scans | Disallow reverse scans | Yes, the same reason as 9. |
15. | pagesplit [L|H|S] | pagesplit [L|H|S] | Yes. |
16. | Sampled statistics | Detailed statistics | Yes. |
17. | Detailed statistics | Sampled statistics | Yes. |
18. | Not clustered | Clustered | Yes. |
19. | Clustered | Not clustered | Yes. The index becomes a clustering index, but the data is not clustered according to this index until the data is reorganized. You can use a partition-level reorganization after attaching the data partition to cluster the data according to this index partition. |
20. | Ignore invalid values | Reject invalid values | Yes. |
21. | Reject invalid values | Ignore invalid values | No. The target index property of rejecting invalid values must be respected, and the source table might have rows that violate this index constraint. |
22. | Index compression enabled | Index compression not enabled | Yes. Compression of the underlying index data does not occur until the index is rebuilt. |
23. | Index compression not enabled | Index compression enabled | Yes. Decompression of the index data does not occur until the index is rebuilt. |