Hello, Both are using the same database and same data, I did some count(*) checks on the tables in Oracle and H2 and they yield the same count. This means that things go wrong on another level, not sure where though.
Lets take a step back and rerun the tests: This is the SQL: /SELECT DISTINCT activity0.activity_id, activity0.sequencenr, activity0.name_mlid, activity0.name, activity0.description_mlid, activity0.description, activity0.durationunit_enumid, activity0.duration, activity0.required, activity0.predecessortype_enumid, activity0.successortype_enumid, activity0.removefromworklist, activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate, activity0.priority_enumid, activity0.notify, activity0.timestamp, activity0.ActivityType_id, activity0.container_id, activity0.realization_id, activity0.kernel_id, activity0.sysrepoperation_id FROM activity activity0 LEFT OUTER JOIN activityhistory activityhistory0 ON activityhistory0.activityhistory_id = activity0.lastactivity_id LEFT OUTER JOIN activityuseraccountrole activityuseraccountrole0 ON activityuseraccountrole0.activity_id = activity0.activity_id LEFT OUTER JOIN activityhistoryuseraccount activityhistoryuseraccount0 ON activityhistoryuseraccount0.ActivityHistory_id = activityhistory0.activityhistory_id WHERE activity0.kernel_id IS NULL AND activity0.realization_id IS NULL AND NOT activityhistory0.activitystate_enumid IN (37, 30, 463, 33, 464) AND ((activityuseraccountrole0.useraccountrole_id IN (1, 3) AND (activity0.removefromworklist = 0 OR activityhistoryuseraccount0.UserAccount_id IS NULL)) OR activityhistoryuseraccount0.UserAccount_id = 600301) / Explain via SQL Developer: explainplan_new.png <http://apache-ignite-users.70518.x6.nabble.com/file/n5355/explainplan_new.png> SQL Via H2: /EXPLAIN ANALYZE SELECT DISTINCT activity0.activity_id, activity0.sequencenr, activity0.name_mlid, activity0.name, activity0.description_mlid, activity0.description, activity0.durationunit_enumid, activity0.duration, activity0.required, activity0.predecessortype_enumid, activity0.successortype_enumid, activity0.removefromworklist, activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate, activity0.priority_enumid, activity0.notify, activity0.timestamp, activity0.ActivityType_id, activity0.container_id, activity0.realization_id, activity0.kernel_id, activity0.sysrepoperation_id FROM "Activity".activity activity0 LEFT OUTER JOIN "Activity".activityhistory activityhistory0 ON activityhistory0.activityhistory_id = activity0.lastactivity_id LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0 ON activityuseraccountrole0.activity_id = activity0.activity_id LEFT OUTER JOIN "Activity".activityhistoryuseraccount activityhistoryuseraccount0 ON activityhistoryuseraccount0.ActivityHistory_id = activityhistory0.activityhistory_id WHERE activity0.kernel_id IS NULL AND activity0.realization_id IS NULL AND NOT activityhistory0.activitystate_enumid IN (37, 30, 463, 33, 464) AND ((activityuseraccountrole0.useraccountrole_id IN (1, 3) AND (activity0.removefromworklist = 0 OR activityhistoryuseraccount0.UserAccount_id IS NULL)) OR activityhistoryuseraccount0.UserAccount_id = 600301) / Explain via H2: /SELECT DISTINCT ACTIVITY0.ACTIVITY_ID, ACTIVITY0.SEQUENCENR, ... FROM "Activity".ACTIVITY ACTIVITY0 /* "Activity"."Activity_cond_idx": KERNEL_ID IS NULL AND REALIZATION_ID IS NULL */ /* WHERE (ACTIVITY0.KERNEL_ID IS NULL) AND (ACTIVITY0.REALIZATION_ID IS NULL) */ /* scanCount: 121058 */ LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0 /* "Activity".PK_ACTIVITYHISTORY: ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID */ ON ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID /* scanCount: 189554 */ LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0 /* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID */ ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID /* scanCount: 1148898 */ LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT ACTIVITYHISTORYUSERACCOUNT0 /* "Activity"."Activityhistoryuseraccount_idx": ACTIVITYHISTORY_ID = ACTIVITYHISTORY0.ACTIVITYHISTORY_ID */ ON ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID = ACTIVITYHISTORY0.ACTIVITYHISTORY_ID /* scanCount: 1060391 */ WHERE ((NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33, 464))) AND ((ACTIVITY0.KERNEL_ID IS NULL) AND (ACTIVITY0.REALIZATION_ID IS NULL))) AND ((ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID = 600301) OR ((ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3)) AND ((ACTIVITY0.REMOVEFROMWORKLIST = 0) OR (ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID IS NULL))))/ Hmm so yeah looks like we are seeing strange numbers in the explain, even though the starting data and queries are the same. -- View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5355.html Sent from the Apache Ignite Users mailing list archive at Nabble.com.
