Jeffrey Lichtman wrote:
1) "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. Why?
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.
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?
2) "SELECT * FROM Customer ORDER BY Id" is fast but "SELECT * FROM
Customer ORDER BY Id DESC" is slow. Why? Can't it scroll the index
backwards?
The structure could support this, the work just has not been done. With
row level locking, concurrent tree splitting, and current assumptions
throughout the access method that scans go top/down, left to right the
work to do a backward scan is harder than doing a forward scan. Also
once the store portion is done, there would be necessary changes to:
optimizer, execution engine, and scan interface to allow the backward
scan. This would be a hard first project.
The current workaround is that you can create both an ascending and
a descending index if you need ordering in both directions to go fast.
You should check out the access/btree white papers Dibyendo (sp?)
produced, on the web site.
I will go ahead and log an enhancement in JIRA (DERBY-884)
Nope - Derby doesn't do backwards scrolling. I think the physical data
layout could support it (i.e. there are backwards pointers), but
backwards scrolling has never been implemented. Could someone more
familiar with the store confirm this (Mike?).
- Jeff Lichtman
[EMAIL PROTECTED]
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
