Index

Could someone provide me a hint in using index?
In which situations could index improve performance and in which could it slow down?

Thanks in advance

Improve your performance:
make indices on those columns which are frequently, heavily participate on search conditions.

Slow down your performance:
dont make indices on those columns where DMLs occur frequently.

Tip: index doesn't help in full table scans
[ November 04, 2004: Message edited by: adeel ansari ]

few point must be noted while creating index.

If your where clause uses more than one condition then create composite index instead of index for individual columns.

An example is you have a table tab1 and it has column A,B,C,D,E,F
and you use everytime A,B and C in your where clause so create composite index on columns A,B,C

Not only this it also depends how you are forming your query

If you have query

Select * from tab1 where A= ? and B=? and C=?

then it will work fine

but in
Select * from tab1 where C= ? and A=? and B=? Use of index is not guaranteed.

however index will be used for

Select * from tab1 where A= ? and B=?

but for

select * from tab1 where B=? and C=? use of index is not guranteed.

Anyone Please Correct me if I am wrong for above statements.

An advice to create index tablespace other then tablespace where data of table is lying.
[ November 04, 2004: Message edited by: Shailesh Chandra ]

but in
Select * from tab1 where C= ? and A=? and B=? Use of index is not guaranteed.

Why?

I had read it somewhere but didn't bookmark the link , But As far as I remember I put here right thing

If there is composite index on a table then first column of index must be required in query to use the index .

Therse are few more thing I want to add

When two columns are not unique individually but are unique together, composite indexes might work very well.
For example, although columns A and B have few unique values, rows with a particular combination of columns A and B are mostly unique. Look for WHERE clauses with AND operators.

If all values of a SELECT statement are in a composite index, Oracle does not query the table; the result is returned from the index

I will put the URL as I find it.

thanks
[ November 04, 2004: Message edited by: Shailesh Chandra ]

I found one URL Here

Find for the text >>> ' A composite index is an index that '

probably you will find answer

Originally posted by David Ulicny:

but in
Select * from tab1 where C= ? and A=? and B=? Use of index is not guaranteed.

Why?

better write composite index, instead of just index.

Originally posted by Shailesh Chandra:
An advice to create index tablespace other then tablespace where data of table is lying.

Highly recommended. dont make indices in normal data tablespaces.

one more is here

Thanks a lot.