db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Karl Wright (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5142) Optimizer uses table scan when it could use index when multiple OR clauses
Date Wed, 23 Mar 2011 14:03:05 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5142?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13010128#comment-13010128
] 

Karl Wright commented on DERBY-5142:
------------------------------------

I'm using 10.7.1.1 plus a patch for the DERBY-5072 issue I also reported.


> Optimizer uses table scan when it could use index when multiple OR clauses
> --------------------------------------------------------------------------
>
>                 Key: DERBY-5142
>                 URL: https://issues.apache.org/jira/browse/DERBY-5142
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL, Store
>            Reporter: Karl Wright
>         Attachments: repro5142.diff
>
>
> The Derby optimizer doesn't seem to recognize that a query like this:
> SELECT id,distance,linktype FROM hopcount WHERE (jobid=? AND linktype=? AND parentidhash=?)
OR (jobid=? AND linktype=? AND parentidhash=?)
> ... might be best planned by using an index declared on hopcount as (jobid,linktype,parentidhash).
 Instead, a table scan is always used, no matter how big the table.  Other databases have
no trouble with constructs like this.
> This is a very common situation, and blocks Apache ManifoldCF from using Derby as its
primary database choice.
> I've verified that the index IS successfully used with the same table statistics when
the query has only ONE clause, e.g.:
> SELECT id,distance,linktype FROM hopcount WHERE (jobid=? AND linktype=? AND parentidhash=?)


--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message