GROUP BY句に指定できる最大列数はいくつなんだろうか？
GROUP BY式およびすべての非distinct集計関数（SUM、AVGなど）は1つのデータベース・ブロック内に収める必要がある
論理データベースの制限
The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block.
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/limits003.htm#i288032
う〜ん、マニュアルを読んだだけではよくわからないので、検証してみる。
-bash-3.00$ for i in {995..1000} do echo $i perl -e '$sql=qq/select deptno from emp group by deptno/;for(1..$ARGV[0]){$sql.=qq/,deptno\n/};print qq/scott\/tiger\n$sql;\n/' $i|sqlplus -s done 995 DEPTNO ---------- 30 20 10 Elapsed: 00:00:00.01 996 DEPTNO ---------- 20 30 10 Elapsed: 00:00:00.01 997 DEPTNO ---------- 30 20 10 Elapsed: 00:00:00.02 998 DEPTNO ---------- 30 10 20 Elapsed: 00:00:00.01 999 DEPTNO ---------- 20 10 30 Elapsed: 00:00:00.01 1000 * ERROR at line 1001: ORA-30489: Cannot have more than one rollup/cube expression list Elapsed: 00:00:00.01
GROUP BY句に指定できる最大列数は 1000 のようだ。列数ではなくバイト数で制限があるかもしれないので、文字数の異なる列で試してみる。
-bash-3.00$ for i in {995..1000} do echo $i perl -e '$sql=qq/select job from emp group by job/;for(1..$ARGV[0]){$sql.=qq/,job\n/};print qq/scott\/tiger\n$sql;\n/' $i|sqlplus -s done 995 JOB --------- SALESMAN MANAGER ANALYST CLERK PRESIDENT Elapsed: 00:00:00.02 996 JOB --------- MANAGER PRESIDENT CLERK ANALYST SALESMAN Elapsed: 00:00:00.01 997 JOB --------- ANALYST CLERK MANAGER SALESMAN PRESIDENT Elapsed: 00:00:00.02 998 JOB --------- CLERK ANALYST PRESIDENT SALESMAN MANAGER Elapsed: 00:00:00.02 999 JOB --------- SALESMAN PRESIDENT MANAGER ANALYST CLERK Elapsed: 00:00:00.02 1000 * ERROR at line 1001: ORA-30489: Cannot have more than one rollup/cube expression list Elapsed: 00:00:00.01
やはり 1000 のようだ。
検証に使った環境は以下の通り。
-bash-3.00$ cat /etc/release
Oracle Solaris 10 9/10 s10s_u9wos_14a SPARC
Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
Assembled 11 August 2010
-bash-3.00$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 23 22:13:06 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
22:13:06 SCOTT> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Elapsed: 00:00:00.01
