Posted by: songbard | August 23, 2008

Sybase :: forcing index in a SQL query

Sometimes, it becomes necessary to force an index in a query when optimizer does not follow indexing structure goes for a full table scan.

Supposing Indx I1 is indexed on columns EMP_NAME, EMP_PHONE from EMPLOYEE table.

Now the following types of query would go for full table scan:

1. select * from EMPLOYEE where EMP_NAME=<SOMENAME>;
2. select * from EMPLOYEE where EMP_PHONE=<SOMEPHONE>;

3. select * from EMPLOYEE where EMP_NAME in (SOMERANGE) and EMP_PHONE=<SOMEPHONE>;

4. select * from EMPLOYEE where EMP_NAME =<SOMENAME> and EMP_PHONE in (SOMERANGE)

In the above queries if with tablename we specify the index to follow, queries avoid the full table scan.
eg.
select * from EMPLOYEE(index I1) where EMP_NAME =<SOMENAME> and EMP_PHONE in (SOMERANGE).
OR
select * from EMPLOYEE(1) where EMP_NAME =<SOMENAME> and EMP_PHONE in (SOMERANGE).

where 1 is the index no. of I1 for EMPLOYEE table.

But am not aware of any flipside of this approach……

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: