EXPERIMENT-11: SQL Indexes

📚 Introduction to SQL Indexes

What is a Database Index?

An index is a database object that improves the speed of data retrieval operations on a database table. It works similarly to an index in a book, allowing the database to find data without having to scan the entire table.

💡 Key Benefits of Indexes

Faster Searches: Quickly locate data without scanning the entire table
Improved Performance: Speed up SELECT queries and WHERE clauses
Efficient Sorting: Speed up ORDER BY operations
Primary Key Enforcement: Automatically created for PRIMARY KEY and UNIQUE constraints

EMPLOYEES Table Schema

Table: EMPLOYEES

Column Name	Data Type	Width	Attributes
Employee_id	CHAR	10	PRIMARY KEY
First_Name	VARCHAR	30	NOT NULL
Last_Name	VARCHAR	30	NOT NULL
DOB	DATE	-	-
Salary	NUMBER	25	NOT NULL
Department_id	CHAR	10	-

⚠️ Important Notes

Indexes improve read performance but can slow down write operations (INSERT, UPDATE, DELETE)
Use indexes on columns frequently used in WHERE, JOIN, and ORDER BY clauses
Avoid over-indexing as it consumes additional storage space

📝 Creating the EMPLOYEES Table

Table Creation SQL

SQL

CREATE TABLE EMPLOYEES (
    Employee_id CHAR(10) PRIMARY KEY,
    First_Name VARCHAR(30) NOT NULL,
    Last_Name VARCHAR(30) NOT NULL,
    DOB DATE,
    Salary NUMBER(25) NOT NULL,
    Department_id CHAR(10)
);

Inserting Sample Data

SQL

-- Inserting sample data into EMPLOYEES table
INSERT INTO EMPLOYEES VALUES ('E1001', 'John', 'Doe', '1990-05-15', 75000, 'D101');
INSERT INTO EMPLOYEES VALUES ('E1002', 'Jane', 'Smith', '1985-08-22', 85000, 'D101');
INSERT INTO EMPLOYEES VALUES ('E1003', 'Robert', 'Johnson', '1992-03-10', 65000, 'D102');
INSERT INTO EMPLOYEES VALUES ('E1004', 'Emily', 'Williams', '1988-11-05', 90000, 'D103');
INSERT INTO EMPLOYEES VALUES ('E1005', 'Michael', 'Brown', '1991-07-18', 72000, 'D102');
INSERT INTO EMPLOYEES VALUES ('E1006', 'Sarah', 'Davis', '1993-09-25', 78000, 'D101');

📋 Table Created Successfully

The EMPLOYEES table has been created with 6 sample records. This table will be used to demonstrate different types of indexes in the following sections.

🔍 Creating Basic Indexes

1. Creating a Simple Index

Let's create an index on the Last_Name and Department_id columns to speed up searches on these columns.

SQL

-- Create an index on Last_Name and Department_id columns
CREATE INDEX employee_idx ON EMPLOYEES(Last_Name, Department_id);

2. Finding ROWID

ROWID is a pseudo-column that returns the address of each row in the database table. Let's find the ROWID for our EMPLOYEES table.

SQL

-- Query to find ROWID for the EMPLOYEES table
SELECT ROWID, Employee_id, First_Name, Last_Name 
FROM EMPLOYEES;

💡 About ROWID

ROWID is a unique identifier for each row in a database
It represents the physical location of a row
ROWID is the fastest way to access a single row

🔑 Unique and Reverse Indexes

1. Creating a Unique Index

Let's create a unique index on the Employee_id column to ensure there are no duplicate employee IDs.

SQL

-- Create a unique index on Employee_id
CREATE UNIQUE INDEX emp_id_idx ON EMPLOYEES(Employee_id);

2. Creating a Reverse Key Index

A reverse key index stores the bytes of the indexed column in reverse order. This is useful for columns with sequential values to avoid I/O bottlenecks.

SQL

