Indexes Creation

views to check 

• dba_indexes
• dba_tables
• dba_tablespaces

Check the details before creating the index creation statement.

select OWNER,INDEX_NAME,TABLESPACE_NAME from dba_indexes where TABLE_NAME='S_AUDIT_ITEM'

Creating Index :

Syntax:

create index <index_name> on owner.table_name (column on which index to be created) tablespace <tablespace_name>

CREATE INDEX S_AUDIT_ITEM_M5 ON SIEBEL.S_AUDIT_ITEM(GROUP_NUM) TABLESPACE S_INDEXES;

Rebuilding Indexes for performance.

select INDEX_NAME,INDEX_TYPE,STATUS from dba_indexes where status='UNUSABLE';

StatusUNUSABLE  can cause a performance issue.

select INDEX_NAME,INDEX_TYPE,STATUS from dba_indexes where status='UNUSABLE' and owner='SAPSR3';

Use the below command to alter the indexes online

alter index SAPBOS."SRRELROLES~002" rebuild online