An Index is a schema object that allows the retrieval of queries faster. In other words, it's an instrument used to improve performance and tuning.
Indexes are always included when a primary key is created, but we can also create indexes on other columns, including columns holding text (varchar2) or dates.
It would be great to have indexes everywhere, making the queries faster, right?
One of the reasons why this is also not a good idea is space. Indexes take up space. Usually, we also have disk space constraints. Besides, they are becoming less relevant because computing speed has improved dramatically, too.
To create an index, we can use the following syntax,
In this case, idxcustname is the index name. We may put any name there, but it's nice if it's relevant to the index.
Warning!!!
The index will work when we use queries such as this one,
But it will be pointless if we use queries such as this one,
For varchars, the index will work if the value we specify in the where clause is precisely the same, but Oracle will not use it if there is a "like" or we use a function such as UPPER or LOWER. If we need a function, we need to set up a function-based index such as UPPER(column_name) and use the same in the where clause.
It will still work, but the index is not used in this case, and the time taken to retrieve results will be the usual one as if the index didn't exist at all. The LIKE operator is telling Oracle to search in every row for names with Charlie in the middle; that's why.
Another explanation,
In Oracle PL/SQL, an index is a database object that improves the speed of data retrieval operations on database tables. An index creates an organized data structure that allows for faster data retrieval, akin to how the index of a book helps you find information quickly. It works like a pointer to the data rows, allowing the database engine to avoid scanning the entire table when searching for specific data.
Indexes are particularly beneficial for large tables, where searching through all rows sequentially can be time-consuming. However, it's essential to note that indexes come with their own overhead: they require additional storage space and must be updated whenever the table data changes, which can impact the performance of data modification operations (inserts, updates, and deletes).
Here's a simple explanation of how indexes work, along with an example:
How Indexes Work:
An index is created on one or more columns of a table. When you perform a search (e.g., using the SELECT statement with a WHERE clause), the database engine can use the index to quickly locate the rows that match the search criteria. This is much faster than scanning the entire table.
Example:
Let's say you have a table named employees with columns employee_id, first_name, last_name, and hire_date. You frequently query this table to retrieve employee data by their employee_id. You can create an index on the
column to speed up these searches.
-- Creating an index on the employee_id column CREATE INDEX idx_employee_id ON employees(employee_id);
Now, when you run a query like:
SELECT * FROM employees WHERE employee_id = 101;
The database engine can use the idx_employee_id index to quickly locate the row with employee_id equal to 101, instead of scanning the entire table.
Remember that while indexes enhance search performance, they can slow down data modification operations because the index needs to be updated whenever the table data changes. Therefore, balancing the number of indexes and the frequency of data modifications is crucial.
In summary, indexes are essential tools in database optimization. They significantly improve the speed of data retrieval operations by allowing the database engine to quickly locate the required data rows. However, planning and managing indexes carefully is essential to ensure they enhance overall performance rather than hinder it.
I hope you all have an excellent day!!!
