This content originally appeared on DEV Community and was authored by Reshma Devi
Databases handle massive data efficiently using indexes and hashing. Instead of scanning entire tables, indexes act like the index of a book, making lookups faster.
In this blog, we’ll build a Students table, create B-Tree, B+Tree, and Hash indexes, and run queries to see their effect.
Key Definitions
Indexing
Indexing is a technique to speed up data retrieval from a database. Instead of scanning the whole table, the database uses an index (like a book index) to locate the rows quickly.
B-Tree Index
A B-Tree (Balanced Tree) index stores keys in a sorted order, allowing logarithmic time searches. It is efficient for:
B+ Tree Index
A B+ Tree is a variation of the B-Tree where all values are stored in the leaf nodes, and internal nodes only store keys for navigation.
Hash Index
A Hash Index uses a hashing function to map keys (like dept) into buckets.
Query Optimization
The process of minimising query execution time by leveraging indexes and writing efficient SQL statements.
Step 1: Create Students Table
CREATE TABLE Students1 (
roll_no INT PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(20),
cgpa NUMBER(3,2)
);
Inserting Sample Records
INSERT INTO Students1 VALUES (101, ‘Ana’, ‘CSBS’, 8.5);
INSERT INTO Students1 VALUES (102, ‘Paul’, ‘CSBS’, 7.8);
INSERT INTO Students1 VALUES (103, ‘Kevin’, ‘ECE’, 9.0);
INSERT INTO Students1 VALUES (104, ‘Angelin’, ‘ME’, 8.2);
INSERT INTO Students1 VALUES (105, ‘Vanessa’, ‘CSBS’, 8.8);
INSERT INTO Students1 VALUES (106, ‘Ria’, ‘ECE’, 7.5);
INSERT INTO Students1 VALUES (107, ‘Samuel’, ‘ME’, 8.7);
INSERT INTO Students1 VALUES (108, ‘Noah’, ‘CSBS’, 6.9);
INSERT INTO Students1 VALUES (109, ‘Marin’, ‘ECE’, 8.0);
INSERT INTO Students1 VALUES (110, ‘Joseph’, ‘CSBS’, 9.2);
INSERT INTO Students1 VALUES (111, ‘Trinita’, ‘ME’, 7.9);
INSERT INTO Students1 VALUES (112, ‘Ryan’, ‘CSBS’, 8.3);
INSERT INTO Students1 VALUES (113, ‘Daniel’, ‘ECE’, 9.1);
INSERT INTO Students1 VALUES (114, ‘Kane’, ‘ME’, 7.7);
INSERT INTO Students1 VALUES (115, ‘Isha’, ‘CSBS’, 8.6);
INSERT INTO Students1 VALUES (116, ‘Sarah’, ‘ECE’, 8.4);
INSERT INTO Students1 VALUES (117, ‘Merlin’, ‘ME’, 8.0);
INSERT INTO Students1 VALUES (118, ‘James’, ‘CSBS’, 7.6);
INSERT INTO Students1 VALUES (119, ‘Page’, ‘ECE’, 8.9);
INSERT INTO Students1 VALUES (120, ‘Reynolds’, ‘ME’, 8.1);
B-Tree Index on roll_no
Most DBMSs use B-Trees to index numeric/ordered columns.
CREATE INDEX idx_roll_no ON Students1(roll_no);
Query with Index
This fetches details of roll_no = 110 in O(log n) instead of scanning all rows.
SELECT * FROM Students1 WHERE roll_no = 110;
B+ Tree Index on CGPA
B+ Trees are used for range queries, making them perfect for CGPA lookups.
CREATE INDEX idx_cgpa ON Students1(cgpa);
Query
Display all students with a CGPA> 8.0
SELECT * FROM Students1 WHERE cgpa > 8.0;
Hash Index on dept
Hashing is great for exact matches (not ranges).
CREATE INDEX idx_dept ON Students1(dept);
Query
Retrieve all students from the CSBS department
SELECT * FROM Students1 WHERE dept = ‘CSBS’;
Wrap Up
In this tutorial, we explored:
B-Tree Index → Fast lookup by roll_no
B+Tree Index → Efficient range queries (CGPA > 8.0)
Hash Index → Quick equality checks (dept = CSBS)
Indexes make queries 10x–100x faster, but they also consume storage & slow down inserts/updates. Use them wisely for query optimization!
Thanks to @santhoshnc Sir for guiding me through indexing and query optimization concepts.
SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database
This content originally appeared on DEV Community and was authored by Reshma Devi