Hello,

I need help optimizing the query shown below. PROJECT-related tables
contain 12 rows each while COPY-related tables contain 14K rows each.
Moreover, PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED' filters out all
but 10 joined rows.

My question is how do I change the plan to force
PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED' filter to be applied
before the join?

Thanks!

Preparing query: SELECT PROJECT."PRIMARY_PROJECT_CODE" AS COL0,
COUNT(PHYSICAL_COPY."ID") AS COL1
FROM PROJECT_CODE_DESCRIPTOR
INNER JOIN PROJECT ON PROJECT_CODE_DESCRIPTOR."PROJECT_ID" = PROJECT."ID"
INNER JOIN COPY_CLASSIFICATION ON
PROJECT_CODE_DESCRIPTOR."PROJECT_CODE" =
COPY_CLASSIFICATION."CLASSIFICATION_CODE"
INNER JOIN COPY ON COPY_CLASSIFICATION."COPY_ID" = COPY."ID"
INNER JOIN PHYSICAL_COPY ON COPY."ID" = PHYSICAL_COPY."COPY_ID"
WHERE ( (PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED')
and (PHYSICAL_COPY."IS_MARKED_DELETED" = 0)
AND (PHYSICAL_COPY."IS_RECYCLED" = 0)
)
GROUP BY PROJECT."PRIMARY_PROJECT_CODE"
Prepare time: 0.004s
Field #01: . Alias:COL0 Type:STRING(256)
Field #02: . Alias:COL1 Type:INTEGER
PLAN SORT (JOIN (PHYSICAL_COPY INDEX (IDX_sKfIZDMkd26ITRo1Da5mzQ==),
COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==), COPY INDEX
(PK_ZM6SRonqR8AHSQuCISgvnQ==), PROJECT_CODE_DESCRIPTOR INDEX
(IDX_lEwvSCR+VZpQCfw5Duxo0A==), PROJECT INDEX
(PK_f3m9slJ+02gL6hFClhrZvg==)))


Executing...
Done.
278233 fetches, 0 marks, 16305 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 69581 index, 0 seq.
Delta memory: -3956 bytes.
Total execution time: 1.243s
Script execution finished.