《Pro Oracle SQL》Chapter7--7.10GROUP BY Restrictions

     7.10 GROUP BY的限制   (page 217)

    Your study of GROUP BY would be incomplete without considering what it cannot do.  The list of restrictions placed on GROUP BY is not very long.  The restrictions are listed in the Oracle 11.2 SQL  Language Reference for Oracle 11.2. For example:
•  LOB columns, nested tables, or arrays may not be used as part of a GROUP BY expression.
•  Scalar subquery expressions are not allowed.
•  Queries cannot be parallelized if the GROUP BY clause references any object type columns.
    如果你不知道GROUP BY不能做什么，那么你对它的学习是不完整的。对GROUP BY的限制列表不是很长。在Oracle 11.2SQL语言参考列出了对Oracle 11.2的限制。例如：
•  LOB 列,嵌套表, 或者数组可能不能被用做GROUP BY表达式的一部分。
•  标量子查询也是不允许的。
•  如果GROUP BY子句引用任何对象类型的列，查询不能并行化。 
    SQL queries were constructed to demonstrate the first two restrictions as shown in Listing 7-20 and 7-21.   The error messages clearly show that LOB columns and scalar subqueries cannot be used as part of GROUP BY clause.
    列表7-20和7-21所示，构造的SQL查询演示了前两个限制。错误消息清晰的说明LOB列和标量子查询不能被用做GROUP BY子句的一部分。
Listing 7-20.  GROUP BY Restrictions – LOB Not Allowed 
SQL> @l_7_2
  1  with lobtest as (
  2     select to_clob(d.dname ) dname
  3     from scott.emp e
  4     join scott.dept d on d.deptno = e.deptno
  5  )
  6  select l.dname
  7  from lobtest l
  8* group by l.dname
group by l.dname;
         *
ERROR at line 8:
ORA-00932: inconsistent datatypes: expected - got CLOB

Listing 7-21.  GROUP BY Restrictions – Scalar Subquery Not Allowed
  1  select d.dname, count(empno) empcount
  2  from scott.emp e
  3  join scott.dept d on d.deptno = e.deptno
  4  group by (select dname from scott.dept d2 where d2.dname = d.dname)
  5  order by d.dname;
group by (select dname from scott.dept d2 where d2.dname = d.dname);
          *
ERROR at line 4:
ORA-22818: subquery expressions not allowed here
 
    The final restriction listed appears to be a documentation error.  Evidence for that can be seen in
Listing 7-22 where the GROUP BY on an OBJECT datatype is being executed in parallel, contrary to what the documentation states.  The member function match in the dept_location type body is used to compare the value for city, and this in turn is used by GROUP BY to group employees by CITY. Should you need to create aggregations based on data in an OBJECT column, you can certainly do so as of Oracle 11.1.0.7. Testing has shown that the GROUP BY of Listing 7-22 will not be executed in parallel in Oracle 11.1.0.6. 
    所列的最后一个限制看起来像一个文档错误。列表7-22所示证实，GROUP BY的OBJECT数据类型并行执行了，相比文档所述。dept_location_type体中的成员函数match用于比较city的值，进而又被GROUP BY用于按CITY分组employees。 你能基于一个OBJECT列的数据创建聚合么？Oracle 11.1.0.7一定能做到。测试显示列表7-22中的GROUP BY在Oracle 11.1.0.6中不能并行执行。

Listing 7-22. GROUP BY on Object Column in Parallel
SQL> create type dept_location_type
  2  as object
  3    (
  4      street_address     VARCHAR2(40)
  5      , postal_code        VARCHAR2(10)
  6      , city               VARCHAR2(30)
  7      , state_province     VARCHAR2(10)
  8      , country_id         CHAR(2)
  9      , order member function match (e dept_location_type) return integer
 10  );
 11  /
 
Type created.
 
SQL>
SQL> create or replace type body dept_location_type
  2  as order member function match (e dept_location_type) return integer
  3  is
  4    begin
  5      if city < e.city then
  6        return -1;
  7      elsif city > e.city then
  8        return 1;
  9      else
 10        return 0; 
11      end if;
 12    end;
 13  end;
 14  /
 
Type body created.
 
