You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2011/01/20 11:20:44 UTC
[jira] Assigned: (DERBY-642) SELECT MAX doesn't use indices
optimally
[ https://issues.apache.org/jira/browse/DERBY-642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reassigned DERBY-642:
----------------------------------------
Assignee: Knut Anders Hatlen
> 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
>
> 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.
-
You can reply to this email to add a comment to the issue online.