Indexing operation - Features,Properties - Database

Q.  Which of the following is true regarding indexing operation?
- Published on 27 Aug 15

a. Established an index for a file
b. Sorts a file using a single key
c. Sorts a file using two keys
d. Both B and C

ANSWER: Established an index for a file
 

    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


Enter the code shown above:
 
(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)