db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-886) allow system to use 2 different access paths for 2 different columns in a select list.
Date Fri, 27 Jan 2006 20:37:08 GMT
allow system to use 2 different access paths for 2 different columns in a select list.

         Key: DERBY-886
         URL: http://issues.apache.org/jira/browse/DERBY-886
     Project: Derby
        Type: Improvement
  Components: SQL  
    Reporter: Mike Matrigali
    Priority: Minor

"SELECT MIN(Id) FROM Customer" and "SELECT MAX(Id) FROM Customer" are both fast, but "SELECT
MIN(Id), MAX(Id) FROM Customer" is slow, taking 5 seconds.   Derby knows how to use an index
to quickly find a minimum or a maximum (by traversing down one side of the B-tree or the other).
It doesn't know how to do both in the same query, which would take two traversals. There may
be a few other cases in Derby where special access paths are used to return function results
(I think there is some magic for finding if a null exists, but not sure). 

Some notes from discussion on the development list:
> Is the work to fix this the same as making IN list use multiple probes,
> and/or makeing OR lists do multiple probes?  There are existing JIRA
> items for those, or is it different enough to have a separate JIRA?

I believe they're different. While each case would use multiple probes, in the MIN/MAX case
one of the probes would go down the right side of the BTree, while in the IN/OR case it would
do "normal" scans. Also, the MIN/MAX case and the IN/OR case would require different logic
to recognize when the optimizations are possible. The costing logic in the optimizer would
be different, too. It's possible the two cases could share some execution code, but the rest
of it would require different implementations. 

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
For more information on JIRA, see:

View raw message