Disclaimer

Saturday 17 August 2024

Local Prefixed Index Vs Local Non-Prefixed Index || Global Non-Partitioned Indexes and Global Partitioned Indexes

 

To explain the difference between local prefixed indexes and local non-prefixed indexes in Oracle, let's break it down step by step in simple terms with examples and syntax.

Layman Explanation:

Think of your table as a giant filing cabinet with drawers (partitions) organized by years. You create indexes (like lists) to quickly find the documents in each drawer.

  1. Local Prefixed Index:

    • This is like labeling each drawer with a specific label (e.g., "Year 2022"), and then inside each drawer, you create an index based on that label. If you want to find a document from 2022, you know exactly which drawer to open and look at that index.
    • Prefixing means that the index is directly tied to how the drawers are organized. If the drawers are organized by year, the index is built using the "year" as the main guide.

  2. Local Non-Prefixed Index:

    • This is like creating an index inside each drawer, but the index isn't necessarily based on the label on the drawer. For example, the drawers are still organized by year, but the index inside could be based on a different label, like "Customer Name" instead of the "Year."
    • The non-prefixed part means that the index is not directly tied to how the drawers are organized. It can be based on a different column or set of columns.

Examples with Syntax:

Let’s say you have a Sales table partitioned by Sale_Date (year). The data is divided into partitions based on the year of the sale.

Step 1: Create the Partitioned Table

sql
CREATE TABLE Sales (
    Sale_ID    NUMBER,
    Customer_Name VARCHAR2(100),
    Sale_Amount NUMBER,
    Sale_Date  DATE
)
PARTITION BY RANGE (Sale_Date) (
    PARTITION sales_2022 VALUES LESS THAN (TO_DATE('01-JAN-2023', 'DD-MON-YYYY')),
    PARTITION sales_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
);

This table has two partitions based on the Sale_Date column (2022 and 2023).

Local Prefixed Index

A local prefixed index is based on the same column that is used to create the partitions (Sale_Date in this case).

Syntax for Local Prefixed Index:

sql
CREATE INDEX sales_local_prefixed_idx
ON Sales (Sale_Date)
LOCAL;

In this case:

  • Local: The index is partitioned along with the table, meaning each partition (sales_2022, sales_2023) has its own index.
  • Prefixed: The index is built on the Sale_Date column, which is the same column used to partition the table.

When you query the table based on Sale_Date, Oracle knows exactly which partition and index to use, making it efficient.

Local Non-Prefixed Index

A local non-prefixed index is based on a different column that is not used to create the partitions. For example, the table is partitioned by Sale_Date, but the index is created on Customer_Name.

Syntax for Local Non-Prefixed Index:

sql
CREATE INDEX sales_local_non_prefixed_idx
ON Sales (Customer_Name)
LOCAL;

In this case:

  • Local: The index is still partitioned along with the table, meaning each partition has its own index. But...
  • Non-Prefixed: The index is based on the Customer_Name column, which is different from the Sale_Date column used to partition the table.

Key Differences:

  1. Local Prefixed Index:

    • Index is built on the same column(s) used to create the partitions.
    • Queries that involve the partitioning column (e.g., Sale_Date) are more efficient because Oracle can directly locate the partition and use the index.

  2. Local Non-Prefixed Index:

    • Index is built on a different column(s) than the one used for partitioning.
    • Useful for queries that don’t directly involve the partitioning column (e.g., searching by Customer_Name), but it may not be as efficient as a prefixed index for partition-based queries.

Example Queries:

  • Local Prefixed Index: If you search for sales data in 2023:

    sql
    SELECT * FROM Sales WHERE Sale_Date = TO_DATE('2023-05-15', 'YYYY-MM-DD');

    Oracle will directly go to the sales_2023 partition and use the sales_local_prefixed_idx index.

  • Local Non-Prefixed Index: If you search for sales by a customer:

    sql
    SELECT * FROM Sales WHERE Customer_Name = 'John Doe';

    Oracle will still use the index on Customer_Name within each partition, but it won’t be as direct as using the partitioning column (Sale_Date).

