Level 3 - Question 5: Creating Indexes for Performance

Scenario

Your database administrator notices that queries searching for customers by email are running slowly. The customers table has millions of rows.

Task

Create an index on the email column of the customers table to improve query performance. Then write a query that would benefit from this index.

Sample Schema: `customers`

customer_id	name	email	city
1	Alice Johnson	alice@example.com	New York
2	Bob Smith	bob@example.com	Chicago
3	Carol White	carol@example.com	Houston

Solution:

-- Create index on email column
CREATE INDEX idx_customer_email ON customers(email);

-- Query that benefits from the index
SELECT customer_id, name, city
FROM customers
WHERE email = 'alice@example.com';

Creating an index on the email column significantly speeds up searches by email. Without an index, Oracle would need to scan every row (full table scan). With an index, it can quickly locate the matching row.

Note: Indexes improve SELECT performance but can slow down INSERT, UPDATE, and DELETE operations since the index must be maintained.

Previous Question Next Question

Level 3 - Question 5: Creating Indexes for Performance

Scenario

Task

Sample Schema: customers

Solution:

Sample Schema: `customers`