Oracle 10g

Cambio en el comportamiento del GROUP BY in Oracle 10g

por

Si usted recientemente actualizo su versión de base de datos oracle a la versión 10g, habrá notado que las consultas que agrupan (GROUP BY) le entrega los resultados en cualquier orden.

Pues bien, desde la versión oracle 10g el comportamiento de esta clausula ha cambiado con respecto a sus predecesores.  Ahora esta usando el nuevo mecanismo  HASH GROUP BY, el cual no garantiza que el resultado este en ningún orden a menos que utiliza la clausula «ORDER BY».

Realicemos una prueba para verificar este comportamiento

En Oracle 9i

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

OWNER    COUNT(1)
CTXSYS    76
HR        25
MDSYS    53
ODM        82
ODM_MTR    12
OE        46
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
44 VIEW VIEW SYS.SYS_DBA_SEGS Cost: 1,688  Bytes: 23,562  Cardinality: 1,386
43 UNION-ALL
……

Como podrá observarse, el primer resultado (oracle 9i) es entregado ordenado ascendentemente, mientras que el segundo (oracle 10g) resultado no tiene orden alguno

Se puede apreciar en los planes de ejecución que utilizan mecanismos distintos para resolver la consulta, mientras en la primera utiliza «SORT GROUP BY», la segunda utiliza el nuevo mecanismo «HASH GROUP BY».

Deshabilitando el «HASH GROUP BY» en oracle 10g

Existen 2 maneras de deshabilitar este comportamiento del group by:
Una es a nivel de session y otra a nivel de base de datos, aunque siempre es posible agregar el order by en las consultas que asi lo requieran.

A nivel de sesion se puede configurar el parametro oculto _gby_hash_aggregation_enabled

alter session set «_gby_hash_aggregation_enabled» = FALSE; para versión oracle 9i

A nivel de base de datos se puede configurar el parametro optimizer_features_enabled

alter system set optimizer_features_enable=’9.2.0′;

alter system set optimizer_features_enable=’8.1.7′;

Estas ultimas configuracion, no requiere el reinicio de la base de datos.

Referencias:

Nota Metalink : 345048.1
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1251893,00.html