Tuning question...

See the three queries below.

Query 3 returns the desired results quite efficiently (< 1 sec). I'm trying to understand why, though. We found the 'solution' quite by accident.

We started with Query 1, which runs in < 1 sec.

We then went to Query 2 and added one more lookup (the care provider name for type 7). This runs pretty much indefinately (I've never let it go to completion -- we're talking way long).

Why is the performance working this way? I'm happy we have the solution, but I need it to occur thoughtfully next time - not by sheer luck.

Appreciate any comments....

Query1

SELECT

 PATIENT_DEMO.PAT_ID,
 PAT_REF_CARE7.CARE_ID,
 PAT_REF_CARE3.CARE_ID,

 PATIENT_DEMO,
 PAT_REF_CARE  PAT_REF_CARE7,
 PAT_REF_CARE  PAT_REF_CARE3,

 AND  ( PAT_REF_CARE7.PAT_ID(+)=PATIENT_DEMO.PAT_ID  )
 AND  ( PAT_REF_CARE7.REFERRING_TYPE(+)=7  )
 AND  ( PAT_REF_CARE3.REFERRING_TYPE(+)=3  )
 AND  ( CARE_PROVIDERS3.CARE_ID=PAT_REF_CARE3.CARE_ID  )
 AND  (

 PATIENT_DEMO.PAT_ID,
 PAT_REF_CARE7.CARE_ID,
 PAT_REF_CARE3.CARE_ID,

 PATIENT_DEMO,
 PAT_REF_CARE  PAT_REF_CARE7,
 PAT_REF_CARE  PAT_REF_CARE3,

 AND  ( PAT_REF_CARE7.PAT_ID(+)=PATIENT_DEMO.PAT_ID  )
 AND  ( PAT_REF_CARE7.REFERRING_TYPE(+)=7  )
 AND  ( PAT_REF_CARE3.REFERRING_TYPE(+)=3  )
 AND  ( CARE_PROVIDERS7.CARE_ID=PAT_REF_CARE7.CARE_ID  )
 AND  ( CARE_PROVIDERS3.CARE_ID=PAT_REF_CARE3.CARE_ID  )
 AND  (

Query 3

SELECT
 PATIENT_DEMO.PAT_ID,
 PAT_REF_CARE7.CARE_ID,
 CARE_PROVIDERS7.LAST_NAME,
 PAT_REF_CARE3.CARE_ID,
 CARE_PROVIDERS3.LAST_NAME
FROM
 PATIENT_DEMO,
 PAT_REF_CARE PAT_REF_CARE7,
 CARE_PROVIDERS CARE_PROVIDERS7,
 PAT_REF_CARE PAT_REF_CARE3,
 CARE_PROVIDERS CARE_PROVIDERS3
WHERE
 ( PAT_REF_CARE3.PAT_ID(+)=PATIENT_DEMO.PAT_ID )

 AND  ( PAT_REF_CARE7.PAT_ID(+)=PATIENT_DEMO.PAT_ID  )
 AND  ( PAT_REF_CARE7.REFERRING_TYPE(+)=7  )
 AND  ( PAT_REF_CARE3.REFERRING_TYPE(+)=3  )
 AND  ( CARE_PROVIDERS7.CARE_ID(+)=PAT_REF_CARE7.CARE_ID  )
 AND  ( CARE_PROVIDERS3.CARE_ID(+)=PAT_REF_CARE3.CARE_ID  )
 AND  (