Is it possible, according to you, that these 2 instructions:

select * from view_first

select count(*) from view_first

where "view_first" is a view, return different rowcount?

I mean that in "select * from view_first" I browse all rows through a
GUI and I can see 200 rows but if I query the scalar value "select
count(*) from view_first" I obtain only 150.

I can't understand why...

On Feb 14, 6:11 am, joker197cin...@gmail.com wrote:
> Is it possible, according to you, that these 2 instructions:
>
> select * from view_first
>
> select count(*) from view_first
>
> where "view_first" is a view, return different rowcount?
>
> I mean that in "select * from view_first" I browse all rows through a
> GUI and I can see 200 rows but if I query the scalar value "select
> count(*) from view_first" I obtain only 150.
>
> I can't understand why...

Yes its possible.
Between the two queries if I delete 50 rows from the underlying
table, I can get your results.
There are a lot other ways I can get the results you see..

Is that what you are asking? Or do you want to be more specific?

Anurag

On Feb 14, 8:27 am, "Anurag Varma" <avora...@gmail.com> wrote:
> On Feb 14, 6:11 am, joker197cin...@gmail.com wrote:
>
> > Is it possible, according to you, that these 2 instructions:
>
> > select * from view_first
>
> > select count(*) from view_first
>
> > where "view_first" is a view, return different rowcount?
>
> > I mean that in "select * from view_first" I browse all rows through a
> > GUI and I can see 200 rows but if I query the scalar value "select
> > count(*) from view_first" I obtain only 150.
>
> > I can't understand why...
>
> Yes its possible.
> Between the two queries if I delete 50 rows from the underlying
> table, I can get your results.
> There are a lot other ways I can get the results you see..
>
> Is that what you are asking? Or do you want to be more specific?
>
> Anurag

Anurag, I could not reproduce what you stated. If this is a normal
view, if you delete rows from the table in a session, the view
automatically shows the same contents as if you directly executed the
SQL in the view definition against the table.

One possible way to reproduce the problem that the OP reported:
CREATE TABLE T1 (
COL1 NUMBER(10),
COL2 NUMBER(10),
COL3 NUMBER(10),
COL4 NUMBER(10));

INSERT INTO T1
SELECT
TRUNC(DBMS_RANDOM.VALUE(1,100000)),
TRUNC(DBMS_RANDOM.VALUE(1,100000)),
TRUNC(DBMS_RANDOM.VALUE(1,100000)),
TRUNC(DBMS_RANDOM.VALUE(1,100000))
FROM
DBA_OBJECTS
WHERE
ROWNUM<=10000;

COMMIT;

SQL> SELECT COUNT(*) FROM T1;

COUNT(*)
----------
10000

OK, there are 10,000 rows as expected.

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Session altered.

BEGIN
SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALE NCE
( NAME => 'JUST_TESTING_SOMETHING',
SOURCE_STMT => 'SELECT COUNT(*) FROM T1',
DESTINATION_STMT => 'SELECT COUNT(*) FROM T1 WHERE ROWNUM<=150',
VALIDATE => FALSE,
REWRITE_MODE => 'TEXT_MATCH' );
END;
/

We just told Oracle that when the user executes "SELECT COUNT(*) FROM
T1;", that Oracle should actually execute "SELECT COUNT(*) FROM T1
WHERE ROWNUM<=150". Now, a test:

SQL> SELECT COUNT(*) FROM T1;

COUNT(*)
----------
150

Looks like my table only has 150 rows, let's try this to make certain:
SQL> SELECT
2 COUNT(*)
3 FROM
4 T1;

COUNT(*)
----------
150

Confirmed, 150 rows.

Now, show the table data:
SQL> SELECT * FROM T1;

COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
92340 16658 62856 1847
50527 76453 73124 49520
43059 28816 49361 59170
83027 53735 77009 84614
24298 50672 76826 96292
99987 53627 12384 51537
65896 88522 84764 5745
37043 14212 60731 35429
37846 26900 69882 64812
50423 35049 38441 77683
49402 37070 2464 37085
....
COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
84814 28978 69816 92300

10000 rows selected.

Looks like there are 10,000 rows in the table, even though SELECT
COUNT(*) FROM T1; shows 150.

EXEC
SYS.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE ('JUST_TESTING_SOMETHING');

SQL> SELECT COUNT(*) FROM T1;

COUNT(*)
----------
10000

Someone could have a bit of entertainment experimenting with this
feature.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

