my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes Optimizing Queries by Looking @ SQL Plans Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Optimizing Queries by Looking @ SQL Plans" Watch "Optimizing Queries by Looking @ SQL Plans" New topic

Optimizing Queries by Looking @ SQL Plans

Udit Manektala
Ranch Hand

Joined: Sep 17, 2006
Posts: 30
So Im trying to optimize this Sybase SQL query to perform faster. I did a show plan and looked for all "Table Scans" in the Plan. One by one I eliminated them all by providing index hints in the SQL itself ((index xyz_idx1).

This eliminated all the Table Scans but the perfoemance is the same. Now all of them are forward scans.

So whats the difference between a forwad scan and a table scan ?
All I knew was that Table Scans were evil. I dont really know how to interpret the elements of a SQL plan. Is there any online resource or book available that can explaion the various elements of a SQL plan ?

And finally, if a forward scan is bad performace-wise, then how do I really fix it?... im already forcing it to use the available indexes.
Bill Cruise
Ranch Hand

Joined: Jun 01, 2007
Posts: 148
A table scan is just what it sounds like. The query has to scan through every record in the table to find ones matching your criteria. An index keeps track of the records that match certain criteria. There are guidelines for creating indexes that you can find in your Sybase docs. Basically, it will tell you what types of indexes are useless because some types of data don't lend themselves very well to being indexed (dates and strings). For best results you want to index columns that have a few unique values.

A forward scan indicates that the query is using an index. It is slightly better than a reverse scan. A forward scan means the scan is in the order specified at the time the index was created.

I've had success using the SQL Cookbook from O'Reilly when optimizing SQL.
I agree. Here's the link:
subject: Optimizing Queries by Looking @ SQL Plans
It's not a secret anymore!