Re: Views & Resources (7.3.4 DB)

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" <ORACLE-L_at_fatcity.com> 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: sfaroult_at_oriole.com
>
> 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: ListGuru_at_fatcity.com (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: lisa_at_cog.ufl.edu

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: ListGuru_at_fatcity.com (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).