Re: Cost explosion

Can you provide the ( "select_2_seconds" ) part?

Martin Haltmayer

Stephan Schaefer wrote:
>
> I have got a select statement that takes 2 seconds to compute
> the result.
> But I wait hours for:
> select count(*) from ( "select_2_seconds" ) ;
> The same happens for
> select max(rownum) from ( "select_2_seconds" ) ;
> and
> create myview as select * from ( "select_2_seconds" ) ;
> select * from myview ;
> This latter view is the thing I actually need. And there I cannot
> wait for hours.
>
> My tablespace is completly analyzed, optimizer mode is "choose".
> There are no outer joins and it uses no other views, only its own
> subqueries.
>
> The explain plans differ completely and they give
> for the fast statement COST=1065
> for the view COST=252839 .
>
> I am running Oracle Version 7.3.3.5.1 on HP-UX 10.20.
>
> Any help or comment welcome.
>
> Stephan Schaefer
> stesch_at_camline.com
>
> ======================================================================
> The construction is like
>
> select cols
> from
> ( select cols, sum(col1)
> from
> ( select cols
> from indexed_tables
> where cond
> )
> group by cols
> ) a,
> ( select cols
> from
> ( select cols, sum(col)
> from indexed_tables
> group by cols
> ) b1,
> ( select cols, sum(col)
> from indexed_tables
> where cond
> ) b2
> where cond
> ) b
> where cond
> ;
  Received on Thu Mar 05 1998 - 00:00:00 CST