OraFAQ Forum: Performance Tuning » Analytic function but bad performance (merged 5 cross-posts)

Home » RDBMS Server » Performance Tuning » Analytic function but bad performance (merged 5 cross-posts)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Analytic function but bad performance (merged 5 cross-posts) [message #141143]

Fri, 07 October 2005 03:12

Kanagaraj Velusamy
Messages: 12
Registered: October 2005
Location: Bangalore

Junior Member

Hi Top brains and experts,

I have attached the text file which will explain my requirements and technical issues. Expecting the best answer from all of you.File Explanation and Query with sample output.I want to tune my query without using partition and creating index.

Thanks and Regards
Kanagaraj
Bangalore

Attachment: Performance and Tuning.txt
(Size: 14.82KB, Downloaded 1477 times)

Report message to a moderator

Analytical function but bad performance [message #141276 is a reply to message #141143]

Sat, 08 October 2005 01:27

Kanagaraj Velusamy
Messages: 12
Registered: October 2005
Location: Bangalore

Junior Member

Embarassed

Dear all,

I have written a query using the analytical function.
I am looking for new solution for this (For Example Query reuse
concept)

Thanks and Regards Kanagaraj 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>

Report message to a moderator

Performance and Tuning [message #141278 is a reply to message #141276]

Sat, 08 October 2005 01:37

Kanagaraj Velusamy
Messages: 12
Registered: October 2005
Location: Bangalore

Junior Member

Embarassed

Embarassed

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>

Attachment: Performance and Tuning.txt
(Size: 18.71KB, Downloaded 1378 times)

Report message to a moderator

Analytical function but bad performance [message #141280 is a reply to message #141276]

Sat, 08 October 2005 02:02

Kanagaraj Velusamy
Messages: 12
Registered: October 2005
Location: Bangalore

Junior Member

Analytical function but bad performance
Dear All,

I have written a query using the analytical function.Output is
fine but bottleneck is performance.It takes 8 to 12 minutes
I want less than one minute execution time.

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.

select
Timebyday_id,
AVG(SUM(actual_net_sales_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING) AT_NET_SALES
AVG(SUM(actual_trans_count )) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING) ACT_TRANS_COUNT
from Bo_daily_business_ctrl_fact
where timebyday_Id between
TO_NUMBER(TO_CHAR((ADD_MONTHS(SYSDATE,-6))-14,'YYYYMMDD'))
and TO_NUMBER(TO_CHAR((ADD_MONTHS((sysdate),0)),'YYYYMMDD')) group by tim
union all
select
Timebyday_id,
AVG(SUM(actual_net_sales_amt)) OVER (ORDER BY timebyday_id DESC
ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING)
AVG(SUM(actual_trans_count )) OVER (ORDER BY timebyday_id DESC
ROWS ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING)
from Bo_daily_business_ctrl_fact
where timebyday_Id between
TO_NUMBER(TO_CHAR((ADD_MONTHS(SYSDATE,-18))-14,'YYYYMMDD'))
and TO_NUMBER(TO_CHAR(ADD_MONTHS(sysdate,-12),'YYYYMMDD')) group by timebyday_id

Query Result

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>

Report message to a moderator

Re: Performance and Tuning [message #141313 is a reply to message #141278]

Sat, 08 October 2005 11:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would help if you posted the output from
SQL> DESC Bo_daily_business_ctrl_fact
and the EXPLAIN_PLAN for your query

Report message to a moderator

Re: Analytic function but bad performance (merged 4 cross-posts) [message #141376 is a reply to message #141143]

Sun, 09 October 2005 23:11

Kanagaraj Velusamy
Messages: 12
Registered: October 2005
Location: Bangalore

Junior Member

Hi,
Please find the details below and attachement in XLS.
I am expecting the good solutions for my query.
Regards
Raj

Table Structure 
CREATE TABLE bo_daily_business_ctrl_fact
    (timebyday_id                   NUMBER(16,0) NOT NULL,
    hierarchy_id                   NUMBER(16,0) NOT NULL,
    projected_net_sales_amt        NUMBER(15,2),
    projected_trans_count          NUMBER(9,0),
    actual_net_sales_amt           NUMBER(15,2),
    actual_trans_count             NUMBER(9,0),
    actual_staff_hours             NUMBER(11,2),
    actual_staff_rate              NUMBER(5,2),
    actual_staff_cost              NUMBER(11,2),
    actual_manager_hrs             NUMBER(11,2),
    projected_manager_hrs          NUMBER(11,2),
    projected_staff_hrs            NUMBER(11,2),
    projected_staff_rate           NUMBER(5,2),
    projected_staff_cost           NUMBER(15,2),
    cash_difference                NUMBER(15,2),
    store_loaded                   NUMBER(1,0),
    store_not_loaded               NUMBER(1,0),
    prev_year_actual_net_sales_amt NUMBER(15,2),
    prev_year_actual_trans_count   NUMBER(15,0),
    sales_comp_flag                NUMBER(1,0),
    non_product_amt                NUMBER(15,2),
    prev_year_non_product_amt      NUMBER(15,2),
    tax_product_sales              NUMBER(15,2),
    tax_non_product_sales          NUMBER(15,2),
    transaction_count_eat_in       NUMBER(9,0),
    transaction_count_take_out     NUMBER(9,0),
    transaction_count_drive        NUMBER(9,0),
    sales_eat_in                   NUMBER(15,2),
    sales_take_out                 NUMBER(15,2),
    sales_drive                    NUMBER(15,2),
    projected_net_sales_amt_base   NUMBER(15,2),
    projected_net_sales_amt_region NUMBER(15,2),
    actual_net_sales_amt_base      NUMBER(15,2),
    actual_net_sales_amt_region    NUMBER(15,2),
    actual_staff_rate_base         NUMBER(5,2),
    actual_staff_rate_region       NUMBER(5,2),
    actual_staff_cost_base         NUMBER(15,2),
    actual_staff_cost_region       NUMBER(15,2),
    projected_staff_rate_base      NUMBER(15,2),
    projected_staff_rate_region    NUMBER(15,2),
    projected_staff_cost_base      NUMBER(15,2),
    projected_staff_cost_region    NUMBER(15,2),
    cash_difference_base           NUMBER(15,2),
    cash_difference_region         NUMBER(15,2),
    prev_year_actual_ns_amt_base   NUMBER(15,2),
    prev_year_actual_ns_amt_region NUMBER(15,2),
    non_product_amt_base           NUMBER(15,2),
    non_product_amt_region         NUMBER(15,2),
    prev_year_non_prod_amt_base    NUMBER(15,2),
    prev_year_non_prod_amt_region  NUMBER(15,2),
    tax_product_sales_base         NUMBER(15,2),
    tax_product_sales_region       NUMBER(15,2),
    tax_non_product_sales_base     NUMBER(15,2),
    tax_non_product_sales_region   NUMBER(15,2),
    sales_eat_in_base              NUMBER(15,2),
    sales_eat_in_region            NUMBER(15,2),
    sales_take_out_base            NUMBER(15,2),
    sales_take_out_region          NUMBER(15,2),
    sales_drive_base               NUMBER(15,2),
    sales_drive_region             NUMBER(15,2),
    gross_sales_eat_in             NUMBER(15,2),
    gross_sales_eat_in_base        NUMBER(15,2),
    gross_sales_eat_in_region      NUMBER(15,2),
    gross_sales_take_out           NUMBER(15,2),
    gross_sales_take_out_base      NUMBER(15,2),
    gross_sales_take_out_region    NUMBER(15,2),
    gross_sales_drive              NUMBER(15,2),
    gross_sales_drive_base         NUMBER(15,2),
    gross_sales_drive_region       NUMBER(15,2),
    market_id                      NUMBER(16,0),
    local_currency_id              NUMBER(16,0),
    base_currency_id               NUMBER(16,0),
    region_currency_id             NUMBER(16,0),
    exchange_rate_id               NUMBER(16,0),
    transaction_count_mccafe       NUMBER(9,0),
    sales_mccafe                   NUMBER(15,2),
    sales_mccafe_base              NUMBER(15,2),
    sales_mccafe_region            NUMBER(15,2),
    transaction_count_counter      NUMBER(9,0),
    sales_counter                  NUMBER(15,2),
    sales_counter_base             NUMBER(15,2),
    sales_counter_region           NUMBER(15,2),
    gross_sales_mccafe             NUMBER(15,2),
    gross_sales_mccafe_base        NUMBER(15,2),
    gross_sales_mccafe_region      NUMBER(15,2),
    gross_sales_counter            NUMBER(15,2),
    gross_sales_counter_base       NUMBER(15,2),
    gross_sales_counter_region     NUMBER(15,2))
  PCTFREE     10
  PCTUSED     40
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  bobjdata_data
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

-- Grants for Table
GRANT ALTER ON bo_daily_business_ctrl_fact TO scott
/
GRANT DELETE ON bo_daily_business_ctrl_fact TO scott
/
GRANT INDEX ON bo_daily_business_ctrl_fact TO scott
/
GRANT INSERT ON bo_daily_business_ctrl_fact TO scott
/
GRANT SELECT ON bo_daily_business_ctrl_fact TO scott
/
GRANT UPDATE ON bo_daily_business_ctrl_fact TO scott
/
GRANT REFERENCES ON bo_daily_business_ctrl_fact TO scott
/
GRANT ON COMMIT REFRESH ON bo_daily_business_ctrl_fact TO scott
/
GRANT QUERY REWRITE ON bo_daily_business_ctrl_fact TO scott
/
GRANT DEBUG ON bo_daily_business_ctrl_fact TO scott
/
GRANT FLASHBACK ON bo_daily_business_ctrl_fact TO scott
/

-- Indexes for BO_DAILY_BUSINESS_CTRL_FACT

CREATE UNIQUE INDEX bo_daily_business_ctrl_f_pk ON bo_daily_business_ctrl_fact
  (
    timebyday_id                    ASC,
    hierarchy_id                    ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  bobjdata_data
  STORAGE   (
    INITIAL     16384
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

CREATE INDEX ind_fk_hierarchy_id_dbc ON bo_daily_business_ctrl_fact
  (
    hierarchy_id                    ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  bobjdata_data
  STORAGE   (
    INITIAL     16384
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

CREATE INDEX ind_fk_timebyday_id_dbc ON bo_daily_business_ctrl_fact
  (
    timebyday_id                    ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  bobjdata_data
  STORAGE   (
    INITIAL     16384
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

-- Constraints for BO_DAILY_BUSINESS_CTRL_FACT

ALTER TABLE bo_daily_business_ctrl_fact
ADD CONSTRAINT pk_bo_daily PRIMARY KEY (timebyday_id, hierarchy_id)
/

Attachment: $Explain$paln$.XLS
(Size: 19.50KB, Downloaded 1290 times)

[Updated on: Thu, 13 October 2005 00:30] by Moderator

Report message to a moderator

Replacement for Analytical Function [message #141761 is a reply to message #141143]

Wed, 12 October 2005 00:07

Kanagaraj Velusamy
Messages: 12
Registered: October 2005
Location: Bangalore

Junior Member

Hi Guys and experts,
I have written a query which will return sum of average of every 14 days from current row of given date to next fourteen dayts for previous six months (180 days) and
It takes 8 to minutes to process. I want this execution time less than two minutes.Pleae give a logc to reduce and avoid the duplicate fetch of the data.
1.For 2 years record count is 18 Million.
2.Timebday_id is primary key indexed.
3.I am not allowed to create any index on market_id
(No use,takes same time if we have market_id is bitmap index ) and partition on timebyday_id.
4 Materialized view should not be used for this query
I am expecting different logic to avoid repeate fetching of data.

Thanks and regards Kanagaraj
My query is
====================================================================
select
Timebyday_id,
AVG(SUM(actual_net_sales_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING),
AVG(SUM(actual_trans_count )) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING)
from Bo_daily_business_ctrl_fact
where timebyday_Id between
TO_NUMBER(TO_CHAR((ADD_MONTHS(SYSDATE,-6))-14,'YYYYMMDD'))
and TO_NUMBER(TO_CHAR((ADD_MONTHS((sysdate),0)),'YYYYMMDD')) group by
timebyday_id
union all
select
Timebyday_id,
AVG(SUM(actual_net_sales_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING),
AVG(SUM(actual_trans_count )) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING)
from Bo_daily_business_ctrl_fact
where timebyday_Id between
TO_NUMBER(TO_CHAR((ADD_MONTHS(SYSDATE,-18))-14,'YYYYMMDD'))
and TO_NUMBER(TO_CHAR(ADD_MONTHS(sysdate,-12),'YYYYMMDD')) group by
timebYDAY_ID
====================================================================
Query Result
Hi Guys and experts,
I have written a query which will return sum of average of every 14 days from current row of given date to next fourteen dayts for previous six months (180 days) and
It takes 8 to minutes to process. I want this execution time less than two minutes.Pleae give a logc to reduce and avoid the duplicate fetch of the data.
1.For 2 years record count is 18 Million.
2.Timebday_id is primary key indexed.
3.I am not allowed to create any index on market_id
(No use,takes same time if we have market_id is bitmap index ) and partition on timebyday_id.
4 Materialized view should not be used for this query
I am expecting different logic to avoid repeate fetching of data.

Thanks and regards Kanagaraj
My query is
====================================================================
select
Timebyday_id,
AVG(SUM(actual_net_sales_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING),
AVG(SUM(actual_trans_count )) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING)
from Bo_daily_business_ctrl_fact
where timebyday_Id between
TO_NUMBER(TO_CHAR((ADD_MONTHS(SYSDATE,-6))-14,'YYYYMMDD'))
and TO_NUMBER(TO_CHAR((ADD_MONTHS((sysdate),0)),'YYYYMMDD')) group by
timebyday_id
union all
select
Timebyday_id,
AVG(SUM(actual_net_sales_amt)) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING),
AVG(SUM(actual_trans_count )) OVER (ORDER BY timebyday_id DESC ROWS BETWEEN 1 FOLLOWING AND 14 FOLLOWING)
from Bo_daily_business_ctrl_fact
where timebyday_Id between
TO_NUMBER(TO_CHAR((ADD_MONTHS(SYSDATE,-18))-14,'YYYYMMDD'))
and TO_NUMBER(TO_CHAR(ADD_MONTHS(sysdate,-12),'YYYYMMDD')) group by
timebYDAY_ID
====================================================================
Query Result
=============

TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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  276268019 23539921.5
    20050409  270932910 23309593.3
    20050408  267178448 23039795.7
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    20050407  267613433 23026135.2
    20050406  269075395 23038705.9
    20050405  271910215   23142828
    20050404  277340494 23371148.4
    20050403  278088499 23845656.7
    20050402  266896383   23429956
    20050401  254449567 22539349.5
    20050331  249485397 22037862.7
    20050330  248218106 21727306.5
    20050329  245047129   21420743
    20050328
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    20041011  262150199 22487808.5
    20041010  261985050 22512022.8
    20041009  261557988 22542497.2
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    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  218351339 20443173.7
    20040409  214968451 20315631.5
    20040408  209933753 20140896.5
TIMEBYDAY_ID          A          B
------------ ---------- ----------
    20040407  205619919   19936626
    20040406  202633890 19792271.7
    20040405  200266266 19687257.6
    20040404  194033538 19755079.7
    20040403  179052873 18890778.5
    20040402  161106908 17586080.8
    20040401  142204500 16477905.8
    20040331  140177088 16140902.3
    20040330  146542361 15999339.5
    20040329  154329236   15529000
    20040328
385 rows selected.
Elapsed: 00:06:42.03

Report message to a moderator

Re: Replacement for Analytical Function [message #141762 is a reply to message #141761]

Wed, 12 October 2005 00:55

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

How about:

select   timebyday_id,
         avg (sum (actual_net_sales_amt) ) over (order by timebyday_id desc rows between 1 following and 14 following),
         avg (sum (actual_trans_count) ) over (order by timebyday_id desc rows between 1 following and 14 following)
    from bo_daily_business_ctrl_fact
   where timebyday_id between to_number (to_char ( (add_months (sysdate, -6) )
                                                  - 14,
                                                  'YYYYMMDD') )
                          and to_number (to_char ( (add_months ( (sysdate), 0) ),
                                                  'YYYYMMDD') )
      or timebyday_id between to_number (to_char ( (add_months (sysdate, -18) )
                                                  - 14,
                                                  'YYYYMMDD') )
                          and to_number (to_char (add_months (sysdate, -12),
                                                  'YYYYMMDD') )
group by timebyday_id

I assume that the datatype of 'timebyday_id' is number.

David

[Updated on: Wed, 12 October 2005 00:55]

Report message to a moderator

Re: Replacement for Analytical Function [message #141770 is a reply to message #141762]

Wed, 12 October 2005 01:51

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

From private message:

Quote:

Hi David,

Your query simply hangs on such large table because OR clause used between two years.
Could you please give some any other logic

Regards,
Kanagaraj

Please show the explain plan for this 'hanging' query. Do you have TOAD or DBA Studio on your PC? If so, then copy the explain plan from there.

David

Report message to a moderator

Re: Replacement for Analytical Function [message #141780 is a reply to message #141770]

Wed, 12 October 2005 02:31

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

From private message:

Hi David I have copied the explain plan result for your query.
Regards
Kanagaraj


In Local Database
=========================================================================

STATEMENT_ID TIMESTAMP REMARKS OPERATION OPTIONS OBJECT_NODE OBJECT_OWNER OBJECT_NAME OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER SEARCH_COLUMNS ID PARENT_ID POSITION COST CARDINALITY BYTES OTHER_TAG PARTITION_START PARTITION_STOP PARTITION_ID OTHER DISTRIBUTION CPU_COST IO_COST TEMP_SPACE ACCESS_PREDICATES FILTER_PREDICATES
WBR 10/12/2005 12:30 SELECT STATEMENT CHOOSE 0 
WBR 10/12/2005 12:30 WINDOW BUFFER 1 0 1 
WBR 10/12/2005 12:30 SORT GROUP BY 2 1 1 
WBR 10/12/2005 12:30 CONCATENATION 3 2 1 
WBR 10/12/2005 12:30 TABLE ACCESS BY INDEX ROWID BOBJDATA BO_DAILY_BUSINESS_CTRL_FACT 1 4 3 1 
WBR 10/12/2005 12:30 INDEX RANGE SCAN BOBJDATA IND_FK_TIMEBYDAY_ID_DBC NON-UNIQUE 1 5 4 1 "BO_DAILY_BUSINESS_CTRL_FACT"."TIMEBYDAY_ID">=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,-18)-14,'YYYYMMDD')) AND "BO_DAILY_BUSINESS_CTRL_FACT"."TIMEBYDAY_ID"<=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,-12),'YYYYMMDD')) 
WBR 10/12/2005 12:30 TABLE ACCESS BY INDEX ROWID BOBJDATA BO_DAILY_BUSINESS_CTRL_FACT 1 6 3 2 LNNVL("BO_DAILY_BUSINESS_CTRL_FACT"."TIMEBYDAY_ID">=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,-18)-14,'YYYYMMDD'))) OR LNNVL("BO_DAILY_BUSINESS_CTRL_FACT"."TIMEBYDAY_ID"<=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,-12),'YYYYMMDD')))
WBR 10/12/2005 12:30 INDEX RANGE SCAN BOBJDATA IND_FK_TIMEBYDAY_ID_DBC NON-UNIQUE 1 7 6 1 "BO_DAILY_BUSINESS_CTRL_FACT"."TIMEBYDAY_ID">=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,-6)-14,'YYYYMMDD')) AND "BO_DAILY_BUSINESS_CTRL_FACT"."TIMEBYDAY_ID"<=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,0),'YYYYMMDD')) 
=================================================================
Production DB (REMOTE)prod_es02 
=================================================================


