Subject | Natural plan still used when index exists |
---|---|
Author | Ed Dressel |
Post date | 2005-12-28T16:28:10Z |
IB 1.5 in IB Express.
I have a table where I need the maximum value from. I ran "select
max(LastUpdated) from TableName" SQL against it and got the following
stats:
Plan
PLAN (TAXYEAR NATURAL)
Adapted Plan
PLAN (TAXYEAR NATURAL)
------ Performance info ------
Prepare time = 0ms
Execute time = 15ms
Avg fetch time = 15.00 ms
Current memory = 742,144
Max memory = 877,436
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 392
Then I created an index on LastUpdated:
Create Index TableNameIdx1 on TableName(LastModified);
and when I rerand the select max()... against it, it is still using
the natural plan:
Plan
PLAN (TAXYEAR NATURAL)
Adapted Plan
PLAN (TAXYEAR NATURAL) // Why natural if an index exists?
------ Performance info ------
Prepare time = 0ms
Execute time = 0ms
Avg fetch time = 0.00 ms
Current memory = 812,408
Max memory = 947,700
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 392
Both use the natural plan, bot have the same number of fetches from
the cache. Shouldn't it use the index in the second case? Am I
misunderstanding what is happening?
Thanks
Ed Dressel
I have a table where I need the maximum value from. I ran "select
max(LastUpdated) from TableName" SQL against it and got the following
stats:
Plan
PLAN (TAXYEAR NATURAL)
Adapted Plan
PLAN (TAXYEAR NATURAL)
------ Performance info ------
Prepare time = 0ms
Execute time = 15ms
Avg fetch time = 15.00 ms
Current memory = 742,144
Max memory = 877,436
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 392
Then I created an index on LastUpdated:
Create Index TableNameIdx1 on TableName(LastModified);
and when I rerand the select max()... against it, it is still using
the natural plan:
Plan
PLAN (TAXYEAR NATURAL)
Adapted Plan
PLAN (TAXYEAR NATURAL) // Why natural if an index exists?
------ Performance info ------
Prepare time = 0ms
Execute time = 0ms
Avg fetch time = 0.00 ms
Current memory = 812,408
Max memory = 947,700
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 392
Both use the natural plan, bot have the same number of fetches from
the cache. Shouldn't it use the index in the second case? Am I
misunderstanding what is happening?
Thanks
Ed Dressel