TKprof output:

select * from lgncc_commoncaseview where
clientid = :b1 and
clienttype = :b2 and (1=1)
union
select * from lgncc_commoncaseview where
xref1 = :b3 and
objecttype = :b4 and (1=1)

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      4.49      34.89       1787      21664          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      4.49      34.90       1787      21664          0           2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  SORT UNIQUE
      2   UNION-ALL
      1    NESTED LOOPS
      1     HASH JOIN
      4      TABLE ACCESS BY INDEX ROWID OBJ#(35867)
      4       INDEX RANGE SCAN OBJ#(38739) (object id 38739)
 117901      HASH JOIN OUTER
 117901       TABLE ACCESS FULL OBJ#(35878) (lgncc_enquiry)
1475502       TABLE ACCESS FULL OBJ#(35880)  (lgncc_enquiryrelation)
      1     TABLE ACCESS BY INDEX ROWID OBJ#(35873)
      1      INDEX UNIQUE SCAN OBJ#(35874) (object id 35874)
      1    NESTED LOOPS OUTER
      1     NESTED LOOPS OUTER
      1      NESTED LOOPS
      1       TABLE ACCESS BY INDEX ROWID OBJ#(35878)
      1        INDEX RANGE SCAN OBJ#(38738) (object id 38738)
      1       TABLE ACCESS BY INDEX ROWID OBJ#(35873)
      1        INDEX UNIQUE SCAN OBJ#(35874) (object id 35874)
      1      INDEX RANGE SCAN OBJ#(38516) (object id 38516)
      1     TABLE ACCESS BY INDEX ROWID OBJ#(35867)
      1      INDEX UNIQUE SCAN OBJ#(35868) (object id 35868)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00           0.00
  db file sequential read                      1382        0.17          16.42
  db file scattered read                        175        0.10           3.45
  latch free                                      6        0.01           0.01
  buffer busy waits                            1137        0.08          11.15
  SQL*Net more data to client                     1        0.00           0.00
  SQL*Net message from client                     2        6.89           6.89


Lgncc_CommonCaseview Definition

 

SELECT
-- [ID]
  ENQUIRY.ID                AS REFERENCE,
  0                         AS TYPE,
-- [DESCRIPTION]
  ENQUIRY.TITLE             AS LINE1,
  TYPE.NAME                   AS LINE2,
-- [CASE]
  ENQUIRY.CASEID            AS CASEID,
  ENQUIRY.CASEREF           AS RELATEDCASE,
  ENQUIRY.ENQUIRYTYPE       AS ENQUIRYTYPE,
  ENQUIRY.OBJECTTYPE        AS OBJECTTYPE,
  ENQUIRY.XREF1             AS XREF1,
  ENQUIRY.XREF2             AS XREF2,
  ENQUIRY.XREF3             AS XREF3,
  ENQUIRY.OBJECTDESC        AS OBJECTDESC,
-- [INTERACTION]
  INT.CLIENTTYPE            AS CLIENTTYPE,
  INT.CLIENTID              AS CLIENTID,
  INT.CLIENTNAME            AS CLIENTNAME,
  INT.LOGID                 AS INTERACTIONID,
  INT.INTREF                AS INTERACTIONREF,
  INT.VERIFIED              AS INTERACTIONVERIFIED,
  nvl(INT.INITCHANNEL, -1)  AS INTERACTIONCHANNEL,
  INT.REFERENCE             AS INTERACTIONREFERENCE,
  INT.STARTTIME             AS INTERACTIONDATE,
-- [STATUS]
  NULL                          AS TARGETDATE,
  ENQUIRY.STATUS            AS STATUS,
-- [AUDIT]
  ENQUIRY.CREATIONDATE      AS CREATIONDATE,
  ENQUIRY.SOURCEID          AS CREATEDBY,
  ENQUIRY.CREATIONDATE      AS MODIFIEDDATE,
  NULL                      AS MODIFIEDBY
FROM
    LGNCC_ENQUIRY ENQUIRY
    INNER JOIN LGNCC_ENQUIRYTYPE TYPE ON 
       ENQUIRY.ENQUIRYTYPE = TYPE.ID
    LEFT OUTER JOIN LGNCC_ENQUIRYRELATION REL ON 
       ENQUIRY.ID = REL.ENQUIRYID AND REL.RELATION = 1
    LEFT OUTER JOIN LGNCC_INTLOGHDR INT ON 
       INT.LOGID = REL.INTERACTIONID
    WHERE 
    ENQUIRY.CASEREF IS NOT NULL AND ENQUIRY.DELETEDDATE IS NULL