[
https://issues.apache.org/jira/browse/DERBY-5142?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-5142:
-----------------------------------
Labels: derby_triage10_10 (was: )
> 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: Improvement
> Components: SQL, Store
> Reporter: Karl Wright
> Labels: derby_triage10_10
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
