-
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?
cheers
Dan
-
procedure to create foreign key
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
execute cree_indx_cons;
set heading off;
set feedback off;
spool c:\create_index.sql
select * from index_to_create;
spool off;
@c:\create_index.sql
Its a bit long but the advantage of my procedure is that the creation of the index is keep in a script!
good luck. :)
Steeve Bisson
Email: steeve_2@videotron.ca
CREATE TABLE INDEX_TO_CREATE (
L_INDE VARCHAR2(1000));
CREATE OR REPLACE Procedure cree_indx_cons is
Cursor sel_cons is
Select constraint_name,table_name
from user_constraints
where constraint_type = 'R';
enr_cons sel_cons%rowtype;
Cursor sel_cols is
Select column_name
from user_cons_columns
where constraint_name = enr_cons.constraint_name
and table_name = enr_cons.table_name
order by position;
enr_cols sel_cols%rowtype;
L_cols_name varchar2(2000);
L_indx_name varchar2(2000);
L_comp number(10) := 0;
L_log varchar2(2000);
L_cree_curs integer;
L_exec_curs integer;
L_COMP2 NUMBER:= 0;
Begin
open sel_cons;
Loop
fetch sel_cons into enr_cons;
exit when sel_cons%notfound;
L_cols_name := null;
open sel_cols;
Loop
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||',';
end if;
L_cols_name := L_cols_name||enr_cols.column_name;
End loop;
close sel_cols;
L_comp := L_comp + 1;
L_indx_name := 'Create index '||enr_cons.constraint_name||' on '
||enr_cons.table_name||'('||L_cols_name||');';
INSERT INTO INDEX_TO_CREATE VALUES(L_indx_name);
L_COMP2 := L_COMP + 1;
IF L_COMP2 > 200 THEN
COMMIT;
L_COMP2 := 0;
END IF;
End loop;
close sel_cons;
End;
/
[Edited by steeve123 on 11-24-2000 at 10:59 AM]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|