GROUP BY句に指定できる最大列数は?

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