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] Updated: (DERBY-5142) Optimizer uses table scan when it could use index when multiple OR clauses
Date Thu, 17 Mar 2011 22:20:29 GMT

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

Karl Wright updated DERBY-5142:
-------------------------------

    Description: 
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=?)




  was:
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 served by using an index declared on hopcount as (jobid,linktype,parentidhash).
 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=?)





> 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
>            Reporter: Karl Wright
>
> 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