Dear all, I have written a query using the analytical function. Functionality of this query =========================== For a given market and date the query should return the values of average of Sum of every fourteen days date by date for previous six months (From every day to previous 14 days i.e. Two weeks data for given market_id) My output is fine with below mentioned query. Now my bottleneck is performance and tuning. My client is not allowing me to use partition on timebyday_id and bitmap index on market_id (No use) and materialized view. Timebyday_id is the primary key for this table. Now it takes 8 to 9 minutes execution for two years data (from 18 million data). Client is asking me to reduce less than one minute. My Query: In this query oracle fetches aggregated values and actual_net_sales_amt and tactual_trans_count of 14 days records for every day from given date to previous six months and same for previous year (previous year date from given date to previous six months of previous year. 1 select 2 Timebyday_id, 3 AVG(SUM(actual_net_sales_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETW 4 AVG(SUM(actual_trans_count )) OVER (ORDER BY timebyday_id DESC ROWS BETW 5 from Bo_daily_business_ctrl_fact 6 where timebyday_Id between 7 TO_NUMBER(TO_CHAR((ADD_MONTHS(SYSDATE,-6))-14,'YYYYMMDD')) 8 and TO_NUMBER(TO_CHAR((ADD_MONTHS((sysdate),0)),'YYYYMMDD')) group by tim 9 union all 10 select 11 Timebyday_id, 12 AVG(SUM(actual_net_sales_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETW 13 AVG(SUM(actual_trans_count )) OVER (ORDER BY timebyday_id DESC ROWS BETW 14 from Bo_daily_business_ctrl_fact 15 where timebyday_Id between 16 TO_NUMBER(TO_CHAR((ADD_MONTHS(SYSDATE,-18))-14,'YYYYMMDD')) 17* and TO_NUMBER(TO_CHAR(ADD_MONTHS(sysdate,-12),'YYYYMMDD')) group by timeb 18 / TIMEBYDAY_ID AT_NET_SALES ACT_TRANS_COUNT 20050930 256451531 21109545.5 20050929 275843297 22774578.1 20050928 276805431 22893415.4 20050927 277253471 22933414.3 20050926 278017940 23000763.7 20050925 278459179 23036435.1 20050924 279072523 23077962.6 20050923 279626352 23097377.6 20050922 279945042 23126543.4 20050921 280430700 23147385 20050920 281112774 23192661.9 20050919 281822147 23040598.9 20050918 282155719 23019036.1 20050917 281697094 22958647.8 20050916 281892035 22990206.4 20050915 284234444 23073941.6 20050914 286924765 23174821.7 20050913 289440113 23241413.7 20050912 292021995 23300797.8 20050911 292825219 23328398 20050910 292889307 23311687.2 20050909 293771027 23344709.5 20050908 296113353 23423309.6 20050907 298057095 23484108 20050906 299912012 23554601.8 20050905 302183269 23836243.7 20050904 302765636 23882788.2 20050903 304488334 23894407.9 20050902 305016020 23860922.8 20050901 304758959 23826165.6 20050831 304703759 23793268.6 20050830 305354976 23821807.6 20050829 306391659 23872559.8 20050828 306498577 23853778.6 20050827 305154334 23788589.1 20050826 305671744 23778724.2 20050825 306422134 23777602.9 20050824 307100651 23768167 20050823 307772788 23764458.1 20050822 308438197 23762291.3 20050821 308482981 23768180.2 20050820 306652829 23795035.2 20050819 307575632 23840557.2 20050818 308308424 23908516.6 20050817 308678272 23994427.1 20050816 308230682 24054107.4 20050815 307874159 24111466.3 20050814 307172365 24155520 20050813 306149776 24197070.2 20050812 304901977 24241693.2 20050811 303539950 24287851.1 20050810 302459955 24349581.7 20050809 301314328 24365808.2 20050808 300347862 24382447.3 20050807 299204542 24380365.4 20050806 298354773 24381296.4 20050805 296972730 24386957.2 20050804 296178587 24378621.4 20050803 295324653 24360012.6 20050802 294901174 24317753.9 20050801 293574534 24261625.4 20050731 293040632 24249075.8 20050730 292959353 24239864.6 20050729 293280035 24244982.4 20050728 293348370 24238497.6 20050727 293537732 24226615 20050726 293984438 24239096.7 20050725 294022543 24247341.6 20050724 294257460 24250623.4 20050723 294874856 24266392.8 20050722 295527533 24269644.7 20050721 295386298 24274115.6 20050720 295657496 24279804.6 20050719 295715733 24300513.6 20050718 295120430 24039722.4 20050717 295192257 24044395 20050716 295887460 24076455.9 20050715 296872310 24141600.6 20050714 297825879 24223410.9 20050713 297338331 24234160.7 20050712 296620265 24228071.2 20050711 295931987 24214765.9 20050710 295411875 24234574.8 20050709 293943063 24230631.5 20050708 292764492 24215901.3 20050707 292615784 24202572.4 20050706 291970834 24176975.8 20050705 291228887 24140584.4 20050704 291623924 24380098.2 20050703 291393508 24302339.1 20050702 291513658 24301913.2 20050701 291044909 24236113.6 20050630 290137569 24132951.6 20050629 290768243 24103272.4 20050628 290780492 24057804.1 20050627 290507589 24013128.2 20050626 291119170 23994298.1 20050625 292945253 24015676.7 20050624 293995127 24019475.3 20050623 293038653 23983848.6 20050622 292177972 23953878.6 20050621 291477219 23928376.8 20050620 290398434 23893869.4 20050619 290686319 23962536.4 20050618 290907415 23985139.2 20050617 290195900 23979344.5 20050616 289134773 23969261.9 20050615 289680985 24051478.4 20050614 288877376 24072348.4 20050613 287003511 23825491.4 20050612 287206840 23763755.4 20050611 286541427 23710619.6 20050610 285686557 23721382 20050609 285288214 23732371 20050608 284252483 23713818 20050607 283055817 23655610.9 20050606 282007167 23601967.4 20050605 281623008 23582896.2 20050604 281888657 23584868.4 20050603 281520189 23556779.7 20050602 280314404 23443850.6 20050601 277678705 23316157.1 20050531 277035875 23250636.1 20050530 279016855 23475709.5 20050529 278691640 23523413.2 20050528 279339287 23601984 20050527 279284216 23574591.8 20050526 278780070 23519344.5 20050525 279290749 23501952.9 20050524 280049289 23512841.9 20050523 280891237 23518303.7 20050522 279440901 23377636.7 20050521 279411273 23411535.4 20050520 280149002 23478957.2 20050519 281602884 23603504.5 20050518 282681671 23685849.1 20050517 283286272 23766080.7 20050516 282968823 23851854.8 20050515 283011101 23892969.1 20050514 282169682 23925861.6 20050513 281859167 23967363.6 20050512 281361345 23986858.1 20050511 280313585 23967747.6 20050510 279112079 23937342.4 20050509 277531721 23924368.3 20050508 278391446 24039592 20050507 277569011 23971122.4 20050506 275846105 23891707.1 20050505 274096592 23832817.8 20050504 272315235 23730077.6 20050503 270779385 23638478.8 20050502 268767222 23522770 20050501 268246471 23496916.5 20050430 269578510 23502104.8 20050429 269527662 23484904.7 20050428 269265444 23460911.7 20050427 269149765 23434658.9 20050426 269157886 23418771.6 20050425 269287121 23402414.9 20050424 269750305 23414901.5 20050423 271272896 23451350.4 20050422 271629771 23413521.9 20050421 271831090 23361927.4 20050420 272113492 23357189.2 20050419 272681295 23362222.1 20050418 272905299 23357087.6 20050417 272915439 23315118 20050416 272702469 23252102 20050415 272856401 23230245.5 20050414 273567824 23264618.9 20050413 273854990 23283589.1 20050412 274698834 23314927.8 20050411 275819318 23330937.4 20050410 271605241 22979360.7 20050409 267575709 22820976.9 20050408 266743982 22747500.6 20050407 267045165 22714508.2 20050406 268094281 22697967.3 20050405 270066776 22742716.3 20050404 273683628 22862529.4 20050403 273775979 23122355 20050402 266241841 22772129.4 20050401 259035869 22169236.1 20050331 257318168 21856807.2 20050330 258377806 21696373.6 20050329 260124986 21611999.5 20050328 265150939 21675751.7 20050327 292231844 24667592.5 20050326 300587804 25248699 20050325 20041008 260366441 22552521.9 20041007 258953099 22520522.5 20041006 257649034 22485031.6 20041005 256374425 22446903.6 20041004 255323676 22397478.4 20041003 254848689 22382100 20041002 253978832 22316752.1 20041001 252241021 22183285.2 20040930 251437281 22060643.1 20040929 251725985 22059180.9 20040928 251996050 22070310.2 20040927 252420835 22077001.8 20040926 253156747 22099034.9 20040925 253796474 22103142.7 20040924 254788966 22101673.6 20040923 255432109 22101969.6 20040922 255850486 22098458.6 20040921 256709409 22127331.1 20040920 257507868 21987696.1 20040919 257627508 21944346.4 20040918 257446970 21884838.6 20040917 257931352 21956749.1 20040916 259062671 22082159.6 20040915 261723410 22207418.1 20040914 264953725 22364344.1 20040913 268022633 22477254 20040912 268219468 22476454.2 20040911 268257693 22430276.9 20040910 268964635 22452451.6 20040909 271151469 22543624 20040908 273507696 22627975.8 20040907 275419311 22677943.1 20040906 277556674 22922282.1 20040905 278295738 22970382 20040904 277643169 22976462.6 20040903 279346063 22951572.9 20040902 280870272 22926140.2 20040901 280493127 22874699.7 20040831 279979850 22819364.9 20040830 280228098 22833842 20040829 280873017 22841932.1 20040828 280039726 22811409.3 20040827 280688350 22787355.9 20040826 281160538 22787258.3 20040825 281728220 22803340.6 20040824 282288269 22827668.1 20040823 282825330 22847194.5 20040822 282785843 22849135.7 20040821 283259709 22875765.5 20040820 282479477 22916331.4 20040819 282533695 22963345.6 20040818 282756588 23004838.8 20040817 282958326 23041779.6 20040816 282652151 23065791.1 20040815 282192643 23110134.1 20040814 281836288 23211144.1 20040813 280957976 23277300.6 20040812 280275145 23301033.1 20040811 279545226 23317228.4 20040810 279154823 23314217.9 20040809 278901616 23310654.4 20040808 278152811 23298385 20040807 276900100 23265709.5 20040806 276027458 23238221.9 20040805 275457794 23213215 20040804 275193068 23227853.3 20040803 274480209 23183909.4 20040802 274216319 23148646.4 20040801 273836114 23100624.4 20040731 273111993 22994676.3 20040730 272943980 22946195.7 20040729 273628733 22941393.9 20040728 274332647 22937060.9 20040727 274591866 22919844.1 20040726 274778419 22888581.1 20040725 275265052 22874850.7 20040724 276096443 22876206.4 20040723 276867654 22866614.6 20040722 276964221 22861853.1 20040721 277143270 22836338.6 20040720 277217879 22838142.8 20040719 276589667 22641658.7 20040718 275596997 22543395.6 20040717 275556760 22537468 20040716 276149719 22586046.1 20040715 275853255 22636469.5 20040714 275493024 22720160.3 20040713 274727102 22736364.6 20040712 273518629 22758868.4 20040711 272419162 22796424.6 20040710 271194143 22812997.9 20040709 269697606 22811482.4 20040708 269761397 22799415.5 20040707 269071158 22775770.1 20040706 268639520 22752392.4 20040705 268648412 22936428.7 20040704 268970461 22987049.4 20040703 269650801 23054548.3 20040702 269158239 23022344.1 20040701 268512639 22938957 20040630 267784515 22820178.7 20040629 267530693 22769952.7 20040628 267339504 22730064.4 20040627 267913428 22692753.6 20040626 269030753 22692295.6 20040625 270151916 22695378.6 20040624 269843808 22681652.8 20040623 269287294 22673203.9 20040622 268596604 22658850.5 20040621 267671905 22644630 20040620 268031309 22697863.8 20040619 269045583 22698610.1 20040618 268874328 22703972.2 20040617 267988125 22714093.9 20040616 267130613 22722128.1 20040615 268274858 22821444.9 20040614 268972517 22668024.5 20040613 270694469 22643224.4 20040612 271315513 22654074.6 20040611 272143721 22687258.1 20040610 271316935 22682122.6 20040609 270750788 22639094.1 20040608 270264566 22593182.6 20040607 269492829 22524046 20040606 269677908 22510079.1 20040605 269134984 22505554.1 20040604 268909676 22476966.5 20040603 268966892 22434040.7 20040602 268906717 22400421.6 20040601 266642376 22255596.6 20040531 264751615 22355141.1 20040530 262938636 22398118.8 20040529 263460335 22429956.6 20040528 262318245 22386665.8 20040527 261668417 22325521.1 20040526 261575314 22300275.9 20040525 261646547 22284066.9 20040524 262377057 22274960.7 20040523 261593982 22146875.9 20040522 261322734 22172006.4 20040521 261785512 22224089.6 20040520 262072745 22272575.8 20040519 262430137 22316327.5 20040518 263285111 22395943.7 20040517 264747793 22500913.1 20040516 265600382 22546865.9 20040515 266540630 22596478.6 20040514 262984986 22309238.1 20040513 256033955 22041190.9 20040512 248747308 21775541 20040511 239966445 21500389.7 20040510 236011858 21212092.4 20040509 229665819 21023274.5 20040508 221310991 20693725.3 20040507 210862653 20266656.3 20040506 201776129 19893053.6 20040505 193649013 19556858.7 20040504 186245652 19207752.5 20040503 177359394 18835981.1 20040502 170339767 18519435 20040501 159575890 18138469.9 20040430 153278934 18146298.4 20040429 153676416 18219804.4 20040428 159268558 18329733.1 20040427 159908715 18340627.7 20040426 158910019 18383337.2 20040425 156195652 18136289.3 20040424 154745809 18003446 20040423 161747598 18120356.4 20040422 171289620 18410165.4 20040421 179325446 18710630 20040420 185540241 18994965.4 20040419 192570433 19266095.4 20040418 197862728 19479871.1 20040417 205376939 19809599.5 20040416 212896246 20076950.1 20040415 218510635 20263682.6 20040414 213347328 20131609.2 20040413 213693734 20079026.9 20040412 211852847 20047925.6 20040411 214534825 20311771.1 20040410 214744155 20359294.3 20040409 207268740 20228397.2 20040408 198052064 19901168.4 20040407 193819755 19725596.6 20040406 190596886 19599745.1 20040405 187780704 19505868.8 20040404 182169239 19535205.4 20040403 170863873 18934574.1 20040402 158624020 18124612.5 20040401 147467946 17568302.7 20040331 147331480 17581533.8 20040330 151308468 17813035 20040329 154446714 18148874 20040328 154485873 19022165.3 20040327 147803434 18898817 20040326 141316677 17656513 20040325 20040404 1244684.37 220686.6 20040403 1212815.7 217792.778 20040402 1190288.59 214245.875 20040401 1190723.15 212112.143 20040331 1202234.36 210852.833 20040330 1256704.57 215028.8 20040329 1364170.66 227385.75 20040328 1312453.15 227489.333 20040327 1218071.62 218178.5 20040326 1107680.47 205420 20040325 396 rows selected. Elapsed: 00:08:35.06 SQL>