In SQL as the user run:
SQL> explain plan for SELECT DISTINCT consumer_id "consumerId", c.first_name "firstName", c.last_name "lastName", c.date_of_birth "dateOfBirth", c.case_id "caseId", cd.individual_id "individualId", cs.family_account_id "familyAccountId" FROM consumer c, child_detail cd, case cs, address a WHERE rownum < = 500 AND c.child_detail_id = cd.child_detail_id (+) AND cs.case_id = c.case_id AND (cs.home_address_id = a.address_id OR cs.mailing_address_id = a.address_id) AND cs.family_account_id = :1 AND a.state_id = :2 ORDER BY UPPER(last_name), UPPER(first_name); Explained. SQL> select * from table(dbms_xplan.display()); Plan hash value: 2032295181 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 352 | 27 (8)| 00:00:01 | | 1 | SORT ORDER BY | | 4 | 352 | 27 (8)| 00:00:01 | | 2 | HASH UNIQUE | | 4 | 352 | 26 (4)| 00:00:01 | |* 3 | COUNT STOPKEY | | | | | | | 4 | NESTED LOOPS OUTER | | 4 | 352 | 25 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 4 | 284 | 19 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 32 | 15 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | CASE | 1 | 23 | 3 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | IDX_FAMILY_ACCOUNT_ID | 1 | | 2 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID | ADDRESS | 1 | 9 | 15 (0)| 00:00:01 | | 10 | BITMAP CONVERSION TO ROWIDS | | | | | | | 11 | BITMAP OR | | | | | | | 12 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 13 | INDEX RANGE SCAN | XPK_ADDRESS_ID | 79044 | | 1 (0)| 00:00:01 | | 14 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 15 | INDEX RANGE SCAN | XPK_ADDRESS_ID | 79044 | | 1 (0)| 00:00:01 | | 16 | TABLE ACCESS BY INDEX ROWID | CONSUMER | 3 | 117 | 4 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | CONSUMER_CASE_ID_IDX | 3 | | 2 (0)| 00:00:01 | | 18 | TABLE ACCESS BY INDEX ROWID | CHILD_DETAIL | 1 | 17 | 2 (0)| 00:00:01 | |* 19 | INDEX UNIQUE SCAN | XPK_CHILD_DETAIL_ID | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------
0 Comments.