Indexing in Oracle

INTRODUCTION

Index is an object which can be defined as the ordered list of values of a column or combination of columns used for faster searching and sorting of data.

Oracle assigns a unique identification number ro every record in the database called “ROWID“- Row Identifier. It contains the physical address/location of a record in the database (hard disk).

ROWID is a pseudo-column associated with each and every record of a table and is represented by radix values as shown below:

Figure 1 HR schema result to demonstrate the ROWID.

Additional Information

The ROWID format is in 10 bytes:

  • Bits 1 to 32 : Data Object ID (0-4294967295)
  • Bits 33 to 44: File number inside the tablespace(0-4095)
  • Bits 45 to 64: Block number inside the database file (0-1048575)
  • Bits 65 to 80: Row number inside the block (0-65535)

When printed, each field is displayed in radix 64 (A-Za-z0-9+/)

By default whenever we search for records with the “WHERE” clause, Oracle looks for the desired records sequentially. This takes considerable time for rhe retrieval of records. Therefore, Oracle provides an object called, “Index” which can be used to speed up the searching of records.

Leave a Reply

Releated