[jira] [Commented] (OAK-1263) optimize oak index to support 'fast ORDER BY' queries to support sorting & pagination for large set of children

["Thomas Mueller (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OAK-1263?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13839056#comment-13839056 ] 

Thomas Mueller commented on OAK-1263:
-------------------------------------

There are two issues, one is efficient "order by" and the second is efficient "offset".

As for efficient "order by", using an index to avoid sorting would be needed, but as you wrote currently child nodes are not ordered in the node store and microkernel API. We would need to either change this, or use a special index implementation that provides reading the data in sorted order. Plus we would need to change the query index interface.

As for efficient "offset", with high values for the offset (offset 10000) and so on, to avoid having to read all previous entries: the best solution I know is counted b-trees, however I only know one key-value store that supports it (the MVStore of the H2 database). However, I'm not aware of any relational database that supports it, and MongoDB also doesn't: https://jira.mongodb.org/browse/SERVER-7745 . So counted b-trees are not an option I think. Are there other options? Please note we have the additional problem of checking access rights.

> optimize oak index to support 'fast ORDER BY' queries to support sorting & pagination for large set of children
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: OAK-1263
>                 URL: https://issues.apache.org/jira/browse/OAK-1263
>             Project: Jackrabbit Oak
>          Issue Type: Improvement
>          Components: query
>    Affects Versions: 0.12
>            Reporter: Stefan Egli
>
> We have a use case where we'd like to be able to use an index in a "pagination-like" fashion. That is, we'd like to be able to have an index on a subtree on a certain property, and then run a query which does ORDER BY that property combined with OFFSET. That way, essentially allowing pagination of child nodes of a particular parent based on 'sorted by a certain property'.
> AFAIU currently the oak index is not optimized to support ORDER BY queries in a fast manner. The index keeps 'the child nodes unsorted', ie to process an ORDER BY, the child nodes would have to be 'manually sorted' which can result in bad performance given a large number of child nodes.



--
This message was sent by Atlassian JIRA
(v6.1#6144)