Re: Select count(*) on view

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 14 Feb 2007 12:44:14 -0800
Message-ID: <1171485854.603435.238470@p10g2000cwp.googlegroups.com>

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. Received on Wed Feb 14 2007 - 14:44:14 CST