SQL>
SQL> create table deptobj
  2  as
  3  select d.deptno,d.dname
  4  from scott.dept d;
Table created.
SQL> alter table deptobj add (dept_location dept_location_type);
Table altered.
SQL> update deptobj set dept_location =
  2    dept_location_type('1234 Money St', '97401','Eugene', 'OR', 'US')
  3  where deptno=20;
1 row updated.
SQL> update deptobj set dept_location =
  2    dept_location_type('459 Durella Street', '97463','Oakridge', 'OR', 'US')
  3  where deptno=40;
1 row updated.
SQL> update deptobj set dept_location =
  2    dept_location_type('12642 Rex Rd', '97006','Beavertown', 'OR', 'US')
  3  where deptno=10;
1 row updated. 
SQL> update deptobj set dept_location =
  2    dept_location_type('9298 Hamilton Rd', '97140','George', 'WA', 'US')
  3  where deptno=30;
1 row updated.
 
1 commit;
Commit complete.
PL/SQL procedure successfully completed.
 
  1  select /*+ gather_plan_statistics parallel(e 2)*/
  2    d.dept_location, count(e.ename) ecount
  3  from scott.emp e, deptobj d
  4  where e.deptno = d.deptno
  5  group by dept_location
  6  order by dept_location ;       -- (按match函数的返回值，分组，排序)
 DEPT_LOCATION(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVI       ECOUNT
------------------------------------------------------------                    ------
DEPT_LOCATION_TYPE('1234 Money St', '97401', 'Eugene', 'OR', 'US')             5
DEPT_LOCATION_TYPE('12642 Rex Rd', '97006', 'Beavertown','OR','US')           3
DEPT_LOCATION_TYPE('9298 Hamilton Rd', '97140', 'George','WA','US')            6
3 rows selected.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
| Id  | Operation                                               | Name           | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                     |      1    |             |      3 |
|   1 |   PX COORDINATOR                             |                     |      1   |             |      3 |
|   2 |     PX SEND QC (ORDER)                     | :TQ10002      |      0   |     14    |      0 |
|   3 |       SORT GROUP BY                           |                     |      0   |     14    |      0 |
|   4 |          PX RECEIVE                                |                     |      0    |     14   |      0 |
|   5 |            PX SEND RANGE                       | :TQ10001     |      0    |     14   |      0 |
|   6 |               HASH GROUP BY                   |                     |      0    |     14   |      0 |
|*  7 |                 HASH JOIN                           |                     |      0     |     14   |      0 |
|   8 |                    BUFFER SORT                  |                     |      0     |           |      0 |
|   9 |                      PX RECEIVE                    |                     |      0    |      4   |      0 |
|  10 |                       PX SEND BROADCAST  | :TQ10000     |      0    |      4   |      0 |
|  11 |                          TABLE ACCESS FULL  | DEPTOBJ    |      1     |      4   |      4 |
|  12 |                    PX BLOCK ITERATOR       |                    |      0     |     14   |      0 |
|* 13 |                      TABLE ACCESS FULL     | EMP             |      0     |     14   |      0 |
--------------------------------------------------------------------------- 
（译者注：使用Oracle 11.2.0运行上面的查询，报错：ORA-12801：并行查询服务器P001中发出错误信号 ORA-04063： type bod "SYS.DEPT_LOCATION_TYPE"有错误 ）


Summary
    Oracle has provided some excellent tools for the SQL practitioner in the form of extensions to the GROUP BY clause. Not only do they reduce code, they improve database efficiency.  They do, however, take some dedication and practice to learn how best to use them.  The introduction here to advanced grouping features is by no means comprehensive.  Most of these features can be combined for many different effects, far more than is practical to include in a book.  Please endeavor to make use of these features in your own applications and continue to experiment with them based on what you have learned here. 

总结
    Oracle已经为SQL实践者以GROUP BY扩展式的形式提供了一些卓越的工具。不仅可以减少代码量而且能提高数据库的性能。它们需要你花些时间和精力来学习如何最佳使用它们。这里所介绍的高级分组特性并不复杂。大部分这些特性能组合而形成多种效果，远超出一本书所包含的实例。请在你自己的应用中努力用到这些特性，把你在这里所学的不断应用于实践。