When to Use:

  • Local Prefixed Index: Use when your queries often involve the partitioning column (e.g., dates, regions).
  • Local Non-Prefixed Index: Use when you need to search based on other columns (e.g., customer names, product types) that are not used to partition the table.


Let’s dive into Global Non-Partitioned Indexes and Global Partitioned Indexes in Oracle, explain them in simple terms, and go over examples including syntax.

Layman Explanation:

Imagine you have a giant warehouse storing products, and you organize the warehouse by year—each year has its own section (partition). You want to create indexes (like lists) to help you quickly find specific products.

  • Global Index: Unlike local indexes, which are divided and tied to specific sections of the warehouse (partitions), a global index covers the entire warehouse. Think of it as one big master list that spans all sections. There are two types:
    1. Global Non-Partitioned Index: This is like having one big list that covers everything in the warehouse. There are no smaller lists per section; it’s just one giant index that helps you find any product, no matter what section it's in.
    2. Global Partitioned Index: This is a global index, but it’s broken into smaller pieces (partitions) based on some criteria. It’s still a global index covering the entire warehouse, but the index itself is split into partitions.

Example with Syntax:

Let’s say you have a Sales table partitioned by Sale_Date (year). The data is divided into partitions based on the year of the sale.

Step 1: Create the Partitioned Table

sql
CREATE TABLE Sales (
    Sale_ID    NUMBER,
    Customer_Name VARCHAR2(100),
    Sale_Amount NUMBER,
    Sale_Date  DATE
)
PARTITION BY RANGE (Sale_Date) (
    PARTITION sales_2022 VALUES LESS THAN (TO_DATE('01-JAN-2023', 'DD-MON-YYYY')),
    PARTITION sales_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
);

This table is partitioned by the Sale_Date column (2022 and 2023 partitions).

Global Non-Partitioned Index

A global non-partitioned index is a single index that covers the entire table, regardless of the partitions. It’s one big list that can be used to search the whole table.

Syntax for Global Non-Partitioned Index:

sql
CREATE INDEX sales_global_idx
ON Sales (Customer_Name);

In this case:

  • The index is global, meaning it covers the entire Sales table across all partitions (2022 and 2023).
  • It’s non-partitioned, so there’s only one index, not separate indexes for each partition.

How It Works:

  • If you search for a customer (e.g., Customer_Name = 'John Doe'), Oracle will use this global index to look through the entire table, regardless of the partition.
  • The index spans the entire table, so if the partition structure changes (e.g., you drop a partition), this index might need to be rebuilt.

Global Partitioned Index

A global partitioned index is still an index that covers the entire table, but the index itself is split into smaller partitions based on some criteria. It’s like breaking the master list into smaller pieces.

Syntax for Global Partitioned Index:

sql
CREATE INDEX sales_global_part_idx
ON Sales (Customer_Name)
GLOBAL PARTITION BY HASH (Customer_Name)
PARTITIONS 4;

In this case:

  • The index is global, meaning it covers the entire table.
  • It’s partitioned by a hashing method on the Customer_Name column, which splits the index into 4 partitions.

How It Works:

  • When you search for a customer (e.g., Customer_Name = 'John Doe'), Oracle will use the partitioned index, but it can narrow down the search to a specific partition of the index, making the search faster than if it had to scan through one giant non-partitioned index.
  • The index partitions don’t align with the table partitions. Instead, they are split based on the index's criteria (e.g., a hash of Customer_Name).

Key Differences:

  1. Global Non-Partitioned Index:

    • One big index for the entire table, covering all partitions.
    • Easier to manage in some cases but can become inefficient if the table grows large.
    • Example use case: If you have queries that don’t rely on the partitioning column (e.g., searching by Customer_Name), but you don’t need a partitioned index.

  2. Global Partitioned Index:

    • The index covers the entire table but is split into smaller partitions based on the index's criteria.
    • More scalable and can offer better performance for larger tables.
    • Example use case: If you have large datasets and want to improve performance by partitioning the index itself, making it more efficient for queries that need to search across the entire table.

