Removal of Aggregate and SQL Statement Length Limitations

Page 209

Jonathan Says...

I love it when a clear and concise addition to the language makes entire classes of a problem much easier to solve. Of course, you do have to keep an eye on the effects this has on execution plans, and test what happens with more complex queries and views, but the initial impressions are good.

I understand that this feature has recently been accepted for inclusion in the next SQL standard.

Here we see that Allen had sales on 2/1 of $100, but that Ward had no sales. Conversely, we see that Allen had no sales on 2/2, but that Ward had $200 of sales.

Oracle Database 10g removes previous restrictions on the number of aggregates allowed in the group by clause. Also, Oracle Database 10g removes the 64K SQL statement size limitation.

New connect by Functionality

Oracle Database 10g offers new functionality using the connect by clause of the select command. These changes specifically apply to hierarchical queries, allowing the return of not only parent-child pairs but also ancestor-dependent pairs. Three new clauses are available when using the connect by clause:

connect_by_iscycle Determines whether the current row has a child row that is also its ancestor

connect_by_isleaf Determines whether the current row is a leaf in the tree defined by the connect by operation

connect_by_root Returns the column value from the root row

The following sections discuss some examples of using each of these operators.