Re: Select count(*) on view

On Feb 14, 3:44 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 14, 8:27 am, "Anurag Varma" <avora..._at_gmail.com> wrote:
>
>
>
> > On Feb 14, 6:11 am, joker197cin..._at_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_EQUIVALENCE
> ( 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 Received on Wed Feb 14 2007 - 15:40:13 CST