Example Queries:

  • Global Non-Partitioned Index: If you search for a customer:

    sql
    SELECT * FROM Sales WHERE Customer_Name = 'John Doe';

    Oracle will use the sales_global_idx to search the entire table, regardless of partitions.

  • Global Partitioned Index: If you search for a customer:

    sql
    SELECT * FROM Sales WHERE Customer_Name = 'John Doe';

    Oracle will use the sales_global_part_idx index but can focus on a specific partition of the index based on the hash of Customer_Name.

When to Use:

  • Global Non-Partitioned Index: Use when you want a simple, single index that covers the entire table, especially when the table isn’t too large or complex.
  • Global Partitioned Index: Use when you have a large table and want to split the index itself into partitions for better performance and scalability, even when queries need to search across the entire table.


========================================================



SQL> select username, default_tablespace from dba_users where username='SAM';

USERNAME           DEFAULT_TABLESPACE
------------------ ------------------------------
SAM                SYSTEM

SQL> create tablespace sam_ts datafile '/data01/RNDDB/sam_ts.dbf' size 100m;

Tablespace created.

SQL> alter user sam default tablespace sam_ts quota unlimited on sam_ts;

User altered.

SQL> select username, default_tablespace from dba_users where username='SAM';

USERNAME       DEFAULT_TABLESPACE
---------------------------------------------
SAM            SAM_TS

SQL>
SQL> create tablespace sam_ts_part datafile '/data01/RNDDB/sam_ts_part.dbf' size 100m;

Tablespace created.

SQL>
SQL> alter user sam quota unlimited on sam_ts_part;

User altered.

SQL>
SQL> create tablespace sam_ts_idx datafile '/data01/RNDDB/sam_ts.idx.dbf' size 100m;

Tablespace created.

SQL>
SQL> alter user sam quota unlimited on sam_ts_idx;

User altered.



CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE sam_ts_part,
PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE sam_ts_part,
PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE sam_ts_part);


SQL> set lines 100
SQL>  desc employees
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPLOYEE_ID                                           NOT NULL NUMBER(4)
 LAST_NAME                                                      VARCHAR2(10)
 DEPARTMENT_ID                                                  NUMBER(2)


set lines 200 pages 300 ;
col segment_name for a25 ;
col owner for a10
col TABLE_NAME for a15
select OWNER,TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,PARTITIONING_KEY_COUNT,SUBPARTITIONING_TYPE from dba_part_tables where owner='SAM';

SQL> SQL> set lines 200 pages 300 ;
col segment_name for a25 ;
col owner for a10
col TABLE_NAME for a15
select OWNER,TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,PARTITIONING_KEY_COUNT,SUBPARTITIONING_TYPE from dba_part_tables where owner='SAM';SQL> SQL> SQL> SQL>

OWNER      TABLE_NAME      PARTITION PARTITION_COUNT PARTITIONING_KEY_COUNT SUBPARTIT
---------- --------------- --------- --------------- ---------------------- ---------
SAM        EMPLOYEES       RANGE                   3                      1 NONE



select segment_name,segment_type ,t.PARTITION_NAME,t.num_rows,t.high_value from dba_segments s, dba_tab_partitions t
where segment_name='EMPLOYEES' and owner='SAM'
and s.PARTITION_NAME=t.PARTITION_NAME
 order by 1
;

SQL> col TABLE_OWNER for a10

SQL> select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions where TABLE_OWNER='SAM';


TABLE_OWNE TABLE_NAME      PARTITION_NAME                    TABLESPACE_NAME
---------- --------------- --------------------------------- ------------------------------
SAM        EMPLOYEES       EMPLOYEES_PART1                   SAM_TS_PART
SAM        EMPLOYEES       EMPLOYEES_PART2                   SAM_TS_PART
SAM        EMPLOYEES       EMPLOYEES_PART3                   SAM_TS_PART

SQL> 
declare
v_no number :=1;
begin
delete employees;
for i in 1..10 loop
insert into employees values(v_no,'name...',v_no);
v_no := v_no+1;
end loop;
end;
/


SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,INDEX_type ,GLOBAL_STATS from user_indexes;

no rows selected

Note:- last_name column is not part of partitions and we have range partitions on "department_id" column 

Now I am going to create a LOCAL index.

