title: Oracle not using index when joining
body: <p>I am very new to index and explain plans, so please bear with me!
  I am trying to tune a query but I am having issues.</p>
  
  <p>I have two tables:</p>
  
  <pre><code>SKU
  ------
  SKUIDX (Unique index)
  CLRIDX (Index)
  ..
  ..
  
  IMPCOST_CLR
  -----------
  ICCIDX (Unique index)
  CLRIDX (Index)
  ...
  ..
  </code></pre>
  
  <p>When I do a <code>select * from SKU where clridx = 122</code>, I can see that it is using the index in the explain plan (it says TABLE ACCESS.. INDEX, it says the index name under OBJECT_NAME and the options is RANGE SCAN).</p>
  
  <p>Now, when I try to join on the same field, it doesn't appear to use the index (it says TABLE ACCESS.. HASH JOIN and under options, it says FULL).</p>
  
  <p>What should I be looking for to try and see why it isn't using the index?
  Sorry, I'm not sure what commands to type to show this so please let me know if you need more information.</p>
  
  <p>Examples:<br>
  1st query:</p>
  
  <pre><code>  SELECT
      *
    FROM
      AP21.SKU
    WHERE
      CLRIDX = 100
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/Zo08a.png" alt="enter image description here"></p>
  
  <p>2nd query: </p>
  
  <pre><code>  SELECT
      *
    FROM
      AP21.IMPCOST_CLR
    WHERE
      CLRIDX = 100
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/mlaYa.png" alt="enter image description here"></p>
  
  <p>3rd query:</p>
  
  <pre><code>  SELECT
      *
    FROM
      AP21.SKU
    INNER JOIN
      AP21.IMPCOST_CLR ON
      IMPCOST_CLR.CLRIDX = SKU.CLRIDX
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/ZHlga.png" alt="enter image description here"></p>