CODEWITHSUNDEEP
sqloracle-databasesql-execution-plan
user2102665
user2102665

Reputation: 505

Oracle SQL EXPLAIN PLAN - full table access

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.

Exlpian Plan

Anyway if necessary to fine tune the query as below?

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;

Upvotes: 0

Views: 20517

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

Full table accesses may still occur on a table with an index, even if a query uses an indexed column simply because the query optimizer may deem if faster to blat the entire table data into memory than bother with the indirection of going to the index, looking for the relevant rows, then picking them off the disk

Full table scanning isn't necessarily a bad thing, though it can be a good place to start if a query is unacceptably long running and the suspicion is due to an FTS on a very large table. On small tables a full scan is pretty insignificant

You asked if it was necessary to fine tune the query - my personal opinion on this is "no, not at this stage" - as per my comment, boost the relevant data tables by a million rows, and run it again to get an idea of how it will scale. It's possible you'll get an entirely different plan. Even if it ends up running for 5 seconds, balance that up against how many times this data will be asked for in prod - if it's every 10 seconds then sure, do something about it. If it's once a month when accounts team sends out the invoices, don't bother with it even if it takes a minute

"Premature optimization is the root of all evil"

Upvotes: 8

Related Questions