============================================================================================================
create index idx_local_last_name on employees(last_name) local tablespace sam_ts_idx; ----------Local Non-Prefixed Index:
============================================================================================================

SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,INDEX_type ,GLOBAL_STATS from user_indexes;

INDEX_NAME                          TABLE_OWNE TABLE_NAME      INDEX_TYPE                  GLO
----------------------------------- ---------- --------------- --------------------------- ---
IDX_LOCAL_LAST_NAME                 SAM        EMPLOYEES       NORMAL                      NO





select locality from user_part_indexes where index_name='IDX_LOCAL_LAST_NAME';

SQL> select locality from user_part_indexes where index_name='IDX_LOCAL_LAST_NAME';

LOCALI
------
LOCAL

col index_name for a20
select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes where INDEX_NAME='IDX_LOCAL_LAST_NAME';



select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL_LAST_NAME';

SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL_LAST_NAME';

INDEX_NAME           PARTITION_NAME                    STATUS
-------------------- --------------------------------- --------
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART1                   USABLE
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART2                   USABLE
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART3                   USABLE

VIP :- 
1) When you create a local index on a partitioned table, it automatically creates index partitions as many as in the table partitions. 




2) Similarly when we add a new partition to the table, Oracle automatically adds one index partition to the index as well.

ALTER TABLE employees ADD PARTITION employees_part4 VALUES LESS THAN (40) TABLESPACE sam_ts_part;

SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL_LAST_NAME';

INDEX_NAME           PARTITION_NAME                    STATUS
-------------------- --------------------------------- --------
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART1                   USABLE
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART2                   USABLE
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART3                   USABLE
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART4                   USABLE


SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME                      NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART1                            9
EMPLOYEES_PART2                            1
EMPLOYEES_PART3                            0
EMPLOYEES_PART4                            0

SQL> select INDEX_NAME,PARTITION_NAME from user_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL_LAST_NAME';

INDEX_NAME           PARTITION_NAME
-------------------- ---------------------------------
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART1
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART2
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART3


select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL_LAST_NAME';

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL_LAST_NAME';

INDEX_NAME           PARTITION_NAME                    STATUS
-------------------- --------------------------------- --------
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART1                   USABLE
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART2                   USABLE
IDX_LOCAL_LAST_NAME  EMPLOYEES_PART3                   USABLE


create index idx_local_dept_id_part_col on employees(department_id) local tablespace sam_ts_idx;  ----->Local Prefixed Index:

SQL> col INDEX_NAME for a45
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL_DEPT_ID_PART_COL';
INDEX_NAME                                    PARTITION_NAME                    STATUS
--------------------------------------------- --------------------------------- --------
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART1                   USABLE
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART2                   USABLE
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART3                   USABLE



----------------------------------------------------------------------------
IDX_LOCAL_LAST_NAME ------------> Local Non Prefixed Index
IDX_LOCAL_DEPT_ID_PART_COL -----> Local Prefixed Index
----------------------------------------------------------------------------

select TABLE_NAME, INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS

SQL> select INDEX_NAME,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAME from USER_IND_PARTITIONS;

INDEX_NAME                                    PARTITION_NAME                    PARTITION_POSITION TABLESPACE_NAME
--------------------------------------------- --------------------------------- ------------------ ------------------------------
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART1                                    1 SAM_TS_IDX
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART2                                    2 SAM_TS_IDX
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART3                                    3 SAM_TS_IDX
IDX_LOCAL_LAST_NAME                           EMPLOYEES_PART1                                    1 SAM_TS_IDX
IDX_LOCAL_LAST_NAME                           EMPLOYEES_PART2                                    2 SAM_TS_IDX
IDX_LOCAL_LAST_NAME                           EMPLOYEES_PART3                                    3 SAM_TS_IDX

6 rows selected.

SQL> drop index IDX_LOCAL_LAST_NAME;

Index dropped.

SQL> select INDEX_NAME,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAME from USER_IND_PARTITIONS;

