DEV Community

Rithika
Rithika

Posted on

Indexing,Hashing & Query optimization in DBMS

Getting Hands-On with Oracle Live SQL: My Journey into Database Fundamentals

I recently dove into Oracle Live SQL to sharpen my database skills, and I wanted to share some of the cool things I learned—especially around clusters, indexing, and querying data efficiently.

🧩Creating a Clustered Setup

I started by experimenting with clusters, which allow related tables to be stored together physically. Here's a snippet of what I tried:

CREATE CLUSTER emp_dept_cluster (
deptno NUMBER(3)
)
SIZE 512;

CREATE INDEX idx_emp_dept_cluster
ON CLUSTER emp_dept_cluster;

Then I created two tables—dept and emp—that share the deptno key and are clustered together:

CREATE TABLE dept (
deptno NUMBER(3),
dname VARCHAR2(14),
loc VARCHAR2(13)
)
CLUSTER emp_dept_cluster (deptno);

CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3)
)
CLUSTER emp_dept_cluster (deptno);

Pro tip: If you get ORA-00955: name is already used by an existing object, it means you’ve already created that object—just drop it or rename it before retrying.

🎓Building a Student Table and Querying Data

Next, I created a simple student table and practiced inserting and retrieving data:

CREATE TABLE student (
roll_no NUMBER,
name VARCHAR2(20),
dept VARCHAR2(10),
cgpa NUMBER
);

INSERT INTO student VALUES (101, 'Alice', 'CS', 8.5);
INSERT INTO student VALUES (110, 'Bob', 'IT', 7.8);
-- more inserts...
SELECT * FROM student;

This helped me understand how to structure data and run basic queries.

⚡Indexing for Performance

I also explored how indexes improve query performance:

-- Primary key index is implicit
CREATE TABLE student (
roll_no NUMBER PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(50),
cgpa NUMBER(3,2)
);

-- Explicit index on name
CREATE INDEX idx_name ON student(name);

-- Composite index
CREATE INDEX idx_dept_cgpa ON student(dept, cgpa);

Using these indexes, I ran queries like:

SELECT * FROM student WHERE name = 'Jack';
SELECT * FROM student WHERE dept = 'CSE' AND cgpa = 9.5;

The results were fast and efficient—Oracle Live SQL even shows you which indexes are being used!

🧮Categorizing with CASE Statements

Finally, I played with the CASE clause to categorize products based on price:

SELECT product_id, product_name,
CASE
WHEN list_price < 200 THEN 'Low'
WHEN list_price BETWEEN 200 AND 500 THEN 'Medium'
ELSE 'High'
END AS cost
FROM products
ORDER BY cost;

This is a great way to add logic directly into your queries.

Thanks to @santhoshnc sir for guiding me through indexing and query optimization concepts.



Top comments (0)