Re: 10gR2 Upgrade .. Watch out

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Thu, 28 Dec 2006 01:43:48 +0200
Message-ID: <6e49b6d00612271543v17ba2531mfc08415f1915648f@mail.gmail.com>

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;