[jira] [Commented] (OAK-3807) Oracle DB doesn't support lists longer than 1000

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

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

Thomas Mueller commented on OAK-3807:
-------------------------------------

The problem with "XYZ in (?,?,?,?,?,?,?,?)" is that the number of entries is not always the same. Let's say we have 12456 entries. If we use a maximum of 1000, what we can do is call it 12 times with 1000 entries each, and once with 456 entries. So we have created two prepared statements: one with 1000 entries, and one with 456 entries. The next time, we have 5678 entries, so we call it 5 times with 1000 entries and once with 678 entries. If you try to work around the limit of 1000 for Oracle, you actually make it worse.
 
In many cases, batch updates can be used instead of "XYZ in (?, ?, ?,...)". That way, you only have one statement: "XYZ = ?", and then use a batch update. Batch updates should be as fast as using "XYZ in (?, ?, ?,...)".

Some databases (PostgreSQL, H2) support other ways to do "XYZ in (?, ?, ?,...)" without having to use batch updates. For H2, you can use an inner join with the "table(...)" function. For PostgreSQL, I believe (not sure how do to it exactly) you can use "XYZ in (?)" and then set the parameter as an array. But I don't know of a way that works with many databases.

> Oracle DB doesn't support lists longer than 1000
> ------------------------------------------------
>
>                 Key: OAK-3807
>                 URL: https://issues.apache.org/jira/browse/OAK-3807
>             Project: Jackrabbit Oak
>          Issue Type: Technical task
>          Components: rdbmk
>    Affects Versions: 1.2.9, 1.0.25, 1.3.12
>            Reporter: Tomek Rękawek
>            Assignee: Julian Reschke
>             Fix For: 1.4
>
>         Attachments: OAK-3807-test.patch
>
>
> There's the ORA-01795 error in Oracle. It's description is:
> {quote}
> ORA-01795 maximum number of expressions in a list is 1000 error
> {quote}
> We should review the RDBBlobStore and RDBDocumentStoreJDBC and split lists longer than 1000 elements to chunks.
> For instance, if the limit would be 3, then our lists can be split as follows:
> {code}
> ... XYZ in (?,?,?,?,?,?,?,?) // initial version
> ... (XYZ in (?,?,?) or XYZ in (?,?,?) or XYZ in (?,?)) // fixed version
> {code}
> In the patch for OAK-3637 there's {{RDBDocumentStoreJDBC#appendInCondition}} method that appends appropriate expression to the query. It should be used every time we have an {{in}} operator with variable number of placeholders.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)