Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Oracle Table Structure

Posted on 2001-06-29
Medium Priority
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
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
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...
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.

Accepted Solution

jpk041897 earned 150 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 143

Expert Comment

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

Author Comment

ID: 6396199
Sorry for the delay!!

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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