STATEMENT_ID TIMESTAMP REMARKS OPERATION OPTIONS OBJECT_NODE OBJECT_OWNER OBJECT_NAME OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER SEARCH_COLUMNS ID PARENT_ID POSITION COST CARDINALITY BYTES OTHER_TAG PARTITION_START PARTITION_STOP PARTITION_ID OTHER DISTRIBUTION CPU_COST IO_COST TEMP_SPACE ACCESS_PREDICATES FILTER_PREDICATES
WBR 10/12/2005 12:33 WINDOW BUFFER 1 0 1 26198 3852 46224 26198 
WBR 10/12/2005 12:33 SORT GROUP BY 2 1 1 26198 3852 46224 26198 
WBR 10/12/2005 12:33 TABLE ACCESS FULL ES02 BOBJDATA BO_DAILY_BUSINESS_CTRL_FACT 1 ANALYZED 3 2 1 26039 141172 1694064 26039 "A1"."TIMEBYDAY_ID">=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,-6)-14,'YYYYMMDD')) AND "A1"."TIMEBYDAY_ID"<=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,0),'YYYYMMDD')) OR "A1"."TIMEBYDAY_ID">=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,-18)-14,'YYYYMMDD')) AND "A1"."TIMEBYDAY_ID"<=TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE@!,-12),'YYYYMMDD'))
WBR 10/12/2005 12:33 SELECT STATEMENT REMOTE CHOOSE 0 26198 26198 3852 46224 26198

