Indexes are schema objects that can speed access to table rows. Index-organized tables are tables stored in an index structure.

This chapter contains the following sections:

• Introduction to Indexes
• Overview of B-Tree Indexes
• Overview of Bitmap Indexes
• Overview of Function-Based Indexes
• Overview of Application Domain Indexes
• Overview of Index-Organized Tables

red hat linux administration training courses malaysia

Introduction to Indexes

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access.

red hat enterprise linux rhel training courses malaysia

Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, you can drop or create an index without physically affecting the indexed table.

red hat certified specialist in server hardening malaysia

Note

If you drop an index, then applications still work. However, access of previously indexed data can be slower.

For an analogy, suppose an HR manager has a shelf of cardboard boxes. Folders containing employee information are inserted randomly in the boxes. The folder for employee Whalen (ID 200) is 10 folders up from the bottom of box 1, whereas the folder for King (ID 100) is at the bottom of box 3. To locate a folder, the manager looks at every folder in box 1 from bottom to top, and then moves from box to box until the folder is found. To speed access, the manager could create an index that sequentially lists every employee ID with its folder location:

CopyID 100: Box 3, position 1 (bottom)
ID 101: Box 7, position 8 
ID 200: Box 1, position 10
.
.
.

Similarly, the manager could create separate indexes for employee last names, department IDs, and so on.

This section contains the following topics:

• Benefits of Indexes
• Index Usability and Visibility
• Keys and Columns
• Composite Indexes
• Unique and Nonunique Indexes
• Types of Indexes
• How the Database Maintains Indexes
• Index Storage

red hat certified specialist in linux performance tuning malaysia