Defining Local Partitioned Indexes using Oracle

In Oracle Database 19c, defining a local partitioned index is a structured process that aligns with the principles of partitioned tables. This process ensures that the index partitions are directly associated with the corresponding table partitions, providing efficient data management and access. To define a local partitioned index, follow the steps outlined below:

1. Prerequisite Check: Ensure your table is partitioned. A local partitioned index can only be created on a partitioned table. The structure and partitioning strategy of the table dictate how the index partitions will be aligned.
2. Syntax Overview: Use the `CREATE INDEX` statement with the `LOCAL` keyword. The syntax for creating a local partitioned index is as follows:
   

   CREATE INDEX index_name ON table_name(column_list)
   LOCAL
   (PARTITION partition_name [TABLESPACE tablespace_name] [other_options],
   ...
   );
   

   
   
   • `index_name`: The name you wish to assign to the index.
   • `table_name(column_list)`: The name of the partitioned table and the columns you want to include in the index.
   • `LOCAL`: Specifies that the index is to be partitioned on the same lines as the table itself, with each index partition corresponding to a table partition.
   • `partition_name`: The name of the index partition, which usually aligns with the table partition names for clarity.
   • `TABLESPACE tablespace_name`: (Optional) Specifies the tablespace where the index partition will be stored. If omitted, the index partition will be created in the default tablespace of the user or the tablespace of the corresponding table partition.
3. Creating the Index: Execute the `CREATE INDEX` statement with the `LOCAL` keyword. For example, if you have a sales table partitioned by year, you can create a local partitioned index on the `sales_amount` column like so:
   

   CREATE INDEX sales_amount_idx ON sales(sales_amount)
   LOCAL;
   

   
   This statement creates an index partition for each partition of the `sales` table, each storing index data related to its corresponding table partition.
4. Customizing Index Partitions: Although the local index partitions are automatically aligned with the table partitions, you have the flexibility to specify storage parameters for each index partition. This includes assigning different tablespaces or settings for each partition. However, ensure that these customizations do not undermine the performance and manageability benefits of local partitioning.
5. Verification: After creating the index, verify its structure and partitioning using data dictionary views such as `USER_IND_PARTITIONS` for index partitions or `USER_PART_INDEXES` to confirm the index type and partitioning strategy.

By following these steps, you can effectively define a local partitioned index in Oracle 19c, leveraging the advantages of partitioning for both data storage and access. Remember, the key benefit of local partitioned indexes is their tight coupling with the table partitions, which simplifies management tasks such as partition maintenance and ensures consistency in performance across partitions.

In a local partitioned index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index handles data that resides in only one partition. An index is defined as local only if the LOCAL attribute is specified when the index is created. An index can be created with only one LOCAL clause. The following series of images below explains the structure of a local partitioned index.

1. A master index node makes the separate indexes appear as a single index.
2. Each local partition has a separate index tree.

To maximize the benefits of partitioning, it sometimes makes sense to partition a table and an index identically so that both

1. the table partition and
2. the index partition

map to the same set of rows. You can automatically implement this type of partitioning, which is called equipartitioning^[1], by specifying an index for a partitioned table as a LOCAL index. Local indexes simplify maintenance, since standard operations, such as dropping a partition, will work transparently with both the index partition and the table partition. The ability to partition tables and indexes has had a dramatic impact on the use of Oracle for data warehousing applications. Object partitioning means that Oracle can handle 1000 terabytes of data.

For data warehouse and DSS applications, local indexes have several advantages over global indexes. The features of local partitioned indexes are described in the table below:

A Decision Support System (DSS) query on a very large table presents a special performance problem. If an ad-hoc query requires a table scan, every record in the table must be inspected, and this may take a long time. Where the query can be restricted to a single partition, or range of partitions, response time is dramatically reduced. The performance of a DSS application can therefore be greatly improved by using a partition scan rather than a table scan.

(DSS) Decision support systems are defined as the class of warehouse system that deals with solving a semi-structured problem. This means the task has a structured component as well as an unstructured component. In short, the unstructured component involves human intuition and requires human interaction with the DSS. The well-structured components of a DSS are the decision rules stored as the problem-processing system. The intuitive, or creative, component is left to the user. The following represent some examples of semi-structured problems:

1. Choosing a spouse. While there are many structured rules (I want someone of my religion, who is shorter than me), there is still the unstructured, unquantifiable component to the process of choosing a spouse.
2. Choosing a site for a factory. This is a nonrecurring problem that has some structured components (cost of land, availability of workers, and so on), but there are many other unstructured components in this decision (i.e., quality of life).
3. Choosing a stock portfolio. Here the structured rules are the amount of risk and the performance of stocks, but the choice of stocks for a portfolio requires human intuition.

Decision support technology recognizes that many tasks require human intuition. For example, the process of choosing a stock portfolio is a task which has both structured and intuitive components. Certainly, rules are associated with choosing a stock portfolio, such as diversification of the stocks and choosing an acceptable level of risk. These factors can be easily quantified and stored in a database system, allowing the user of the system to create what-if scenarios. However, just because a system has well-structured components does not guarantee that the entire decision process is well-structured. One of the best ways to tell if a decision process is semi-structured is to ask the question:
Question: Do people with the same level of knowledge demonstrate different levels of skill?

The well skilled DBA knows to always create a local partitioned index with the same key as the partitioned table. That way, all of the index keys in the local index point to values within the corresponding table partition.
In summary, partitioned indexes work very well in conjunction with partitioned tables, and these constructs can lead to some very real performance benefits.
Once you create index partitions, you will need to maintain them. The next lesson explores index maintenance with partitioned indexes.

[1]equipartitioning: If you partition a table that has a nested table, then Oracle Database uses the partitioning scheme of the original base table as the basis for how the nested table is partitioned. This partitioning of one base table partition for each nested table partition is called equipartitioning. By default, nested tables are automatically partitioned when the base table is partitioned. Note, however, that composite partitioning is not supported for nested tables.