Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

ALTER INDEX Remove or Add column in Oracle

Leave a Comment / SQL, INDEX / By

ALTER INDEX Remove or Add column in Oracle

ALTER INDEX features for developers:

Developers can use ALTER INDEX command for the following operations:
  • RENAME Index Name
  • Disabling Index
  • Enabling Index
  • Creating UNUASABLE Index
  • Making Index INVISIBLE
  • Making Index VISIBLE

RENAME Index Name with ALTER INDEX:

Creating Setup for Index:
--ALTER INDEX Remove or Add column in Oracle: Setup

CREATE TABLE EMP_INDEX_TAB
(EMPLOYEE_ID        NUMBER(6),
 TXN_DATE           DATE,
 FIRST_NAME         VARCHAR2(30),
 LAST_NAME          VARCHAR2(30),
 JOB_ID             VARCHAR2(30),
 DEPARTMENT_ID      NUMBER(4)
);
CREATE INDEX EMP_INDEX_TAB_INDX ON EMP_INDEX_TAB(EMPLOYEE_ID, TXN_DATE);
Querying the data dictionary:
--ALTER INDEX Remove or Add column in Oracle: Setup

SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX_TAB_INDX';
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX_TAB_INDX';
ALTER INDEX EMP_INDEX_TAB_INDX RENAME TO EMP_INDEX;

Now we will query the data dictionary for old index name EMP_INDEX_TAB_INDX.

We can check that no rows is selected.

SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX_TAB_INDX';

Now data dictionary will be visible for new Index name EMP_INDEX.

SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX';

DISABLE Index with ALTER INDEX:

  • Disable Index is only applicable to function based indexes.
  • This clause allows us to disable the use of function based index.
  • Disabling an index means the index is still exists in data base. But optimizer will ignore the index.
  • Function are usually either system defined or user defined
  • For user defined function DETERMINISTIC clause is mandatory. Other wise Oracle will not create the index.
Creating setup for Function Based Index:
Index based on System defined function:
CREATE INDEX UPPER_FNAME_NAME_INDX ON EMP_INDEX_TAB(UPPER(FIRST_NAME));
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'UPPER_FNAME_NAME_INDX';
ALTER INDEX UPPER_FNAME_NAME_INDX DISABLE;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'UPPER_FNAME_NAME_INDX';
Index based on User defined Function:
Creating the user defined deterministic function:
--ALTER INDEX Remove or Add column in Oracle: Example

CREATE OR REPLACE FUNCTION FUN_GET_FIRST_NAME(P_EMPLOYEE_ID NUMBER)
RETURN VARCHAR2 DETERMINISTIC
IS
  V_FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
  SELECT FIRST_NAME
  INTO V_FIRST_NAME
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;
  --
  RETURN V_FIRST_NAME;
EXCEPTION
  WHEN OTHERS THEN
    V_FIRST_NAME := NULL;
    RETURN V_FIRST_NAME;
END;
/
CREATE INDEX FIRST_NAME_INDX ON EMP_INDEX_TAB(FUN_GET_FIRST_NAME(EMPLOYEE_ID));
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'FIRST_NAME_INDX';
ALTER INDEX FIRST_NAME_INDX DISABLE;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'FIRST_NAME_INDX';

ENABLE Index with ALTER INDEX:

  • Enable option allows us to enable a function based index only
  • This option will enable a forcefully disabled function based index
  • Or it helps us to enable a function based index where the function is dropped or modified
  • Before enabling the index following criteria should meet:
    • The function should currently be exists in data base and in compiled state
    • Signature of the function should meet the function signature during index creation
    • The function should be a deterministic function

Enabling a System defined function based index:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'UPPER_FNAME_NAME_INDX';
ALTER INDEX UPPER_FNAME_NAME_INDX ENABLE;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'UPPER_FNAME_NAME_INDX';

Enabling an User defined function based index:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'FIRST_NAME_INDX';
ALTER INDEX FIRST_NAME_INDX ENABLE;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'FIRST_NAME_INDX';

