OK, so don't hit the tab bar twice, it will send the email before you are done!! :) I've finished my request and filled in the data, sorry folks for the spam! Any help, probably something blaring on an issue here that I have been looking straight at and the sinus infection I'm battling has moved onto my brain, so I appreciate it! :) ~Kellyn --- On Tue, 2/2/10, Kellyn Pedersen <kjped1313@xxxxxxxxx> wrote: From: Kellyn Pedersen <kjped1313@xxxxxxxxx> Subject: Minimize Performance Hit on Sort...Help! To: "oracle Freelists" <oracle-l@xxxxxxxxxxxxx> Date: Tuesday, February 2, 2010, 9:57 AM I'm at a total brain block on this one- Query with a order by that MUST be done in Oracle as the file is an output to a dataset in SAS. The query is through a view and I would prefer to avoid parallel, as this makes the sorting problem worse, (sorting large amounts in parallel is just NOT a good thing...) The view- SELECT /*+(index(i INDV_IDX03) parallel(o 4) use_hash(i g INDV_IDX03) */ i .ibid, i.ind_mod_key, i.ibhid, DECODE (i.geo_census_id, 0, 'N', NVL2 (g.geo_census_tract, 'B', 'Z')) AS cmatch, g.zipcode, g.zpwhite, g.zpblack, g.zpasian, g.zporace, g.zphisp, g.zpmale, g.zpfeml, g.zpag01, g.zpag02, g.zpag03, g.zpag04, g.zpag05, g.zpag06, g.zpag07, g.zpag08, g.zpag09, g.zpmhher, g.zpfhher, g.zpchild, g.zh1524, g.zh2534, g.zh3544, g.zh4554, g.zh5564, g.zh6574, g.zh7584, g.zh85up, g.zhp01, g.zhp02, g.zhp03, g.zhp04, g.zhp05, g.zfmar, g.zfmch, g.zfnfm, g.zfnmch, g.zfnff, g.zfnfch, g.zfc0002, g.zfc0305, g.zfc0611, g.zfc1213, g.zfc1417, g.zpnm, g.zpmar, g.zpwid, g.zpdiv, g.zplogn, g.zplosp, g.zploie, g.zploap, g.zpfb, g.zpfbcit, g.zpnocit, g.zpmtc, g.zpmtp, g.zpwah, g.zptrv1, g.zptrv2, g.zptrv3, g.zptlh6, g.zptlh1, g.zptlh2, g.zptlh3, g.zptlh4, g.zptlh5, g.zpsch01, g.zpsch02, g.zpsch03, g.zpsch04, g.zpsch05, g.zpsch06, g.zpedu1, g.zpedu2, g.zpedu3, g.zpedu4, g.zpedu5, g.zpvet, g.zhfull, g.zfhww, g.zpocc1, g.zpocc2, g.zpocc3, g.zpocc4, g.zpocc5, g.zpocc6, g.zhinco1, g.zhinco2, g.zhinco3, g.zhinco4, g.zhinco5, g.zhinco6, g.zhinco7, g.zhinco8, g.zhinco9, g.zhinc10, g.zhinc11, g.zmsal99, g.zmsel99, g.zmint99, g.zmsoc99, g.zmsec99, g.zmret99, g.zpbe1, g.zpbe2, g.zpae1, g.zpae2, g.zphe1, g.zphe2, g.zhbi1, g.zhbi2, g.zhbi3, g.zhai1, g.zhai2, g.zhai3, g.zhhi1, g.zhhi2, g.zhhi3, g.zhmm, g.zhff, g.zhhup, g.zpgcrn, g.zpgcry, g.zplfr, g.zplit, g.zplpt, g.zplge, g.zplhe, g.zplgr, g.zplru, g.zplpl, g.zplsl, g.zplps, g.zplin, g.zplch, g.zpljp, g.zplko, g.zplsa, g.zplvn, g.zplta, g.zplar, g.zplaf, g.zpsanc, g.zpfaar, g.zpfaen, g.zpfacz, g.zpfadu, g.zpfafr, g.zpfage, g.zpfagr, g.zpfair, g.zpfait, g.zpfano, g.zpfapl, g.zpfaru, g.zpfasi, g.zpfasc, g.zpfaaf, g.zpfasw, g.zpfawi, g.zppbneu, g.zppbweu, g.zppbseu, g.zppbeeu, g.zppbeas, g.zppbcas, g.zppbsea, g.zppbafr, g.zppbcrb, g.zppbcam, g.zppbsam, g.zuurb, g.zurur, g.zuoo, g.zuro, g.zuoowh, g.zuoobl, g.zuooas, g.zuoohs, g.zunr1, g.zunr2, g.zunr3, g.zunr4, g.zuhu1, g.zuhu2, g.zuhu3, g.zuhu4, g.zuhu5, g.zuage1, g.zuage2, g.zuage3, g.zuage4, g.zulr1, g.zulr2, g.zulr3, g.zulr4, g.zulr5, g.zugas, g.zuele, g.zuoil, g.zuofu, g.zubed1, g.zubed2, g.zubed3, g.zubed4, g.zutel, g.zuveh1, g.zuveh2, g.zuveh3, g.zuveh4, g.zuplumb, g.zukitch, g.zurent1, g.zurent2, g.zurent3, g.zurent4, g.zurent5, g.zurent6, g.zugr01, g.zugr02, g.zugr03, g.zuval01, g.zuval02, g.zuval03, g.zuval04, g.zuval05, g.zuval06, g.zuval07, g.zmvall99, g.zudebt, g.zusmrtg, g.zumrtg0, g.zumrtg1, g.zumrtg2, g.zumrtg3, g.zumrtg4, g.zminc99 FROM individual_dim i, geo_census_dim g WHERE g.geo_census_id = i.geo_census_id AND EXISTS (SELECT /*+ use_hash(o,i) */ * FROM order_sum o WHERE o.ibid = i.ibid AND o.recency_key = 48 AND o.member_id = 0); The simple query with the order by-, (without the order by, the data will come back in seconds...) select smap.* from CENSUS_0_48 smap order by ibhid, ibid; Work area usage by this query with the order by included: Operation pga memory temp SORT (v2) 511 1024 2403 GROUP BY (SORT) 1 95 1907 Is there a better way to sort this data inside the database? Inside the view? Something wrong with the view itself? These were written a long time ago and I see the group by and the sort as the contributing factor here... It's a LOT of data, about 95 million records... :( Kellyn Pedersen Multi-Platform DBA I-Behavior Inc. http://www.linkedin.com/in/kellynpedersen www.dbakevlar.blogspot.com "Go away before I replace you with a very small and efficient shell script..."