Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My query has lot of select statements and unions. I think it is querying database multiple times and combining data. Is there a way we can eliminate multiple passes to the database?

I would like to reduce the execution as it is taking many hours to complete..

SELECT '2013' PER,
  BK.PROD,
  SEG.TRAV_ON,
  SEG.REC,
  SEG.FRQ,
  SUM (BK.HH_PROD) HHR,
  SUM (BK.NET_PAX) NET_PAX,
  SUM (BK.NET_SALES) NET_SALES,
  SUM (BK.GP) GP,
  SUM (0) VA_GP,
  SUM (0) ADV,
  SYSDATE
FROM
  (SELECT AL3.BASE_CODE PROD,
    AL1.EPSILON_CUSTOMER_NUMBER,
    SUM (AL2.NET_CORP_HH_RENEWAL) HH_CORP,
    SUM (AL2.NET_BASE_CODE_HH_RENEWAL) HH_PROD,
    SUM (AL2.NET_PAX) NET_PAX,
    SUM (AL2.NET_SALE_AMT) NET_SALES,
    SUM (AL2.GROSS_PROFIT) GP
  FROM DM.DM_CUSTOMER_DIM AL1,
    DM.DM_DAILY_TRANSACTION_FCT AL2,
    DM.DM_PRODUCT_HIERARCHY_MKTRPT_VW AL3,
    DM.DM_DATE_DIM AL4
  WHERE AL1.DM_CUSTOMER_DKEY = AL2.DM_CUSTOMER_DKEY
  AND AL3.DM_PRODUCT_HKEY    = AL2.DM_PRODUCT_HKEY
  AND AL4.DM_DATE_DKEY       = AL2.DM_TRANSACTION_DT_DKEY
  AND AL2.PRODUCT_YEAR       = 2013
  AND AL4.CALENDAR_DATE     <= TRUNC (SYSDATE, 'day') - 1
  AND AL3.BASE_CODE                                  IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB' , 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA4', 'NEL', 'TMH', 'FCT', 'MYR')
  GROUP BY AL3.BASE_CODE,
    AL1.EPSILON_CUSTOMER_NUMBER
  ) BK
LEFT JOIN
  (SELECT TRAV_ON,
    REC,
    FREQUENCY_GRP_2 FRQ,
    EPSILON_CUSTOMER_NUMBER
  FROM AW.WRK_EVR_NVR_CUST_NBR_2013
  ) SEG
ON BK.EPSILON_CUSTOMER_NUMBER = SEG.EPSILON_CUSTOMER_NUMBER
GROUP BY BK.PROD,
  SEG.TRAV_ON,
  SEG.REC,
  SEG.FRQ
UNION ALL
SELECT '2013' PER,
  VA.PROD,
  SEG.TRAV_ON,
  SEG.REC,
  SEG.FRQ,
  SUM (0) HHR,
  SUM (0) NET_PAX,
  SUM (0) NET_SALES,
  SUM (0) GP,
  SUM (VA.VA_GP) VA_GP,
  SUM (0) ADV,
  SYSDATE
FROM
  (SELECT AL3.BASE_CODE PROD,
    REF_CST.EPSILON_CUSTOMER_NUMBER,
    SUM (AL2.GROSS_PROFIT) VA_GP
  FROM DM.DM_DAILY_TRANSACTION_FCT AL2,
    DM.DM_PRODUCT_HIERARCHY_MKTRPT_VW AL3,
    DM.DM_DATE_DIM AL4,
    AW.WRK_EVR_NVR_CUST_NBR_2013 AL6,
    DM.DM_CUSTOMER_DIM_EPSILON_FLAG AL7,
    (SELECT AL1.RESERVATION_NUMBER,
      AL2.EPSILON_CUSTOMER_NUMBER
    FROM DM.DM_DAILY_RESERVATION_FCT AL1,
      DM.DM_CUSTOMER_DIM AL2,
      DM.DM_DATE_DIM AL3
    WHERE AL1.DM_REFERRAL_CUSTOMER_DKEY = AL2.DM_CUSTOMER_DKEY
    AND AL1.DM_RESERVATION_DT_DKEY      = AL3.DM_DATE_DKEY
    AND AL1.PRODUCT_YEAR                = 2013
    AND AL3.CALENDAR_DATE              <= TRUNC (SYSDATE, 'day') - 1
    GROUP BY AL1.RESERVATION_NUMBER,
      AL2.EPSILON_CUSTOMER_NUMBER
    ) REF_CST
  WHERE AL2.RESERVATION_NUMBER    = REF_CST.RESERVATION_NUMBER
  AND AL3.DM_PRODUCT_HKEY         = AL2.DM_PRODUCT_HKEY
  AND AL6.EPSILON_CUSTOMER_NUMBER = AL7.EPSILON_CUSTOMER_NUMBER
  AND AL7.DM_CUSTOMER_DKEY        = AL2.DM_CUSTOMER_DKEY
  AND AL4.DM_DATE_DKEY            = AL2.DM_TRANSACTION_DT_DKEY
  AND AL6.REC                    IN ('VA')
  AND AL2.PRODUCT_YEAR            = 2013
  AND AL4.CALENDAR_DATE          <= TRUNC (SYSDATE, 'day') - 1
  AND AL3.BASE_CODE                                       IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB' , 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA4', 'NEL', 'TMH', 'FCT', 'MYR')
  GROUP BY AL3.BASE_CODE,
    REF_CST.EPSILON_CUSTOMER_NUMBER
  ) VA