-- Create a reverse key index on Employee_id
CREATE INDEX emp_id_reverse_idx ON EMPLOYEES(Employee_id) REVERSE;

3. Creating a Composite Unique Index

Let's create a composite unique index on multiple columns to ensure the combination is unique.

SQL

-- Create a composite unique index on First_Name and Last_Name
CREATE UNIQUE INDEX emp_name_unique_idx ON EMPLOYEES(First_Name, Last_Name);

⚠️ Note on Unique Indexes

Attempting to insert duplicate values in columns with a unique index will result in an error. For example, trying to insert another employee with the same Employee_id will fail.

🔧 Function-based Indexes

1. Creating a Function-based Index

Function-based indexes allow you to create indexes on the results of functions or expressions. This is useful for case-insensitive searches.

SQL

-- Create a function-based index for case-insensitive search on Last_Name
CREATE INDEX emp_lastname_upper_idx ON EMPLOYEES(UPPER(Last_Name));

2. Using the Function-based Index

Now you can perform case-insensitive searches efficiently:

SQL

-- This query will use the function-based index
SELECT * FROM EMPLOYEES 
WHERE UPPER(Last_Name) = 'SMITH';

💡 When to Use Function-based Indexes

Case-insensitive searches
Searching with string functions (e.g., UPPER, LOWER, SUBSTR)
Mathematical expressions in WHERE clauses
Date manipulations in queries

🛠️ Managing Indexes

1. Viewing Existing Indexes

Let's see all the indexes we've created on the EMPLOYEES table.

SQL (Oracle)

-- For Oracle
SELECT index_name, index_type, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

SQL (MySQL)

-- For MySQL
SHOW INDEX FROM EMPLOYEES;

2. Dropping an Index

Let's drop the function-based index we created earlier.

SQL

-- Drop the function-based index
DROP INDEX emp_lastname_upper_idx;

3. Checking Index Usage

It's important to monitor whether your indexes are being used effectively.

SQL (Oracle)

-- Check if indexes are being used (Oracle)
SELECT name, used 
FROM v$object_usage 
WHERE index_name = 'EMPLOYEE_IDX';

✅ Best Practices for Index Management

Regularly monitor and remove unused indexes
Rebuild or reorganize fragmented indexes
Consider the overhead of indexes on DML operations
Use the EXPLAIN PLAN to verify index usage

🧠 Knowledge Check Quiz

Question 1: What is the purpose of a database index?

A. To improve the speed of data retrieval operations

B. To store additional data about tables

C. To create relationships between tables

D. To back up table data

Question 2: Which of the following statements creates a function-based index?

A. CREATE INDEX idx_name ON employees(last_name);

B. CREATE INDEX idx_upper_name ON employees(UPPER(last_name));

C. CREATE UNIQUE INDEX idx_id ON employees(employee_id);

D. CREATE INDEX idx_dept ON employees(department_id) REVERSE;

Question 3: What is the main disadvantage of having too many indexes?

A. They take up too much disk space

B. They slow down SELECT queries

C. They slow down INSERT, UPDATE, and DELETE operations

D. They make the database harder to back up

📚 Summary & Best Practices

Key Takeaways

Indexes are database objects that improve the speed of data retrieval operations
Different types of indexes serve different purposes (B-tree, Bitmap, Function-based, etc.)
Indexes should be created on columns frequently used in WHERE, JOIN, and ORDER BY clauses
While indexes speed up SELECT queries, they can slow down INSERT, UPDATE, and DELETE operations
Regular maintenance of indexes is important for optimal database performance

Best Practices

When to Create Indexes

On columns used in WHERE, JOIN, and ORDER BY clauses
On foreign key columns
On columns with high cardinality (many unique values)
For columns used in search conditions with operators like =, >, <, BETWEEN, etc.

When to Avoid Indexes

On small tables
On columns with few unique values (low cardinality)
On columns that are frequently updated
On tables with frequent bulk data loads

📊 EXPERIMENT-11