title: Oracle SQL EXPLAIN PLAN - full table access
body: <p>I have a query that joined up with 5 tables, it executed with elapse time about 0.2 seconds to retrieve 36 records from my DB. Attached below is the analysis of explain plan, as you can see full table access still occur even those tables already came with indexes. </p>
  
  <p><a href="https://i.stack.imgur.com/DoT5V.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DoT5V.png" alt="Exlpian Plan"></a></p>
  
  <p>Anyway if necessary to fine tune the query as below?</p>
  
  <pre><code>SELECT 
  
    CASE WHEN DS.NAME = 'InteractiveCustomer' THEN 'NA' ELSE CUS.SOURCE_SYSTEM END AS SOURCE_SYSTEM, 
    OU.ORGUNIT_CODE AS ORGANIZATION_UNITS, 
    SUM(
      CASE WHEN WS.NAME = 'Pending Autoclosure' THEN 1 ELSE 0 END
    ) AS PENDING_AUTOCLOSURE, 
    SUM(
      CASE WHEN WS.NAME = 'New' THEN 1 ELSE 0 END
    ) AS NEW, 
    SUM(
      CASE WHEN WS.NAME = 'Under Investigation' THEN 1 ELSE 0 END
    ) AS UNDER_INVESTIGATION, 
    SUM(
      CASE WHEN WS.NAME = 'Escalated' THEN 1 ELSE 0 END
    ) AS ESCALATED, 
    SUM(
      CASE WHEN WS.NAME = 'Recommend True Positive' THEN 1 ELSE 0 END
    ) AS RECOMMEND_TRUE_POSITIVE, 
    SUM(
      CASE WHEN WS.NAME = 'Reopen Under Investigation' THEN 1 ELSE 0 END
    ) AS REOPEN_UNDER_INVESTIGATION 
  FROM 
    WORKFLOW_STATUSES WS 
    JOIN WORKFLOW_WORKITEM WW ON WS.ID = WW.STATUS_ID 
    JOIN WLM_ALERT_HEADER WAH ON WW.ENTITY_KEY = WAH.ALERT_KEY 
    INNER JOIN ORGANIZATION_UNITS OU ON OU.ID = WAH.CUSTOMER_ORGUNIT_ID 
    LEFT JOIN CUSTOMERS CUS ON CUS.CUSTOMER_ID = WAH.CUSTOMER_ID 
    INNER JOIN DATA_SOURCE DS ON WAH.AT_DATASOURCE_ID = DS.ID 
  WHERE 
    WW.ENTITY_NAME = 'WLM Alert' 
  GROUP BY 
    OU.ORGUNIT_CODE, 
    CUS.SOURCE_SYSTEM, 
    DS.NAME;
  </code></pre>