INDEX_NAME                                    PARTITION_NAME                    PARTITION_POSITION TABLESPACE_NAME
--------------------------------------------- --------------------------------- ------------------ ------------------------------
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART1                                    1 SAM_TS_IDX
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART2                                    2 SAM_TS_IDX
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART3                                    3 SAM_TS_IDX


select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='IDX_LOCAL_DEPT_ID_PART_COL';


SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='IDX_LOCAL_DEPT_ID_PART_COL';

INDEX_NAME                                    INDEX_TYPE                  GLO
--------------------------------------------- --------------------------- ---
IDX_LOCAL_DEPT_ID_PART_COL                    NORMAL                      NO


=====================================================================================================================


2. What is global index?

Global index used in OLTP environments and offer efficient access to any individual record. 
We have two types of Global index. 
They are Global Non-partitioned index and Global partitioned index. 

Global Non-partitioned indexes behave just like a non-partitioned index.

Global partitioned index partition key is independent of Table partition key. 
The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. 

If you want to add new partition, always, you need to split the MAX partition. 
If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. 
If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. 
You cannot drop the highest partition in a global index.

Example of Global Non-partitioned index.

SQL> CREATE INDEX employees_global_idx ON employees(employee_id) tablespace SAM_TS_IDX;


SQL> select INDEX_NAME,INDEX_type,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';

INDEX_NAME                                    INDEX_TYPE                  GLO
--------------------------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_IDX                          NORMAL                      YES

SQL>

Example of Global Partitioned index.
*****************************************

CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(3),
PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN(11),
PARTITION p4 VALUES LESS THAN(20),
PARTITION p5 VALUES LESS THAN(MAXVALUE));


SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                                    INDEX_TYPE                  GLO
--------------------------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_PART_IDX                     NORMAL                      NO

SQL> select INDEX_NAME,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAME from USER_IND_PARTITIONS;

INDEX_NAME                                    PARTITION_NAME                    PARTITION_POSITION TABLESPACE_NAME
--------------------------------------------- --------------------------------- ------------------ ------------------------------
EMPLOYEES_GLOBAL_PART_IDX                     P1                                                 1 SAM_TS
EMPLOYEES_GLOBAL_PART_IDX                     P2                                                 2 SAM_TS
EMPLOYEES_GLOBAL_PART_IDX                     P3                                                 3 SAM_TS
EMPLOYEES_GLOBAL_PART_IDX                     P4                                                 4 SAM_TS
EMPLOYEES_GLOBAL_PART_IDX                     P5                                                 5 SAM_TS
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART1                                    1 SAM_TS_IDX
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART2                                    2 SAM_TS_IDX
IDX_LOCAL_DEPT_ID_PART_COL                    EMPLOYEES_PART3                                    3 SAM_TS_IDX

8 rows selected.

SQL> select INDEX_NAME,PARTITION_NAME from user_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                                    PARTITION_NAME
--------------------------------------------- ---------------------------------
EMPLOYEES_GLOBAL_PART_IDX                     P1
EMPLOYEES_GLOBAL_PART_IDX                     P2
EMPLOYEES_GLOBAL_PART_IDX                     P3
EMPLOYEES_GLOBAL_PART_IDX                     P4
EMPLOYEES_GLOBAL_PART_IDX                     P5


SQL>
SQL> ALTER index employees_global_part_idx drop partition P5;
ALTER index employees_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 employees_global_part_idx drop partition P4;

Index altered.

SQL>
SQL>
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

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

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

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

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

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


Now we will drop the partition P3 and see status. When we drop this partition, 
it should invalidate the next highest partition. Here, P5 is next highest partition.

SQL>  ALTER index employees_global_part_idx drop partition P3;

Index altered.

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                                    PARTITION_NAME                    STATUS
--------------------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX                     P1                                USABLE
EMPLOYEES_GLOBAL_PART_IDX                     P2                                USABLE
EMPLOYEES_GLOBAL_PART_IDX                     P5                                UNUSABLE


SQL>
SQL> alter index employees_global_part_idx rebuild partition p5;

Index altered.

SQL>  select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

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

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

100 Oracle DBA Interview Questions and Answers

  Here are 100 tricky interview questions tailored for a Senior Oracle DBA role. These questions span a wide range of topics, including perf...