Dividing a table

I have a big table with 250 columns. We want to add many more columns to this table. But instead of doing this, a new table will be added just a extension of the existing one.
What should be the PK on that extension table same as the main table ? Should that be called FK so that it will be populated with main table then should I have additional primary key on extension table ?

main table ( X Primary key)
x y
1 2
2 2
3 4

extension table ( X will be common between the two tables with same corresponding values, unique and not null)
x z
1 9
2 7
3 5

What are the advantages/ disadvantages of doing this ?
What is the limit of number of columns in Oracle 8.0.6 tables ?

Thanks
Sonali

X shoud be a PK in both tables. In addition, extension_table should have an FK to table.

1.A table can have 1000 columns .
2.How many rows will be there in this table .Give some more details on this
3.Will this table be accessed more frequently
4.If is less than say 10000 rows I will not go for spliting ....

omegamark - 1.A table can have 1000 columns .
2.How many rows will be there in this table .Give some more details on this
- Right now 50,000 rows but there could be many more.
- About 150 columns right now

3.Will this table be accessed more frequently
- Yes, this is a heart of database structure and it has 6 child tables with details from this table which are also pulled with join everytime for any information (DML)

4.If is less than say 10000 rows I will not go for spliting ....
When would one do spliting, what are its advantages ?

I don't know why but its been recommended to do spliting here, thats why I wanted to know more about it.

marist89 -
X shoud be a PK in both tables.
In addition, extension_table should have an FK to table.
- So you are saying that I should have PK on extension table and a FK from the main table on extension, right ?
What is combination key ? When would one use it ?
How does it work, can you please give an example ?

Thanks
Sonali

Quote:

---

Originally posted by sonaliak

- So you are saying that I should have PK on extension table and a FK from the main table on extension, right ?

---

Yup.

Quote:

---

What is combination key ? When would one use it ?
How does it work, can you please give an example ?

---

I'm not familiar with that term. Do you mean a multi-field primary key?

Yes, a multi field PK in which one will be my PK and other field FK from other table. Will it make it better or worst as far as the performance goes ?
This table is extensively used.
I must have PK and FK combination on this extension to be able to access main table right ?
Suppose I just keep PK on this ext table which will be populated by application making sure that it will be same as the main table, then when I do a SQL query against these 2 tables I won't be able to join PK on main table with PK on child table, can I ?

One customer has 1 million rows in this table.

thanks
Sonali

[Edited by sonaliak on 09-27-2001 at 03:47 PM]

Quote:

---

Originally posted by sonaliak
Yes, a multi field PK in which one will be my PK and other field FK from other table. Will it make it better or worst as far as the performance goes ?

---

A PK is a PK is a PK. You would use a multi-field PK if for every record in main_table you could have 1 or more records in extention_table. It is my understanding that you have a 1-1 relationship.

Quote:

---

This table is extensively used.
I must have PK and FK combination on this extension to be able to access main table right ?

---

No, that's the logical relationship. You don't HAVE to have either a PK or an FK to access the table. However, it would be a poor design if it did not.

Quote:

---

Suppose I just keep PK on this ext table which will be populated by application making sure that it will be same as the main table, then when I do a SQL query against these 2 tables I won't be able to join PK on main table with PK on child table, can I ?

---

Sure you can.

Lets take an example:

Code:

---

SQL>  create table person (id number(5) primary key, pname varchar2(20));

Table created.
SQL> create table person_att (id number(5) primary key,
  2  hair_color varchar2(20),
  3  eye_color varchar2(10));

Table created.
SQL>  alter table person_att add constraint person_att#person
  2  foreign key (id) references person(id);
Table altered.

SQL> insert into person values (1, 'ronnie macdonald');

1 row created.

SQL> insert into person_att values (1, 'orange','brown');

1 row created.

SQL> commit;

Commit complete.
Table altered.

---

Now, you have a similar relationship to what you are describing. To query the two tables:

Code:

---

SQL> l
  1  select p.pname, a.hair_color
  2  from person p, person_att a
  3* where p.id = a.id
SQL> /

PNAME                HAIR_COLOR
-------------------- --------------
ronnie macdonald    orange

---

Notice, however, if I dropped my constraints, I could still query the tables:

Code:

---

SQL> alter table PERSON_ATT drop constraint PERSON_ATT#PERSON;

Table altered.

SQL> alter table PERSON_ATT disable constraint SYS_C00873;

Table altered.

SQL> alter table PERSON_ATT drop constraint SYS_C00873;

Table altered.

SQL> alter table person disable constraint sys_c00872;

Table altered.

SQL> alter table person drop constraint sys_c00872;

Table altered.

SQL> select p.pname, a.hair_color
  2  from person p, person_att a
  3  where p.id = a.id
  4  /

PNAME                HAIR_COLOR
-------------------- --------------------
ronnie macdonald    orange

---

My query was much slower, but it worked.

If there is a Table with say 200 columns and frequently be using about 25 columns and the others all once in a while mostly.

Why we split the table(Denormalize) is because
1.)since this is the main table we will be using this more frequently

2.)while doing so every time we will be wasting about 80% data in the db buffer cache which we do not require (all the other columns)

3.)the no of physical reads will be more and the hit ratio will come down .

Thanks for all this clarification