[
https://issues.apache.org/jira/browse/DERBY-642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12995383#comment-12995383
]
Knut Anders Hatlen commented on DERBY-642:
------------------------------------------
As to the suggestion with previous key locking for serializable, we still have
the problem that we need to save another position than the one we're trying to
lock. When we have to wait for the previous key lock, it's the current key we
want to save, whereas the locking code will save the key we're trying to lock
(that is, the previous key). So even if we use previous key lock calls to solve
this problem, we'll need to get the code to save the position immediately to
the right of the row that we're waiting for.
> SELECT MAX doesn't use indices optimally
> ----------------------------------------
>
> Key: DERBY-642
> URL: https://issues.apache.org/jira/browse/DERBY-642
> Project: Derby
> Issue Type: Improvement
> Components: Store
> Affects Versions: 10.2.1.6
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Attachments: derby-642-1a.diff, derby-642-1b-withTests.diff
>
>
> I tried running SELECT MAX on an indexed column in a big (8 GB)
> table. It took 12 minutes, which is about 12 minutes more than I
> expected.
> After a bit of investigation, I found out that a full index scan was
> performed because all the rows referenced from the rightmost B-tree
> node were actually deleted.
> Possible improvements:
> 1. Implement backwards scan in the B-trees (this is also suggested
> in the comments in BTreeMaxScan).
> 2. Go up to the parent node and down to the next leaf node on the
> left side, and continue until a valid max value is found. In
> Derby, traversing up in a B-tree is not allowed, but would it be
> ok to go up if the latches were kept on the higher-level nodes in
> the tree? Of course, this would have negative impact on
> concurrency.
> 3. Right-to-left traversal on the leaf level is possible (using
> ControlRow.getLeftSibling()), but will throw an exception if the
> page cannot be latched without waiting. It is therefore possible
> to try a right-to-left search for the max value, and only fall
> back to the left-to-right approach if a conflict arises.
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
