SQL Index – Organizing and Locating Data

SQL indexes may be comparable to the index for a phone directory, because indexes in SQL utilize pointers and fields of data in order to help locate information in a database; similarly, phone directories use categories indicated with page numbers and sections identifiers to organize information. In addition to pointers, the index of a database also incorporates the Data Dictionary. When you view information in a table, such as by using a SELECT statement, you can see information for your table based on the data retrieved by the query. Information that you may see for your table could include the column names and the field values. The output of a query is somewhat of a basic view of a database as there are actually many levels of information and characteristics involved with the information and functions used for databases. A Data Dictionary helps manage the structure of a database which entails information for table views, columns in a table, primary keys for a table, etc. Pointers may be described as being variables that can dynamically change locations within a database, pointers may point to specific locations in tables within a database in order to enable one to access queried information.

Have you considered using SQL queries for reporting purposes? If so, then take a class on Udemy.

Pointers involve factors within memory management for databases. Without going in depth in detail of complex algorithms and other processes used for buffer caches, library caches, data dictionary caches, cursors (or pointers), and other factors involved with database memory management; I will briefly explain that an index of a database is able to locate and help retrieve information about tables in a database in a real-time manner due to the incorporation of I/O (input and output) operations, cached data, measurements of processing involved with operations, pointers, session logs, and other variables.

Creating an Index

The basic syntax for creating an Index is as follows:

CREATE INDEX <index name>
ON
<table name><specified columns(ordered) for the index>

For example, this is how you could create an index for a table named Student_Tbl:

CREATE INDEX student_first_last_class_idx
ON
Student_Tbl (STUDENT_FIRST, STUDENT_LAST, CLASS_ID);

To delete an index, you can use a DROP INDEX statement.

Examples of Data Dictionary Tables

Tables that a Data Dictionary may use include, and are definitely not limited to, the following: user_tab_columns, v$session_event, dba_tables, dba_types, dba_mview_keys, all_tab_comments, and user_views. Each table in the Data Dictionary contains indexed information. The user_tab_columns table contains columns such as column_id, column_name, data_type, data_length, and nullable.

You can use a SELECT statement along with columns from a Data Dictionary table and the Data Dictionary Table name to locate information for an index that had been created for a table. You can decide which columns from the Data Dictionary table that you would want to use. For instance; if you have table named Student_Tbl, then you could use a SELECT statement along with the user_tab_columns Data Dictionary table, such as the following:

SELECT
COLUMN_ID,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
NULLABLE
FROM USER_TAB_COLUMNS
WHERE
TABLE_NAME = ‘Student_Tbl’
ORDER BY COLUMN_ID;

Output:

As you can see, the Student_Tbl table contains only three columns, which are for the student first names, last names, and id numbers. NULL values are allowed for all fields with the exception of the fields for the Class_Id column.

The following is the data that your table contains:

SELECT * FROM Student_Tbl;

Tables within the Data Dictionary that will enable you to locate information about indexes that have been created for tables include user_indexes and user_ind_columns. Columns included in the user_index table include such columns as index_name, uniqueness, column_name, tablespace_name, and table_name. Columns included in the user_ind_columns table include such columns as index_name, column_name, column_position, and table_name.

For example; you could use a SELECT statement for Student_Tbl (same table used for the previous example) along with the user_indexes Data Dictionary table, such as the following:

SELECT
INDEX_NAME,
TABLE_NAME,
TABLESPACE_NAME
FROM USER_INDEXES
WHERE
TABLE_NAME = ‘Student_Tbl’;

Output:

Why Creating an Index Can Help With Searches and Other Tasks

A database component that is essential with retrieving information as efficiently as possible is called an Optimizer. The Optimizer uses algorithms and other analytical tools in order to strategically handle the way in which searches are conducted. For instance, when you use a SELECT statement, some of the “background processes” being conducted include those conducted by the Optimizer. An Optimizer will include indexes in its analysis, which is one reason indexes can be very helpful with searches and other data retrieval or processing tasks.

Would you like to learn more about the inner workings of databases? Take a Udemy class.

Considerations

Although indexes can help with the efficient processing of some tasks on some databases, there are cases in which indexes can cause processes to become slower. As discussed earlier, indexes utilize variables involved with memory management as well as other variables that assist in enabling pointers to detect where data is located and to be able to update as other information for a database updates. If a database is being updated, then the indexes will also have to update; this will require more resources for processing and storage for the updates. Pointers will have to be reassessed as the locations and types of data change.

I compare the concept of the indexes updating as changes are made within a database to the concept of more tasks having to be performed by a disk defragmentation tool when many changes have been made to a file system. Database administration should typically include determining which tables and columns should use indexes.

Are you interested in finding more information for database administration? Take a Udemy class.

Examples of Indexes Created for Searches

Consider the following SELECT statement:

SELECT
STUDENT_ID,
STUDENT_FIRST,
STUDENT_LAST,
CLASS_ID,
ENROLLMENT_DATE
FROM
Class_Tbl
WHERE
ENROLLMENT_DATE >= ’01-JAN-2011’
AND
ENROLLMENT_DATE <= ’31-DEC-2012’
ORDER BY
STUDENT_LAST,
ENROLLMENT_DATE,
CLASS_ID;

As opposed to a query reading the entire table and then the Optimizer determining the best search method to use, an index can be created on the Class_Tbl for the Enrollment_Date column in order to add an index option for the Optimizer.

The following is an index that could be created for the Class_Tbl table:

CREATE INDEX class_enrollment_date_range_idx
ON
Class_Tbl (ENROLLMENT_DATE);

Conclusion

Indexes created for databases can greatly assist with data lookups and other tasks. There are many processes that take place that enable queries to be performed and data to be updated. Considerations for factors such as I/O measurements and frequency of updates for tables should be taken before creating an index for a table. Indexes that are created as tables are created as well as indexes that have been created after a table has been created assist with organization and performance tuning.