Solved

Oracle Table Structure

Posted on 2001-06-29
5
1,135 Views
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?
0
Comment
Question by:ankhan100599
  • 3
5 Comments
 
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...
Cheers
0
 
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.
Cheers
0
 
LVL 6

Accepted Solution

by:
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.
0
 
LVL 142

Expert Comment

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

Author Comment

by:ankhan100599
ID: 6396199
Sorry for the delay!!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

810 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