You rock Andrey, the trick worked , tried the same in api, including your
response for everyone
Orginal query
-----------------------------------------------------
SELECT st.entryID,st.attrName,st.attrValue, st.attrsType
FROM
(SELECT entryID as entryID FROM "objectclass".IGNITE_OBJECTCLASS WHERE
attrValue = 'person'
) t
INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE st
ON st.entryID = t.entryID
NOTE : "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE has index defined
on entryID , following is the explain plan obtained, its scanning the
entire
table
SELECT
ST.ENTRYID,
ST.ATTRNAME,
ST.ATTRVALUE,
ST.ATTRSTYPE
FROM "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST
/* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE.__SCAN_ */
INNER JOIN (
SELECT
ENTRYID AS ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS
WHERE ATTRVALUE = 'person'
) T
/* SELECT
ENTRYID AS ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS
/++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =
'person'
AND ENTRYID IS ?1
++/
WHERE (ATTRVALUE = 'person')
AND (ENTRYID IS ?1): ENTRYID = ST.ENTRYID
*/
ON 1=1
WHERE ST.ENTRYID = T.ENTRYID
(1 row, 1 ms)
How should I approach that "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE
full scan can be avoided ?
----------------------------------------------------
*Andrey suggested*
--------------------------------------------------
Looks like, H2 optimizer changes join order.
Try to set enforceJoinOrder=true for the query if you use ignite API.
If you use JDBC then flag can be set on per connection basis.
---------------------------------------------------
*After suggestion:*
[[SELECT
ST__Z2.ENTRYID AS __C0_0,
ST__Z2.ATTRNAME AS __C0_1,
ST__Z2.ATTRVALUE AS __C0_2,
ST__Z2.ATTRSTYPE AS __C0_3
FROM (
SELECT
__Z0.ENTRYID AS ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS __Z0
WHERE __Z0.ATTRVALUE = 'person'
) T__Z1
/* SELECT
__Z0.ENTRYID AS ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS __Z0
/++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =
'person' ++/
WHERE __Z0.ATTRVALUE = 'person'
*/
INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST__Z2
/* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX:
ENTRYID = T__Z1.ENTRYID */
ON 1=1
WHERE ST__Z2.ENTRYID = T__Z1.ENTRYID
ORDER BY 1], [SELECT
__C0_0 AS ENTRYID,
__C0_1 AS ATTRNAME,
THanks,
Rajesh
__C0_2 AS ATTRVALUE,
__C0_3 AS ATTRSTYPE
FROM PUBLIC.__T0
/* "dn"."merge_sorted" */
ORDER BY 1
On Sat, Feb 3, 2018 at 8:48 PM, Rajesh Kishore <ra...@gmail.com>
wrote:
> Hey Andery,
> Trying this with H2 console as of now , any setting there?
>
> Thanks,
> Rajesh
>