[jira] [Updated] (DERBY-5142) Optimizer uses table scan when it could use index when multiple OR clauses

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-5142?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-5142:
---------------------------------

           Component/s: Store
                        SQL
      Issue & fix info: [Repro attached]
    Bug behavior facts: [Performance, Seen in production]  (was: [Performance])

> 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