Database indexing plays an important role in improving query speeds and keeping systems running smoothly. It decreases the time needed to search through massive amounts of data, making everything from simple lookups to complex reports feel quicker.
Why do we need Database Indexing
Think about search for a friend's name in a massive phone book with no index—you'd slog through every page top to bottom. In database language, it is called a full table scan, and it effects performance when the data is huge. Indexes step in like handy shortcuts, speeding up searches, sorting, and linking tables in a flash. The only downside is they take up a bit more storage space, and adding or changing data becomes slightly slower because the system has to update the index at the same time.
1. B-tree Index
B-tree indexes are the go-to choice in most relational databases like Oracle. They store data in a self-balancing tree that keeps everything organized. This keeps searches quick, even as the database grows.
Syntax:
CREATE INDEX <index_name> ON <table_name> (<column_name>,<column_name>…) TABLESPACE <tablespace_name>;
When to use B-tree index
Use a B-tree index when your table has lots of rows and you need to search, sort, or filter data by ranges (like dates or numbers). It’s also good if the data changes often, because it can handle inserts, updates, and deletes without slowing down too much.
Advantages of B-tree index
- Handles range searches like a champ—think pulling all sales from last month without scanning everything.
- Keeps searches, adds, and deletes super quick since the tree stays balanced, no wild height swings.
- Works great for busy apps that mix reads, writes, and sorts all day long.
- Fewer disk reads 'cause you only go 3-4 levels deep, even on huge tables.
- Sorts data on its own, so no extra hassle for ORDER BY queries.
- Flexible for equals, greater-than, or starts-with—fits most real-world needs.
Disadvantages of B-tree index
- B-Trees are based on disk-based data structures and can have a high disk usage.
- Not the best for all cases. For small datasets, the search time in a B-Tree might be slower compared to a binary search tree, as each node may contain multiple keys.
2. Bitmap Index
A bitmap index in Oracle is a type of index that uses 0s and 1s to represent the values in a column. It works best for columns with only a few distinct values and is commonly used in reporting or data warehouse systems.
Syntax:
CREATE BITMAP INDEX Index_Name ON Table_Name (Column_Name);
When to use Bitmap index
Use a bitmap index for columns that have very few unique values (low-cardinality), like gender, status, or yes/no fields. It’s best for reporting or analytics where data doesn’t change frequently.
Advantages of Bitmap index
- Efficiency in terms of insertion deletion and updation.
- Faster retrieval of records
Disadvantages of Bitmap index
Only suitable for large tables Bitmap Indexing is time-consuming
3. Hash Index
A hash index works by taking the value of a column and running it through a formula (called a hash function). That formula turns the value into a specific storage location, often called a bucket. Instead of searching the whole table, the database jumps straight to that bucket to find the matching data, which makes exact-match searches very fast.
Syntax:
CREATE INDEX <index_name> ON <table_name> USING hash (column);When to use Hash index
Use a hash index when you need super-fast lookups for exact matches, like searching for a specific ID. It’s not good for ranges or columns that change a lot.
Advantages of Hash index
- Efficiency in terms of insertion deletion and updation.
- Faster retrieval of records
Disadvantages of Hash index
- Doesn’t handle ranges well
- Sometimes multiple values land in the same spot
- Not very flexible
- Not the first choice in most cases
Comments
Post a Comment