Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

SQL2 Query

Avatar

Level 4

Hello Team,

I am using a query 

SELECT p.* FROM [dam:AssetContent] AS p WHERE ISDESCENDANTNODE([/content/dam/abc/def/xyz]) AND  (p.[cq:lastReplicated] >= CAST('2016-03-02T23:00:02.423-08:00' AS DATE) AND p.[cq:lastReplicated] <= CAST('2016-03-03T00:00:02.423-08:00' AS DATE)

to get all the assets that were lastReplicated under the specified path within a particular time range.

But the logs are showing something like below.

03.03.2016 00:00:02.622 *WARN* [pool-7-thread-5] org.apache.jackrabbit.oak.spi.query.Cursors$TraversingCursor Traversed 2000 nodes with filter Filter(query=SELECT p.* FROM [dam:AssetContent] AS p WHERE ISDESCENDANTNODE([/content/dam/abc/def/xyz]) AND  (p.[cq:lastReplicated] >= CAST('2016-03-02T23:00:02.423-08:00' AS DATE) AND p.[cq:lastReplicated] <= CAST('2016-03-03T00:00:02.423-08:00' AS DATE)), path=/content/dam/abc/def/xyz//*, property=[cq:lastReplicated=[[2016-03-02T23:00:02.423-08:00.., ..2016-03-03T00:00:02.423-08:00]]]); consider creating an index or changing the query

Is there something wrong with the above query ?

Thanks

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

There is nothing wrong with the query. The warning which you are seeing in the error log is thrown because the query engine could not find any index definition for the query and thats why it is querying the entire repository and logging this message as a warning to create an index definition to speed up the query execution. Out of the box in AEM there is an index definition created for dam:Asset type which aggregates the child nodes including the jcr:content. So to use that index you can change your query like below -

SELECT p.* FROM [dam:Asset] As s INNER JOIN [dam:AssetContent] AS p ON ISCHILDNODE(p,s) WHERE ISDESCENDANTNODE(p,[/content/dam]) AND  (p.[cq:lastReplicated] >= CAST('2016-03-02T23:00:02.423-08:00' AS DATE) AND p.[cq:lastReplicated] <= CAST('2016-03-03T00:00:02.423-08:00' AS DATE))

You shouldn't see any warnings with the above query. 

View solution in original post

4 Replies

Avatar

Correct answer by
Employee Advisor

There is nothing wrong with the query. The warning which you are seeing in the error log is thrown because the query engine could not find any index definition for the query and thats why it is querying the entire repository and logging this message as a warning to create an index definition to speed up the query execution. Out of the box in AEM there is an index definition created for dam:Asset type which aggregates the child nodes including the jcr:content. So to use that index you can change your query like below -

SELECT p.* FROM [dam:Asset] As s INNER JOIN [dam:AssetContent] AS p ON ISCHILDNODE(p,s) WHERE ISDESCENDANTNODE(p,[/content/dam]) AND  (p.[cq:lastReplicated] >= CAST('2016-03-02T23:00:02.423-08:00' AS DATE) AND p.[cq:lastReplicated] <= CAST('2016-03-03T00:00:02.423-08:00' AS DATE))

You shouldn't see any warnings with the above query. 

Avatar

Level 10

DId you get a result set - or are you only concerned about the WARN message? 

Avatar

Level 4

I was only concerned about  the warning message

Avatar

Level 4

Thanks Kunal for the detailed explanation. Appreciate it.