Which is the better one a table with 1000 rows or 10 tables with 100 rows?

[srinivasm]

I have a problem like this

i have a table, the records in that table will be more like for example 20,000 records . and the table has 10 columns.
This is better or i will split the table into 10 tables with 2000 rows per a table.

which is the best way? and why? (make a comment on both performance wise and memory wise)

please help!!!

[JChiappa]

i have a table, the records in that table will be more like for example 20,000 records . and the table has 10 columns.
This is better or i will split the table into 10 tables with 2000 rows per a table

I feel this way : IMHO it´s best to have 10 small tables (2000 rows) because :

a) you can easily to pin in memory as needed

b) you can use a full-table-scan (very fast in small tables)

c) if and when you need indexes, when you alter the table n the Oracle engine will need to alter only a small index (if you have just one bigger table, the index will be bigger too, and it costs more to update/maintain)

Of course, IMHO and YMMV.

[]s
Chiappa

[chrisrlong]

Whoa!

You have not provided nearly enough information. The question in and of itself is un-answerable. Your database design needs to be driven by the requirements, the objects and the rules of normalization. If these are 20,000 Employees, then they better bloody well be in the same table. Why would you conceivably split them? Simply put, you would not. Now, if we are talking about code values, I know some people like to put them together into one big table because they are all 'codes' and they think this is easier for some reason. They are wrong. They are separate sets of values having nothing logically to do with each other and therefore belong in separate tables. Pragmatically speaking, you cannot even do DRI on the values when you have the value mixed.

So basically, I am trying to guess as to why you would even ask this question. Unfortunately, I cannot come up with any situation where 'all other things would be equal' and you would be left with a choice of one or many tables.

The argument of pinning tables does not make sense either. If they are all logically equivalent rows, then either all or none should be pinned, making the decision of 1 or many tables irrelevant. If you would only pin some of the tables, then it would follow that they are likely logically disparate sets of values and should have been in separate tables anyway. Unfortunately, the other points in the reply are similarly flawed.

What matters is what the data is and how it will be used. That will determine the structure, not the answer to this question.

Sorry,

- Chris

[marist89]

Amen, brother Long...