Index In Oracle

We are living on the planet with the seven billion population.Each and every human being’s have some information. Data is an important factor in the business’s world.  Every day in business’s operation we deal with data. It can be structure and un-structure data. All the report of business prepare with the help of data. We take a quick review on how data is really matters on a drill. Data of  Railways, Healthcare, Education, Real state,  Banking, Entertainment industries, research and development etc. plays an important role in the development of a country. To manage data in a bulk amount technical market provide some better solutions to the organization. With the help of best solutions, an organization can preserve its treasure(DATA). Due to the bulk amount of data in any organization. It is laborious for users.

What is Index?

The index is a database structure which is used to improves the speed of data retrieval operations on a database table the cost of additional writes and storage space to maintain the index data structure.

Advantages of the index are mention below:-

  1. Indexes make search queries much faster.
  2. It helps to make a row unique or without duplicates(primary, unique) .
  3. They can be used for sorting. A post-fetch-sort operation can be eliminated.
  4. An index is a physical structure contains pointers to the data.
  5. An index allows the program to find the specific data in a table without scanning the entire table.
  6. An index can be used to avoid sorting the results during execution of the query.
  7. To pre-materialize a sort over the data.
  8. To enable singleton lookups (seeks) for particular values.
  9. To reduce locking requirements caused by scans.

Disadvantages of the index are mention below:-

  1. Indexes take additional disk space.
  2. Indexes slow down INSERT, UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field.  INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.

Keep some points in mind while creating the index.

  1. Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
  2. Index columns used for joins to improve performance on joins of multiple tables.
  3. Create an index if you frequently want to retrieve less than 15% of the rows in a large table.
  4. Small tables do not require indexes.

Types of Index in Oracle

Sno.  Index Type  Usage
1 B-tree  By default best for columns with high cardinality.
2 IOT It is efficient when most of the column values are included in the primary key.
3 Unique A form of B-tree index; used to enforce uniqueness in column values; often used with primary key and unique key constraints but can be created independently of constraints.
4 Reverse key A form of B-tree index; useful for balancing I/O in an index that has many sequential inserts.
5  Key Compressed  Good for concatenated indexes in which the leading column in often repeated ; compresses leaf block entries; applies to B-tree and IOT indexes.
 6  Descending  A form of B-tree index; used with indexes in which corresponding column vaues are sorted in a descending order but by default ascending.
 7  Bitmap  Excellent in data warehouse environments with low cardinality columns and SQL statements using many AND or OR operators in the WHERE clause. Bitmap index are not appropriate for OLTP database in which rows are frequently updated. you can create unique bitmap index.
 8  Bitmap join  Useful in data warehouse enviorments for queries that use star schema structures that join fact and dimension tables.
9 Function based Good for column that have SQL functions applied to them; can be used with either a B-tree or bitmap index.
10 Indexed virtual column An index defined on a virtual column (of a table); useful for columns that have SQL funcations applied to him ; a viable alternative to a function based index.
11 Virtual Allows you to create an index with no physical segment or extents via the NOSEGMENT clause of CREATE INDEX;  useful in tuning SQL without consuming resouces required to build the physical index. Any index type can be created as virtual.
12 Invisible The index is not visible to the query optimizer. However , the structure of the index  is maintained as table data are motdified . Useful for the testing an index before making visible to the application. Any index type can be created as invisible. Any type of index can be invisible.
13 Global partitioned Global index across all partitions in a partitioned  or regular table; can be a B-tree index type and can’t be a bitmap index type.
14 Local partitioned Local index based on individual partitions in a partitioned table; can be either a B-tree or bitmap index type.
15 Domain Specific for an application or cartridge.
16 B-tree cluster Used with clustered tables.
17 Hash cluster Used with hash clusters.
18 Reserve Key A form of B-tree index ; used for  balancing I/O in an index that has many sequential inserts.

 

 

Tagged:

About The Author

"The fragrance always remains in the hand that gives the rose." I am a person who believes in sharing knowledge among all. My Oracle rewards are OCI, OCP, OCA.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.