Rebuilding Partial local index Partition in Oracle

For index partitions to remain effective, they must be properly maintained. There are cases where Oracle performance can be improved by re-building an index using the

ALTER INDEX REBUILD

The rebuilds can be run concurrently.

The capability to rebuild only part of an index is a great performance feature for the following reasons:

1. Only the index tree that is out-of-balance needs to be rebuilt.
2. One index partition can be rebuilt while the other partitions continue to service SQL queries.

Do not rebuild indexes unless you have a solid reason to do so. Rebuilding indexes is generally unnecessary

1. unless an index is corrupt or
2. you want to move an index to different tablespace.

In Oracle 19c, the correct syntax to rebuild an index is:

ALTER INDEX <index_name> REBUILD;

Using this command to improve Oracle performance in 19c:

1. Rebuild vs. Rebuild Online: In Oracle 19c, consider using the `ALTER INDEX <index_name> REBUILD ONLINE;` command instead. This allows the index to be rebuilt while remaining usable, minimizing performance impact on running queries.
2. Evaluate Necessity: Rebuilding an index is not always necessary for performance improvement. Analyze the index usage and fragmentation level before rebuilding. Sometimes, simply dropping and recreating the index might be more efficient.
3. Consider Alternatives: Before rebuilding, explore other performance optimization techniques, like analyzing query plans, optimizing SQL code, or adjusting database parameters. Rebuilding can be resource-intensive, so use it strategically.

Remember, even in 19c, rebuilding an index should be a well-informed decision based on specific performance bottlenecks and potential impacts.

The

ALTER INDEX REBUILD PARTITION 

statement can be used to regenerate a single partition in a local or global partitioned index. This saves you from having to perform DROP INDEX and then CREATE INDEX, which would affect all partitions in the index.
"ALTER INDEX REBUILD PARTITION" has four important applications:

1. To recluster an index partition to recover space and improve performance.
2. To repair an index partition in case of a media failure on the volume where the index partition resides or a software corruption of the segment containing the index partition.
3. To regenerate a local index partition after loading the underlying table partition with Import or SQL*Loader. These utilities offer a performance option to bypass index maintenance, mark the affected index partitions INDEX UNUSABLE, and let the DBA rebuild them later. In other words, the strategy of "drop index then re-create index" can be replaced by a strategy of mark index partition UNUSABLE then rebuild index partition.
4. To rebuild index partitions rendered UNUSABLE by partition maintenance operations on the underlying table.

The statement "ALTER INDEX REBUILD PARTITION" is still valid and supported in Oracle 19. This command allows you to specifically rebuild a single partition of a partitioned index. There are a few key points to keep in mind:

• Rebuilding vs. Online Rebuilding: Similar to full index rebuilding, there's also an
  

  ALTER INDEX REBUILD PARTITION ONLINE 
  

  
  option that allows the partition to be rebuilt while remaining usable. Consider using this whenever possible to minimize downtime.
• Applicable Only to Partitioned Indexes: This command is only useful when the index is partitioned. It cannot be used on non-partitioned indexes.
• Specify the Partition: You need to specify the specific partition you want to rebuild by using the `<partition_name>` clause.
• Limited Options: Compared to rebuilding the entire index, you have fewer options when rebuilding a partition. You can only specify the tablespace for the partition with the `TABLESPACE` clause.

In conclusion, "ALTER INDEX REBUILD PARTITION" is a valid and useful command in Oracle 19c, but it's specifically designed for partitioned indexes and has limited options compared to rebuilding the entire index.

There are several advantages to reorganizing selected partitions within an index.
Table Partitions

1. Can be reorganized faster than the whole table
2. Can be segregated into separate tablespaces independently of the other partitions
3. Can be made read only, independently of other table partitions. This is a great feature for a data warehouse.

Question: What Are Index-Organized Tables?
An index-organized table, in contrast to an ordinary table, has its own way of structuring, storing, and indexing data. If you compare this concept to an ordinary table it may help to explain its uniqueness.

A row in an ordinary table has a stable physical location. Once this location is established, the row never completely moves. Even if it is partially moved with the addition of new data, there is always a row piece at the original physical address which is identified by the original physical rowid from which the system can find the rest of the row. As long as the row exists, its physical rowid does not change. An index in an ordinary table stores both the column data and the rowid. A row in an index-organized table does not have a stable physical location. It keeps data in sorted order, in the leaves of a B*-tree index built on the table's primary key. These rows can move around to preserve the sorted order. For example, an insertion can cause an existing row to move to a different slot, or even to a different block. The leaves of the B*-tree index hold the primary key and the actual row data. Changes to the table data. For example, adding new rows, or updating or deleting existing rows, result only in updating the index.

This partial index rebuilding technique has been extended to the partitioning environment with the ALTER INDEX REBUILD SUBPARTITION syntax. The SUBPARTITION can only be used with local partitioned indexes. Global partitioned indexes cannot use this feature. Just like with the ALTER INDEX REBUILD command, it is important to remember to use the TABLESPACE clause, particularly if you need the index to be in the same tablespace.

Oracle recommends, but does not require, that you store each index partition in a separate tablespace for the following reasons:

1. Independent backup and recovery of each partition
2. Reduced potential for data corruption
3. Control of partition mapping to disk drive, which affects I/O load balancing

Partitioning Option: Oracle introduced partitioning as an option with Oracle8 to provide a higher degree of manageability and availability. You can take individual partitions offline for maintenance while other partitions remain available for user access. In data warehousing implementations, partitioning is sometimes used to implement rolling windows based on date ranges. Other partitioning types include hash partitioning (used to divide data into partitions using a hashing function and providing an even distribution of data) and list partitioning (enabling partitioning of data based on discrete values such as geography). Starting with Oracle Database 11g, interval partitioning can also be used to automatically create new fixed ranges as needed during data insertions. Many of these partitioning types can be used in combination as composite partitions. Examples of composite partitions in Oracle Database 11g include range-range, range-hash, range-list, list-range, list-hash, and list-list.

There are times when it is not obvious in which partition data should reside, although the partitioning key can be identified. Rather than group similar data, there are times when it is desirable to distribute data such that it does not correspond to a business or a logical view of the data, as it does in range partitioning. With hash partitioning, a row is placed into a partition based on the result of passing the partitioning key into a hashing algorithm. Using this approach, data is randomly distributed across the partitions rather than grouped. This is a good approach for some data, but may not be an effective way to manage historical data. However, hash partitions share some performance characteristics with range partitions. For example, partition pruning is limited to equality predicates. You can also use partition-wise joins, parallel index access, and parallel DML. As a general rule, use hash partitioning for the following purposes:

1. To enable partial or full parallel partition-wise joins with likely equisized partitions.
2. To distribute data evenly among the nodes of an MPP platform that uses Oracle Real Application Clusters. Consequently, you can minimize interconnect traffic when processing internode parallel statements.
3. To use partition pruning and partition-wise joins according to a partitioning key that is mostly constrained by a distinct value or value list .
4. To randomly distribute data to avoid I/O bottlenecks if you do not use a storage management technique that stripes and mirrors across all available devices.

Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index. But before you perform either task weigh the costs and benefits of each option and choose the one that works best for your situation. Table 5-6 is a comparison of the costs and benefits associated with rebuilding and coalescing indexes

Experienced DBAs will double-size their index tablespaces so they can rebuild an index partition in the same tablespace.
The tablespace must have enough room for the index and for the temporary segments in the new index.
The next lesson wraps up this module.