aspose file tools*
The moose likes JDBC and the fly likes Index Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Index" Watch "Index" New topic
Author

Index

David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724
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


SCJP<br />SCWCD <br />ICSD(286)<br />MCP 70-216
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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 ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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 ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724

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


Why?
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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 ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

I found one URL Here


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

probably you will find answer
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

one more is here
David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724
Thanks a lot.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Index
 
Similar Threads
strange searching
WA #1.....word association
select vs count(*) - Performance Comparison
Alternative to String.split()
JButton onClick change background color