Re: 10gR2 Upgrade .. Watch out

Not always BTW 9.2 produced group by in sorted order :)

See below (I a priori agree that query rewrite isn't the most used
feature in the Oracle world, but it may happen).

Yeahh and of course each release may be buggy and/or app code is buggy
and therefore one has to test app before deploying it in production on
the next release or face the consequences :)


SQL> select * from v$version;

BANNER
------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SQL> create table test (a number, b number);

Table created.

SQL> insert into test select mod(rownum, 5), rownum from dba_users;

39 rows created.

SQL> commit;

Commit complete.

SQL> set autotrace on
SQL> select a, sum(b) from test group by a;

        A     SUM(B)
---------- ----------
        0        140
        1        148
        2        156
        3        164
        4        172


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=82 Bytes=21
         32)

  1    0   SORT (GROUP BY) (Cost=4 Card=82 Bytes=2132)
  2    1     TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=82 Bytes=2132
         )


Statistics
----------------------------------------------------------
         5  recursive calls
         0  db block gets
        12  consistent gets
         0  physical reads
         0  redo size
       531  bytes sent via SQL*Net to client
       503  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         3  sorts (memory)
         0  sorts (disk)
         5  rows processed

SQL> alter table test add constraint tst_pk primary key (b);

Table altered.

SQL> CREATE MATERIALIZED VIEW LOG ON test
 2  with rowid, (a) including new values;

Materialized view log created.

SQL> create materialized view testgrp refresh fast on commit
 2  enable query rewrite
 3  as select a, sum(b) from test group by a;

Materialized view created.

SQL> set autotrace off
SQL> insert into test select -mod(rownum, 5), -rownum-10
 2  from dba_users;

39 rows created.

SQL> commit;

Commit complete.

SQL> set autotrace on
SQL> select a, sum(b) from test group by a;

        A     SUM(B)
---------- ----------
        0        -70
        1        148
        2        156
        3        164
        4        172
       -4       -252
       -3       -244
       -2       -236
       -1       -228

9 rows selected.


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=327 Bytes=8
         502)

  1    0   TABLE ACCESS (FULL) OF 'TESTGRP' (Cost=2 Card=327 Bytes=85
         02)

Statistics
----------------------------------------------------------
        11  recursive calls
         0  db block gets
        19  consistent gets
         0  physical reads
         0  redo size
       587  bytes sent via SQL*Net to client
       503  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         4  sorts (memory)
         0  sorts (disk)
         9  rows processed

Gints Plivna
http://www.gplivna.eu
--
http://www.freelists.org/webpage/oracle-l

Other related posts: