Oracle Table Structure

I have a huge flat file that contains a number of name value pairs. And I have to decide on a relational structure for the flat file.  

What are the trade offs of having one table with having four fields : sequence no, foreign key from parent table, name and tag fields. And I can insert all the records into one table. Literally hundreds of thousands of records.

Conversely, I can normalize into a number of smaller tables and insert the same data there. Smaller tables with lesser numbers of rows.

Note that on query for a particular key value in the parent table, all related records are to be displayed to the user.

What are the performance issues here? Are there any other issues that I need to look at?
Who is Participating?
jpk041897Connect With a Mentor Commented:
Oracles indexes are based on a quad tree structure, so you can a prety good idea on your performace by getting the fourth root of the number and multiply it by your avarage disk seek speed.

Oracle starts to shine when considering the multiple users and multiple transactions/sec.

As to design issues, weather you break up the table into smaller tables or not depends on two issues:

1.- Will the table remain normalized? If not, the issue is moot and you should maintain a single table structure.

2.- Will breaking up the table simplify designing your queries?

The more tables you have, the more index files need to be accesed and traversed, not to metion comparisons between foreign key and primary fields amongst the tables. This clearly indicates that from a pure performance perspective a single table is certanly faster

Add to this the fact that leaf indexes in the key partitions contain pointers to next and previous keys on the key quad tree and you can see that for sequential traversals you almost want a single table.

The issue then is not so much wether you shoul use a single table or multiple tables, but rather, what kind of queries you will be using and what the primary key(s) should be.

One often ovelooked optimization trik for very large tables is to convert String secondary keys to integers via a simple hashing function (say ASCII addition of the strings characters). You then perform a lookup on the integer key (obtaining a result set with some false positive results) and a second query on this result set for the specific string value.

On large tables with large string keys the increase in performance can be outstanding, though at the cost of slower insert operations and a somewhat larger cost in disk space.

Hope this helps.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You could use a partitionned table: thus you have 1 table, but physically there are several ones...
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that <1M rows start to be large, but is still reasonable size, when correctly indexed.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just a friendly reminder...
ankhan100599Author Commented:
Sorry for the delay!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.