title: Oracle composite index not used when joining 2 tables
body: <p>I am using oracle 11.2.0.4.0. First I create 2 tables:</p>
  <pre><code>create table DIAGNOSIS
  (
    pk_guid                VARCHAR2(32) default sys_guid() not null,
    patient_id             VARCHAR2(20),
    visit_no               VARCHAR2(20),
    diagnosis_mode         VARCHAR2(1)
  )
  
  
  create table PAT_VISIT
  (
    pk_guid                    VARCHAR2(32) default sys_guid() not null,
    patient_id                 VARCHAR2(50),
    visit_no                   VARCHAR2(50),
    visit_time                 DATE
  )
  </code></pre>
  <p>Then I add composite indexes for both tables:</p>
  <pre><code>create index IND_PAT_VISIT_1 on PAT_VISIT (PATIENT_ID, VISIT_NO)
  
  create index IND_DIAGNOSIS_1 on DIAGNOSIS (PATIENT_ID, VISIT_NO)
  </code></pre>
  <p>Then I run a SQL query:</p>
  <pre><code>SELECT A.*
  FROM PAT_VISIT A,DIAGNOSIS d
  where a.patient_id= d.patient_id and a.visit_no = d.visit_no
  </code></pre>
  <p>It is fast to get results, and I can see the plan is:</p>
  <pre><code> Plan Hash Value  : 2734544121 
  
  -------------------------------------------------------------------------------------------
  | Id  | Operation               | Name            | Rows   | Bytes     | Cost  | Time     |
  -------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT        |                 | 487790 | 125362030 | 12214 | 00:02:27 |
  | * 1 |   HASH JOIN             |                 | 487790 | 125362030 | 12214 | 00:02:27 |
  |   2 |    INDEX FAST FULL SCAN | IND_DIAGNOSIS_1 | 603733 |  11470927 |   878 | 00:00:11 |
  |   3 |    TABLE ACCESS FULL    | PAT_VISIT       | 487790 | 116094020 |  4682 | 00:00:57 |
  -------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ------------------------------------------
  * 1 - access(&quot;A&quot;.&quot;PATIENT_ID&quot;=&quot;D&quot;.&quot;PATIENT_ID&quot; AND &quot;A&quot;.&quot;VISIT_NO&quot;=&quot;D&quot;.&quot;VISIT_NO&quot;)
  </code></pre>
  <p>I see there is a INDEX FAST FULL SCAN used.</p>
  <p>but when I change the SQL to :</p>
  <pre><code>SELECT *
  FROM PAT_VISIT A inner join DIAGNOSIS d
  on a.patient_id= d.patient_id and a.visit_no = d.visit_no
  </code></pre>
  <p>it is slow to get the results, and the plan is :</p>
  <pre><code> Plan Hash Value  : 1641001685 
  
  ----------------------------------------------------------------------------------
  | Id  | Operation            | Name      | Rows   | Bytes     | Cost  | Time     |
  ----------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT     |           | 487790 | 236578150 | 23877 | 00:04:47 |
  | * 1 |   HASH JOIN          |           | 487790 | 236578150 | 23877 | 00:04:47 |
  |   2 |    TABLE ACCESS FULL | PAT_VISIT | 487790 | 116094020 |  4682 | 00:00:57 |
  |   3 |    TABLE ACCESS FULL | DIAGNOSIS | 603733 | 149122051 |  6035 | 00:01:13 |
  ----------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ------------------------------------------
  * 1 - access(&quot;A&quot;.&quot;PATIENT_ID&quot;=&quot;D&quot;.&quot;PATIENT_ID&quot; AND &quot;A&quot;.&quot;VISIT_NO&quot;=&quot;D&quot;.&quot;VISIT_NO&quot;)
  </code></pre>
  <p>Actually, I need to use <code>left join</code>, and it executes slow and does not use the index either:</p>
  <pre><code>SELECT *
  FROM PAT_VISIT A left join DIAGNOSIS d
  on a.patient_id= d.patient_id and a.visit_no = d.visit_no
  </code></pre>
  <p>The plan is:</p>
  <pre><code> Plan Hash Value  : 1685561654 
  
  ----------------------------------------------------------------------------------
  | Id  | Operation            | Name      | Rows   | Bytes     | Cost  | Time     |
  ----------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT     |           | 487790 | 236578150 | 23877 | 00:04:47 |
  | * 1 |   HASH JOIN OUTER    |           | 487790 | 236578150 | 23877 | 00:04:47 |
  |   2 |    TABLE ACCESS FULL | PAT_VISIT | 487790 | 116094020 |  4682 | 00:00:57 |
  |   3 |    TABLE ACCESS FULL | DIAGNOSIS | 603733 | 149122051 |  6035 | 00:01:13 |
  ----------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ------------------------------------------
  * 1 - access(&quot;A&quot;.&quot;VISIT_NO&quot;=&quot;D&quot;.&quot;VISIT_NO&quot;(+) AND &quot;A&quot;.&quot;PATIENT_ID&quot;=&quot;D&quot;.&quot;PATIENT_ID&quot;(+))
  </code></pre>
  <p>Both are full scan.</p>
  <p>Because it is too slow when I execute the <code>left join</code> SQL, around 40 seconds, but the first SQL (comma sql) is very fast.</p>
  <p>I do not understand why composite index does not work when I use <code>left join</code> or <code>inner join</code>; how can I use the index when I write <code>left join</code>?</p>