Set and Arno, Thank you both of you for your solutions! Arno's solution required swapping the order of PROJECT and PROJECT_CODE_DESCRIPTOR tables in the join and use LEFT JOIN instead of INNER JOIN to join them. This is so simple and the results are amazing.
PLAN SORT (JOIN (JOIN (JOIN (PROJECT NATURAL, PROJECT_CODE_DESCRIPTOR INDEX (FK_zDTEgB/EMb14zlRjEdzCZw==)), COPY_CLASSIFICATION INDEX (IDX_epDQN2rI7u0hoaXiWuFXAw==)), JOIN (COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==), PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==)))) 459 fetches, 0 marks, 6 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 108 index, 12 seq. Delta memory: -104 bytes. Total execution time: 0.025s I think I will stick with this solution, but I appreciate Set educating me on how to fool the query optimizer :) Arno, how did you know that using LEFT JOIN will cause the query optimizer to choose the plan we wanted? Thanks, Alec On Tue, Feb 21, 2012 at 2:15 AM, Arno Brinkman <[email protected]> wrote: > ** > > > What about : > > > SELECT > PROJECT."PRIMARY_PROJECT_CODE" AS COL0, > COUNT(PHYSICAL_COPY."ID") AS COL1 > FROM > PROJECT > LEFT JOIN PROJECT_CODE_DESCRIPTOR ON PROJECT_CODE_DESCRIPTOR."PROJECT_ID" > = PROJECT."ID" > JOIN COPY_CLASSIFICATION ON COPY_CLASSIFICATION."CLASSIFICATION_CODE" = > PROJECT_CODE_DESCRIPTOR."PROJECT_CODE" > JOIN COPY ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID" > JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" > WHERE > ( (PROJECT_CODE_DESCRIPTOR."PROJECT_ID" IS NOT NULL) and > > (PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED') and > (PHYSICAL_COPY."IS_MARKED_DELETED" = 0) and > > (PHYSICAL_COPY."IS_RECYCLED" = 0) ) > GROUP BY > PROJECT."PRIMARY_PROJECT_CODE" > > Regards, > Arno > > > [Non-text portions of this message have been removed] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
