Indexes

Create

B-Tree (Default)

CREATE INDEX [schema.]<NewIndexName>
ON [schema.]TableName (col1, col2 ...)
TABLESPACE TableSpaceName;

CREATE INDEX hr.emp_hiredate_ndx ON hr.emp (hire_date) TABLESPACE hr;

Bitmap

CREATE BITMAP INDEX [schema.]<NewIndexName>
ON [schema.]Tablename (col1, col2 ...)
TABLESPACE TableSpaceName;

CREATE BITMAP INDEX hr.emp_dept_ndx ON hr.emp (dept) TABLESPACE hr;

Display

-- Indexes for a Schema

set line size 250
COL tablespace_name FORMAT a15
COL table_name FORMAT a35
COL owner      FORMAT a20
COL index_name FORMAT a30
SELECT owner,tablespace_name,table_name,index_name FROM dba_indexes 
WHERE owner = 'SCOTT' 
ORDER BY owner,tablespace_name,table_name,index_name;

-- Indexes for a Table

SELECT owner,table_name,index_name,tablespace_name
FROM dba_indexes
WHERE table_name = 'EMP'; 

-- Invalid Indexes

SELECT index_name, index_type, status
FROM dba_indexes
WHERE  status != 'VALID'
AND    owner   = 'SCOTT'; 

-- Parallel Value > 1 [default]

COL owner      FORMAT a20
COL index_name FORMAT a30
COL degree     FORMAT a6

SELECT owner,index_name,degree 
FROM dba_indexes 
WHERE to_number(degree) > 1
AND   owner NOT IN('APEX_040200','AUDSYS','CTXSYS','SYS','SYSTEM','WMSYS','XDB') 
ORDER BY owner,index_name;

To Reset Parallel Back to 1
ALTER INDEX SCOTT.MY_IDX PARALLEL 1;

-- Check If Index Needs to Be Rebuilt
This is derived from Oracle's: How to Determine When an Index Should be Rebuilt? (Doc ID 1373415.1).

PROMPT -- BLEVEL Check
SELECT owner, index_name, table_name, blevel 
FROM dba_indexes 
WHERE BLEVEL > 3;

PROMPT -- Ratio Check
SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio 
FROM INDEX_STATS;

-- Tablespace Index is In

SELECT owner,table_name,index_name,tablespace_name
FROM dba_indexes
WHERE tablespace_name != 'USERS'
AND   owner            = 'SCOTT';

Drop

DROP INDEX [schema.]<NewIndexName>;

DROP INDEX hr.emp_dept_ndx;

Move/Rebuild

Indexes are moved by rebuilding in them in the desired tablespace.

Standard Index

1. Determine index to move. SELECT index_name FROM dba_indexes WHERE table_name='&TABLE_NAME';
2. Move/Rebuild

  ALTER INDEX [schema.]<NewIndexName> 
  REBUILD PARALLEL ONLINE TABLESPACE <TablespaceName>;

Index Partition

1. Determine index to move.
   SELECT index_name FROM dba_indexes WHERE table_name='&TABLE_NAME';
2. Determine index partition names.

   SELECT index_name,partition_name,tablespace_name 
   FROM dba_ind_partitions WHERE index_name = '&INDEX_NAME';

3. Move
   ALTER INDEX index_name REBUILD PARTITION TABLESPACE NOLOGGING;

<- Schema