From the course: Oracle Database 12c: Advanced SQL

Introduction to indexes - Oracle Database Tutorial

From the course: Oracle Database 12c: Advanced SQL

Start my 1-month free trial

Introduction to indexes

“

- [Instructor] Indexes are oracle schema objects that can be created to improve the performance of data access using SQL queries. Imagine a large sales table which contains millions of rows and say you wish to retrieve the sales records from a specific date using a SQL query, say all sales which have occurred on January 1, 2018. Without indexes, Oracle will have to scan the entire table, millions of rows and all to locate the specific rows that match our required criteria. Remember that a regular table in the Oracle database, also known as a heap table, contains an unordered set of records. So to locate a specific record, based on one or more values, you have to scan the entire table. This can actually be addressed using indexes. An index is a schema object created on a specific set of one or more columns on a specific database table. Indexes are automatically used by the Oracle database to improve performance for certain queries. The Oracle database supports several types of indexes, but in our course we will focus on the default and the most common index type, also known as a B-tree index. Keep in mind that indexes can either be explicitly created by users, using a create index command, which we see in our next video or implicitly created by Oracle when you create primary keys or unique constraints. A B-tree index is a data structure similar in its form to a tree. Let's review a simplified version of how a B-tree index behaves. A B-tree index has two types of blocks, branch blocks for searching and leaf blocks for storing values. Each branch block in the index points to a range of values and each leaf block in the index contains key values that fall in that range. When you create an index on a specific column in your table, for example the employee ID column, and run a query, say to retrieve employee ID number four, what will actually happen is that the query will use the index to return the data. Oracle will traverse the index to find the branch block that is relevant for employee ID four, so for example in our simplified use case, the first branch block because it points to leaf blocks containing values from one to 50 and then proceed to look into the leaf note of the index that is relevant. So in our case it's the leaf note that contains the actual entries for rows that fall between values one and 25 for the employee ID. Once in the leaf note, it will find the relevant value of four and obtain what is known as the Oracle row ID, which is a unique identifier to the physical location of the row in the table. Using the row ID, the query will be able to accurately and directly retrieve the row from the table without having to scan the entire table. Of course this is all an oversimplification of how index access works in Oracle and there are additional performance related benefits and considerations for using indexes in the Oracle database but these are beyond the scope of this course. These aspects of database indexes are usually covered in detail in Oracle performance unit courses. In our course, we focus on Oracle database indexes from a more practical and introductory level. Also it's important to keep in mind that creating indexes on too many of your table columns isn't ideal, as the more indexes you create on your tables, the slower your update, delete and insert commands will be. Each time you insert, delete or update a row in your table, indexes associated with that table will also have to be updated as well. As such, create your indexes wisely. Only on specific table columns that are often used in the where clause of your queries for filtering.

Contents