Global Index partition in Oracle

CREATE TABLE sales (
    sale_id       NUMBER,
    sale_date     DATE,
    product_id    NUMBER,
    sale_amount   NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_jan2024 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
    PARTITION sales_feb2024 VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
    PARTITION sales_mar2024 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
    PARTITION sales_default VALUES LESS THAN (MAXVALUE)
);


-- Insert data for January 2024 (sales_jan2024 partition)
INSERT INTO sales (sale_id, sale_date, product_id, sale_amount)
VALUES (1, TO_DATE('2024-01-05', 'YYYY-MM-DD'), 101, 1500);

INSERT INTO sales (sale_id, sale_date, product_id, sale_amount)
VALUES (2, TO_DATE('2024-01-15', 'YYYY-MM-DD'), 102, 2300);

-- Insert data for February 2024 (sales_feb2024 partition)
INSERT INTO sales (sale_id, sale_date, product_id, sale_amount)
VALUES (3, TO_DATE('2024-02-05', 'YYYY-MM-DD'), 103, 1200);

INSERT INTO sales (sale_id, sale_date, product_id, sale_amount)
VALUES (4, TO_DATE('2024-02-20', 'YYYY-MM-DD'), 104, 1750);

-- Insert data for March 2024 (sales_mar2024 partition)
INSERT INTO sales (sale_id, sale_date, product_id, sale_amount)
VALUES (5, TO_DATE('2024-03-05', 'YYYY-MM-DD'), 105, 1450);

INSERT INTO sales (sale_id, sale_date, product_id, sale_amount)
VALUES (6, TO_DATE('2024-03-25', 'YYYY-MM-DD'), 106, 2100);

-- Insert data for the default partition (sales_default partition)
INSERT INTO sales (sale_id, sale_date, product_id, sale_amount)
VALUES (7, TO_DATE('2024-04-15', 'YYYY-MM-DD'), 107, 3000);



CREATE INDEX sales_global_part_idx ON sales(sale_id) GLOBAL
PARTITION BY RANGE(sale_id)
(
    PARTITION p1 VALUES LESS THAN(100),
    PARTITION p2 VALUES LESS THAN(500),
    PARTITION p3 VALUES LESS THAN(1000),
    PARTITION p4 VALUES LESS THAN(5000),
    PARTITION p5 VALUES LESS THAN(MAXVALUE)
);



SQL> select INDEX_NAME,INDEX_type , status, GLOBAL_STATS from user_indexes;

INDEX_NAME                          INDEX_TYPE                  STATUS                GLO
----------------------------------- --------------------------- ----------            ---
SALES_GLOBAL_PART_IDX               NORMAL                      N/A ==(local index)    NO




SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name = 'SALES_GLOBAL_PART_IDX';

INDEX_NAME                          PARTITION_NAME            STATUS
----------------------------------- ------------------------- ----------
SALES_GLOBAL_PART_IDX               P1                        USABLE
SALES_GLOBAL_PART_IDX               P2                        USABLE
SALES_GLOBAL_PART_IDX               P3                        USABLE
SALES_GLOBAL_PART_IDX               P4                        USABLE
SALES_GLOBAL_PART_IDX               P5 --                     USABLE



SQL> ALTER index SALES_GLOBAL_PART_IDX drop partition P5;
ALTER index SALES_GLOBAL_PART_IDX drop partition P5
                                                 *
ERROR at line 1:
ORA-14078: you may not drop the highest partition of a GLOBAL index



SQL> ALTER index SALES_GLOBAL_PART_IDX drop partition P4;

Index altered.

SQL> SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name = 'SALES_GLOBAL_PART_IDX'; 

INDEX_NAME                          PARTITION_NAME            STATUS
----------------------------------- ------------------------- ----------
SALES_GLOBAL_PART_IDX               P1                        USABLE
SALES_GLOBAL_PART_IDX               P2                        USABLE
SALES_GLOBAL_PART_IDX               P3 --                     USABLE----
SALES_GLOBAL_PART_IDX               P5                        USABLE





