Oracle CONNECT BY clause after GROUP BY clause

I think this is just an insignificant syntax difference.

More specifically, I think this is a documentation bug. The syntax diagram for 8i implies that either order is supported. Nothing in the 8i reference implies the order makes any difference. But that diagram also kind of implies that you can have multiple group_by_clause or hierarchical_query, which isn't true:

--You can't group twice: ORA-01787: only one clause allowed per query blockselect level from dual connect by level <= 2 group by level group by level;

My guess is that when Oracle fixed the syntax diagram for 9i they also forgot the order could be different. Or maybe they intentionally left it out, because it seems more logical to do the hierarchical part first.

There are several minor syntax variations like this that are undocumented. I don't think it means that they are unsupported. Oracle probably regrets allowing so many weird options and wants things to at least look simple. For example, HAVING can come before GROUP BY, many of the old parallel features still work (but are ignored), etc. (This is why I always laugh when people say they are going to quickly "parse SQL" - good luck figuring this out!)

Oracle 8i syntax:

Oracle 9i syntax:

Look at the execution plans. In my environment they are identical, with a CONNECT BY operation feeding into a HASH GROUP BY. So it appears that placing the GROUP BY first is just an odd syntax that produces the same result as the more natural ordering.

Technically, this is probably a bug in the parser, since as you say the spec indicates that the hierarchical-query clause should come prior to the group-by clause. But it doesn't appear to make any difference in how the query is executed.