Indexes are used to access data in the table faster.  A book is like a table of contents.

When searching for a topic in the book, we can quickly find out the page number of the topic we’re looking for from the table of contents.

If the book doesn’t have a table of contents, we’ll cycle through the pages until we find the topic we’re looking for.  The indexes in our database are the same.  Stores a row in our table and the ROWID information that corresponds to that row.

When a data is searched in a table, Oracle first checks the presence of the index.  If there is no index, it reads all the records in the table until you find the desired record.  This is known as FULL TABLE SCAN

It is not correct to use statements such as “If data is too fast if an index exists” or “Index must be created in each table”.

When we encounter a low-performing SQL, the first thing we usually check is that the index we’ve used in the WHERE condition of the query is indexed?

At the same time, the selectivity of the data of the column in the WHERE condition is important for us.

Creating an index is a costly process.  Sometimes the index on one column provides super performance for one query, while the other 3-4 queries can cause serious performance problems.

When creating an index, it is necessary to consider what kind of index to use, which column or columns of the table to be created, NOLOGGING, compression, uniqueness, which tablespace it will be in, the effect on queries, the effect on DML statements, and so on.

Briefly talking about the things we need to consider when creating an index,

• When create index a column, we need to check if other queries are affected.
• We must select the correct index type for example, instead of creating a B * tree index, creating a bitmap index is not correct.
• It is also a good idea to create a separate tablespace for indexes.
• When creating primary key and unique key constraints, let’s specify tablespace for the indexes that are created with these constraints.

Detailed information about indexes can be found by querying dba_ind_columns, all_ind_columns, user_ind_columns, dba_indexes, all_indexes, and user_indexes views.

Creating an Index

Indexes are generally created with the CREATE INDEX clause.  The user who creates the Index must have CREATE ANY INDEX system privilege.

B-tree Index

The default index type in the Oracle database is B-tree (Balanced Tree) index.

CREATE INDEX JOB_INDX ON EMPLOYEES(JOB_ID);

The index created in this way is created in the default tablespace of the user doing the operation.  This is not the right method a separate tablespace should be provided as follows,

CREATE INDEX JOB_INDX ON EMPLOYEES(JOB_ID) TABLESPACE HR_INDEX;

The B-tree index is similar to the tree structure as in the picture.

When Oracle wants to access the index, it starts with the outermost root block.  The root level determines the second level of the branch block.  Branch block leads to what we call leaf.

This block has the ROWID information required for us.  In this structure, it will need to make 3 readings to access the ROWID information.

After reaching the ROWID information, it will now read the block containing the relevant ROWID information in our table.

NOTE: 

Each row in each table has an address.  Address of the line; datafile number, block number, the location of the row in the block and the object number.  This address is known as Oracle ROWID.

Concatenated Index

We can create indexes on multiple columns.  Multiple column indexes created in this way are called Concatenated Index.

CREATE INDEX EMP_INFO ON HR.EMPLOYEES(FIRST_NAME, LAST_NAME) TABLESPACE HR_INDEX;

In this case, we can use our index with the following query,

SELECT * FROM HR.employees WHERE FIRST_NAME='Donald' AND LAST_NAME='OConnell';

Function-Based Index

The index is not used if the column from which an index is created uses mathematical or character functions in the WHERE condition.

In order to use the index, it must be function-based.

To make an example,

CREATE INDEX EMP_X1 ON HR.EMPLOYEES(FIRST_NAME) TABLESPACE HR_INDEX;

Run the query and look at the execution plan,

set autotrace traceonly

SELECT * FROM HR.EMPLOYEES WHERE lower(FIRST_NAME)='Donald';

Although we create an index, we see that the index is not used and the table is fully scanned (TABLE ACCESS FULL).  This is because we use the lower function.

Let’s create the index as follows and check the work plan again.

CREATE INDEX EMPLOYEE_X2 ON HR.EMPLOYEES(LOWER(FIRST_NAME)) TABLESPACE HR_INDEX;

You can see that Index is used.

Unique Index

By default, Oracle creates an index non-unique in the b-tree index structure.  If the column on which the index will be created contains unique data, it is best to create a unique index here. In this way, access to the data will be more performance.

The data in the column in which the unique index was created must be unique. But it can contain NULL values.

Bitmap Index

Bitmap Indexes are created on columns with many repetitive values. For example, repetitive recordings such as Y and H, YES and NO, M and F have very low selectivity (cardinality, distinct value).

It is correct to use Bitmap index on columns that contain such records.  Bitmap indexes are mostly used in DSS (Decision Support Systems), reporting, data warehouse databases.

Using bitmap indexes in an OLTP database with high INSERT / UPDATE / DELETE operations causes performance problems.

Sample usage,

CREATE BITMAP INDEX EXP_1 ON HR.EMPLOYEES(SEX);

Reverse Key Index

B-tree is like indexes.  When the index is created, the values of the index key are reversed.

For example, if the index values are 102, 203 and 304, the revrese key index values are 201, 302 and 403.

Compressed Index

The use of index compression in indexes with multiple columns can be very useful, especially if the column contains data repetition.

Less disk space and less I / O operation to achieve compressed index.

CREATE INDEX INDX_COMP ON HR.EMPLOYEES (FIRST_NAME, LAST_NAME) COMPRESS 2;

Employees may have the same name or surname. We’ve done compression on the concatenated index.

Using Parallel When Creating Index

We may have problems creating intraday indexes in our large volume tables.  Parallel usage is very useful to minimize index creation time.

CREATE INDEX INDX_4 ON EMPLOYEES (JOB_ID) PARALLEL 4 TABLESPACE HR_INDEX;

The number of parallelism is usually given by the number of processor cores.

Invisible Index

One of the features that come with 11g.  Invisible indexes are not visible by Oracle optimizer.

Especially useful when testing the effect of the index on our queries.  We can make the index invisible at any time and make it visible at any time.

When an index is disabled with UNUSABLE, the index must be rebuilt before it can be reused.  This process takes time.

If an index is invisible and made visible again, the index does not need to be rebuilt.

ALTER INDEX INDX_1 INVISIBLE;

ALTER INDEX INDX_1 VISIBLE;

An index created as invisible, by setting the value of the OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE, will allow the optimizer to consider invisible indexes.

CREATE INDEX INDX_6 ON HR.EMPLOYEES (JOB_ID) INVISIBLE TABLESPACE HR_INDEX;

ALTER SYSTEM SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

SET AUTOTRACE TRACEONLY;

SELECT /*+ INDEX (m INDX_6) */ * FROM HR.EMPLOYEES m WHERE EMPLOYEE_ID=103;

When we run the query, the optimizer will use it even if the index is not visible.

We’re at the end of this article. See you in the next article.

Oracle Virtual Private Database (VPD) -2

Do you want to learn Oracle Database for Beginners, then read the following articles

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/