LEFT JOIN
  (SELECT TRAV_ON,
    REC,
    FREQUENCY_GRP_2 FRQ,
    EPSILON_CUSTOMER_NUMBER
  FROM AW.WRK_EVR_NVR_CUST_NBR_2013
  ) SEG
ON VA.EPSILON_CUSTOMER_NUMBER = SEG.EPSILON_CUSTOMER_NUMBER
GROUP BY VA.PROD,
  SEG.TRAV_ON,
  SEG.REC,
  SEG.FRQ
UNION ALL
SELECT '2013' PER,
  CASE
    WHEN AL3.BASE_CODE_SUMMARY = 'TMHTMR'
    THEN 'TMH'
    WHEN AL3.BASE_CODE_SUMMARY = 'YA5YA4'
    THEN 'YA4'
    ELSE AL3.BASE_CODE_SUMMARY
  END PROD,
  AL1.TRAV_ON,
  AL1.REC,
  AL1.FREQUENCY_GRP_2 FRQ,
  0 HHR,
  0 NET_PAX,
  0 NET_SALES,
  0 GP,
  0 VA_GP,
  SUM (AL3.TIMED_UNIT_ADV_COST) ADV,
  SYSDATE
FROM AW.WRK_EVR_NVR_CUST_NBR_2013 AL1,
  AW.CM_ADV_2013 AL3
WHERE AL1.EPSILON_CUSTOMER_NUMBER = AL3.EPSILON_CUSTOMER_NUMBER
AND AL3.BASE_CODE_SUMMARY        IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB', 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA5YA4', 'NEL', 'TMHTMR', 'FCT', 'MYR')
GROUP BY AL3.BASE_CODE_SUMMARY,
  AL1.TRAV_ON,
  AL1.REC,
  AL1.FREQUENCY_GRP_2
UNION ALL
SELECT '2012' PER,
  BK.PROD,
  SEG.TRAV_ON,
  SEG.REC,
  SEG.FRQ,
  SUM (BK.HH_PROD) HHR,
  SUM (BK.NET_PAX) NET_PAX,
  SUM (BK.NET_SALES) NET_SALES,
  SUM (BK.GP) GP,
  SUM (0) VA_GP,
  SUM (0) ADV,
  SYSDATE
FROM
  (SELECT AL3.BASE_CODE PROD,
    AL1.EPSILON_CUSTOMER_NUMBER,
    SUM (AL2.NET_CORP_HH_RENEWAL) HH_CORP,
    SUM (AL2.NET_BASE_CODE_HH_RENEWAL) HH_PROD,
    SUM (AL2.NET_PAX) NET_PAX,
    SUM (AL2.NET_SALE_AMT) NET_SALES,
    SUM (AL2.GROSS_PROFIT) GP
  FROM DM.DM_CUSTOMER_DIM AL1,
    DM.DM_DAILY_TRANSACTION_FCT AL2,
    DM.DM_PRODUCT_HIERARCHY_MKTRPT_VW AL3,
    DM.DM_DATE_DIM AL4
  WHERE AL1.DM_CUSTOMER_DKEY = AL2.DM_CUSTOMER_DKEY
  AND AL3.DM_PRODUCT_HKEY    = AL2.DM_PRODUCT_HKEY
  AND AL4.DM_DATE_DKEY       = AL2.DM_TRANSACTION_DT_DKEY
  AND AL2.PRODUCT_YEAR       = 2012
  AND AL4.CALENDAR_DATE     <= TRUNC (SYSDATE, 'day') - 365
  AND AL3.BASE_CODE                                  IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB' , 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA4', 'NEL', 'TMH', 'FCT', 'MYR')
  GROUP BY AL3.BASE_CODE,
    AL1.EPSILON_CUSTOMER_NUMBER
  ) BK
