I can generate SQL from SQl to index all foreign keys automatically, however, this puts an index on each column in a composite foreign key. I'd like the code to create 1 index for the whole composite foreign key. Any ideas?
Hi, heres a procedure that create all index for foreign key
and also for foreign key with more than 1 column.
do the following:
create the table
create the procedure
set heading off;
set feedback off;
select * from index_to_create;
Its a bit long but the advantage of my procedure is that the creation of the index is keep in a script!
good luck. :)
fetch sel_cons into enr_cons;
exit when sel_cons%notfound;
L_cols_name := null;
fetch sel_cols into enr_cols;
exit when sel_cols%notfound;
if (L_cols_name is not null) and
(enr_cols.column_name is not null) then
L_cols_name := L_cols_name||',';
L_cols_name := L_cols_name||enr_cols.column_name;
L_comp := L_comp + 1;
L_indx_name := 'Create index '||enr_cons.constraint_name||' on '
INSERT INTO INDEX_TO_CREATE VALUES(L_indx_name);
L_COMP2 := L_COMP + 1;
IF L_COMP2 > 200 THEN
L_COMP2 := 0;