Re: Views & Resources (7.3.4 DB)

> "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).