Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Smile Unanswered: Help on Informix (IDS 7.3x) database design

    I need your advice on this matter:

    [1]. What is the different between specifying 'primary key constraint' during table creation (together in the same schema) and creating 'unique index key' after table table has been created?

    [2]. Referring to the question [1] above, which provide better performance e.g.
    (a) --> create empty table (without any primary
    key or index)
    --> load data into table
    --> create index.

    -or-

    (b) --> create empty table (with primary key
    specified
    --> load data into table
    --> create index

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Help on Informix (IDS 7.3x) database design

    Originally posted by sengchoon
    I need your advice on this matter:

    [1]. What is the different between specifying 'primary key constraint' during table creation (together in the same schema) and creating 'unique index key' after table table has been created?

    [2]. Referring to the question [1] above, which provide better performance e.g.
    (a) --> create empty table (without any primary
    key or index)
    --> load data into table
    --> create index.

    -or-

    (b) --> create empty table (with primary key
    specified
    --> load data into table
    --> create index
    Hi,

    I suggest you that:
    For this case, create table with primary key "disabled", next load data, next run "set constraints for "table" enabled, next create all index and next run update statistics for "table" with distributions.

    Gustavo.

  3. #3
    Join Date
    Aug 2002
    Posts
    21

    Re: Help on Informix (IDS 7.3x) database design

    What is the purpose of "set constraints for "table" enabled you've mentioned. And also what is the purpose "with distributions" means? Actually, I'm still beginner in this term.


    Originally posted by gurey
    Hi,

    I suggest you that:
    For this case, create table with primary key "disabled", next load data, next run "set constraints for "table" enabled, next create all index and next run update statistics for "table" with distributions.

    Gustavo.
    Last edited by sengchoon; 12-19-03 at 11:11.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Help on Informix (IDS 7.3x) database design

    Originally posted by sengchoon
    What is the purpose of "set constraints for "table" enabled you've mentioned. And also what is the purpose "with distributions" means? Actually, I'm still beginner in this term.
    Hi,

    Ok, for example:
    create table xxxx
    ( col1 smallint not null primary key pk_col1 disabled,
    col2 integer,
    .......
    )

    This create the table with the primary key but not create the index.
    Next you can load data and next enabled the primary key, then now the index is created.
    During load of data, none index is used, then load fastest.

    Gustavo.

  5. #5
    Join Date
    Aug 2002
    Posts
    21

    Re: Help on Informix (IDS 7.3x) database design

    Hi, how to enable primary key back?


    Originally posted by gurey
    Hi,

    Ok, for example:
    create table xxxx
    ( col1 smallint not null primary key pk_col1 disabled,
    col2 integer,
    .......
    )

    This create the table with the primary key but not create the index.
    Next you can load data and next enabled the primary key, then now the index is created.
    During load of data, none index is used, then load fastest.

    Gustavo.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •