Thanks for the input. The individual views are pretty complex , so I will try to reorganize starting from a combined view. I do want try the union all with an outer select distinct to remove duplicates to see if that will help.
thanks again, lc ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, December 01, 2002 2:18 PM > > "Lisa R. Clary" wrote: > > > > I have 3 individual views (average #records=76,000) with the exact > > same variable names and datatypes. There is another view that is the > > union of the 3 individual views. If I use a count(*) operation (in > > sqlplus) on the view that is the union (or the individual view), I run > > out of temp space e.g. ORA-01652: unable to extend temp segment by 256 > > in tablespace TEMP (108M temp space, ). If I do subsets, selects, > > whatever, the views are fairly quick with no problems-- I just can't > > perform count(*) operations > > > > Any ideas as to parameters in environment/temp space that would > > eliminate the extent problem relative to the count function? > > > > thanks, > > lc > > Lisa, > > Somebody has already suggested using UNION ALL instead of UNION, > which is an excellent idea. I would go further and suggest you rewrite > your fourth view, starting from the text of the three other views rather > than the views themselves. It will look horrid at first, but a lot of > simplifications may become obvious - and lead to strong performance > improvements. In particuliar, patterns such as > > select ... > from T1, A > union all > select ... > from T2, A > union all > select ... > from T3, A > > sometimes (it depends on what is in your WHERE clauses, really) benefit > strongly from being rewritten > > select ... > from (select ... > from T1 > union all > select ... > from T2 > union all > select ... > from T3) T, > A > ... > > and forget about 'parameters'. If only there were such thing as the > magical parameter, I would long have been out of a job. > > HTH, > > Stephane Faroult > Oriole Software > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Stephane Faroult > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lisa R. Clary INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
