Cost explosion

From: Stephan Schaefer <stephan.schaefer_at_camline.com>
Date: 1998/03/04
Message-ID: <34FD1CBD.CDA07872@camline.com>#1/1

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 Wed Mar 04 1998 - 00:00:00 CST