LEFT JOIN
  (SELECT TRAV_ON,
    REC,
    FREQUENCY_GRP_2 FRQ,
    EPSILON_CUSTOMER_NUMBER
  FROM AW.WRK_EVR_NVR_CUST_NBR_2012
  ) SEG
ON BK.EPSILON_CUSTOMER_NUMBER = SEG.EPSILON_CUSTOMER_NUMBER
GROUP BY BK.PROD,
  SEG.TRAV_ON,
  SEG.REC,
  SEG.FRQ
UNION ALL
SELECT '2012' PER,
  VA.PROD,
  SEG.TRAV_ON,
  SEG.REC,
  SEG.FRQ,
  SUM (0) HHR,
  SUM (0) NET_PAX,
  SUM (0) NET_SALES,
  SUM (0) GP,
  SUM (VA.VA_GP) VA_GP,
  SUM (0) ADV,
  SYSDATE
FROM
  (SELECT AL3.BASE_CODE PROD,
    REF_CST.EPSILON_CUSTOMER_NUMBER,
    SUM (AL2.GROSS_PROFIT) VA_GP
  FROM DM.DM_DAILY_TRANSACTION_FCT AL2,
    DM.DM_PRODUCT_HIERARCHY_MKTRPT_VW AL3,
    DM.DM_DATE_DIM AL4,
    AW.WRK_EVR_NVR_CUST_NBR_2013 AL6,
    DM.DM_CUSTOMER_DIM_EPSILON_FLAG AL7,
    (SELECT AL1.RESERVATION_NUMBER,
      AL2.EPSILON_CUSTOMER_NUMBER
    FROM DM.DM_DAILY_RESERVATION_FCT AL1,
      DM.DM_CUSTOMER_DIM AL2,
      DM.DM_DATE_DIM AL3
    WHERE AL1.DM_REFERRAL_CUSTOMER_DKEY = AL2.DM_CUSTOMER_DKEY
    AND AL1.DM_RESERVATION_DT_DKEY      = AL3.DM_DATE_DKEY
    AND AL1.PRODUCT_YEAR                = 2012
    AND AL3.CALENDAR_DATE              <= TRUNC (SYSDATE, 'day') - 365
    GROUP BY AL1.RESERVATION_NUMBER,
      AL2.EPSILON_CUSTOMER_NUMBER
    ) REF_CST
  WHERE AL2.RESERVATION_NUMBER    = REF_CST.RESERVATION_NUMBER
  AND AL3.DM_PRODUCT_HKEY         = AL2.DM_PRODUCT_HKEY
  AND AL6.EPSILON_CUSTOMER_NUMBER = AL7.EPSILON_CUSTOMER_NUMBER
  AND AL7.DM_CUSTOMER_DKEY        = AL2.DM_CUSTOMER_DKEY
  AND AL4.DM_DATE_DKEY            = AL2.DM_TRANSACTION_DT_DKEY
  AND AL6.REC                    IN ('VA')
  AND AL2.PRODUCT_YEAR            = 2012
  AND AL4.CALENDAR_DATE          <= TRUNC (SYSDATE, 'day') - 365
  AND AL3.BASE_CODE                                       IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB' , 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA4', 'NEL', 'TMH', 'FCT', 'MYR')
  GROUP BY AL3.BASE_CODE,
    REF_CST.EPSILON_CUSTOMER_NUMBER
  ) VA
LEFT JOIN
  (SELECT TRAV_ON,
    REC,
    FREQUENCY_GRP_2 FRQ,
    EPSILON_CUSTOMER_NUMBER
  FROM AW.WRK_EVR_NVR_CUST_NBR_2012
  ) SEG
