DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: indexing composite foreign keys

  1. #1
    Join Date
    Nov 2000
    Posts
    1

    Question

    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

  2. #2
    Join Date
    Nov 2000
    Posts
    440

    Thumbs up 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
  •  


Click Here to Expand Forum to Full Width