🚀 Oracle SQL Indexes: Create, Drop, Unique, Function-based & Bitmap

🚀 Practice: Improve Query Performance with Indexes

Indexes help speed up data retrieval in Oracle SQL. Below are practical steps and sample queries to improve performance using normal, unique, function-based, and bitmap indexes.

1. Create a table

CREATE TABLE EMPLOYEE (
  EMP_ID     NUMBER PRIMARY KEY,
  EMP_NAME   VARCHAR2(50),
  EMAIL      VARCHAR2(100),
  GENDER     CHAR(1),
  SALARY     NUMBER,
  STATUS     VARCHAR2(20)
);

2. Insert sample data

INSERT INTO EMPLOYEE VALUES (101, 'Rafiq Hossain', 'rafiq@mail.com', 'M', 60000, 'Active');
INSERT INTO EMPLOYEE VALUES (102, 'Nasima Akter',  'nasima@mail.com', 'F', 58000, 'Active');
INSERT INTO EMPLOYEE VALUES (103, 'Tanvir Rahman', 'tanvir@mail.com', 'M', 49000, 'Inactive');
-- (Add more rows as needed)

3. Create useful indexes

• Normal index on frequently searched column:

CREATE INDEX IDX_EMP_NAME ON EMPLOYEE(EMP_NAME);

• Unique index on EMAIL (enforces uniqueness):

CREATE UNIQUE INDEX IDX_EMAIL_UNIQUE ON EMPLOYEE(EMAIL);

• Function-based index for case-insensitive search:

CREATE INDEX IDX_UPPER_NAME ON EMPLOYEE(UPPER(EMP_NAME));

• Bitmap index on low-cardinality column (e.g., gender):

CREATE BITMAP INDEX IDX_GENDER ON EMPLOYEE(GENDER);

4. Run SELECT queries and compare performance

-- Using index on name
SELECT * FROM EMPLOYEE WHERE EMP_NAME = 'Nasima Akter';

-- Using function-based index
SELECT * FROM EMPLOYEE WHERE UPPER(EMP_NAME) = 'NASIMA AKTER';

-- Using bitmap index
SELECT * FROM EMPLOYEE WHERE GENDER = 'F';

Run EXPLAIN PLAN before and after creating indexes to see performance improvements.

5. Drop indexes after test (optional)

DROP INDEX IDX_EMP_NAME;
DROP INDEX IDX_EMAIL_UNIQUE;
DROP INDEX IDX_UPPER_NAME;
DROP INDEX IDX_GENDER;

---

⚡ Use the right type of index based on column usage and data pattern to boost Oracle SQL performance!