SQL2 Query | Community
Skip to main content
V
vishwanath881
New Participant
March 4, 2016
Solved

SQL2 Query

  • March 4, 2016
  • 4 replies
  • 1628 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Kunal_Gaba_

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. 

4 replies

V
vishwanath881Author
New Participant
March 5, 2016

Thanks Kunal for the detailed explanation. Appreciate it.

V
vishwanath881Author
New Participant
March 4, 2016

I was only concerned about  the warning message

smacdonald2008
smacdonald2008
New Participant
March 4, 2016

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

Kunal_Gaba_
Kunal_Gaba_Accepted solution
New Participant
March 4, 2016

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. 

Terms & ConditionsAccessibility statement

Loading footer...