ORACLE DATABASE ADMINISTRATION

Module 8 : Indexes

ORACLE DATABASE ADMINISTRATION SERIES

S.N STEPHEN N NJOROGE

LinkedIn: www.linkedln.com/in/stephen-njoroge

SENIOR DATABSE ADMINISTRATOR | MSSQL | ORACLE| PostgreSQL |OCI |AWS |

DATABASE ADMINISTRATION SERIES |

Module 8: Indexes

Deciding When to Create an Index

 Proactively Creating Indexes
 Reactively Creating Indexes

Planning for Robustness

 Creating Separate Tablespaces for Indexes

Creating Indexes
 Creating B-tree Indexes
 Creating Concatenated Indexes
 Implementing Function-Based Indexes
 Creating Unique Indexes
 Implementing Bitmap Indexes
 Creating Bitmap Join Indexes
 Implementing Reverse-Key Indexes
 Creating Key-Compressed Indexes
 Parallelizing Index Creation
 Avoiding Redo Generation When Creating an Index
 Implementing Invisible Indexes

Maintaining Indexes
 Renaming an Index
 Displaying Code to Recreate an Index
 Rebuilding an Index
 Making Indexes Unusable
 Monitoring Index Usage
 Dropping an Index

Indexing Foreign Key Columns

 Implementing an Index on a Foreign Key Column
 Determining if Foreign Key Columns Are Indexed

Deciding When to Create an Index

An index is a database object that improves the speed of data retrieval operations on a table. It works similarly to an
index in a book — it allows Oracle to find rows with specific column values quickly without scanning the entire table.
However, every index comes with overhead: additional storage and slower DML operations like (INSERT, UPDATE,
and DELETE). So, we must be strategic about when to create them."

Proactively Creating Indexes

This refers to the act of designing indexes during the database design phase based on anticipated queries and access
patterns.

Proactive Index Creation Demonstration

CREATE TABLES

Module 8: Indexes
CUSTOMERS table:

CREATE TABLE customers (

customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
city VARCHAR2(50),
created_date DATE DEFAULT SYSDATE
);

PRODUCTS table

CREATE TABLE products (

product_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
product_name VARCHAR2(100),
category VARCHAR2(50),
price NUMBER(10,2),
stock_quantity NUMBER DEFAULT 0
);

ORDERS table (references customers)

CREATE TABLE orders (

order_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id NUMBER NOT NULL,
order_date DATE DEFAULT SYSDATE,
total_amount NUMBER(10,2),
status VARCHAR2(20) DEFAULT 'PENDING',
-- Foreign key constraint
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES
customers(customer_id)
);

Module 8: Indexes
ORDER_ITEMS table (references orders and products)

CREATE TABLE order_items (

item_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_id NUMBER NOT NULL,
product_id NUMBER NOT NULL,
quantity NUMBER,
unit_price NUMBER(10,2),
-- Foreign key constraints
CONSTRAINT fk_orderitems_order FOREIGN KEY (order_id) REFERENCES orders(order_id),
CONSTRAINT fk_orderitems_product FOREIGN KEY (product_id) REFERENCES
products(product_id)
);

PROACTIVE INDEX CREATION

Primary Keys: Automatically Indexed by Oracle (manually created by oracle)
 customers(customer_id)
 products(product_id)
 orders(order_id)
 order_items(item_id)

-- Set column display formats for clean output

COLUMN index_name FORMAT A30 HEADING "Index Name"
COLUMN table_name FORMAT A20 HEADING "Table Name"
COLUMN column_name FORMAT A20 HEADING "Column Name"
COLUMN uniqueness FORMAT A10 HEADING "Uniqueness"
COLUMN generated FORMAT A10 HEADING "Generated"
-- Query to verify indexes on primary key and foreign key columns
SELECT
i.index_name,

Module 8: Indexes
 i.table_name,
ic.column_name,
i.uniqueness,
i.generated
FROM user_indexes i
JOIN user_ind_columns ic ON i.index_name = ic.index_name
WHERE UPPER(ic.column_name) IN ('CUSTOMER_ID', 'PRODUCT_ID', 'ORDER_ID', 'ITEM_ID')
AND UPPER(i.table_name) IN ('CUSTOMERS', 'PRODUCTS', 'ORDERS', 'ORDER_ITEMS')
ORDER BY i.table_name, ic.column_position;

Foreign Key Columns Indexes .

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

CREATE INDEX idx_orderitems_order_id ON order_items(order_id);

CREATE INDEX idx_orderitems_product_id ON order_items(product_id);

Module 8: Indexes
Index Columns Used in WHERE, JOIN, ORDER BY, GROUP BY

-- Frequent queries by customer email

CREATE INDEX idx_customers_email ON customers(email);

-- Query orders by date range

CREATE INDEX idx_orders_date ON orders(order_date);

-- Filter products by category

CREATE INDEX idx_products_category ON products(category);

Composite Indexes for Multi-Column Filters

-- Common query: "Find orders by customer + status + date"

CREATE INDEX idx_orders_complex
ON orders(customer_id, status, order_date);

-- Query products by category and price range

CREATE INDEX idx_products_category_price
ON products(category, price);

-- Query order items with filtering on product and quantity

Module 8: Indexes
CREATE INDEX idx_orderitems_product_qty
ON order_items(product_id, quantity);

Reactively Creating Indexes

Occurs after identifying performance bottlenecks. This is typically driven by slow-running queries reported by users or
identified via monitoring tools.

