oracle知识点记录：1.table的列数，index最多包含多少个列

 

1.Oracle中的table的列数最多有1000

create or replace procedure test_cols(cols number)
as
l_sql dbms_sql.varchar2s;
l_cursor number;
l_rows number;
begin
l_cursor := dbms_sql.open_cursor;
l_sql(1) :='create table cols'||cols||'(';
for i in 2..cols+1 loop
l_sql(i) := 'a' || to_char(i-1) || ' int,';
end loop;
l_sql(cols+1) := 'a' || to_char(cols) || ' int)';
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;
/

exec test_cols(1000);

2.
oracle的index最多可以包含多少个列呢?
普通index最多可以包含32个列
bitmap index最多可以包含30个列

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;
/
exec test_inds(32,'COLS100');