ON VA.EPSILON_CUSTOMER_NUMBER = SEG.EPSILON_CUSTOMER_NUMBER
GROUP BY VA.PROD,
  SEG.TRAV_ON,
  SEG.REC,
  SEG.FRQ
UNION ALL
SELECT '2012' PER,
  CASE
    WHEN AL3.BASE_CODE_SUMMARY = 'TMHTMR'
    THEN 'TMH'
    WHEN AL3.BASE_CODE_SUMMARY = 'YA5YA4'
    THEN 'YA4'
    ELSE AL3.BASE_CODE_SUMMARY
  END PROD,
  AL1.TRAV_ON,
  AL1.REC,
  AL1.FREQUENCY_GRP_2 FRQ,
  0 HHR,
  0 NET_PAX,
  0 NET_SALES,
  0 GP,
  0 VA_GP,
  SUM (AL3.TIMED_UNIT_ADV_COST) ADV,
  SYSDATE
FROM AW.WRK_EVR_NVR_CUST_NBR_2012 AL1,
  AW.CM_ADV_2012_PIT AL3
WHERE AL1.EPSILON_CUSTOMER_NUMBER = AL3.EPSILON_CUSTOMER_NUMBER
AND AL3.BASE_CODE_SUMMARY        IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB', 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA5YA4', 'NEL', 'TMHTMR', 'FCT', 'MYR')
GROUP BY AL3.BASE_CODE_SUMMARY,
  AL1.TRAV_ON,
  AL1.REC,
  AL1.FREQUENCY_GRP_2
UNION ALL
SELECT '2013P' PER,
  PROD,
  TRAVEL_ON,
  REC,
  FRQ,
  SUM (HH_PROD) HHR,
  SUM (NET_PAX) NET_PAX,
  SUM (NET_SALES) NET_SALES,
  SUM (GP) GP,
  SUM (VA_GP) VA_GP,
  SUM (0) ADV,
  SYSDATE
FROM (
  (SELECT AL3.BASE_CODE PROD,
    AL5.TRAV_ON_SEGMENT AS TRAVEL_ON,
    CASE
      WHEN AL1.RECENCY IN ('LTRAV1YR', 'LTRAV2YR', 'VA', 'GROUPSPR')
      THEN AL1.RECENCY
      WHEN AL1.RECENCY IN ('NTFOTHER', 'PROSPECT')
      THEN 'PROSPECT'
      ELSE 'REACT'
    END REC,
    AL1.FREQUENCY_GRP_2 FRQ,
    SUM (AL2.HH_RENEWED_BASECODE) HH_PROD,
    SUM (AL2.NET_PAX) NET_PAX,
    SUM (AL2.NET_SALES) NET_SALES,
    SUM (AL2.GROSS_PROFIT) GP,
    SUM (AL2.VA_GROSS_PROFIT) VA_GP,
    SUM (0) ADV
  FROM DM.DM_SEGMENTATION_DIM AL1,
    AW.DM_PLAN_TIMED_TRVON_BKG AL2,
    DM.DM_PRODUCT_HIERARCHY_MKTRPT_VW AL3,
    DM.DM_DATE_DIM AL4,
    DM.DM_REF_TRAVEL_ON_SEGMENT_DIM AL5
  WHERE AL1.DM_SEGMENTATION_DKEY = AL2.DM_SEGMENTATION_DKEY
  AND AL5.DM_TRAV_ON_SEGM_DKEY   = AL2.DM_TRAVEL_ON_DKEY
  AND AL3.DM_PRODUCT_HKEY        = AL2.DM_PRODUCT_HKEY
  AND AL4.DM_DATE_DKEY           = AL2.DM_BOOK_WEEK_END_DKEY
  AND AL2.YEAR                   = 2013
  AND AL4.CALENDAR_DATE         <= TRUNC (SYSDATE, 'day') - 1
  AND AL3.BASE_CODE                                      IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB', 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA4', 'NEL', 'TMH', 'FCT', 'MYR')
  GROUP BY AL3.BASE_CODE,
    AL5.TRAV_ON_SEGMENT ,
    CASE
      WHEN AL1.RECENCY IN ('LTRAV1YR', 'LTRAV2YR', 'VA', 'GROUPSPR')
      THEN AL1.RECENCY
      WHEN AL1.RECENCY IN ('NTFOTHER', 'PROSPECT')
      THEN 'PROSPECT'
      ELSE 'REACT'
    END,
    AL1.FREQUENCY_GRP_2
  ) )
