DB Concepts at General Connection dot Com Logo
N A V I G A T I O N
Home
Database Terminology
All about DBMS
Whats New?
 
Indexes and Keys of a Database Management System


What are Keys and Indexes


Indexes :

  1. used for fast retrieval of the (rows of a table by an RDBMS
  2. used to cluster data of a table in a certain order
  3. goal is to improve the performance of an RDBMS


Keys :

  1. used for maintaining referential) integrity constraints
  2. used to uniquely identify a tuple in a table

  • If an index is not defined, the entire table must be searched to satisfy a query criteria
  • An index can be created by the application programmer on any column(s) of the table
  • An index must be created on a primary key, if a column(s) of a table is declared as primary key
  • If the primary key is not indexed then the table may not be accessed by data manipulation operations

 

  • The index file consists of records with each record having two fixed length fields
  •    First field -      the key of the table on which an index is being created
  •     Second field -         consists of a block address, i.e., the address of the wanted tuple of the table in the memory
  • The index space is simple table space containing only the index


Different Kinds of Indexes

Clustered

  • An index in which the physical order and the logical (index) order of the data (key values) is the same
  • The physical order of rows on the database device is the same as the indexed order of the rows
  • It determines the physical order of the data in the table
  • A clustered index can be made unique such that the values on the column(s) on which the index is created, will be unique
  • There can be only one clustered index on a table
  • If duplicate data exists already on the column(s) unique indexes cannot be created


Non-Clustered

  • An index in which the data is not sorted on the key order
  • It stores key values and pointers to the data It is typically created on foreign keys
  • Accessing records with clustered index is faster than that with nonclustered index








© 2010 www.generalconnection.com. All rights reserved. | www.generalconnection.com