On 2/12/12 9:10 AM, anthonyri wrote:
I have a table with and index and yet the optimiser chooses a full table scan
instead of using it - why does this happen? Can I force it to use the index?
create table test(id smallint, seq smallint, type smallint);
select id, seq, type from test where type< 1000;
Hi Anthony,
It is hard to say without knowing what the index looks like and without
seeing the query plan for this SELECT statement. Two frequent causes of
this problem are:
1) The optimizer statistics on the table are stale. You can refresh the
statistics on a table by calling the SYSCS_UTIL.SYSCS_UPDATE_STATISTICS
procedure. See
http://db.apache.org/derby/docs/10.8/ref/ref-single.html#rrefupdatestatsproc
2) The index does not carry all of the columns needed to evaluate the
query, requiring a probe of the base table in order to pick up the other
columns. Based on the table statistics, the optimizer may decide it's
cheaper to just scan the whole base table end to end.
It sounds like you have seen the query plan, so you are probably
familiar with the optimizer material in the Derby Tuning Guide:
http://db.apache.org/derby/docs/10.8/tuning/
Hope this helps,
-Rick