GROUP BY PROD,
  TRAVEL_ON,
  REC,
  FRQ
UNION ALL
SELECT '2013P' PER,
  AL3.BASE_CODE PROD,
  AL4.TRAV_ON_SEGMENT AS TRAVEL_ON,
  CASE
    WHEN AL1.RECENCY IN ('LTRAV1YR', 'LTRAV2YR', 'VA', 'GROUPSPR')
    THEN AL1.RECENCY
    WHEN AL1.RECENCY IN ('NTFOTHER', 'PROSPECT')
    THEN 'PROSPECT'
    ELSE 'REACT'
  END REC,
  AL1.FREQUENCY_GRP_2 FRQ,
  SUM (0) HHR,
  SUM (0) NET_PAX,
  SUM (0) NET_SALES,
  SUM (0) GP,
  SUM (0) VA_GP,
  SUM (AL2.ADV_COST) ADV,
  SYSDATE
FROM DM.DM_SEGMENTATION_DIM AL1,
  AW.DM_PLAN_TIMED_TRVON_ADV AL2,
  DM.DM_PRODUCT_HIERARCHY_MKTRPT_VW AL3,
  DM.DM_DATE_DIM AL4,
  DM.DM_REF_TRAVEL_ON_SEGMENT_DIM AL4
WHERE AL1.DM_SEGMENTATION_DKEY = AL2.DM_SEGMENTATION_DKEY
AND AL3.DM_PRODUCT_HKEY        = AL2.DM_PRODUCT_HKEY
AND AL4.DM_TRAV_ON_SEGM_DKEY   = AL2.DM_TRAVEL_ON_DKEY
AND AL4.DM_DATE_DKEY           = AL2.DM_BOOK_WEEK_END_DKEY
AND AL2.YEAR                   = 2013
AND AL2.SCENARIO               = 'Plan 6'
AND AL2.ITERATION              = 1
AND AL4.CALENDAR_DATE         <= TRUNC (SYSDATE, 'day') - 1
AND AL3.BASE_CODE                                      IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB', 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA4', 'NEL', 'TMH', 'FCT', 'MYR' )
GROUP BY AL3.BASE_CODE,
  AL4.TRAV_ON_SEGMENT,
  CASE
    WHEN AL1.RECENCY IN ('LTRAV1YR', 'LTRAV2YR', 'VA', 'GROUPSPR')
    THEN AL1.RECENCY
    WHEN AL1.RECENCY IN ('NTFOTHER', 'PROSPECT')
    THEN 'PROSPECT'
    ELSE 'REACT'
  END,
  AL1.FREQUENCY_GRP_2

Here is the link for Explain_Plan https://docs.google.com/file/d/0B3lwUG79n7JFekxQZnVvYmdGSHc/edit

share|improve this question

migrated from stackoverflow.com Jun 27 '13 at 12:12

This question came from our site for professional and enthusiast programmers.

5  
Every time you ask a performance question you're told that an execution plan is needed, but you never supply one. Perhaps you should ask how to get an exectuion plan. –  David Aldridge Jun 24 '13 at 18:20
    
Hello David, Added Url for Explain_Plan doc –  user2503175 Jun 24 '13 at 18:39
    
Look into using Commont Table Expressions, the WITH clause, to reduce the code. It looks like the last and second-to-last query are almost identical. Also, for the explain plans, make sure to always include the cardinality. That's much more important than the cost, since it will often tell you when Oracle made a big mistake. For example, if Oracle thinks a join produces 1 row, but you know it actually produces 1 million, that's likely going to be a problem. –  jonearles Jun 25 '13 at 5:28
    
There appear to be pairs of similar queries that differ only in that they pull data from different years. If I were you, I might try combining them. The way the short table aliases are assigned, though, it's difficult to work with your query for someone not very much familiar with the schema. It would be easier if aliases were more consistent (i.e. if identical tables were assigned identical aliases as often as possible, as well as if the aliases themselves were easier to associate with the tables they are assigned to, e.g. instead of DM.DM_CUSTOMER_DIM AL1 –> DM.DM_CUSTOMER_DIM CD). –  Andriy M Jun 29 '13 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.