Are indexes always useful and mandatory on a table or Is Table scan on a table always a bad news for us? To understand what is good for us we need to know the exact purpose of indexes and what is meant by a Table scan? When a user executes a query and the Sybase server has to iterate over each row of each page in a Sybase table then the scan that Sybase server is currently performing is called a Table scan.
In case we have created indexes and used proper SARGs then the Sybase server will pick up an appropriate index and fetch our results more quickly than any Table scan. However, this is just one part of the story and is applicable only when tables are of very large size. So, always create indexes on a table which is of very large size otherwise there are lots of overheads to maintain and keep the indexes up to date in Sybase. In case of smaller tables it is better to be without Indexes than using an Index. So, what to do in case we have some data in the table and have some indexes created on the table and still want to force Sybase to perform a table scan? This is usually required when the data in a table is serialized or sequential and we are sure that first few records is all that we need to iterate on.
So, now knowing that we need a Table scan in Sybase we need to make sure that Sybase server does not use any index while executing the query. This is called forcing a table scan. To force a table scan in Sybase we can use a simple statement like this -
select * from my_table (index 0) where col1 = @col1
As we can see here that we have suggested (index 0) to be used in the above mentioned query? Wondering why and what does (index 0) specify? Well, 0 here refers to the 0th entry in sysindexes table which is actually representing a table scan. Hence by suggesting the index we can force a table scan in Sybase.
In case we have created indexes and used proper SARGs then the Sybase server will pick up an appropriate index and fetch our results more quickly than any Table scan. However, this is just one part of the story and is applicable only when tables are of very large size. So, always create indexes on a table which is of very large size otherwise there are lots of overheads to maintain and keep the indexes up to date in Sybase. In case of smaller tables it is better to be without Indexes than using an Index. So, what to do in case we have some data in the table and have some indexes created on the table and still want to force Sybase to perform a table scan? This is usually required when the data in a table is serialized or sequential and we are sure that first few records is all that we need to iterate on.
So, now knowing that we need a Table scan in Sybase we need to make sure that Sybase server does not use any index while executing the query. This is called forcing a table scan. To force a table scan in Sybase we can use a simple statement like this -
select * from my_table (index 0) where col1 = @col1
As we can see here that we have suggested (index 0) to be used in the above mentioned query? Wondering why and what does (index 0) specify? Well, 0 here refers to the 0th entry in sysindexes table which is actually representing a table scan. Hence by suggesting the index we can force a table scan in Sybase.
No comments:
Post a Comment