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