Theory
The 50,000 Book Search
Imagine entering the CampusLib campus library to find a textbook titled 'Mastering Oracle'. The librarian points to a massive pile of 50,000 books dumped randomly on the floor. To find your book, you have to pick up every single book one by one until you find a match. This painful process is exactly what Oracle does during a Full Table Scan when a column is not indexed. How can we fix this?
Theory
The Textbook Index
Think of a database index exactly like the index pages at the back of your BCA textbooks. If you want to find where 'Subqueries' are explained, you do not flip through all 400 pages. You turn to the back index, look up 'Subqueries', find page number 142, and turn directly to that page. The database index does the same, replacing page numbers with Oracle's internal unique pointers called ROWIDs.
Theory
What is an Index?
Formally, an Index is a schema object that contains an entry for each value that appears in the indexed columns of a table and provides direct, fast access to rows. In Oracle SQL, when you create an index, Oracle automatically builds a B Tree structure. This structure maps the column value to its exact physical location on the disk, known as the ROWID. This avoids reading the entire table during data retrieval operations.
At a glance
Comparison of data retrieval methods in Oracle SQL
| Feature | Full Table Scan | Index Scan |
|---|---|---|
| Search Method | Scans every row from top to bottom | Jumps directly to matching rows via ROWID |
| Execution Speed | Slower as table size grows | Lightning fast, independent of table size |
| Resource Cost | High CPU and disk IO usage | Low memory and disk IO usage |
| Best Used For | Retrieving a large percentage of rows | Retrieving a specific few rows (less than 5 percent) |
Practical
Creating and Dropping an Index on CampusLib
-- Step 1: Let us speed up searches for book titles in CampusLib
CREATE INDEX idx_book_title
ON books (title);
-- Step 2: Test a query that will now use this index
SELECT book_id, price
FROM books
WHERE title = 'Mastering Oracle';
-- Step 3: Remove the index if titles change too frequently
DROP INDEX idx_book_title;This example runs in Gri-Learn on the web, where you can edit it and see the output.
Quiz
If you create an index on the price column of the books table, what happens behind the scenes when you run an INSERT statement?
- Oracle inserts the new row into the table and automatically updates the index.
- Oracle updates the table but you must manually rebuild the index.
- Oracle prevents the INSERT because the index locks the table.
- The INSERT runs faster because the index helps find the empty space.
Show the answer
Oracle inserts the new row into the table and automatically updates the index.
Oracle automatically maintains indexes. When you insert, update, or delete rows, Oracle updates the index structure instantly. However, this extra work makes write operations slightly slower, which is a major university exam question trap!
Follow along
How Oracle Uses an Index During a Query
- 1. Query Parsing The Oracle optimizer checks the WHERE clause to see if an index exists for the search column.
- 2. Index Lookup Oracle searches the internal B Tree index structure to locate the specific key value.
- 3. ROWID Extraction The index provides the exact ROWID, which is the physical address of the matching row on disk.
- 4. Direct Row Fetch Oracle bypasses all other records and fetches the block directly using the ROWID.
Watch out
The Over Indexing Trap
Do not index every column! Students often think more indexes equal a faster database. Remember: while an index speeds up SELECT queries, it slows down INSERT, UPDATE, and DELETE statements because Oracle must constantly rewrite the index structure. Create indexes only on columns frequently used in WHERE clauses or JOIN conditions.
Think first
Oracle DROP INDEX Syntax Check
Mental Check: Look at this command: 'DROP INDEX books.idx_book_title;'. Will this run successfully in Oracle SQL? Think before you tap.
Show the answer
No, it will fail! In Oracle SQL, index names are unique within a schema. The correct syntax is simply 'DROP INDEX idx_book_title;'. Unlike some other SQL dialects like SQLite, you do not use the table name prefix format during a drop operation.
Theory
The Hidden Primary Key Index
Did you know you have already been creating indexes? In Oracle, whenever you define a PRIMARY KEY or UNIQUE constraint on a table (like book_id in books), Oracle automatically creates a unique index for that column behind the scenes. You never need to manually create an index for primary key columns!
Summary
Key takeaways
- An index is an optional database structure that accelerates data retrieval queries.
- Oracle uses a specialized tree structure mapping column values directly to unique ROWIDs.
- Use CREATE INDEX to build it and DROP INDEX followed by the index name to delete it.
- Indexes speed up SELECT queries but add overhead to INSERT, UPDATE, and DELETE tasks.
- Oracle automatically builds unique indexes for PRIMARY KEY and UNIQUE columns.
- Memory hook: Indexing is like bookmarking: fast to find, slow to rearrange!