[jira] Updated: (DERBY-886) allow system to use 2 different access paths for 2 different columns in a select list.

["Mike Matrigali (JIRA)" <de...@db.apache.org>]

     [ http://issues.apache.org/jira/browse/DERBY-886?page=all ]

Mike Matrigali updated DERBY-886:
---------------------------------

    Description: 
"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. 



  was:

"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. 




a possible workaround was suggested, not sure if it has been verified:
For your original problem with min() and max() in one query, you may be
able to use the following query as a work-around.

Select min(myid), max(myid) from (select min(id) from Customer union all
select max(id) from Customer) myTab(myid);

> 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:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira