[ https://issues.jboss.org/browse/TEIID-1566?page=com.atlassian.jira.plugin... ] Mark Addleman edited comment on TEIID-1566 at 4/24/11 11:58 AM: ---------------------------------------------------------------- Last statement should read: I think there are two problems with the plan for this query: First, neither the ORDER BY nor the LIMIT clause are pushed down to DB2. However, even if it is wasn't pushed down, the plan should apply the ORDER & LIMIT before it tries to join to the notes table. was (Author: markaddleman): Last statemnet should read: I think there are two problems with the plan for this query: First, neither the ORDER BY nor the LIMIT clause are pushed down to DB2. However, event if it is wasn't pushed down, the plan should apply the ORDER & LIMIT before it tries to join to the notes table. -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://issues.jboss.org/browse/TEIID-1566?page=com.atlassian.jira.plugin... ] Mark Addleman commented on TEIID-1566: -------------------------------------- Sorry, I missed that field: Version 7.3 plus patches 1492, 1529, 1545, 1554 and 1445. The query plan is below: 0 [main] DEBUG com.ca.chorus.db.DbExecutor - Executing SQL SELECT B."SYSID", B."USERID", J0.APPNAME as "SYSOFUSER@APPNAME", B."NAME", B."PROC", B."SUSPEND", B."UID", (select count(*) from (select * from notes.RETRIEVE_NOTES where OBJECT_PKEY = xmlserialize(xmlelement("SECURITY.BASEUSER", XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM "SECURITY.BASEUSER" as B left join security.sysinfo J0 on J0.sysid=B.sysid ORDER BY J0.APPNAME ASC LIMIT 9990,10; args: [] 265720 [main] DEBUG com.ca.chorus.db.DbExecutor - Plan for sql SELECT B."SYSID", B."USERID", J0.APPNAME as "SYSOFUSER@APPNAME", B."NAME", B."PROC", B."SUSPEND", B."UID", (select count(*) from (select * from notes.RETRIEVE_NOTES where OBJECT_PKEY = xmlserialize(xmlelement("SECURITY.BASEUSER", XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM "SECURITY.BASEUSER" as B left join security.sysinfo J0 on J0.sysid=B.sysid ORDER BY J0.APPNAME ASC LIMIT 9990,10: LimitNode + Output Columns: 0: SYSID (string) 1: USERID (string) 2: SYSOFUSER@APPNAME (string) 3: NAME (string) 4: PROC (string) 5: SUSPEND (string) 6: UID (integer) 7: C_notesForObject (integer) 8: __objecttype__ (string) + Statistics: 0: Node Output Rows: 10 1: Node Process Time: 265374 2: Node Cumulative Process Time: 265374 3: Node Cumulative Next Batch Process Time: 72870 4: Node Next Batch Calls: 23627 5: Node Blocks: 23626 + Cost Estimates:Estimated Node Cardinality: 10.0 + Child 0: SortNode + Output Columns: 0: SYSID (string) 1: USERID (string) 2: SYSOFUSER@APPNAME (string) 3: NAME (string) 4: PROC (string) 5: SUSPEND (string) 6: UID (integer) 7: C_notesForObject (integer) 8: __objecttype__ (string) + Statistics: 0: Node Output Rows: 10240 1: Node Process Time: 0 2: Node Cumulative Process Time: 0 3: Node Cumulative Next Batch Process Time: 72785 4: Node Next Batch Calls: 23646 5: Node Blocks: 23626 + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns: 0: SYSID (string) 1: USERID (string) 2: SYSOFUSER@APPNAME (string) 3: NAME (string) 4: PROC (string) 5: SUSPEND (string) 6: UID (integer) 7: C_notesForObject (integer) 8: __objecttype__ (string) + Statistics: 0: Node Output Rows: 23746 1: Node Process Time: 651 2: Node Cumulative Process Time: 265334 3: Node Cumulative Next Batch Process Time: 72548 4: Node Next Batch Calls: 23719 5: Node Blocks: 23672 + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: AccessNode + Output Columns: 0: SYSID (string) 1: USERID (string) 2: APPNAME (string) 3: NAME (string) 4: PROC (string) 5: SUSPEND (string) 6: UID (integer) + Statistics: 0: Node Output Rows: 23746 1: Node Process Time: 264683 2: Node Cumulative Process Time: 264683 3: Node Cumulative Next Batch Process Time: 5 4: Node Next Batch Calls: 48 5: Node Blocks: 1 + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:SELECT g_0.SYSID, g_0.USERID, g_1.APPNAME, g_0.NAME, g_0.PROC, g_0.SUSPEND, g_0.UID FROM SECURITY.BASEUSER AS g_0 LEFT OUTER JOIN security.sysinfo AS g_1 ON g_1.sysid = g_0.sysid + Model Name:security + Select Columns Subplan 0: ProjectNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: GroupingNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: DependentProcedureAccessNode + Output Columns: 0: ID (long) 1: NOTE (string) 2: USER_ID (string) 3: NOTES_TYPE (string) 4: ACCESS_MODE (string) 5: CREATED_ON (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY) + Model Name:notes + Select Columns:ID + Select Columns:X.ID + Sort Mode:false + Select Columns:COUNT(*) + Select Columns Subplan 1: ProjectNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: GroupingNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: DependentProcedureAccessNode + Output Columns: 0: ID (long) 1: NOTE (string) 2: USER_ID (string) 3: NOTES_TYPE (string) 4: ACCESS_MODE (string) 5: CREATED_ON (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY) + Model Name:notes + Select Columns:ID + Select Columns:X.ID + Sort Mode:false + Select Columns:COUNT(*) + Select Columns Subplan 2: ProjectNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: GroupingNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: DependentProcedureAccessNode + Output Columns: 0: ID (long) 1: NOTE (string) 2: USER_ID (string) 3: NOTES_TYPE (string) 4: ACCESS_MODE (string) 5: CREATED_ON (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY) + Model Name:notes + Select Columns:ID + Select Columns:X.ID + Sort Mode:false + Select Columns:COUNT(*) + Select Columns Subplan 3: ProjectNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: GroupingNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: DependentProcedureAccessNode + Output Columns: 0: ID (long) 1: NOTE (string) 2: USER_ID (string) 3: NOTES_TYPE (string) 4: ACCESS_MODE (string) 5: CREATED_ON (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY) + Model Name:notes + Select Columns:ID + Select Columns:X.ID + Sort Mode:false + Select Columns:COUNT(*) + Select Columns Subplan 4: ProjectNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: GroupingNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: DependentProcedureAccessNode + Output Columns: 0: ID (long) 1: NOTE (string) 2: USER_ID (string) 3: NOTES_TYPE (string) 4: ACCESS_MODE (string) 5: CREATED_ON (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY) + Model Name:notes + Select Columns:ID + Select Columns:X.ID + Sort Mode:false + Select Columns:COUNT(*) + Select Columns Subplan 5: ProjectNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: GroupingNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: DependentProcedureAccessNode + Output Columns: 0: ID (long) 1: NOTE (string) 2: USER_ID (string) 3: NOTES_TYPE (string) 4: ACCESS_MODE (string) 5: CREATED_ON (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY) + Model Name:notes + Select Columns:ID + Select Columns:X.ID + Sort Mode:false + Select Columns:COUNT(*) + Select Columns Subplan 6: ProjectNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: GroupingNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: DependentProcedureAccessNode + Output Columns: 0: ID (long) 1: NOTE (string) 2: USER_ID (string) 3: NOTES_TYPE (string) 4: ACCESS_MODE (string) 5: CREATED_ON (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY) + Model Name:notes + Select Columns:ID + Select Columns:X.ID + Sort Mode:false + Select Columns:COUNT(*) + Select Columns Subplan 7: ProjectNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: GroupingNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: DependentProcedureAccessNode + Output Columns: 0: ID (long) 1: NOTE (string) 2: USER_ID (string) 3: NOTES_TYPE (string) 4: ACCESS_MODE (string) 5: CREATED_ON (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY) + Model Name:notes + Select Columns:ID + Select Columns:X.ID + Sort Mode:false + Select Columns:COUNT(*) + Select Columns Subplan 8: ProjectNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: GroupingNode + Output Columns:count (integer) + Cost Estimates:Estimated Node Cardinality: 1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Output Columns:ID (long) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: DependentProcedureAccessNode + Output Columns: 0: ID (long) 1: NOTE (string) 2: USER_ID (string) 3: NOTES_TYPE (string) 4: ACCESS_MODE (string) 5: CREATED_ON (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY) + Model Name:notes + Select Columns:ID + Select Columns:X.ID + Sort Mode:false + Select Columns:COUNT(*) + Select Columns: 0: B.SYSID 1: B.USERID 2: J0.APPNAME AS "SYSOFUSER@APPNAME" 3: B.NAME 4: B.PROC 5: B.SUSPEND 6: B.UID 7: (SELECT COUNT(*) FROM (SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY = XMLSERIALIZE(XMLELEMENT(NAME SECURITY.BASEUSER, XMLATTRIBUTES(B.SYSID AS SYSID, B.USERID AS USERID)) AS String)) AS foo) AS C_notesForObject 8: 'SECURITY.BASEUSER' AS "__objecttype__" + Sort Columns:["SYSOFUSER@APPNAME"] + Sort Mode:SORT + Row Offset:9990 + Row Limit:10 ==================================== -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://issues.jboss.org/browse/TEIID-1566?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-1566: ---------------------------------- Fix Version/s: 7.4 Affects Version/s: 7.4 Complexity: Low -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://issues.jboss.org/browse/TEIID-1566?page=com.atlassian.jira.plugin... ] Mark Addleman commented on TEIID-1566: -------------------------------------- Confirm the fix. Thanks for the quick turnaround! -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira