Re: 10gR2 Upgrade .. Watch out
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: GovindanK <gkatteri@xxxxxxxxxxx>
- Date: Thu, 28 Dec 2006 01:43:48 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