Now lets dropped the user defined function to see the impact.

DROP FUNCTION FUN_GET_FIRST_NAME;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'FIRST_NAME_INDX';

So we can see the index FIRST_NAME_INDX is now in disabled status.

To enable this index we have recreate the user defined function FUN_GET_FIRST_NAME. 

--ALTER INDEX Remove or Add column in Oracle: Example

CREATE OR REPLACE FUNCTION FUN_GET_FIRST_NAME(P_EMPLOYEE_ID NUMBER)
RETURN VARCHAR2 DETERMINISTIC
IS
  V_FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
  SELECT FIRST_NAME
  INTO V_FIRST_NAME
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;
  --
  RETURN V_FIRST_NAME;
EXCEPTION
  WHEN OTHERS THEN
    V_FIRST_NAME := NULL;
    RETURN V_FIRST_NAME;
END;
/

Now lets query the data dictionary to check the status of the index.

SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'FIRST_NAME_INDX';

So the data dictionary is showing the index FIRST_NAME_INDX still in disabled status.

To enable the index we have to execute the following command:

ALTER INDEX FIRST_NAME_INDX REBUILD;

Or you can use the following command as well:

ALTER INDEX FIRST_NAME_INDX ENABLE;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'FIRST_NAME_INDX';

UNUSABLE Index with ALTER INDEX:

When a PLSQL procedure or Function uncompiled for any reason then Oracle recompiled them in first access. They do not require any manual recompilation. After first access, most of the time they become in VALID state.

But for an index this theory does not work. Indexes are not automatically move to USABLE state once they are in UNUSABLE state. They always require manual intervention. The reason behind this is Index is associated with ROWIDs of the table based on index key values sorted in specific order. ROWID itself is the pointer to the physical location that index key refers. For any reason if ROWIDs of the table change then Index will become UNUSABLE. There could be various reasons for which ROWIDs of the table can change. Few of the most common reasons are:

  • SQL Loader Utility
  • Oracle Imports Utility
  • ALTER TABLE..MOVE command
  • Table Partition Maintenance
SQL Loader Utility:

Using Direct Path Load with Option SKIP_INDEX_MAINTENANCE as TRUE will cause the Index to be INVALID and UNUSABLE.

Oracle Imports Utility:

An Oracle Import utility with Parameter SKIP_UNUSABLE_INDEX as Y make the Index UNUSABLE.

ALTER TABLE..MOVE command:

This command will cause the physical placement of all the rows. So Index entries will point to wrong rows. This will cause the Index to be UNUSABLE.

Table Partition Maintenance:

Partition operation on Table like MOVE, SPLIT, TRUNCATE PARTITION etc. will cause the shift of ROWIDs. So the Index will become UNUSABLE.

To repair the Index we have to use ALTER INDEX..REBUILD command manually.

INVISIBLE Index with ALTER INDEX:

  • From 11g onwards, oracle allows us to make an index as INVISIBLE.
  • INVISIBLE indexes are exist in data base but it is ignored by the optimizer
  • Ignorance of optimizer can be overwritten by the parameter OPTIMIZER_USE_INVISIBLE_INDEXES. This parameter can be set at instance level or session level. Its value will be TRUE.
  • Index can be created with INVISIBLE status. Later they can be made VISIBLE with ALTER INDEX command.
  • Index can be made INVISIBLE with ALTER INDEX command
Uses of INVISIBLE Indexes:
  • INVISIBLE indexes are useful in a situation where creating the index would adversely affect some functional areas. Then we can use their invisible feature with the help of parameter OPTIMIZER_USE_INVISIBLE_INDEXES as and when required.
  • They are also useful to test the impact of an index without dropping and recreating it. But this is not good for Production environment but for Development environment.
Setup for INVISIBLE Index:
--ALTER INDEX Remove or Add column in Oracle: INVISIBLE Index

