In the dynamic world of data, time is of the essence, and Oracle Database Indexing takes center stage as your superhero, dedicated to ensuring that your system operates at lightning speed while data retrieval becomes as smooth as spreading butter on warm toast.
One of the key features that contribute to its performance is indexing. In this blog, we will delve into the world of Oracle Database Indexing, exploring what it is, how it works, and why it’s crucial for optimizing query performance.
It’s a data structure that allows the database management system to quickly locate and retrieve the rows that match a certain query. This allows for efficient data retrieval, especially when dealing with large datasets.
Example: Imagine you have a library with thousands of books, and you want to find a specific book among them. Without an index, you would have to go through each book one by one until you find the one, you’re looking for. An index, in this context, is like the library’s catalog.
Once we run that query, what exactly goes on behind the scenes to find employees who are named Jenny?
The database would literally have to look at every single row in the Employee table to see if the Employee_Name for that row is ‘Jenny’. And, because we want every row with the name ‘Jenny’ inside it, we cannot just stop looking once we find just one row with the name ‘Jenny’, because there could be other rows with the name Jenny.
So, every row up until the last row must be searched – which means thousands of rows in this scenario will have to be examined by the database to find the rows with the name ‘Jenny’. This is what is called a full table scan.
Think of it like a phone book. Instead of reading every name in the phone book to find a person’s number, you can use the index (the alphabetical order) to quickly locate the right page where their name is listed. This saves a lot of time.
Now, if this index is organized in a particular way, like a B-tree, it not only helps you find things fast but also keeps them neatly sorted. Sorting is like arranging items in order, just like organizing your books on a shelf. When things are sorted, it becomes even easier to find what you need because they are in a logical order.
When we make a B-tree index for the “Employee_Name” column, it’s like making an organized list of employee names. Imagine the names are in alphabetical order, from A to Z.
Now, if we want to find an employee named “Jenny,” we don’t have to search through the whole employee list. Instead, we can use the index, which is like a quick guide. It tells us where all the names that start with “J” are located in the list.
Since the index is sorted, it’s much faster to find names. All the “J” names are right next to each other in the index. Plus, the index keeps track of where each name is in the actual employee table, so we can easily get other info about the employees, like their job titles or salaries.
In a nutshell, the index helps us find names quickly because it’s like a sorted list with pointers to where the names are in the employee table.
So, now you know that a database index is created on a column in a table and that the index stores the values in that specific column. However, it is important to understand that a database index does not store the values in the other columns of the same table.
For Example: If we create an index on the Employee_Name column, this means that the Employee_Age and Employee_Address column values are also not stored in the index. If we did just store all the other columns in the index, then it would be just like creating another copy of the entire table – which would take up way too much space and would be very inefficient.
An index also stores a pointer to the table row So, the question is if the value that we are looking for is found in an index (like ‘Jenny’), how does it find the other values that are in the same row (like the address of Jenny and his age)? Well, it’s quite simple – database indexes also store pointers to the corresponding rows in the table.
We can say that in Oracle row id is working as a pointer for the row.
Oracle by default uses a B-tree (Balanced Tree) structure for indexing. B-trees are highly efficient for both insertion and retrieval operations.
Each index entry consists of a key value and a pointer to the corresponding row in the table.
The keys are stored in a sorted order, allowing fast binary search operations.
Whenever we create a column(s) with Primary key constraints, Oracle implicitly creates a Normal index (Clustered Type Index). This index is not a Unique index. Why because those columns already have uniqueness because of Unique or Primary key constraints (NOT NULL).
Explicit Indexes are user-defined. Explicit index created by “Create Index Command”.
Syntax: CREATE UNIQUE INDEX index_name ON table_name(column1,column2,...);
Example: CREATE UNIQUE INDEX i_emp_mobile_no ON employee(emp_mobile_no);
Syntax: CREATE INDEX index_name ON table_name (column1, column2,...);
Example: CREATE INDEX i_emp_name ON employee (first_name, last_name);
Syntax: CREATE INDEX index_name ON table_name (field_name);
Example: CREATE INDEX i_emp_name ON employee (employee_name);
Interesting Reads: SQL DBA Services for One of the Largest ERP System
Note: that a function-based index can be a btree or bitmap index.
Remember, function-based indexes require more effort to maintain than regular indexes, so having concatenated indexes in this manner may increase the incidence of index maintenance compared to a function-based index on a single column.
Syntax: CREATE INDEX index_name ON table_name (expression);
Example: CREATE INDEX i_employee_name ON employee(UPPER(employee_name));
A clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is reported in the case of the clustered index. Clustered indexes are Physically stored in order (ascending or descending) Only one per table When a primary key is created a clustered index is automatically created as well.
If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended. Columns with values that will not change at all or very seldom are the best choices. ie. Dictionary.
Up to 249 non-clustered indexes are possible for each table. In the case of a non-clustered index, the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
When you create an index on a table column, Oracle extracts the data from that column and organizes its index.
When a query is executed that involves the indexed column, Oracle quickly locates the relevant rows.
Once the index points to the rows that match the query conditions, Oracle retrieves the data from the table using the pointers stored in the index.
While Oracle Database indexes offer substantial benefits, they are not without their challenges.
Interesting Read: Oracle Performance Tuning: Root Cause, Problems, And Solutions
Indexing is like a super tool for making searches faster and handling big piles of data with ease. Indexes are like shortcuts that make finding information in a database faster and easier when they are used properly.
However, you need to plan and take care of them properly to make sure they keep working well without adding extra work.
To get the most out of Oracle Database in your projects, it’s important to learn how indexing works and make smart choices.
SPEC INDIA, as your single stop IT partner has been successfully implementing a bouquet of diverse solutions and services all over the globe, proving its mettle as an ISO 9001:2015 certified IT solutions organization. With efficient project management practices, international standards to comply, flexible engagement models and superior infrastructure, SPEC INDIA is a customer’s delight. Our skilled technical resources are apt at putting thoughts in a perspective by offering value-added reads for all.