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

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Component/s: SQL
• Labels: derby_triage10_11
• Urgency: Low
• Assignee: Unassigned
• Reporter: Mike Matrigali
• Created: 28/Jan/06 05:36
• Updated: 25/Jun/13 15:03

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.

Issue Links

• relates to: DERBY-6226 enhance optmizer to use multiple probes into multiple indexes to satisfy OR queries on different columns.