oracle的index最多可以包含多少个列？

根据http://ningoo.itpub.net/post/2149/265635的试验，我们知道oracle中一个table最多可以拥有1000个列，那么oracle的index最多可以包含多少个列呢？

还是做个试验来验证，使用上面链接中的存储过程test_cols创建指定列数的测试表。然后根据再建一个创建指定列数的index的过程如下：

create or replace procedure test_inds(cols number,tb_name varchar2,ix_type int default 0)
-- ix_type: 0 - btree index 1 - bitmap index
as
l_sql dbms_sql.varchar2s;
l_cursor number;
l_rows number;
begin
l_cursor := dbms_sql.open_cursor;
if ix_type = 0 then
l_sql(1) :='create index ix_'||cols||' on '|| tb_name || '(';
else
l_sql(1) :='create bitmap index ix_'||cols||' on '|| tb_name || '(';
end if;
for i in 2..cols+1 loop
l_sql(i) := 'a' || to_char(i-1) || ',';
end loop;
l_sql(cols+1) := 'a' || to_char(cols) || ')';
dbms_sql.parse(c=>l_cursor,
statement=>l_sql,
lb=>l_sql.first,
ub=>l_sql.last,
lfflg=>TRUE,
language_flag=>dbms_sql.native);
dbms_sql.close_cursor(l_cursor);
end;
/

首先，创建一个有100个列的测试表

NING@ning>exec test_cols(100);

PL/SQL procedure successfully completed.

然后，测试创建普通index可以包含多少个列：

NING@ning>exec test_inds(32,'COLS100');

PL/SQL procedure successfully completed.

NING@ning>exec test_inds(33,'COLS100');
BEGIN test_inds(33,'COLS100'); END;

*
ERROR at line 1:
ORA-01793: maximum number of index columns is 32
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1608
ORA-06512: at "SYS.DBMS_SQL", line 33
ORA-06512: at "NING.TEST_INDS", line 18
ORA-06512: at line 1

可见，普通index最多可以包含32个列

再来看看bitmap indexNING@ning>exec test_inds(32,'COLS100',1);
BEGIN test_inds(32,'COLS100',1); END;

*
ERROR at line 1:
ORA-25112: maximum number of BITMAP index columns is 30
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1608
ORA-06512: at "SYS.DBMS_SQL", line 33
ORA-06512: at "NING.TEST_INDS", line 18
ORA-06512: at line 1

NING@ning>exec test_inds(30,'COLS100',1);

PL/SQL procedure successfully completed.

可见，bitmap index最多可以包含30个列。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50320/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50320/