Oracle Table Structure

Posted on 2001-06-29
Last Modified: 2012-05-04
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?
Question by:ankhan100599
  • 3
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6237409
You could use a partitionned table: thus you have 1 table, but physically there are several ones...
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6237413
note that <1M rows start to be large, but is still reasonable size, when correctly indexed.

Accepted Solution

jpk041897 earned 50 total points
ID: 6247341
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.
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6353442
just a friendly reminder...

Author Comment

ID: 6396199
Sorry for the delay!!

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now