Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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-lReceived on Wed Dec 27 2006 - 17:43:48 CST
![]() |
![]() |