Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Problem with a view: table from it contains wrong number of rows
Hi,
Our database: Oracle 10g Release 2, IBM AIX, 2 proc machine.
I have the following problem and I've run out of ideas :-((
Suppose I have a view:
create or replace view v_200601 as (
select a,b,c,sum(d) d from mytable group by a,b,c);
Then I do:
create table t_200601 as (select * from v_200601);
And then the mystery:
select count(*) from v_200601; ==> 274324
select count(*) from t_200601; ==> 274278
Some data is missing in the table t_200601. I can see what data is
missing, I can find it when querying the view directly:
select * from v_200601 where a='TXL' and b='MUC' and c='BER';
==> 1 row returned.
select * from t_200601 where a='TXL' and b='MUC' and c='BER';
==> 0 rows returned.
The data is *not* in table t_200601, although created from the view.
Should I go to weekend, perhaps on Monday I'll see what is wrong?
Can Oracle have such an apparent bug? Oracle does not crash, no alert in the alert.log, it just continues to work as usual...I'm quite anxious because this kind of statement is in our apps hundreds of times and potentially that bug destroyed all our data (apparently we can't verify data with a database which calculates wrong data...).
Regards,
Alex
Received on Fri Feb 17 2006 - 13:42:34 CST