tknayak
asked on
index is not being used if not forced..why?
Hi,
I have one table named "resources"
which has 2 nonclustered indexes
1. 1 composite on "dep_num" and "date" , name - idx_date_name
2. 1 on single on "id" - idx_id
when i use the query as
select * from resources where id='xyz' sybase optimizer is ignoring the index 2.
where as if I use the force index option as in
select * from resources (index idx_id) where id='xyz'
it uses the index properly. what could be the problem? the table has around 15 mn rows.
I have one table named "resources"
which has 2 nonclustered indexes
1. 1 composite on "dep_num" and "date" , name - idx_date_name
2. 1 on single on "id" - idx_id
when i use the query as
select * from resources where id='xyz' sybase optimizer is ignoring the index 2.
where as if I use the force index option as in
select * from resources (index idx_id) where id='xyz'
it uses the index properly. what could be the problem? the table has around 15 mn rows.
Supporting Jan_Franek -
Yes, this could be explainable by lack of update statistics. We will need to see the exact method you're using for this, for example if you were passing in a local variable and asking WHERE id=@local_var, we wouldn't expect the index to be used there.
Has the index been used before and only recently stopped, or has it never been used?
Another thing to check is datatypes. These limits are being relaxed in every major release of Sybase, but it's possible that some differences here could prevent an index. So long as you're on a recent version, comparing CHAR to VARCHAR isn't an issue, but NUMERIC(8,0) is not the same as NUMERIC(11,0), for instance.
Yes, this could be explainable by lack of update statistics. We will need to see the exact method you're using for this, for example if you were passing in a local variable and asking WHERE id=@local_var, we wouldn't expect the index to be used there.
Has the index been used before and only recently stopped, or has it never been used?
Another thing to check is datatypes. These limits are being relaxed in every major release of Sybase, but it's possible that some differences here could prevent an index. So long as you're on a recent version, comparing CHAR to VARCHAR isn't an issue, but NUMERIC(8,0) is not the same as NUMERIC(11,0), for instance.
ASKER
no.. this is a standalone sql. and index has never been used. even after recreating the index.
Hmm! Creating the index on a table containing data generates default statistics on the leading column of the index, and if it's standalone SQL exactly as you showed us above, it doesn't seem datatypes could be an issue.
Can we get some more detailed information from you?
If you run :
dbcc traceon(3604, 302)
go
set showplan on
set noexec on
go
select * from resources where id='xyz'
go
set noexec off
go
set showplan off
go
dbcc traceoff(3604,302)
go
you will get a showplan (to confirm the index isn't being used), and the output of traceflag 302, which will explain the costing the optimiser used (ie. why it didn't think the index was useful). There will be a lot of output!
There are still circumstances where it might be correct to not use this index. If every row had 'xyz' as it's value in the id column, a table scan is more effective than an index scan, for instance. But that's getting fairly contrived.
Can we get some more detailed information from you?
If you run :
dbcc traceon(3604, 302)
go
set showplan on
set noexec on
go
select * from resources where id='xyz'
go
set noexec off
go
set showplan off
go
dbcc traceoff(3604,302)
go
you will get a showplan (to confirm the index isn't being used), and the output of traceflag 302, which will explain the costing the optimiser used (ie. why it didn't think the index was useful). There will be a lot of output!
There are still circumstances where it might be correct to not use this index. If every row had 'xyz' as it's value in the id column, a table scan is more effective than an index scan, for instance. But that's getting fairly contrived.
My one cent,
How many rows do you have in that table if there are fewer no of data pages optimiser will ignore the indexes.
Cheers,
Mak
How many rows do you have in that table if there are fewer no of data pages optimiser will ignore the indexes.
Cheers,
Mak
ASKER
as I have pointed out in the question the table has around 15 million rows.
Joe_Woodhouse .. I dont have sa permission on that to run dbcc command. so could not try your method.
Joe_Woodhouse .. I dont have sa permission on that to run dbcc command. so could not try your method.
One more idea: what type is column id ? Try to explicitly convert search criteria to the same type and see if the optimizer will use correct index.
Oh! Sorry, yes, even dbcc traceon needs SA privileges, apologies.
Is the id column unique in the table? If so, has the index been created unique? Unique indexes are always more attractive and helpful to the optimiser.
Generally the optimiser is somewhat pessimistic about nonclustered indexes.
Hmm, a quick experiment... try :
set statistics io on
go
select * from resources where id='xyz'
go
select * from resources (index idx_id) where id='xyz'
go
Can we confirm that there are indeed fewer I/Os using this index?
Then
Is the id column unique in the table? If so, has the index been created unique? Unique indexes are always more attractive and helpful to the optimiser.
Generally the optimiser is somewhat pessimistic about nonclustered indexes.
Hmm, a quick experiment... try :
set statistics io on
go
select * from resources where id='xyz'
go
select * from resources (index idx_id) where id='xyz'
go
Can we confirm that there are indeed fewer I/Os using this index?
Then
Sorry, ignore the last line "Then" is last comment.
Yes, Jan's idea is useful. Can we see the output of
sp_help resources
Yes, Jan's idea is useful. Can we see the output of
sp_help resources
ASKER
> select * from resources where id='xyz'
Table: Inventory scan count 1, logical reads: (regular=275036 apf=0 total=275036), physical reads: (regular=8 apf=25461 total=25469), apf IOs used=25461
Total writes for this command: 0
> select * from resources (index idx_id) where id='xyz'
Table: Inventory scan count 1, logical reads: (regular=14 apf=0 total=14), physical reads: (regular=3 apf=0 total=3), apf IOs used=0
id is a char(9) data type field.
idx_id nonclustered located on default id
Table: Inventory scan count 1, logical reads: (regular=275036 apf=0 total=275036), physical reads: (regular=8 apf=25461 total=25469), apf IOs used=25461
Total writes for this command: 0
> select * from resources (index idx_id) where id='xyz'
Table: Inventory scan count 1, logical reads: (regular=14 apf=0 total=14), physical reads: (regular=3 apf=0 total=3), apf IOs used=0
id is a char(9) data type field.
idx_id nonclustered located on default id
Ok, that's pretty conclusive that we should be using the index!
Is the id field unique? If so, dropping and recreating the index as unique could make the difference.
Is there a reason you don't have a clustered index? If id is unique, dropping the index and recreating it as unique and clustered would be a big improvement.
Lastly, which version of ASE is this?
Is the id field unique? If so, dropping and recreating the index as unique could make the difference.
Is there a reason you don't have a clustered index? If id is unique, dropping the index and recreating it as unique and clustered would be a big improvement.
Lastly, which version of ASE is this?
ASKER
no. the id is not unique. but out of 15 million rows there are only 9000 different values for "id" is possible. Thats the reason I can not use clustered index.
ASE version is Adaptive Server Enterprise/11.9.2.6
surprisingly the same version does not give me any problem in another database, i.e., its using the index even if i dont force it.
ASE version is Adaptive Server Enterprise/11.9.2.6
surprisingly the same version does not give me any problem in another database, i.e., its using the index even if i dont force it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
Split Joe_Woodhouse, Jan_Franek
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
leonstryker
EE Cleanup Volunteer
I will leave the following recommendation for this question in the Cleanup topic area:
Split Joe_Woodhouse, Jan_Franek
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
leonstryker
EE Cleanup Volunteer
How exactly are you using this query ? Is it part of stored procedure or some script ? If yes, please post it.