On Feb 14, 3:44 pm, "Charles Hooper" <hooperc2...@yahoo.com> wrote:
> On Feb 14, 8:27 am, "Anurag Varma" <avora...@gmail.com> wrote:
>
>
>
> > On Feb 14, 6:11 am, joker197cin...@gmail.com wrote:
>
> > > Is it possible, according to you, that these 2 instructions:
>
> > > select * from view_first
>
> > > select count(*) from view_first
>
> > > where "view_first" is a view, return different rowcount?
>
> > > I mean that in "select * from view_first" I browse all rows through a
> > > GUI and I can see 200 rows but if I query the scalar value "select
> > > count(*) from view_first" I obtain only 150.
>
> > > I can't understand why...
>
> > Yes its possible.
> > Between the two queries if I delete 50 rows from the underlying
> > table, I can get your results.
> > There are a lot other ways I can get the results you see..
>
> > Is that what you are asking? Or do you want to be more specific?
>
> > Anurag
>
> Anurag, I could not reproduce what you stated. If this is a normal
> view, if you delete rows from the table in a session, the view
> automatically shows the same contents as if you directly executed the
> SQL in the view definition against the table.
>
> One possible way to reproduce the problem that the OP reported:
> CREATE TABLE T1 (
> COL1 NUMBER(10),
> COL2 NUMBER(10),
> COL3 NUMBER(10),
> COL4 NUMBER(10));
>
> INSERT INTO T1
> SELECT
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000)),
> TRUNC(DBMS_RANDOM.VALUE(1,100000))
> FROM
> DBA_OBJECTS
> WHERE
> ROWNUM<=10000;
>
> COMMIT;
>
> SQL> SELECT COUNT(*) FROM T1;
>
> COUNT(*)
> ----------
> 10000
>
> OK, there are 10,000 rows as expected.
>
> SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
>
> Session altered.
>
> BEGIN
> SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALE NCE
> ( NAME => 'JUST_TESTING_SOMETHING',
> SOURCE_STMT => 'SELECT COUNT(*) FROM T1',
> DESTINATION_STMT => 'SELECT COUNT(*) FROM T1 WHERE ROWNUM<=150',
> VALIDATE => FALSE,
> REWRITE_MODE => 'TEXT_MATCH' );
> END;
> /
>
> We just told Oracle that when the user executes "SELECT COUNT(*) FROM
> T1;", that Oracle should actually execute "SELECT COUNT(*) FROM T1
> WHERE ROWNUM<=150". Now, a test:
>
> SQL> SELECT COUNT(*) FROM T1;
>
> COUNT(*)
> ----------
> 150
>
> Looks like my table only has 150 rows, let's try this to make certain:
> SQL> SELECT
> 2 COUNT(*)
> 3 FROM
> 4 T1;
>
> COUNT(*)
> ----------
> 150
>
> Confirmed, 150 rows.
>
> Now, show the table data:
> SQL> SELECT * FROM T1;
>
> COL1 COL2 COL3 COL4
> ---------- ---------- ---------- ----------
> 92340 16658 62856 1847
> 50527 76453 73124 49520
> 43059 28816 49361 59170
> 83027 53735 77009 84614
> 24298 50672 76826 96292
> 99987 53627 12384 51537
> 65896 88522 84764 5745
> 37043 14212 60731 35429
> 37846 26900 69882 64812
> 50423 35049 38441 77683
> 49402 37070 2464 37085
> ...
> COL1 COL2 COL3 COL4
> ---------- ---------- ---------- ----------
> 84814 28978 69816 92300
>
> 10000 rows selected.
>
> Looks like there are 10,000 rows in the table, even though SELECT
> COUNT(*) FROM T1; shows 150.
>
> EXEC
> SYS.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE ('JUST_TESTING_SOMETHING');
>
> SQL> SELECT COUNT(*) FROM T1;
>
> COUNT(*)
> ----------
> 10000
>
> Someone could have a bit of entertainment experimenting with this
> feature.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Charles,

I was just trying to stress that we do not know what the query
behind that view is ..
or how the OP came to that conclusion.

session1> select * from table; -- shows 200 rows
session2> <delete 50 rows from the table> and commit;
session1> select count(*) from table; -- shows 150 rows

... there .. thats what I meant.
Thus, there is a lot of details OP needs to supply for anyone to
guess whats happening. Heck, its even possible OP is running into
this:

session1> <delete 50 rows but do not commit>
session1> select * from table; -- shows 150 rows
session2> select count(*) from table; -- shows 200 rows


... or as you show, more complicated things can
result in what OP is seeing. In fact he/she is referring to
a VIEW .. who knows what that VIEW is? At least OP
should post what the query behind that view is?

Maybe its:
create or replace view test5_view
as select * from test5 where rownum < dbms_random.value(1,10);

ORA92> select * from test5_view;

A B
---------- ----------
1 0
2 1
1 1
2 2
3 3
4 4
5 5
6 6
7 7

9 rows selected.

ORA92> select count(*) from test5_view;

COUNT(*)
----------
8

1 row selected.

Anurag