Why Cant I Hear My Microphone Samson R10s

Change in behavior of GROUP BY in Oracle 10g


If you recently upgraded your version of Oracle database to version 10g have noticed that the consultations grouped (GROUP BY) gives the results in any order.

Well, from the Oracle 10g the behavior of this clause has changed from its predecessors. Now using the new HASH GROUP BY mechanism, which does not guarantee that the result is in any order unless you use the clause "ORDER BY".
perform a test to verify this behavior




In Oracle 9i

SQL> select owner, count (1) from dba_segments group by owner;

OWNER COUNT (1)

CTXSYS 76 HR 25 MDSYS ODM

53 82 46 OE ODM_MTR 12

OLAPSYS 149
ORDSYS 7 OUTLN 6

Plan de Ejecución

SELECT STATEMENT CHOOSE 50 SORT GROUP BY

49 VIEW SYS.SYS_DBA_SEGS 48 UNION-ALL ……

En Oracle 10g


SQL> select owner,count(1) from dba_segments group by owner;

NEURONET 9

HR2 34 HABITAT 6 MDSYS 125

CTA_CTE1 392

RMAN 132
TSMSYS 4
DMSYS 4
DESIGNER6I 1902
DESIGNERTEST 1903

Plan de Ejecución


SELECT STATEMENT ALL_ROWSCost: 1.689 Bytes: 23.562 Cardinality: 1.386
45 HASH GROUP BY Cost: 1.689 Bytes: 23.562 Cardinality: 1.386

SYS.SYS_DBA_SEGS VIEW 44 VIEW Cost: 1.688 Bytes: 23.562 Cardinality: 1.386
43 UNION-ALL

... ...

As can be seen, the first result (oracle 9i) is given in ascending order, while the second (oracle 10g) result does not warrant any

can be seen in the implementation plans that use different mechanisms to resolve the query, while in first use "GROUP BY SORT", the second uses the new mechanism "HASH GROUP BY". Disabling

the "HASH GROUP BY" in oracle 10g

There are 2 ways to disable this behavior by group:

A session is level and another at the database, although it is always possible to add the order by in consultations that require it.