SQL2 Query

[vishwanath881]

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

[Kunal_Gaba_ · Accepted solution]

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. 

[smacdonald2008]

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

[vishwanath881]

I was only concerned about  the warning message

[vishwanath881]

Thanks Kunal for the detailed explanation. Appreciate it.