Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with a view: table from it contains wrong number of rows

Re: Problem with a view: table from it contains wrong number of rows

From: <schonlinner_at_yahoo.com>
Date: 17 Feb 2006 12:09:23 -0800
Message-ID: <1140206963.216776.308530@z14g2000cwz.googlegroups.com>


Hi,

> what was the count from the view just prior to creating the table?
> are the data in the tables underlying the view static?
> the first place to look is to see who/what might be changing the underlying data.

select count(*) from v_200601 is always the same.

The data in the table underlying the view is static: It's a real, physical, standard table in a standard tablespace. A view (V1) selects from that table, then a new view (V2) selects from that view (i.e. from V1) and then the third view (V_200601) selects from the V2-view (this has been made to keep our application code readable).

Nothing is changing the underlying data, there is no kind of "recursion" or similar.

So basically it's similar to:
create or replace view V1 as (select blah from mytable);

create or replace view V2 as (select blah from V1);

create or replace view v_200601 as (
select a,b,c,sum(d) d from mytable group by a,b,c);

Some computations like sum(pax) are made in the views, and the third view contains a left outer join which aggregates data.

But regardless of how exactly I describe the problem: It all comes down to:

Why is a table created *directly* from a view different from the view contents?

Am I totally brain-dead? I always thought that the two things will always deliver the same results if the underlying data is static (and it *is* static...).

Regards,
  Alex Received on Fri Feb 17 2006 - 14:09:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US