Oracle indexes - create, rebuild, select, drop

John D K -

Select all indexes by status

Check indexes by status by two queries from metadata of Oracle. Depending on your needs and rights you can use them:

  • all_indexes - this will group and count all indexes by status
  • user_objects - this will show you all indexes and their information. You can filter by status.
  • dba_indexes - third query is preparing script for dropping all indexes for a particular owner
--select all indexes grouped by status
SELECT 
 status, 
COUNT(*) 
FROM   all_indexes 
GROUP BY status

--view all invalid indexes
select * 
from user_objects
where object_type = 'INDEX'
and status ='INVALID'--and status ='VALID' 

--prepared script for dropping all indexes for MYOWNER
SELECT 'drop index MYOWNER.' || index_name || ';'
FROM dba_indexes
WHERE table_owner = 'MYOWNER';

Index rebuild

In oracle indexes rebuilding is fairly an easy operation. It depends on the type of the index:

  • normal index rebuild
alter index index_name rebuild;
  • normal index rebuild has this syntax:
alter index <index_name> rebuild partition <partition_name>;

Information about the partition can be taken from the table DDL using tools like SQL Developer.

alter index index_name rebuild partition partition1;
alter index index_name rebuild partition partition2;
alter index index_name rebuild partition partition3;
...
alter index index_name rebuild partition partition64;

Script collecting all needed subpartitions

SELECT 'ALTER INDEX '||index_owner||'.'|| INDEX_NAME ||' rebuild SUBPARTITION ' || SUBPARTITION_NAME ||'  ONLINE PARALLEL;'
FROM dba_ind_subpartitions
WHERE INDEX_NAME = 'INDEX_NAME';

Script collecting all needed partitions

SELECT 'ALTER INDEX '||'MYOWNER'||'.'|| 'INDEX_NAME' ||' rebuild PARTITION ' || PARTITION_NAME ||'  ONLINE PARALLEL;'  
FROM all_ind_partitions  
WHERE INDEX_NAME = 'INDEX_NAME'
and INDEX_OWNER = 'MYOWNER'; 

SELECT 'ALTER INDEX '||'MYOWNER'||'.'|| 'INDEX_NAME' ||' rebuild PARTITION ' || PARTITION_NAME ||'  ONLINE PARALLEL;'  
FROM dba_ind_partitions  
WHERE INDEX_NAME = 'INDEX_NAME'

Alter Index Offline

In oracle you may need to make the index unusable or to drop it prior to rebuild it:

  • make index unusable
alter index index_name unusable;
  • drop index
drop index index_name

Create index

After dropping an index or if you need a new one you can create it by:

-- create unique index
CREATE  UNIQUE INDEX INDEX_NAME ON "TABLE" ("COLUMN")  ONLINE;

-- create online index 
CREATE  INDEX "INDEX_NAME" ON "TABLE" ("COLUMN", "COLUMN2")  ONLINE;

--create index parallel 
create index "MYOWNER"."MYTABLE$IND" on "MYOWNER"."MYTABLE" (column1,column2,column3) tablespace ts_indexes local parallel 12 online;


--create partioned global
create index "MYOWNER"."MYTABLE$IND" on "MYOWNER"."MYTABLE"  (column_date) global;

--create partioned local
create index "MYOWNER"."MYTABLE$IND" on "MYOWNER"."MYTABLE"  (column_date) local;

--create partion index by hash
CREATE INDEX INDEX_NAME ON "MYOWNER"."MYTABLE" (column1,column2,column3) GLOBAL
     PARTITION BY HASH (column1,column2)
     (PARTITION p1  TABLESPACE tbs_1,
      PARTITION p2  TABLESPACE tbs_2,
      PARTITION p3  TABLESPACE tbs_3,
      PARTITION p4  TABLESPACE tbs_4);
      
      
--create index by range
CREATE INDEX "MYOWNER"."MYTABLE$IND" ON "MYOWNER"."MYTABLE" ("COLUMN1","COLUMN1") 
GLOBAL PARTITION by range (COLUMN1)
    (
    PARTITION r1 VALUES LESS THAN (TO_DATE('1-JAN-1999','DD-MON-YYYY')),
    PARTITION r2 VALUES LESS THAN (TO_DATE('1-JAN-2000','DD-MON-YYYY')),
    PARTITION r3 VALUES LESS THAN (TO_DATE('1-JAN-2001','DD-MON-YYYY')),
    ...
    );

Check index usage

Explain plan

If you wonder whether the index is used and what is the performance you can check execute plan for a given query by :

explain plan for select * from mytable;
select * from table(dbms_xplan.display);
  • Run first query and replace select * from mytable with your query
  • wait for explained to be shown
  • run select * from table(dbms_xplan.display); in order to get the result
  • result would be something like:
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    111|    444|     5 |
|   1 |  TABLE ACCESS FULL   | MYTABLE     |    111|    444|     5 |

SQL Tunning Advisor

You can use also SQL Tunning Advisor which is available after version 3.0 of SQL Developer(it requires also Oracle Database 11g Enterprise Edition with access to the Tuning and Diagnostic management packs and with the sample schema installed). It can be executed by F10 while you selected your query or by right click on it and select SQL Tunning Advisor.

It will give you information for the several execution plans, recommendations for new indexes or query enhancements:

  • findings on SQL profiles
  • gathering statistics
  • useful indexes
  • rewrites query

The output would be:

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : 11111
Tuning Task Owner  : MyOwner
Workload Type      : Single SQL Statement


-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------

2- Index Finding (see explain plans section below)
--------------------------------------------------

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------

2- Original With Adjusted Cost
------------------------------

3- Using New Indices
--------------------

4- Using SQL Profile
--------------------

Errors related to indexes

For some queries on Oracle tables with select SQL statement, Oracle can return the following error:

ORA-01502: index ‘string.string’ or partition of such index is in unusable state

The error indicates an attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation.

The solution would be:

1.Drop the specified index and/or recreate the index
2.Rebuild the specified index
3.Rebuild the unusable index partition

Generally, the following SQL manipulation language will be enough to rebuild the unusable index:

  • index rebuild
ALTER INDEX index_name REBUILD