¿bug en Oracle 10g con ROLLUP y ORDER BY?

[matanga]

Hola,

Te dejo el output y plan de ejecucion en 11g (11.1.0.6)

Código:

SQL> SELECT A, B, COUNT(DISTINCT C) AS C
  2  FROM (
  3  SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL
  4  UNION ALL
  5  SELECT 1 AS A, 3 AS B, 3 AS C FROM DUAL
  6  UNION ALL
  7  SELECT 2 AS A, 1 AS B, 2 AS C FROM DUAL
  8  UNION ALL
  9  SELECT 2 AS A, 2 AS B, 3 AS C FROM DUAL
 10  )
 11  GROUP BY ROLLUP(A), B
 12  ORDER BY B;

         A          B          C
---------- ---------- ----------
         2          1          1
                    1          1
                    2          1
         2          2          1
                    3          2
         1          3          2

6 rows selected.

SQL> explain plan for
  2  SELECT A, B, COUNT(DISTINCT C) AS C
  3  FROM (
  4  SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL
  5  UNION ALL
  6  SELECT 1 AS A, 3 AS B, 3 AS C FROM DUAL
  7  UNION ALL
  8  SELECT 2 AS A, 1 AS B, 2 AS C FROM DUAL
  9  UNION ALL
 10  SELECT 2 AS A, 2 AS B, 3 AS C FROM DUAL
 11  )
 12  GROUP BY ROLLUP(A), B
 13  ORDER BY B;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1718372963

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     4 |    36 |     9  (12)| 00:00:01 |
|   1 |  SORT ORDER BY        |      |     4 |    36 |     9  (12)| 00:00:01 |
|   2 |   SORT GROUP BY ROLLUP|      |     4 |    36 |     9  (12)| 00:00:01 |
|   3 |    VIEW               |      |     4 |    36 |     8   (0)| 00:00:01 |
|   4 |     UNION-ALL         |      |       |       |            |          |
|   5 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   6 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

15 rows selected.

Ahora si cierra la consulta con SORT ORDER BY y no con SORT GROUP BY ROLLUP. Habra que ver en 10.2.0.3 y 10.2.0.4.

Saludos