db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Why does the query optimiser not use the tables index?
Date Mon, 13 Feb 2012 14:17:30 GMT
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

Mime
View raw message