oracle index

 

Purpose

Use the CREATE INDEX statement to create an index on:

• One or more columns of a table, a partitioned table, an index-organized table, or a cluster

• One or more scalar typed object attributes of a table or a cluster

• A nested table storage table for indexing a nested table column

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Oracle Database supports several types of index:

• Normal indexes. (By default, Oracle Database creates B-tree indexes.)

• Bitmap indexes, which store rowids associated with a key value as a bitmap

• Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table

• Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.

• Domain indexes, which are instances of an application-specific index of type indextype

  


  
  
  

  ---

  ndexes in Oracle are as follows.....
  
  1.Normal index 
      Whenever we created a column(s) with Primary key or 
  Unique constraints ,Oracle implicitly creates Normal 
  index.This index is not Unique index .Why because those 
  columns already having uniqeness because of Unique or 
  Primary key constriants.
  
  2.Bitmap indexs
  3.Function based indexs
  4.Domain indexes.
   if we want to create Unique indexes we have to use Unique 
  keyword when we are creating indexes.
  
  But if you refer Oracle 9i corporation books you can read 
  as follows...
  
   They gave these statement in wrong.
  
  Indexes can be created in two types
  
  1.Unique indexes(Oracle creates it implicitly whenever we 
  created column(s) with PK or Unique constraints
  
  2. Non unique indexes
    This indexes can be created by developers

  
       
       
  We have mainly two different indexes .
  
  Implicit index and Explicit index.
  
  Explicit indexes are again of many types like
  simple index,unique index,Bitmap index,Functional 
  index,Organisational index,cluster index.
   

  ---