Lesson 26 Advanced Level
26 of 30 lessons

Indexes

Learn how to create and manage indexes to improve query performance and database speed. Indexes are crucial for database optimization.

What is an Index?

An index in Oracle SQL is a database object that improves the speed of data retrieval operations on a table at the cost of additional space and slower writes (INSERT/UPDATE/DELETE).

Creating an Index

CREATE INDEX idx_emp_lastname
ON employees (last_name);

This creates an index named idx_emp_lastname on the last_name column of the employees table.

Dropping an Index

DROP INDEX idx_emp_lastname;

When to Use Indexes

  • When querying large tables frequently by a specific column
  • When using JOINs on columns that aren’t primary keys
  • To improve search performance on foreign keys

Types of Indexes

  • Single-column index: Index on one column
  • Composite index: Index on multiple columns
  • Unique index: Ensures no duplicate values
  • Function-based index: Based on expressions

Example: Composite Index

CREATE INDEX idx_emp_dept_salary
ON employees (department_id, salary);

Practice Task