Term
| What is the purpose of an index? |
|
Definition
| To reduce the necessity of disk I/O (input/output) by using an indexed path to locate data quickly. |
|
|
Term
| When is the index used and maintained? |
|
Definition
| Automatically by the Oracle server. No direct activity is required by the user once the index has been created. |
|
|
Term
| What is the relationship between the index and the table it indexes? |
|
Definition
| They are logically and physically independent. This means they can be created or dropped at any time and will have no effect on the base tables or other indexes. |
|
|
Term
| What happens when you drop a table? |
|
Definition
| The corresponding indexes are also dropped. |
|
|
Term
| What are the two types of indexes? |
|
Definition
| Unique index and nonunique index. |
|
|
Term
|
Definition
| The Oracle Server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE KEY constraint. |
|
|
Term
| What name is automatically given to a unique index? |
|
Definition
| The name given to the constraint. |
|
|
Term
| What is a nonunique index? |
|
Definition
| An index the user can create to speed up access to the rows. |
|
|
Term
| Who is usually responsible for creating and maintaining an index? |
|
Definition
| The database administrator. |
|
|
Term
| For a SELECT statement to use an INDEX what must it clause must it contain? |
|
Definition
| The WHERE CLAUSE that keys on the index column where the index column is not part of a function (like uppercasing, etc) |
|
|
Term
|
Definition
| A hexadecimal string representation of the row address contain the block identifier, row location in the block, and database file identifier. |
|
|
Term
| What is the fastest way to access any particular row? |
|
Definition
|
|
Term
| What is the syntax for creating an index? |
|
Definition
CREATE INDEX indexname
ON table-name(column,..,column) |
|
|
Term
| What privilege must you have to create an index in your schema? |
|
Definition
|
|
Term
| What privilege must you have to create an index in any schema? |
|
Definition
| You need the CREATE ANY INDEX privilege. |
|
|
Term
| What conditions should be met before creating an INDEX? |
|
Definition
1. The column contains a wide range of values.
2. A column contains a large number of null values
3. One or more columns are frequently used together in a WHERE clause or a join condition
4. The table is large and most queries are expected to retrieve less than 2-4% of the rows. |
|
|
Term
| When should you not create an INDEX? |
|
Definition
1. The table is small
2. The columns are not often used as a condition in the query.
3. Most queries are expected to retrieve more than 2-4% of the rows in the table.
4. The table is updated frequently.
5. The indexed columns are referenced as part of an expression. |
|
|
