Thanks Dmitriy, The EXPLAIN PLAN
[[SELECT
STORE__Z1.ENTRYID AS __C0_0,
STORE__Z1.ATTRNAME AS __C0_1,
STORE__Z1.ATTRVALUE AS __C0_2,
STORE__Z1.ATTRSTYPE AS __C0_3
FROM "dn".IGNITE_DN DN__Z0
/* "dn".IGNITE_DN.__SCAN_ */
/* WHERE ((DN__Z0.PARENTDN LIKE 'dc=ignite,%')
OR ((DN__Z0.RDN = 'dc=ignite')
AND (DN__Z0.PARENTDN = ',')))
AND (DN__Z0.ENTRYID IN(
(SELECT
AT1__Z2.ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2
/++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =
'subentry' ++/
WHERE AT1__Z2.ATTRVALUE = 'subentry')
UNION
(SELECT
AT1__Z3.ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3
/++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =
'ldapsubentry' ++/
WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry')))
*/
INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE STORE__Z1
/* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX:
ENTRYID = DN__Z0.ENTRYID */
ON 1=1
WHERE (STORE__Z1.ATTRKIND IN('u', 'o'))
AND ((DN__Z0.ENTRYID = STORE__Z1.ENTRYID)
AND (((DN__Z0.PARENTDN LIKE 'dc=ignite,%')
OR ((DN__Z0.RDN = 'dc=ignite')
AND (DN__Z0.PARENTDN = ',')))
AND (DN__Z0.ENTRYID IN(
(SELECT
AT1__Z2.ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2
/* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =
'subentry' */
WHERE AT1__Z2.ATTRVALUE = 'subentry')
UNION
(SELECT
AT1__Z3.ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3
/* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =
'ldapsubentry' */
WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry')))))
ORDER BY 1], [SELECT
__C0_0 AS ENTRYID,
__C0_1 AS ATTRNAME,
__C0_2 AS ATTRVALUE,
__C0_3 AS ATTRSTYPE
FROM PUBLIC.__T0
/* "Ignite_DSAttributeStore"."merge_sorted" */
ORDER BY 1
/* index sorted */]]
Thanks
-Rajesh
On Fri, Feb 2, 2018 at 5:32 AM, Dmitriy Setrakyan <[email protected]>
wrote:
> Rajesh, can you please show your query here together with execution plan?
>
> D.
>
>
> On Thu, Feb 1, 2018 at 8:36 AM, Rajesh Kishore <[email protected]>
> wrote:
>
>> Hi Andrey
>> Thanks for your response.
>> I am using native ignite persistence, saving data locally and as of now I
>> don't have distributed cache, having only one node.
>>
>> By looking at the doc, it does not look like affinity key is applicable
>> here.
>>
>> Pls suggest.
>>
>> Thanks Rajesh
>>
>> On 1 Feb 2018 6:27 p.m., "Andrey Mashenkov" <[email protected]>
>> wrote:
>>
>>> Hi Rajesh,
>>>
>>>
>>> Possibly, you data is not collocated and subquery return less retults as
>>> it executes locally.
>>> Try to rewrite IN into JOIN and check if query with
>>> query#setDistributedJoins(true) will return expected result.
>>>
>>> It is recommended
>>> 1. replace IN with JOIN due to performance issues [1].
>>> 2. use data collocation [2] if possible rather than turning on
>>> distributed joins.
>>>
>>> [1] https://apacheignite-sql.readme.io/docs/performance-and-
>>> debugging#section-sql-performance-and-usability-considerations
>>> [2] https://apacheignite.readme.io/docs/affinity-collocation
>>> #section-collocate-data-with-data
>>>
>>> On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <[email protected]>
>>> wrote:
>>>
>>>> Hi All,
>>>>
>>>> As of now, we have less than 1 M records , and attribute split into
>>>> few(3) tables
>>>> with index created.
>>>> We are using combination of join & IN clause(sub query) in the SQL
>>>> query , for some reason this query does not return any response.
>>>> But, the moment we remove the IN clause and use just the join, the
>>>> query returns the result.
>>>> Note that as per EXPLAIN PLAN , the sub query also seems to be using
>>>> the defined
>>>> indexes.
>>>>
>>>> What are the recommendations for using such queries , are there any
>>>> guidelines, What we are doing wrong here?
>>>>
>>>> Thanks,
>>>> Rajesh
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Best regards,
>>> Andrey V. Mashenkov
>>>
>>
>
package org.kishore.backends.ignite;
import org.apache.ignite.cache.query.annotations.QuerySqlField;
/**
* The DN vo for the dns.
*
*/
public final class Ignite_DN
{
/**
* The id.
*/
@QuerySqlField( index= true)
private Long id;
@QuerySqlField(orderedGroups = { @QuerySqlField.Group(name = "EP_DN_IDX",
order = 0) })
private Long entryID;
public Long getId() {
return id;
}
public Long getEntryID() {
return entryID;
}
public String getRdn() {
return rdn;
}
public String getParentDN() {
return parentDN;
}
@QuerySqlField(orderedGroups = { @QuerySqlField.Group(name = "RP_DN_IDX",
order = 1) })
private String rdn;
@QuerySqlField(orderedGroups = {
@QuerySqlField.Group(name = "EP_DN_IDX", order = 1),
@QuerySqlField.Group(name = "RP_DN_IDX", order = 0) })
private String parentDN;
/**
* The default constructor.
*/
public Ignite_DN()
{
// TODO Auto-generated constructor stub
}
/**
* The constructor.
*
* @param entryID The entryID.
* @param rdn The rdn.
* @param parentDN The parent rdn.
*/
Ignite_DN(Long entryID, String rdn, String parentDN)
{
this.entryID = entryID;
this.rdn = rdn;
this.parentDN = parentDN;
}
}
Ignite_DSAttributeStore.java
Description: Binary data
Ignite_EntryIndexedAttributes.java
Description: Binary data
Ignite_ObjectClass.java
Description: Binary data
IgniteClient.java
Description: Binary data
