Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    splitting up the table

    I might have asked this before.....

    say I have a normalized table with 200 columns

    if i split it to 2 tables:

    table_part_a(
    table_id
    para_1a
    ..
    ...

    table_part_b(
    table_id
    para_1b
    ..
    ...
    )


    table_part_a and table_part_b are strictly one-to-one with table_id as the key.

    would this design have a difference in efficiency? or it's just a matter of personal taste?

    TIA

    Mark

  2. #2
    Join Date
    Nov 2002
    Posts
    98
    i will just reply with my observation:

    1. update/delete might need some exception control in a multi-user enviornment

    2. doesn't look like efficiency of query will be affected

    3. will be more convinient when altering table schema(?) -- what i mean is if part_b will need a new column, at least user can still query part_a while part_b is updating

    any comment is appreciated

    Mark

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by mchih
    i will just reply with my observation:

    1. update/delete might need some exception control in a multi-user enviornment

    2. doesn't look like efficiency of query will be affected

    3. will be more convinient when altering table schema(?) -- what i mean is if part_b will need a new column, at least user can still query part_a while part_b is updating

    any comment is appreciated

    Mark
    1. It makes insert, update and delete more complicated.
    2. It increases the amount of reads for queries (unless you use a cluster, which I suppose might be an option).
    3. Presumably tables won't be altered very often.

    What are your reasons for wanting to split into 2 tables?

    I had a discussion with Tom Kyte about this a while ago where he recommended against doing this unless you have more than 255 columns in a table. It is here:

    http://asktom.oracle.com/pls/ask/f?p...D:469621337269

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by andrewst
    1. It makes insert, update and delete more complicated.
    2. It increases the amount of reads for queries (unless you use a cluster, which I suppose might be an option).
    3. Presumably tables won't be altered very often.

    What are your reasons for wanting to split into 2 tables?

    I had a discussion with Tom Kyte about this a while ago where he recommended against doing this unless you have more than 255 columns in a table. It is here:

    http://asktom.oracle.com/pls/ask/f?p...D:469621337269
    I've just realised my answer above assumed Oracle - forgot this was Database Concepts & Design. Please bear that in mind!

Posting Permissions

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