SELECT PARTICIPANT.*,
       EMPLOYEE_GROUPS.*
       FROM PARTICIPANT, 
       SRVC_HIST,
       EMPLOYEE_GROUPS 
       WHERE CLONE_TYPE IS NULL 
       AND PARTICIPANT.PART_ID=SRVC_HIST.PART_ID 
       AND SRVC_HIST.EMP_GRP_ID=EMPLOYEE_GROUPS.EMP_GRP_ID 
       AND (PART_SSN =:"SYS_B_0" OR PARTNER_PART_ID = :"SYS_B_1")


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  ajbsrh2phsgbw, child number 0
-------------------------------------
SELECT PARTICIPANT.*, EMPLOYEE_GROUPS.* FROM PARTICIPANT, SRVC_HIST,
EMPLOYEE_GROUPS WHERE CLONE_TYPE IS NULL AND
PARTICIPANT.PART_ID=SRVC_HIST.PART_ID AND
SRVC_HIST.EMP_GRP_ID=EMPLOYEE_GROUPS.EMP_GRP_ID AND (PART_SSN =
:"SYS_B_0" OR PARTNER_PART_ID = :"SYS_B_1")

Plan hash value: 2097804779

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |       |       |  1181 (100)|          |
|*  1 |  HASH JOIN                    |                  |  1986 |   453K|  1181   (2)| 00:00:15 |
|*  2 |   TABLE ACCESS FULL           | PARTICIPANT      |   116 | 15080 |   946   (2)| 00:00:12 |
|   3 |   NESTED LOOPS                |                  |       |       |            |          |
|   4 |    NESTED LOOPS               |                  |  2003 |   203K|   234   (1)| 00:00:03 |
|   5 |     MERGE JOIN CARTESIAN      |                  |    16 |  1520 |     9   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL        | EMPLOYEE_GROUPS  |     4 |    24 |     3   (0)| 00:00:01 |
|   7 |      BUFFER SORT              |                  |     4 |   356 |     6   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS FULL       | EMPLOYEE_GROUPS  |     4 |   356 |     2   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN          | SH_EMP_GRP_ID_IX |    70 |       |    12   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID| SRVC_HIST        |   125 |  1125 |    14   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("PART_ID"="PART_ID")
   2 - filter(("CLIENT_ID"=6 AND ("PARTNER_PART_ID"=:SYS_B_1 OR "PART_SSN"=:SYS_B_0) AND
              "CLONE_TYPE" IS NULL))
   6 - filter("CLIENT_ID"=6)
   8 - filter("CLIENT_ID"=6)
   9 - access("EMP_GRP_ID"="EMP_GRP_ID")
       filter("EMP_GRP_ID"="EMP_GRP_ID")