SQL>  ALTER index SALES_GLOBAL_PART_IDX drop partition P3;

Index altered.

INDEX_NAME                          PARTITION_NAME            STATUS
----------------------------------- ------------------------- ----------
SALES_GLOBAL_PART_IDX               P1                        USABLE
SALES_GLOBAL_PART_IDX               P2                        USABLE
SALES_GLOBAL_PART_IDX               P5                        USABLE


SQL> ALTER index SALES_GLOBAL_PART_IDX drop partition P1;

Index altered.

SQL> SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name = 'SALES_GLOBAL_PART_IDX'; 
INDEX_NAME                          PARTITION_NAME            STATUS
----------------------------------- ------------------------- ----------
SALES_GLOBAL_PART_IDX               P2                        UNUSABLE -----------
SALES_GLOBAL_PART_IDX               P5                        USABLE


SQL> SELECT index_name, partition_name, status, LAST_ANALYZED FROM user_ind_partitions;

INDEX_NAME                          PARTITION_NAME            STATUS     LAST_ANAL
----------------------------------- ------------------------- ---------- ---------
SALES_GLOBAL_PART_IDX               P5                        USABLE     26-NOV-24
SALES_GLOBAL_PART_IDX               P2  ----                  UNUSABLE   26-NOV-24



Solution:- Rebuild the partition P2

SQL>
SQL> alter index SALES_GLOBAL_PART_IDX rebuild partition P2;

Index altered.

SQL> SELECT index_name, partition_name, status, LAST_ANALYZED FROM user_ind_partitions;

INDEX_NAME                          PARTITION_NAME            STATUS     LAST_ANAL
----------------------------------- ------------------------- ---------- ---------
SALES_GLOBAL_PART_IDX               P5                        USABLE     26-NOV-24
SALES_GLOBAL_PART_IDX               P2                        USABLE     27-NOV-24

Scenario Overview:

• You have a sales table where data is stored based on different months using partitioning. This means that the data for January, February, and March 2024 is divided into separate sections, called partitions, for easier management.
• You also created an index (a data structure that speeds up searches) called SALES_GLOBAL_PART_IDX on the sale_id column, but instead of one single index for the entire table, you divided it into different partitions based on the sale_id values.

The Problem:

1. Index Partitions: When you create a global partitioned index, Oracle divides the index into multiple partitions (just like how the sales table is divided). These partitions are separate parts of the index that each hold a portion of the data to speed up queries.

2. Dropping Partitions: Initially, your index had partitions P1, P2, P3, P4, and P5. You attempted to drop (remove) these partitions one by one.

   • Dropping P5 (the last partition) caused an error because Oracle does not allow dropping the highest partition in a global index. This is a rule Oracle has for global indexes.
   • You successfully dropped partitions P4, P3, and P1 without issues.

3. Index Becomes Unusable: After dropping partitions, Oracle marked the remaining partition (P2) as UNUSABLE. This means that the index could not be used until it was fixed.

4. Rebuilding the Partition: To fix the issue, you used the command to rebuild the P2 partition. Rebuilding a partition restores its functionality, making it USABLE again.

   • After rebuilding, P2 was marked as USABLE and was ready to be used for queries once more.

In Summary:

• You were working with a global partitioned index on your sales table to improve query performance.
• When you tried to drop some of the partitions (P5, P4, P3, P1), Oracle allowed most of the drops, except the last one (P5), which it doesn’t allow to be dropped.
• After dropping some partitions, Oracle made the remaining partition (P2) UNUSABLE.
• To restore functionality, you rebuilt the unusable partition (P2), which made it usable again.

Key Takeaways:

• Global partitioned index means the index spans across all table partitions but is itself partitioned by the chosen column (sale_id in your case).
• Dropping partitions is a way to remove unused parts of the index, but the highest partition (P5) can't be removed.
• UNUSABLE status means the index is temporarily non-functional, but it can be fixed by rebuilding the partition.