Indexing in Oracle database 11g

"Indexes in Oracle not only improve performance but also help to enforce primary and unique key constraints. in addition Oracle can manage table locking issues when indexes are placed on FK's columns."  Oracle 11g provides various types of indexes. we will explain each type of indexes in a separate discussions.

B-tree | IOT | Unique | Reverse Key | Key Compressed | Descending | Bitmap | Bitmap Join | Function-based | Indexed virtual column | Virtual | Invisible | Global Partitioned | Local Partitioned | Domain | B-tree cluster | Hash Cluster
Generating DDL for creating an index:
1, Using the DBMS_METADATA package SQL> select DBMS_METADATA.get_ddl('INDEX','EMP_NAME_IDX') from dual;
Note: get_ddl procedure of the DBMS_METADATA package provides a quick way to generate the DDL for recreating both indexes as well as tables. if you want to create DDL for all indexes in a schema execute  SQL> select DBMS_METADATA.get_ddl('INDEX',di.index_name) from dba_indexes di where owner='DBAREF';
To format the output nicely formatted
SQL>set linesize 80
SQL>column xyz format a100 word_wrapped
SQL>column x format a200 word_wrapped
You can also use SESSION_TRANSFORM procedure to customize the output generated by get_ddl procedure. If you want to restrict the object to be retrieved by DBMS_METADATA use SET_FILTER procedure.
2. Using Data Pump a) execute  $ expdp content=metadata_only $impdp sqlfile=mydumpfile (the dump file generated in step a).
Collecting STATS for indexes: You can specify CASCADE=>true option with the GATHER_DATABASE_STATS, GATHER_SCHEMA_STATS, GATHER_TABLE_STATS procedure to collect the STATS for indexes along with the tables.
SQL> execute dbma_stats.gather_schema_stats('DBAREF', cascade=>TRUE);
SQL> execute dbma_stats.gather_table_stats(ownname=>'DBAREF', tabname=>'EMPLOYEE',cascade=>TRUE);
SQL> execute dbma_stats.gather_index_stats('DBAREF','EMPLOYEE_PK');
SQL> execute dbma_stats.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);
==============================================================================
you can also set the default preference to collect the index statistics automatically when collecting table statistics.
SQL>exec dbms_stats.set_database_prefs('CASCADE','TRUE');

==============================================================================
Rebuilding  UNUSABLE indexes:
 SET SERVEROUTPUT ON FEEDBACK OFF
DECLARE
  CURSOR idx_rbld IS
    WITH scaler1 AS (SELECT object_name FROM user_recyclebin)
    SELECT 'ALTER INDEX '||index_name||' REBUILD ONLINE' rbld_stmt
    FROM user_indexes
    WHERE status='UNUSABLE'
    AND index_name NOT IN(SELECT object_name FROM scaler1)
    UNION ALL
    SELECT 'ALTER INDEX '||index_name||' REBUILD PARTITION '||partition_name||' ONLINE' rbld_stmt
    FROM user_ind_partitions
    WHERE status='UNUSABLE'
    AND index_name NOT IN(SELECT object_name FROM scaler1)
    AND partition_name NOT IN(SELECT object_name FROM scaler1)
    UNION ALL
    SELECT 'ALTER INDEX '||index_name||' REBUILD SUBPARTITION '||subpartition_name||' ONLINE' rbld_stmt
    FROM user_ind_subpartitions
    WHERE status='UNUSABLE'
    AND index_name NOT IN(SELECT object_name FROM scaler1)
    AND subpartition_name NOT IN(SELECT object_name FROM scaler1);
BEGIN
  FOR i IN idx_rbld
  LOOP
    BEGIN
      -- DBMS_OUTPUT.PUT_LINE(i.rbld_stmt);
      EXECUTE IMMEDIATE i.rbld_stmt;
    EXCEPTION
      WHEN others THEN
        DBMS_OUTPUT.PUT_LINE(i.rbld_stmt);
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
  END LOOP;
EXCEPTION
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/