Solved

Oracle Table Structure

Posted on 2001-06-29
5
1,153 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 143

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 143

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 143

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

730 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