We will explore Transactions, Deadlocks, and Log-Based Recovery using SQL.
We use the Accounts table as our base schema and demonstrate ACID concepts with rollback, simulate a deadlock, and discuss log-based recovery.
definition:
Indexing: A database technique that improves data retrieval speed by creating auxiliary data structures on table columns.
Hash Indexing: An indexing method that uses a hash function to map keys to specific locations for fast equality searches.
B+ Index: A balanced tree index where all data records are stored in leaf nodes, and internal nodes hold only keys for efficient range queries.
B- Index: (B-Tree Index): A self-balancing tree index where keys and data can be stored in both internal and leaf nodes for balanced access.
Query Optimization: The process of determining the most efficient way to execute a database query using indexes, statistics, and execution plans.
Creating the Students Table:
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Gender CHAR(1),
Department VARCHAR(50),
Email VARCHAR(100) UNIQUE
);
Inserting Sample Records:
INSERT INTO Student (StudentID, FirstName, LastName, Age, Gender, Department, Email) VALUES
(1, 'Alice', 'Johnson', 20, 'F', 'Computer Science', 'alice.johnson@example.com'),
(2, 'Bob', 'Smith', 22, 'M', 'Mechanical Engineering', 'bob.smith@example.com'),
(3, 'Clara', 'Williams', 19, 'F', 'Electrical Engineering', 'clara.williams@example.com'),
(4, 'David', 'Brown', 21, 'M', 'Mathematics', 'david.brown@example.com'),
(5, 'Eva', 'Davis', 23, 'F', 'Business Administration', 'eva.davis@example.com'),
(6, 'Frank', 'Miller', 20, 'M', 'Civil Engineering', 'frank.miller@example.com'),
(7, 'Grace', 'Wilson', 21, 'F', 'Computer Science', 'grace.wilson@example.com'),
(8, 'Henry', 'Moore', 22, 'M', 'Mechanical Engineering', 'henry.moore@example.com'),
(9, 'Ivy', 'Taylor', 19, 'F', 'Electrical Engineering', 'ivy.taylor@example.com'),
(10, 'Jack', 'Anderson', 24, 'M', 'Mathematics', 'jack.anderson@example.com'),
(11, 'Karen', 'Thomas', 20, 'F', 'Business Administration', 'karen.thomas@example.com'),
(12, 'Leo', 'Jackson', 21, 'M', 'Civil Engineering', 'leo.jackson@example.com'),
(13, 'Mia', 'White', 22, 'F', 'Computer Science', 'mia.white@example.com'),
(14, 'Noah', 'Harris', 23, 'M', 'Mechanical Engineering', 'noah.harris@example.com'),
(15, 'Olivia', 'Martin', 20, 'F', 'Electrical Engineering', 'olivia.martin@example.com'),
(16, 'Paul', 'Thompson', 21, 'M', 'Mathematics', 'paul.thompson@example.com'),
(17, 'Quinn', 'Garcia', 22, 'F', 'Business Administration', 'quinn.garcia@example.com'),
(18, 'Ryan', 'Martinez', 19, 'M', 'Civil Engineering', 'ryan.martinez@example.com'),
(19, 'Sophia', 'Robinson', 23, 'F', 'Computer Science', 'sophia.robinson@example.com'),
(20, 'Tom', 'Clark', 24, 'M', 'Mechanical Engineering', 'tom.clark@example.com');
Creating a B-Tree Index on roll_no:
CREATE INDEX idx_student_id
ON Student (StudentID);
Query: Fetch student with Student_ID = 10;
SELECT * FROM StudentWHERE StudentID = 10;
Creating a B+ Tree Index on age:
CREATE INDEX idx_student_age ON Student (Age);
Query: Display all students with age = 21;
SELECT * FROM Student WHERE Age = 21;
Creating an Index on dept for Fast Equality Search
CREATE INDEX idx_student_dept ON Student (Department);
Query: Retrieve all students from the Computer Science department
SELECT * FROM Students WHERE dept = 'Computer Science';
Steps Summary:-
1. Create the Student table → Defined columns like StudentID, FirstName, Age, Department, etc.
2. Insert sample records → Added 20 student records for testing queries and indexing.
3. Create a B-Tree index on StudentID → Fast lookups and range queries by student ID (already exists as PK).
4. Query using the StudentID index → Equality (=), range (BETWEEN), and sorting (ORDER BY).
5. Create a B+ Tree index on Age → Efficient for searching students by specific age or within an age range.
6. Query using the Age index → Equality search, range queries, and ordered retrieval by age.
7. Create an index on Department (Hash / B+ Tree) → Optimized for fast equality search on departments.
8. Query using the Department index → Retrieve all students in a particular department quickly.
9. Note on index choice:
B+ Tree = supports equality + range queries.
Hash Index = best for equality only.
Conclusion:
Indexes (B-Tree, B+ Tree, and Hash) significantly improve database performance by enabling faster equality, range, and ordered queries depending on the use case.
Thanks to @santhoshnc Sir for guiding me through indexing and query optimization concepts.
SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database
Top comments (0)