troubleshooting Question

Oracle SQL performance tuning

Avatar of sventhan
sventhanFlag for United States of America asked on
Oracle Database
10 Comments1 Solution509 ViewsLast Modified:
Experts -

I've this SQL running slow when using nested loop but not with Hash Join as explained below. I'm just looking for the reason why its NOT using Hash Join as a optimal method to execute this SQL.

1) When the criteria AND AL1.CUST_NUMBER = AL1.EN_CUST_NUMBER  is used Oracle is choosing a nested loop join that runs in 10 minutes. It takes 3300 sec just to scan DM_CUST_DIM.
2) When I replace this wiht the  with a case statement (commented out), Oracle is choosing a HASH JOIN that runs in 1 minute.


Is there any explanation?



SELECT  AL1.EN_CUST_NUMBER,
     AL3.RECENCY,
     AL3.FREQUENCY_DETAIL
  FROM DM.DM_CUST_DIM AL1,
     DM.DM_CUST_SEG AL2,
     DM.DM_SEGMENT_DIM AL3,
     DM.DM_REF_DIM AL4,
     DM.DM_TRAVELER_STATUS_DIM AL5
  WHERE   AL1.DM_CUST_DKEY = AL2.DM_CUST_DKEY
     AND AL2.DM_SEGMENT_DKEY = AL3.DM_SEGMENT_DKEY
     AND AL2.DM_TRAV_ON_SEGM_DKEY = AL4.DM_TRAV_ON_SEGM_DKEY
     AND AL2.DM_STATUS_DKEY = AL5.DM_STATUS_DKEY
     --**********************************************************
     --   AND AL1.EN_CUST_NUMBER = 90909300
     --*********************************************************
     AND AL2.REFERENCE_YEAR IN (2014)
     
       AND AL1.CUST_NUMBER = AL1.EN_CUST_NUMBER  -- Replaced by case statemtn below to influence better optimizer choice
     /* AND CASE
        WHEN AL1.EN_CUST_NUMBER = AL1.CUST_NUMBER THEN 'Y'
        ELSE 'N'
       END = 'Y' */

Thanks,
sve
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros