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.
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.
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
sqlCREATE 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:
sqlCREATE 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_Datecolumn, 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:
sqlCREATE 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_Namecolumn, which is different from theSale_Datecolumn used to partition the table.
Key Differences:
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.
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:
sqlSELECT * FROM Sales WHERE Sale_Date = TO_DATE('2023-05-15', 'YYYY-MM-DD');Oracle will directly go to the
sales_2023partition and use thesales_local_prefixed_idxindex.Local Non-Prefixed Index: If you search for sales by a customer:
sqlSELECT * FROM Sales WHERE Customer_Name = 'John Doe';Oracle will still use the index on
Customer_Namewithin 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:
- 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.
- 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
sqlCREATE 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:
sqlCREATE INDEX sales_global_idx
ON Sales (Customer_Name);
In this case:
- The index is global, meaning it covers the entire
Salestable 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:
sqlCREATE 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_Namecolumn, 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:
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.
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:
sqlSELECT * FROM Sales WHERE Customer_Name = 'John Doe';Oracle will use the
sales_global_idxto search the entire table, regardless of partitions.Global Partitioned Index: If you search for a customer:
sqlSELECT * FROM Sales WHERE Customer_Name = 'John Doe';Oracle will use the
sales_global_part_idxindex but can focus on a specific partition of the index based on the hash ofCustomer_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 Non-prefixed index.
============================================================================================================
SQL> 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 non-prefixed 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.
SQL> 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
Create the Local Prefixed Index:-
SQL> create index idx_local_dept_id_part_col on employees(department_id) local tablespace sam_ts_idx; ----->Local Prefixed Index: ( it means creating the index on Partitioned column, here partitioned are created on "department_id" column
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.
Let's drop the Local Non Prefixed index and see the impact on the table and it's partitions
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
No comments:
Post a Comment