CREATE TABLE EMP_INDEX_TAB
(EMPLOYEE_ID        NUMBER(6),
 TXN_DATE           DATE,
 FIRST_NAME         VARCHAR2(30),
 LAST_NAME          VARCHAR2(30),
 JOB_ID             VARCHAR2(30),
 DEPARTMENT_ID      NUMBER(4)
);

CREATE INDEX EMP_INDEX_TAB_INDX ON EMP_INDEX_TAB(EMPLOYEE_ID, TXN_DATE) INVISIBLE;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX_TAB_INDX';
DROP INDEX EMP_INDEX_TAB_INDX;
CREATE INDEX EMP_INDEX_TAB_INDX ON EMP_INDEX_TAB(EMPLOYEE_ID, TXN_DATE);
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX_TAB_INDX';

Now we can make this index EMP_INDEX_TAB_INDX as INVISIBLE by using INVISIBLE command.

ALTER INDEX EMP_INDEX_TAB_INDX INVISIBLE;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX_TAB_INDX';

VISIBLE Index with ALTER INDEX:

Index is created by default as VISIBLE.

But we can also make the INVISIBLE index as VISIBLE by using ALTER INDEX command.

DROP INDEX EMP_INDEX_TAB_INDX;
CREATE INDEX EMP_INDEX_TAB_INDX ON EMP_INDEX_TAB(EMPLOYEE_ID, TXN_DATE);
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX_TAB_INDX';

Making the index as INVISIBLE.

ALTER INDEX EMP_INDEX_TAB_INDX INVISIBLE;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX_TAB_INDX';

Making the Index as VISIBLE.

ALTER INDEX EMP_INDEX_TAB_INDX VISIBLE;
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, STATUS, PARTITIONED, GENERATED, GLOBAL_STATS, FUNCIDX_STATUS, VISIBILITY, INDEXING 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_INDEX_TAB_INDX';

ALTER INDEX Remove or Add column:

  • We cannot ALTER INDEX to add or remove column in the index definition.
  • We have to DROP the index and then recreate the same
  • Or we can use the VISIBLE or INVISIBLE feature for an index. In this case we will create our desired index. Then at a later time, we can make it INVISIBLE when the requirement is over. In this way we can reduce the load of dropping and recreating an index.
DROP INDEX EMP_INDEX_TAB_INDX;
CREATE INDEX EMP_INDEX_TAB_INDX ON EMP_INDEX_TAB(EMPLOYEE_ID, TXN_DATE);
ALTER INDEX EMP_INDEX_TAB_INDX ADD COLUMN(JOB_ID);
ALTER INDEX Remove or Add column in Oracle: Error Output
ALTER INDEX Remove or Add column in Oracle: Error Output
ALTER INDEX EMP_INDEX_TAB_INDX ON EMP_INDEX_TAB ADD COLUMN(JOB_ID);
ALTER INDEX Remove or Add column in Oracle: Error Output
ALTER INDEX EMP_INDEX_TAB_INDX ON EMP_INDEX_TAB DROP COLUMN(TXN_DATE);
ALTER INDEX Remove or Add column in Oracle: Error Output

So oracle is not allowing to ADD or DROP column to or from an index. So we have to drop it and then recreating it.

DROP INDEX EMP_INDEX_TAB_INDX;
CREATE INDEX EMP_INDEX_TAB_INDX ON EMP_INDEX_TAB(EMPLOYEE_ID, TXN_DATE, JOB_ID);

Using VISIBLE/INVISIBLE feature:

DROP INDEX EMP_INDEX_TAB_INDX;
CREATE INDEX EMP_INDEX_TAB_INDX ON EMP_INDEX_TAB(EMPLOYEE_ID, TXN_DATE);

Now, if we want to add one more column, then we will create an another index. When our requirement is over then we will mark that as INVISIBLE.

CREATE INDEX EMP_INDEX_TAB_INDX_1 ON EMP_INDEX_TAB(EMPLOYEE_ID, TXN_DATE, JOB_ID);
ALTER INDEX EMP_INDEX_TAB_INDX_1 INVISIBLE;

Author

Leave a Comment

Your email address will not be published.