Indexing operation - Features,Properties - Database

Discussion

• Nirja Shah   -Posted on 05 Nov 15
  - When using indexes in an application, you might need to request that the DBA grant privileges or make changes to initialization parameters.
  
  - To create a new index, you must own, or have the INDEX object privilege for, the corresponding table.
  
  - The schema that contains the index must also have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege.
  
  - To create an index in another user's schema, you must have the CREATE ANY INDEX system privilege.
  
  - You can create indexes on columns to speed up queries.
  
  - Indexes provide faster access to data for operations that return a small portion of a table's rows.
  
  - In general, create an index on a column in any of the following situations:
  
  1. The column is queried frequently.
  2. A referential constraint exists on the column.
  3. A UNIQUE key constraint exists on the column.
  
  - You can create an index on any column; however, if the column is not used in any of these situations, creating an index on the column does not increase performance and the index takes up resources unnecessarily.
  
  - Although the database creates an index for you on a column with a constraint, explicitly creating an index on such a column is recommended.
  
  - You can use the following techniques to determine which columns are best candidates for indexing:
  
  1. Use the EXPLAIN PLAN feature to show a theoretical execution plan of a given query statement.
  
  2. Use the dynamic performance view V$SQL_PLAN to determine the actual execution plan used for a given query statement.
  
  - Sometimes, if an index is not being used by default and it would be more efficient to use that index, you can use a query hint so that the index is used.

➨ Post your comment / Share knowledge