Hello,

We tracked down the query that generated a 10GB temp file running
against a 1.5GB database. Can anybody explain why the query is not
using an index on PHYSICAL_COPY."COMMIT_NUMBER"?

Thanks,

Alec

Preparing query: SELECT FIRST (1000) PHYSICAL_COPY."ID",
PHYSICAL_COPY."COMMIT_NUMBER", PHYSICAL_COPY."COPY_ID",
PHYSICAL_COPY."REPOSITORY_TYPE", PHYSICAL_COPY."CANONICAL_LOCATION",
PHYSICAL_COPY."CANONICAL_NAME", PHYSICAL_COPY."DISPLAY_LOCATION",
PHYSICAL_COPY."DISPLAY_NAME", PHYSICAL_COPY."LOCATION_TYPE",
PHYSICAL_COPY."IS_RECYCLED", PHYSICAL_COPY."SIZE",
PHYSICAL_COPY."LAST_MODIFIED_TIMESTAMP",
PHYSICAL_COPY."CREATED_TIMESTAMP", PHYSICAL_COPY."EXTENSION",
PHYSICAL_COPY."IS_MARKED_DELETED", PHYSICAL_COPY."IS_DIRTY",
PHYSICAL_COPY."CANONICAL_LOCATION_HASH",
PHYSICAL_COPY."CANONICAL_LOCATION_CHECKSUM", COPY."ID",
COPY."COMMIT_NUMBER", COPY."VERSION_ID", COPY."EXISTENCE_TIMESTAMP",
COPY."REGISTRATION_TIMESTAMP", COPY."PREDECESSOR_ID",
COPY."PREDECESSOR_CONFIDENCE_LEVEL", COPY."SOURCE_ID",
COPY."THREAD_ID", COPY."ROOT_ID", COPY."IS_DIRTY",
COPY_CLASSIFICATION."ID", COPY_CLASSIFICATION."COMMIT_NUMBER",
COPY_CLASSIFICATION."COPY_ID",
COPY_CLASSIFICATION."CLASSIFICATION_CODE",
COPY_CLASSIFICATION."CONFIRMATION_STATUS",
COPY_CLASSIFICATION."LABEL_CLASSIFICATION_CODE",
COPY_CLASSIFICATION."LABEL_CONFIRMATION_STATUS",
COPY_CLASSIFICATION."CUTOFF_TIMESTAMP",
COPY_CLASSIFICATION."IS_CUTOFF_TIME_CONFIRMED",
COPY_CLASSIFICATION."IS_DIRTY" FROM PHYSICAL_COPY INNER JOIN 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" = '123431234')
AND (PHYSICAL_COPY."COMMIT_NUMBER" >= 10000000) ORDER BY
PHYSICAL_COPY."COMMIT_NUMBER" ASC
Prepare time: 3.969s
PLAN SORT (JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==),
PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==),
COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==)))

Executing...
Done.
6273556 fetches, 12 marks, 808464 reads, 10 writes.
0 inserts, 0 updates, 0 deletes, 1563789 index, 4789 seq.
Delta memory: 607388 bytes.
Total execution time: 0:02:04 (hh:mm:ss)
Script execution finished.