Hi Gurus and top brains, I have a complicated performance and tuning issue for a query. Herewith I am sending Detailed description of the data and query. I have used analytical function reduced From 9 to 13 minutes in to 3 minutes. But my client is asking less than a minute. I am expecting a new approach to get less than one minute. Regards Kanagaraj Table WBR having records counts 1,87,48,468 for two years data with following structure timebyday_id number, - > A Market_id number, - > B Current_Year_sal_value number, - > C previous_year_sal_value number, - > D Current_Year_sal_qty number, - > E previous_youer_sales_qty number - > F There are many columns in this table but not required for my query is above mentioned columns only primary keys are timebyday_id, Hierarchy_id. But for my query timebayday_id PK is required Functionality of this query Weekly Business Review 14 days Rollback Q1.Average of aggretegate values of previous_year_sal_value, Current_Year_sal_qty, previous_youer_sales_qty for every 14days values from given data to previous sixmonths Q2.Average of aggretegate values of Current_Year_sal_value, previous_year_sal_value, Current_Year_sal_qty, previous_youer_sales_qty for every 14days From previous year date of given data to previous sixmonths Sample data in the table WBR ------------------------------------------------ A B C D E F ------------------------------------------------ 20030101 21 11 5 . 20030201 21 12 10 . 20030301 21 13 15 . 20030401 21 14 20 . 20030501 21 15 25 . 20030601 21 16 30 ,, ,,, 20040101 21 41 11 95 5 . 20040201 21 42 12 100 10 . 20040301 21 43 13 105 15 . 20040401 21 44 14 110 20 . 20040501 21 45 15 115 25 . 20040601 21 46 16 120 30 ,, ,,,, 20050101 21 91 41 265 95 . 20050201 21 92 42 270 100 . 20050301 21 93 53 275 105 . 20050401 21 94 64 280 110 . 20050501 21 95 75 285 115 . 20050601 21 96 86 290 120 ,, ,,,, ------------------------------------------------ Out put should be like this only for paramentes business date 20050601 and market_id 21 -------------------------------------------------------------------------------------------------------------- Day C D E F --------------------------------------------------------------------------------------------------------------- Current year 14days 20050601 sum(C values from 20050601 to previous 14 dayes)/14 20050531 sum(C values from 20050531 to previous 14 dayes)/14 ....... 20050518 sum(C values from 20050518 to previous 14 dayes)/14 20050517 sum(C values from 20050517 to previous 14 dayes)/14 ...... 20050102 sum(C values from 20050102 to previous 14 dayes)/14 (This is last date for 2005 But it should take next 14 days) 20050101 sum(C values from 20050101 to previous 14 dayes)/14 Previous year 14days 20040601 sum(C values from 20050601 to previous 14 dayes)/14 20040531 sum(C values from 20050531 to previous 14 dayes)/14 ....... 20040518 sum(C values from 20050518 to previous 14 dayes)/14 20040517 sum(C values from 20050517 to previous 14 dayes)/14 ...... 20040102 sum(C values from 20050102 to previous 14 dayes)/14 (This is last date for 2004 But it should take next 14 days) 20040101 sum(C values from 20050101 to previous 14 dayes)/14 --------------------------------------------------------------------------------------------------------------- SELECT TO_DATE(current_year.timebyday_id,'YYYYMMDD') DAT ,'CY' Current_YEAR ,SUBSTR(current_year.timebyday_id,1,4) ,current_year.Cy_sales ,current_year.Cy_py_sales ,current_year.Cy_units ,current_year.Cy_py_units , ( SELECT week_no FROM BO_TIME_BY_DAY WHERE timebyday_id=current_year.timebyday_id) Timebyday_id, NULL, NULL, NULL FROM ( SELECT timebyday_id ,AVG(SUM(actual_net_sales_amt+non_product_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING) Cy_sales, AVG(SUM(prev_year_actual_net_sales_amt+prev_year_non_product_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING) Cy_py_sales, AVG(SUM(actual_trans_count)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING) Cy_units, AVG(SUM(prev_year_actual_trans_count)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING) Cy_py_units FROM bo_daily_busINess_ctrl_fact WHERE timebyday_id >=(SELECT TO_NUMBER(TO_CHAR(MAX(ADD_MONTHS(TIME_BY_DAY_DATE,-6))-14,'YYYYMMDD')) FROM bo_time_by_day WHERE year||LPAD(week_no,2,0) IN (200535)) AND timebyday_id <=(SELECT TO_NUMBER(TO_CHAR(MAX(TIME_BY_DAY_DATE),'YYYYMMDD')) FROM bo_time_by_day WHERE ear||LPAD(week_no,2,0) IN (200535)) AND market_id IN (SELECT distinct market_id FROM bo_region_dim WHERE MARKET_NAME IN ('Netherlands') ) AND sales_comp_flag = 1 AND DECODE(nvl(BO_DAILY_BUSINESS_CTRL_FACT.ACTUAL_TRANS_COUNT,0),0,0, DECODE(nvl(BO_DAILY_BUSINESS_CTRL_FACT.PREV_YEAR_ACTUAL_TRANS_COUNT,0),0,0,1)) = 1 GROUP BY timebyday_id ) Current_Year WHERE timebyday_id >=(SELECT TO_NUMBER(TO_CHAR(MAX(ADD_MONTHS((TIME_BY_DAY_DATE),-6)),'YYYYMMDD')) FROM bo_time_by_day WHERE year||LPAD(week_no,2,0) IN (200535) ) UNION ALL SELECT ADD_MONTHS(TO_DATE(previous_YEAR.timebyday_id,'YYYYMMDD'),12) DAT, 'PY' previous_YEAR, SUBSTR(previous_YEAR.timebyday_id,1,4), previous_YEAR.py_sales, previous_YEAR.py_py_sales, previous_YEAR.py_units, previous_YEAR.py_py_units, (SELECT week_no FROM bo_time_by_day WHERE timebyday_id=previous_YEAR.timebyday_id) Timebyday_id, NULL, NULL, NULL FROM ( SELECT timebyday_id, AVG(SUM(actual_net_sales_amt+non_product_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING ) Py_sales, AVG(SUM(prev_year_actual_net_sales_amt+prev_year_non_product_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING ) Py_py_sales, AVG(SUM(actual_trans_count)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING ) Py_units, AVG(SUM(prev_year_actual_trans_count)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING ) Py_py_units FROM bo_daily_busINess_ctrl_fact WHERE timebyday_id >=(SELECT TO_NUMBER(TO_CHAR(MAX(ADD_MONTHS(TIME_BY_DAY_DATE,-18))-14,'YYYYMMDD')) FROM bo_time_by_day WHERE year||LPAD(week_no,2,0) IN (200535)) and timebyday_id <=(SELECT TO_NUMBER(TO_CHAR(MAX(ADD_MONTHS(TIME_BY_DAY_DATE,-12)),'YYYYMMDD')) FROM bo_time_by_day WHERE year||LPAD(week_no,2,0) IN (200510)) AND market_id IN ( SELECT distINct market_id FROM bo_region_dim WHERE MARKET_NAME IN ('Netherlands') ) AND sales_comp_flag = 1 and DECODE(nvl(BO_DAILY_BUSINESS_CTRL_FACT.ACTUAL_TRANS_COUNT,0),0,0, DECODE(nvl(BO_DAILY_BUSINESS_CTRL_FACT.PREV_YEAR_ACTUAL_TRANS_COUNT,0),0,0,1 )) = 1 GROUP BY timebyday_id ) Previous_Year WHERE timebyday_id >=(SELECT TO_NUMBER(TO_CHAR(MAX(ADD_MONTHS((TIME_BY_DAY_DATE),-18)),'YYYYMMDD')) FROM bo_time_by_day WHERE year||LPAD(week_no,2,0) in (200535)) TIMEBYDAY_ID CY_SALES CY_PY_SALES CY_UNITS CY_PY_UNITS ------------ ---------- ----------- ---------- ----------- 20051231 0 264248127 0 20466552.2 20051230 0 271516468 0 20950006.6 20051229 0 268323208 0 20914534.5 20051228 0 265293538 0 20920500.8 20051227 0 261905513 0 20895479.6 20051226 0 258822843 0 20839967 20051225 0 262807250 0 21012925.3 20051224 0 280268660 0 22535302.1 20051223 0 289643316 0 23128563.6 20051222 0 287374533 0 23058311.8 20051221 0 284322214 0 22995455.6 TIMEBYDAY_ID CY_SALES CY_PY_SALES CY_UNITS CY_PY_UNITS ------------ ---------- ----------- ---------- ----------- 20051220 229.867857 280298147 47.2142857 22857799.9 20051219 229.867857 276697233 47.2142857 22768174.8 20051218 229.867857 275427087 47.2142857 22788209.1 20051217 229.867857 275293875 47.2142857 22803143.9 20051216 388.347857 275896746 89.3571429 22793766.1 20051215 388.347857 274756216 89.3571429 22766990.7 20051214 388.347857 273483105 89.3571429 22729501.2 20051213 388.347857 272454439 89.3571429 22722756.4 20051212 388.347857 270852297 89.3571429 22702828 20051211 388.347857 268804273 89.3571429 22678147.6 20051210 388.347857 265813931 89.3571429 22538266.9 TIMEBYDAY_ID CY_SALES CY_PY_SALES CY_UNITS CY_PY_UNITS ------------ ---------- ----------- ---------- ----------- 20051209 388.347857 264057461 89.3571429 22382173.4 20051208 388.347857 259267925 89.3571429 21548099.4 20051207 388.347857 258568048 89.3571429 21559185.4 20051206 158.48 257739979 42.1428571 21568911.3 20051205 158.48 256743968 42.1428571 21553963.9 20051204 158.48 255095878 42.1428571 21517502.4 20051203 158.48 252954735 42.1428571 21444146.9 20051202 0 251428864 0 21408553.4 20051201 0 250731359 0 21352156.2 20051130 0 250319191 0 21297673.7 20051129 0 250192120 0 21262874 TIMEBYDAY_ID CY_SALES CY_PY_SALES CY_UNITS CY_PY_UNITS ------------ ---------- ----------- ---------- ----------- 20051128 0 250683089 0 21269530.2 20051127 0 250827932 0 21284198.5 20051126 0 252036314 0 21405185.6 20051125 0 253185733 0 21558247 20051124 0 257853661 0 22372796.9 20051123 0 258098335 0 22337711 20051122 0 258252152 0 22294332 20051121 0 259081756 0 22290578.8 20051120 0 260350988 0 22327443.1 20051119 0 261481602 0 22374272 20051118 0 263717302 0 22420833.4 TIMEBYDAY_ID CY_SALES CY_PY_SALES CY_UNITS CY_PY_UNITS ------------ ---------- ----------- ---------- ----------- 20051117 0 265553129 0 22444815.1 20051116 0 267278918 0 22485420.7 20051115 0 269407118 0 22577023.1 20051114 0 270714464 0 22649333.7 20051113 0 271874689 0 22736191.8 20051112 0 271786193 0 22745393.3 20051111 0 272064399 0 22801871.8 20051110 0 271695572 0 22856833.9 20051109 0 271259208 0 22874423.1 20051108 0 271380343 0 22902096.3 20051107 0 271217738 0 22937487.6 TIMEBYDAY_ID CY_SALES CY_PY_SALES CY_UNITS CY_PY_UNITS ------------ ---------- ----------- ---------- ----------- 20051106 0 271115955 0 22934495 20051105 0 269826583 0 22908193.1 20051104 0 268482192 0 22913246.6 20051103 0 266943890 0 22939681.3 20051102 0 265215674 0 22908738.1 20051101 0 262868042 0 22808549.3 20051031 0 261480542 0 22719528.9 20051030 0 260506884 0 22631949.4 20051029 247.369286 260511103 50.0714286 22620149.2 20051028 247.369286 260160389 50.0714286 22580052.9 20051027 247.369286 260071854 50.0714286 22538257.6 TIMEBYDAY_ID CY_SALES CY_PY_SALES CY_UNITS CY_PY_UNITS ------------ ---------- ----------- ---------- ----------- 20051026 247.369286 260118184 50.0714286 22477659.4 20051025 247.369286 260818429 50.0714286 22473361.4 20051024 247.369286 261520683 50.0714286 22432500.1 20051023 488.950714 260731146 106.071429 22369232.2 20051022 488.950714 261109060 106.071429 22299211.4 20051021 488.950714 261745392 106.071429 22240471.7 20051020 11750730.9 262675617 565475.286 22218725.6 20051019 31106776.2 268213559 2129011.79 22237462