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>;

Note: Using PARALLEL in the rebuild command will only temporarily increase parallelism for that specific operation without affecting your permanent index PARALLEL settings.

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