Sean,

I would like to note that the fact that LEFT JOIN can generate an optimal
plan where INNER JOIN fails indicates that all our index statistics are
up-to-date and that there is some discrepancy between LEFT and INNER JOIN
optimization that has already bit us several times. The good thing about
LEFT is that it pushes SORT down to the table and uses an index on it
whereas INNER does SORT after the join.

Now back to your questions.

I had to modify your first query a little for it to compile. Here are the
stats:

Preparing query: SELECT FIRST (1000) PHYSICAL_COPY."ID"
FROM (
SELECT PHYSICAL_COPY."ID"
FROM PHYSICAL_COPY
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE
(PHYSICAL_COPY."IS_DIRTY" = 1) AND
(COPY."SOURCE_ID" = '5d74876f-1b14-466a-aeee-f000b8b16512') AND
(PHYSICAL_COPY."COMMIT_NUMBER" >= 10000000)
ORDER BY
PHYSICAL_COPY."COMMIT_NUMBER"
) AbstractTable
INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY."ID" = AbstractTable."ID"
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
Prepare time: 0.204s
Field #01: PHYSICAL_COPY.ID Alias:ID Type:STRING(38)
PLAN JOIN (JOIN (SORT (JOIN (ABSTRACTTABLE COPY INDEX
(IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), ABSTRACTTABLE PHYSICAL_COPY INDEX
(IDX_AlJS5EmMT9tODQnFqmid0w==))), PHYSICAL_COPY INDEX
(PK_lx3z+dt9CnekKkF+ZE8NIA==)), JOIN (COPY INDEX
(PK_ZM6SRonqR8AHSQuCISgvnQ==), COPY_CLASSIFICATION INDEX
(IDX_soqMJd+Yux0RNvCbmE9rrg==)))
Executing...
Done.
6262754 fetches, 0 marks, 808411 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 1563528 index, 0 seq.
Delta memory: 527584 bytes.
Total execution time: 0:01:58 (hh:mm:ss)
Script execution finished.

Here is your second query (has very high reads):

Preparing query: SELECT
PHYSICAL_COPY."ID"
FROM COPY
INNER JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
WHERE (PHYSICAL_COPY."IS_DIRTY" = 1) AND (COPY."SOURCE_ID" =
'5d74876f-1b14-466a-aeee-f000b8b16512') AND (PHYSICAL_COPY."COMMIT_NUMBER"

>= 10000000) ORDER BY PHYSICAL_COPY."COMMIT_NUMBER" ASC

Prepare time: 0.015s
Field #01: PHYSICAL_COPY.ID Alias:ID Type:STRING(38)
PLAN SORT (JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), PHYSICAL_COPY
INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==), COPY_CLASSIFICATION INDEX
(IDX_soqMJd+Yux0RNvCbmE9rrg==)))
Executing...
Done.
6262689 fetches, 0 marks, 808409 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 1563505 index, 0 seq.
Delta memory: 517200 bytes.
Total execution time: 47.437s
Script execution finished.

On Sun, Jun 3, 2012 at 3:27 PM, Leyne, Sean <Sean@...>wrote:

> **
>
>
> Alec,
>
>
> > Sean,
> >
> > The query you proposed will not produce the same result because you are
> > sorting and topping the join before applying the filter.
>
> You are correct.
>
> But a small change to my proposed query would resolve that issue, the
> new/corrected query would be:
>
>
> SELECT
> FIRST (1000)
> PHYSICAL_COPY."ID", ...
> FROM (
> SELECT
> PHYSICAL_COPY."ID"
> FROM PHYSICAL_COPY
> INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" ) AbstractTable
> WHERE
> (PHYSICAL_COPY."IS_DIRTY" = 1) AND
> (COPY."SOURCE_ID" = '123431234') AND
> (PHYSICAL_COPY."COMMIT_NUMBER" >= 10000000)
> ORDER BY
> PHYSICAL_COPY."COMMIT_NUMBER"
> ) AbstractTable
> INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY."ID" = AbstractTable."ID"
> INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
> INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
>
> > We have a one-to-one relationship between PHYSICAL_COPY and COPY and
> > COPY_CLASSIFICATION. So, the solution I went with was to replace INNER
> > JOINs with LEFT JOINs which changed the execution plan from (I translated
> > index names):
>
> > This is the second time (see my older post "Force query plan to filter
> before
> > join") where LEFT join saved the day. It would be nice to understand why
> > LEFT join optimization works so much better for us. Is there an
> explanation of
> > FB optimization techniques available somewhere?
>
> What is the performance of your query like if you change the query to be:
>
>
> SELECT
> PHYSICAL_COPY."ID", ...
> FROM COPY
> INNER JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
>
> INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
> WHERE ...
>
> (the position of COPY and PHYSICAL_COPY in the query was changed, but the
> defined relationship is the same)
>
> Sean
>
>
>


[Non-text portions of this message have been removed]