Oracle Index Basics: Boosting Query Performance Quiz

Discover how Oracle indexes, including B-Tree, composite, and function-based types, optimize database queries. Learn key principles for efficient index strategies and common pitfalls developers face.

1. Index Fundamentals

   What is the primary reason to create an index on a database table column?

   1. To ensure columns are always unique
   2. To automatically back up data
   3. To improve query performance by speeding up data retrieval
   4. To decrease storage space requirements

   Explanation: The main purpose of an index is to help the database quickly locate rows, making queries much faster. Indexes do not make columns unique (unless a unique index is specifically created), do not perform backups, and usually increase rather than decrease storage space.

2. Full Table Scans

   What happens during a full table scan?

   1. Only the first matching row is read
   2. Only updated rows are scanned
   3. Every row of the table is read to find matching data
   4. Rows are read using an index

   Explanation: A full table scan requires the engine to read each row to locate desired data, which can be inefficient. Indexes, in contrast, allow targeted row retrieval. The other options either mischaracterize scanning or refer to unrelated query methods.

3. Index Types

   Which type of index is the default in Oracle databases?

   1. Hash index
   2. B-Tree index
   3. Bitmap index
   4. Clustered index

   Explanation: B-Tree indexes are the standard in Oracle, offering efficient sorted access. Bitmap and hash indexes are specialized types, and Oracle does not have clustered indexes as a default.

4. Index Structure

   What do the leaf blocks of a B-Tree index contain?

   1. Complete table rows
   2. Indexed column values and ROWIDs
   3. The query results
   4. SQL statements

   Explanation: Leaf blocks store the indexed values and their corresponding ROWIDs, which point to the row location. They do not hold entire rows, query results, or SQL code.

5. Composite Index Usage

   Why would a composite index be preferred over two single-column indexes for filtering by multiple columns?

   1. It allows efficient combined filtering and follows column order for optimal access
   2. It is always faster regardless of query structure
   3. It reduces table size to zero
   4. It decreases the number of table columns

   Explanation: A composite index speeds up queries using multiple columns by following their order. It doesn't change the number of columns, is not always faster in all query situations, and cannot reduce table size to zero.

6. Leading Edge Rule

   When using a composite index on (cust_id, sale_date), which WHERE clause can make effective use of the index?

   1. WHERE cust_id = 50 AND sale_date = '2023-10-01'
   2. WHERE amount > 5000
   3. WHERE notes = 'holiday'
   4. WHERE sale_date = '2023-10-01'

   Explanation: An index on (cust_id, sale_date) needs the leading column, cust_id, in the filter to be effective. Filtering only by sale_date ignores the leading edge, and filters on unrelated columns won't use this index.

7. Column Order Importance

   Why is the order of columns critical when creating a composite index?

   1. The index is sorted by the first column and followed by the next
   2. Indexes can only work on the last column specified
   3. The index size depends on the order
   4. Oracle ignores all but the first column

   Explanation: Ordering affects index efficiency as the B-Tree structure sorts data first by the initial column. Index size doesn't change based on column order alone, and all columns are part of the index unless ignored in filters.

8. Function-Based Indexes (FBI)

   What issue does a function-based index resolve in Oracle?

   1. It eliminates duplicate rows
   2. It allows queries with functions (like UPPER) on columns to benefit from indexing
   3. It prevents all table scans
   4. It forces unique constraints on columns

   Explanation: A function-based index stores the result of expressions like UPPER(column), enabling fast searches. It doesn't directly stop all table scans, remove duplicates, or enforce uniqueness unless combined with constraints.

9. Case-Insensitive Searches

   If a query searches for UPPER(cust_name) = 'ACME CORP', why does a standard index on cust_name not help?

   1. The standard index stores actual values, not computed function results
   2. Standard indexes only sort NULL values
   3. Indexes are dropped when functions are used
   4. Indexes can never be used on text columns

   Explanation: A basic index cannot help if the search transforms data (e.g., uppercasing), as it stores original values. Text columns can be indexed, indexes are not automatically dropped with function use, and standard indexes are not limited to NULLs.

10. Best Practices for Indexing

    What is a recommended method for choosing which columns go first in a composite index?

    1. Put columns with the smallest data type first
    2. Always list columns alphabetically
    3. Put the most selective or most frequently used column first
    4. Choose columns randomly

    Explanation: Placing the most selective or lead filter column first improves index effectiveness. Alphabetic order, smallest data type, or random choice ignore query patterns and data characteristics.