[ https://issues.apache.org/jira/browse/OAK-9345?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17687486#comment-17687486 ]
Perini Nicola commented on OAK-9345: ------------------------------------ A similar issue is occurring for the queries introduced e.g. {code:java} select ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY, DELETEDONCE, VERSION, SDTYPE, SDMAXREVTIME, T_DATA, BDATA from NODES where not (ID like :1 or ID like :2 or ID like :3 ) and (VERSION is null or VERSION < :4 ) {code} > Query performance issue > ----------------------- > > Key: OAK-9345 > URL: https://issues.apache.org/jira/browse/OAK-9345 > Project: Jackrabbit Oak > Issue Type: Bug > Components: query > Environment: DocumentNodeStoreService: RDB > Oracle 18C > Jackrabbit OAK 1.36 > Reporter: Ivar Suits > Priority: Critical > Attachments: repository-config_rdb.json > > > Added over 5 million documents to Jackrabbit OAK after that performance > problems started with following query. Using Oracle DB for > DocumentNodeStoreService It s doing table access full scans. > {code} > select ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY, DELETEDONCE, VERSION, > SDTYPE, SDMAXREVTIME, DATA, BDATA > from NODES where ID > :1 > and ID < :2 > order by ID FETCH FIRST ? ROWS ONLY > {code} > Looks like Oracle DB oes not like ID column index and switches to table > full scan, because ID column is not number. As a solution probably index > hint need to be added and there should index name for ID column. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)