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 <fbsupport@...> 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]