Query performance problem [message #440451] |
Mon, 25 January 2010 00:24 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mbmalasenthil
Messages: 27 Registered: July 2008
|
Junior Member |
|
|
Hi Friends,
I have the following query which takes hours to execute. The query selects records from the table COMPETITIVE_RATE_SHOPPING_FACT. The columns in the where clause have unique index created on them.
SELECT
CURR.BOOKING_RATE_CALENDAR_KEY,
CURR.ARV_CALENDAR_KEY,
CURR.SITE_KEY AS SITE_KEY,
CURR.DAYS_TO_ARRIVAL_DATE,
CURR.LENGTH_OF_STAY,
CURR.NUMBER_OF_GUEST,
CURR.DATA_SOURCE_CODE,
CURR.COMPETITOR_PRODUCT_NAME,
MIN(CURR.RATE_AMT_USD) AS MIN_RATE_AMT_USD,
(
SELECT
MIN(PREV.RATE_AMT_USD)
FROM
OPERATIONS.COMPETITIVE_RATE_SHOPPING_FACT PREV
WHERE
PREV.ARV_CALENDAR_KEY = CURR.ARV_CALENDAR_KEY AND
PREV.SITE_KEY = CURR.SITE_KEY AND
PREV.LENGTH_OF_STAY = CURR.LENGTH_OF_STAY AND
PREV.NUMBER_OF_GUEST = CURR.NUMBER_OF_GUEST AND
PREV.DATA_SOURCE_CODE = CURR.DATA_SOURCE_CODE AND
PREV.COMPETITOR_PRODUCT_NAME = CURR.COMPETITOR_PRODUCT_NAME AND
PREV.BOOKING_RATE_CALENDAR_KEY = CURR.BOOKING_RATE_CALENDAR_KEY - 7 AND
PREV.REPORTING_COMPETITOR_ID = PREV.RUBICON_PROPERTY_ID
) AS MIN_PREV_RATE_AMT_USD
FROM
OPERATIONS.COMPETITIVE_RATE_SHOPPING_FACT CURR
WHERE
CURR.REPORTING_COMPETITOR_ID = CURR.RUBICON_PROPERTY_ID AND
CURR.BOOKING_RATE_CALENDAR_KEY > 0 AND
CURR.DM_INSERTED_DATE >= (SELECT MAX(C.DM_INSERTED_DATE) - 1 FROM OPERATIONS.COMPETITIVE_RATE_SHOPPING_FACT C)
GROUP BY
CURR.BOOKING_RATE_CALENDAR_KEY,
CURR.BOOKING_RATE_DAY_TIME_KEY,
CURR.ARV_CALENDAR_KEY,
CURR.DAYS_TO_ARRIVAL_DATE,
CURR.LENGTH_OF_STAY,
CURR.NUMBER_OF_GUEST,
CURR.DATA_SOURCE_CODE,
CURR.RUBICON_PROPERTY_ID,
CURR.COMPETITOR_PRODUCT_NAME,
CURR.SITE_KEY;
Can you please advice me on what could be reason for the performance? I have also attached the plan for the above query in Plan.jpg file. Any help would be appreciated.
Thanks,
Senthil
-
Attachment: Plan.JPG
(Size: 13.40KB, Downloaded 865 times)
|
|
|
|
Re: Query performance problem [message #440512 is a reply to message #440452] |
Mon, 25 January 2010 05:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
The problem is the scalar sub-query in the SELECT clause. It gets executed for every row returned.
Run it with the scalar sub-query removed and compare the performance. I expect you will find it goes from hours down to minutes or even seconds.
If I am right, you need to find another way to write the query - as a join most probably.
Ross Leishman
|
|
|