I will have a look at it tomorrow.

David

Report message to a moderator

Re: Replacement for Analytical Function [message #141782 is a reply to message #141780]

Wed, 12 October 2005 02:34

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

You said:

Quote:

2.Timebday_id is primary key indexed.

but in the explain plan it says "IND_FK_TIMEBYDAY_ID_DBC NON-UNIQUE". Am I reading the explain plan incorrectly or is the Timebday_id index not the primary key?

David

Report message to a moderator

Re: Replacement for Analytical Function [message #141895 is a reply to message #141761]

Wed, 12 October 2005 13:53

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

I have merged all five of your cross-posts here. Please continue in one thread. Please do not post various pieces of your question on various forums and stop sending me personal messages just to get attention. The net effect is that after I have had to waste so much time cleaning up the mess, I am not likely to have time to even look at your question.

Report message to a moderator

Re: Replacement for Analytical Function [message #141937 is a reply to message #141782]

Wed, 12 October 2005 18:19

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

You said:

Quote:

The primary key columns are timebyday_id, Hierarchy_id.
For my query I am not using Hierarchy_id and timebyday_id also referenced from a table Bo_time_by_id table(IND_FK_TIMEBYDAY_ID_DBC NON-UNIQUE 3000 records only)

I'll think about this today. Maybe someone else has an idea.

David

Report message to a moderator

Re: Replacement for Analytical Function [message #142213 is a reply to message #141937]

Thu, 13 October 2005 18:00

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

@Database gurus: What 'things' can Kanagaraj do to his database server to speed this up? Let's put it another way, what settings on the DB server could inhibit this very large query performing optimally? SGA, ABC, DEF, whatever the various blocky bits are called (I'm an applications level DBA, not a true softhead). What settings, areas, spacings could he reasonably expect to affect his performance?

David

Report message to a moderator

Re: Analytic function but bad performance (merged 5 cross-posts) [message #142446 is a reply to message #141143]

Fri, 14 October 2005 10:43

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

I can't really take the question seriously based on the lack of info provided and the format it was provided in and the lack of followup by the OP. Too many others ask good questions and ask them well. Suggest the OP read the sticky in the plsql newbie forum and this forum and try again to ask the question, but only ask it once not spammed everywhere.

Report message to a moderator

Previous Topic:	tuning documentation
Next Topic:	How can I prove plan invalidations

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 12:09:45 CDT 2024