It is range partitioned on week_no, four weeks to a partition. trackback. Oracle also maintains the index partitioning automatically when partitions in the underlying table are added, dropped, merged, or split, or when hash partitions or subpartitions are added or coalesced. A bitmap index on a partitioned table must be a local index. Oracle can take advantage of the fact that a local index is equipartitioned with the underlying table to generate better query access plans. When creating a partitioned table or index, you include a partitioning clause in the CREATE TABLE statement. Physical attributes (other than TABLESPACE, as explained in the preceding) of partitions of local indexes created during processing ALTER TABLE ADD PARTITION are set to the default physical attributes of each index. For range-partitioned tab… Prior to 12c, several partition maintenance commands would cause the partition to be locked against DML commands until the partition maintenance command completed. Global indexes can also be created as partial indexes, with only the flagged partitions included in the index. Oracle 12c. Figure 3-6 Global Prefixed Partitioned Index. Local indexes support more availability when there are partition or subpartition maintenance operations on the table. For example, in Figure 3-4, if the predicate is deptno=15, the optimizer knows to apply the predicate only to the second partition of the index. I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit – Joe Oct 15 '17 at 22:51 Because these entries may be scattered across all partitions or subpartitions of the index, mixed with entries for other partitions or subpartitions that are not being recovered, there is no way to accomplish this except by re-creating the entire global index. It executes quickly and uses few system resources (Undo and Redo). So not only DML are immediately executed, but also the partition move operation is completely transparent for users. One or multiple columns can be used as partition key. A global partitioned index is nonprefixed if it is not partitioned on a left prefix of the index columns. Management of global partitioned indexes presents several challenges. Advanced compression works well on all supported indexes, including those indexes that are not good candidates for prefix compression. You cannot drop a partition of a hash-partitioned table. The physical attributes of index partitions are described in this topic. Bitmap indexes on nonpartitioned tables cannot be partitioned. Issue the ALTER TABLE DROP PARTITION statement without maintaining global indexes. Figure 3-4 illustrates another example of a local prefixed index. 12c | If the partition contains data and one or more global indexes are defined on the table, then use one of the following methods (method 1, 2 or 3) to drop the table partition. Index partitions can be merged or split as necessary. Articles | Linux. All commands worked for me up to: --Creating Partition online ALTER TABLE t1 MODIFY PARTITION BY RANGE (yr_qtr) INTERVAL (1) ( PARTITION P1 VALUES LESS THAN (20141) ) ONLINE; Not sure why.. Default physical attributes can later be modified using ALTER INDEX MODIFY DEFAULT ATTRIBUTES. 7 WHITE PAPER / Oracle Partitioning Only indexes on partitioned tables can be partial indexes. For example, a query using the predicate "acctno between 40 and 45" on the table checks of Figure 3-5 causes parallel scans of all the partitions of the nonprefixed index ix3. With a prefixed index, the likelihood to get partition pruning is much higher than with a non-prefixed index. Dropping a partition will discard the rows stored in that partition as a DDL statement. 10g | Asynchronous (Delayed) Global Index Maintenance for DROP and TRUNCATE Partition in Oracle Database 12c Release 1 Oracle 12c can optimize the performance of some DROP PARTITION and TRUNCATE PARTITION commands by deferring the associated index maintenance, while leaving the global indexes in a valid state.