Hi, is there a plan to implement this (join on UPDATE) or to improve the
performance of the "IN" operator ?
I have an UPDATE query with two "IN" operators.
UPDATE PUBLIC.AAA_STR_HIE_ACCOUNT
SET MBR_TYPE = 'B'
WHERE ID IN (
SELECT ID
...'
AND ID NOT IN (
SELECT PARENT
...
)
);
I have 1700 rows and I stopped the query after 5 minutes
If the IN in the subquery with a join, I get a runtime of about 10 second.
UPDATE PUBLIC.AAA_STR_HIE_ACCOUNT AS t3
SET MBR_TYPE = 'B'
WHERE ID IN (SELECT DISTINCT t1.ID
FROM PUBLIC.AAA_STR_HIE_ACCOUNT t1
LEFT JOIN PUBLIC.AAA_STR_HIE_ACCOUNT t2 ON t2.PARENT = t1.ID
WHERE t2.PARENT IS NULL
)
The runtime of the subquery went down from 3.7s to 17ms. How can I get rid
of the first "IN" to speed up my whole query ?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