-- Create the CUSTOMERS table

CREATE TABLE CUSTOMERS (
CUSTOMER_ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100),
EMAIL VARCHAR2(150),
PHONE VARCHAR2(20),
CREATED_DATE DATE
);

-- Insert sample data (simulate large dataset)

INSERT INTO CUSTOMERS (CUSTOMER_ID, NAME, EMAIL, PHONE, CREATED_DATE)
SELECT
ROWNUM,
'Customer ' || ROWNUM,
'email' || ROWNUM || '@domain.com',
'+1-' || LPAD(ROWNUM, 10, '0'),
SYSDATE - DBMS_RANDOM.VALUE(1, 1000)
FROM DUAL
CONNECT BY ROWNUM <= 1000000;

-- Gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'CUSTOMERS');

Module 8: Indexes
Planning for Robustness
Creating Separate Tablespaces for Indexes

CREATE TABLESPACE idx_tbs

DATAFILE '/u01/app/oracle/oradata/ORCL/idx_tbs01.dbf'
SIZE 2G
AUTOEXTEND ON
NEXT 500M
MAXSIZE 8G
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

Creating Indexes

-- Sample Tables
CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(100)
);

CREATE TABLE employees (

emp_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
salary NUMBER,
dept_id NUMBER,
status VARCHAR2(20), -- 'ACTIVE', 'INACTIVE'
created_date DATE,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Module 8: Indexes
CREATE TABLE sales (
sale_id NUMBER,
product VARCHAR2(50),
amount NUMBER,
emp_id NUMBER
);

Creating B-tree Indexes

CREATE INDEX idx_employees_last_name

ON employees(last_name)
TABLESPACE idx_tbs;

Creating Concatenated Indexes (Composite Index)

CREATE INDEX idx_emp_name_dept

ON employees(last_name, first_name, dept_id)
TABLESPACE idx_tbs;

Module 8: Indexes
Implementing Function-Based Indexes

-- Index on uppercase email for case-insensitive search

CREATE INDEX idx_employees_email_upper
ON employees(UPPER(email))
TABLESPACE idx_tbs;

Creating Unique Indexes

-- Explicit unique index

CREATE UNIQUE INDEX uk_employees_email
ON employees(email)
TABLESPACE idx_tbs;

ALTER TABLE employees

ADD CONSTRAINT uk_emp_email UNIQUE (email)
USING INDEX uk_employees_email;

Implementing Bitmap Indexes

CREATE BITMAP INDEX idx_employees_status

ON employees(status)
TABLESPACE idx_tbs;

Module 8: Indexes
Creating Bitmap Join Indexes

CREATE BITMAP INDEX idx_sales_dept_name

ON sales(d.dept_name) -- indexed column from join
FROM sales s, departments d
WHERE s.dept_id = d.dept_id
TABLESPACE idx_tbs;

Implementing Reverse-Key Indexes

CREATE INDEX idx_emp_id_reverse

ON employees(emp_id)
REVERSE
TABLESPACE idx_tbs;

Creating Key-Compressed Indexes

CREATE INDEX idx_emp_dept_compressed
ON employees(dept_id, last_name, first_name)
COMPRESS 1 -- compress first key (dept_id)
TABLESPACE idx_tbs;

Module 8: Indexes
Parallelizing Index Creation

CREATE INDEX idx_sales_amount

ON sales(amount)
TABLESPACE idx_tbs
PARALLEL 4;

Avoiding Redo Generation When Creating an Index

CREATE INDEX idx_no_redo
ON employees(salary)
TABLESPACE idx_tbs
NOLOGGING;

Implementing Invisible Indexes

CREATE INDEX idx_employees_created_date

ON employees(created_date)
INVISIBLE
TABLESPACE idx_tbs;

Module 8: Indexes
Maintaining Indexes
Renaming an Index

ALTER INDEX idx_employees_dept_id RENAME TO idx_emp_dept_fk;

Displaying Code to Recreate an Index

-- Enable output
SET LONG 10000
SET PAGESIZE 0
SET LINESIZE 200
SET TRIMSPOOL ON
SET HEAD OFF

-- Get DDL for a specific index

SELECT DBMS_METADATA.GET_DDL('INDEX', 'IDX_EMP_DEPT_FK') FROM DUAL;

Rebuilding an Index
ALTER INDEX idx_emp_dept_fk REBUILD;

Module 8: Indexes
Making Indexes Unusable
ALTER INDEX idx_emp_dept_fk UNUSABLE;

ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

Monitoring Index Usage

ALTER INDEX idx_emp_dept_fk MONITORING USAGE;

Module 8: Indexes
Dropping an Index

DROP INDEX idx_emp_dept_fk;

Indexing Foreign Key Columns

-- Create primary table

CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(100)
);

-- Create foreign table

CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(50),
dept_id NUMBER,
CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Implementing an Index on a Foreign Key Column

CREATE INDEX idx_employees_dept_id ON employees(dept_id) TABLESPACE idx_tbs;

Module 8: Indexes
Identify Missing Index on Foreign Key
SELECT
c.table_name AS child_table,
c.column_name AS fk_column,
c.constraint_name
FROM user_cons_columns c
JOIN user_constraints t ON c.constraint_name = t.constraint_name
WHERE t.constraint_type = 'R' -- 'R' = Referential (Foreign Key)
AND NOT EXISTS (
SELECT 1
FROM user_ind_columns i
WHERE i.table_name = c.table_name
AND i.column_name = c.column_name
);

Module 8: Indexes