Hello,

I have a query which joins several tables and then sorts on two
fields. Both fields have ascending indexes declared on them. It takes
over 10 seconds to run this query. If I remove the second sort
criterion, then the query runs 100 times faster. The query plans for
both queries are shown below.

Note that SIZE (first sort column) values are almost unique. Also,
different VERSION_ID (second sort column) values correspond to
different SIZE (first sort column) values and vice versa. So, in this
case adding the second sort value does not change the order of rows
returned.

I noticed that the number of reads goes down from 6494 to 36, but I am
not sure how to reduce this number.

Could anybody help me optimize the query to make the sort on two fields faster?

Thanks!

Preparing query: SELECT FIRST 1000 *
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 (PHYSICAL_COPY."IS_MARKED_DELETED" = 0) AND
((PROJECT."PRIMARY_PROJECT_CODE" = 'ACME') AND
(PHYSICAL_COPY."IS_RECYCLED" = 0))
ORDER BY PHYSICAL_COPY."SIZE" ASC
, COPY."VERSION_ID" ASC
Prepare time: 0.000s
...
Field #27: COPY.VERSION_ID Alias:VERSION_ID Type:STRING(38)
...
Field #46: PHYSICAL_COPY.SIZE Alias:SIZE Type:BIGINT
...
PLAN SORT (JOIN (PHYSICAL_COPY INDEX (IDX_cF3Z9NMr/P8YkbENbAJVkA==),
COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==),
PROJECT_CODE_DESCRIPTOR INDEX (IDX_lEwvSCR+VZpQCfw5Duxo0A==), PROJECT
INDEX (PK_f3m9slJ+02gL6hFClhrZvg==), COPY INDEX
(PK_ZM6SRonqR8AHSQuCISgvnQ==)))

Executing...
Done.
1084756 fetches, 0 marks, 6494 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 250684 index, 0 seq.
Delta memory: 67104396 bytes.
Total execution time: 10.250s
Script execution finished.


Preparing query: SELECT FIRST 1000 *
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 (PHYSICAL_COPY."IS_MARKED_DELETED" = 0) AND
((PROJECT."PRIMARY_PROJECT_CODE" = 'ACME') AND
(PHYSICAL_COPY."IS_RECYCLED" = 0))
ORDER BY PHYSICAL_COPY."SIZE" ASC
--, COPY."VERSION_ID" ASC
Prepare time: 0.000s
...

PLAN JOIN (PHYSICAL_COPY ORDER IDX_PHYSICAL_COPY_SIZE_ASC INDEX
(IDX_cF3Z9NMr/P8YkbENbAJVkA==), COPY_CLASSIFICATION INDEX
(IDX_soqMJd+Yux0RNvCbmE9rrg==), PROJECT_CODE_DESCRIPTOR INDEX
(IDX_lEwvSCR+VZpQCfw5Duxo0A==), PROJECT INDEX
(PK_f3m9slJ+02gL6hFClhrZvg==), COPY INDEX
(PK_ZM6SRonqR8AHSQuCISgvnQ==))

Executing...
Done.
13208 fetches, 4 marks, 36 reads, 4 writes.
0 inserts, 0 updates, 0 deletes, 2695 index, 914 seq.
Delta memory: -2308 bytes.
Total execution time: 0.109s
Script execution finished.