select  * from (
SELECT
  ENQUIRY.ID AS EnquiryID,
  CASES.CaseId,
  CASES.CaseRef,
  SUBJECT.id AS SubjectID,
  SUBJECT.name AS SubjectName,
  REASON.id AS ReasonID,
  REASON.name AS ReasonName,
  TYPE.id AS TypeID,
  TYPE.name AS TypeName,
  ENQUIRY.CreationDate AS OpenedDateTime
FROM LGNCC_CLOSEDCASEHDR CASES, LGNCC_ENQUIRY ENQUIRY,
     LGNCC_ENQUIRYSUBJECT SUBJECT, LGNCC_ENQUIRYREASON REASON, LGNCC_ENQUIRYTYPE TYPE
WHERE CASES.enquiryId = ENQUIRY.ID
  AND ENQUIRY.deleteddate is null
  AND ENQUIRY.enquirytype = TYPE.id
  AND TYPE.reason = REASON.id
  AND REASON.subject = SUBJECT.id
)
where upper(caseref) like '109969%'
and (1=1) order by openeddatetime desc;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.26       0.41          0         27          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      2.34       4.25      13312      13337          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.60       4.67      13312      13364          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 118  (FLINE_611)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT ORDER BY
      1   HASH JOIN
     27    INDEX FULL SCAN LGNCC_ENQUIRYSUBJECT_IDX_001 (object id 131778)
      1    HASH JOIN
     80     INDEX FULL SCAN LGNCC_ENQUIRYREASON_IDX_002 (object id 131772)
      1     HASH JOIN
    280      INDEX FAST FULL SCAN LGNCC_ENQUIRYTYPE_IDX_008 (object id 131782)
      1      HASH JOIN
      1       TABLE ACCESS FULL LGNCC_CLOSEDCASEHDR
1426265       TABLE ACCESS FULL LGNCC_ENQUIRY