INDEXES ------1) General thumb rule for indexing is that you create an index if you anticipate frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the where clause. Note- More the Index, more is the time for update,insert and deletions. Examples --------CREATE INDEX i_emp_ename ON emp (ename); DROP INDEX index_name SELECT index_name From user_indexes Where table_name = ‘EMP’; Note ---1) Consider indexing columns that are used frequently in WHERE clauses. 2) An index’s selectivity is good if few rows have the same value. 3) Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints. These indexes are the most selective and the most effective in optimizing performance. 4) ANALYZE command is used to check the index use 5) Do not index columns that are frequently modified. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables.They also generate additional undo and redo information. 6)Do not index columns that appear only in WHERE clauses with functions or operators. A WHERE clause that uses a function (other than MIN or MAX) or an operator with an indexed column does not make available the access path that uses the index. 7) Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. With UPDATE and DELETE, such an index allows Oracle to modify data in the child table without locking the parent table. 8) If all the columns in the index are used in the WHERE Clause, they should be used in the order they are indexed. 9) Use >= and <= instead of BETWEEN operator. 10) Avoiding the use of IN operator.Use 'EXISTS' instead of 'IN'
11) Avoid the use of functions like TRUNC( ) , TO_DATE( ), TO_CHAR( ) etc. on the columns in WHERE Clause.Even if the column is indexed, index is never used if functions are used. 12) Create a separate tablespace for the indices and place the datafile of this tablespace on a different drive other than the one where Oracle data files are placed. This improves the performance drastically. 13) Use COUNT(ROWID) instead of COUNT(*), wherever possible. 14) Use of <= instead of < 15) CREATE UNIQUE INDEX I_emp_empno ON emp(empno); 16) Not More than 64 columns in indexes 17) user_indexes,user_ind_columns 18) It is advisable to place index in Different tablespace on different drive.In that case oracle can retrieve data parallely. Note If insert or delete or update is made of indexed key then the index is dropped and it is rebuilt and then table is again sorted