db-derby-dev mailing list archives

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

     [ 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

Mime
View raw message