How many different types of indexes we have in oracle?
Answers were Sorted based on User's Feedback
Answer / gnskannan
Indexes 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
Is This Answer Correct ? | 83 Yes | 28 No |
Answer / siva
There are four reguler types of Indexes in oracle
1.Normal index
2.Bitmap indexs
3.Function based indexs
4.Domain indexes.
The custom and recommended types are of 2 types
Implicit index and Explicit index.
Is This Answer Correct ? | 39 Yes | 8 No |
Answer / ananthram
Just and Addition to the above discussion.
I cant agree completely with answer #3 when it says
"This index is not Unique index "
Because Oracle makes sure that the uniqueness is maintained
in case of Primary and unique key by imposing UNIQUE INDEX
on it.
So I find it difficult when someone says that the implicit
indexes created by oracle while the creation of PK and UK
are not UNIQUE indexes.
Is This Answer Correct ? | 32 Yes | 9 No |
Answer / ron
some more additions to the above:
context indexes etc. you can read about it here:
http://www.databasedesign-resource.com/oracle-indexes.html
hope this helps.
Is This Answer Correct ? | 42 Yes | 31 No |
Answer / satya krishna
B-TREE INDEX, BITMAP INDEX, REVERSE INDEX, FUNCTION-BASED INDEX, ASCENDING & DESCENDING INDEX, Cluster indexes, bitmap join indexes, text indexes, Hash cluster indexes, DOMAIN INDEXES.
Indexes are logically and physically independent of the data in the associated table.
ONCE AFTER CREATING THE INDEX ORACLE WILL AUTOMATICALLY HANDLE IT LATER.
Is This Answer Correct ? | 12 Yes | 1 No |
Answer / surya
some more additions to the above:
Reverse key index
Is This Answer Correct ? | 18 Yes | 8 No |
total indexes are in oracle is 5
1.b tree
2.bitmap
3.function_based
4.domain
5.ascending or descending
Is This Answer Correct ? | 27 Yes | 17 No |
Answer / venkat rao
Normal index
Bitmap index
partitioned index
Function based index
Domain index
composite index
Is This Answer Correct ? | 8 Yes | 1 No |
What does `(+)` do in a where clause?
In what script is snap$ created? In what script is the scott/tiger schema created?
What is a package ? What are the advantages of package ?
How to manage transaction isolation level?
Are truncate and delete commands same? If so why?
how to join query for one source type is oracle another source type is sql server ?
What do you mean by merge in oracle and how can we merge two tables?
What is raw datatype in oracle?
Assuming that you are an End User How to find that in the payment Batch some of the Invoice was Missing To pay How to find That??
How to assign values to variables?
Can any one explain me when i execute below query.. select months_between('07-JUL-12','10-FEB-12') from dual; Out put:- 4.90322581 How oracle calculate?
Table1- have two column filename data AFGDFD-20112011 hi how r u bsdasd-23042011 name shoud be in bold Now i want output like filename data AFGDFD hi how r u bsdasd name shoud be in bold Kindly answer this