| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Table design --ask opinion
I would like to propose that here performance is going to be more
a function of table and index access, rather than having fragmentation
play more of a role.
If you always know which of the two types of files you need in advance, then splitting the data into two tables may give you some manageability in terms of total number of rows getting cut down per query.
However, if you have to query two different tables for each access of a "FileInfo", it will be much better to just have one table, where the type will be the first field of the primary key and will serve to isolate required rows.
Note that an "OR" in your query actually goes into two accesses of the join table.
Just a thought. Experienced experts please advise. I believe that splitting the data to two tables is only good when the physical access of the application is always demarcated to either one or the other, at application design time.
Akshay Jain
-----Original Message-----
From: Herman [mailto:Sherman_at_bcsis.com]
Sent: Thursday, July 20, 2000 11:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Table design --ask opinion
Hi,
I'm still new at this mailing list. Looks like this list is very cool., a lot of thing can be learned ;)
btw, I need your opinions/advises out there.
I'm designing database tables right now, and I have this one table that actually I can separate it into two tables, Here is the structure :
Table name : FileInfo
ProcessingDateTime
FileType = Outward, Inward,
Outward Return, Inward Return, etc
FileName
FileFormat
etc
I intend to divide the FileInfo table into two tables , so there's no need
to specify the FileType at these two tables.
and no need to create additional indexes for filetpye, which i might think
will help a lot when creating report ,
because it's already segragated into two tables / de-normalize.
Table name : OutwardFile
ProcessingDateTime
FileName
FileFormat
etc
Table name : InwardFile
ProcessingDateTime
FileName
FileFormat
etc
The situation is :
There will be about 500.000 records (outward , inward, outward return, etc)
flowing into FileInfo table per day,
and the management want to purge the data after 3 days.
My questions are :
1. what are the benefits to combine those two information into one table ?
and what are the disadvantages ?
2 what are the benefit to separate those two information into two tables ?
and what are the disadvantages ?
in my opinion :
if I combine into one table, it's more efficient, not too many tables but the table size will grow very fast, and after 3 days will be truncated,
I want to know if i choose option 1 or 2, how will this affect the database pefomance. what is the implication ? will there be a fragmentation ? at what level ? table level or data file level ?
Is there a way to monitor which table is fragmented or which data file is
fragmented ?
Is there a way to create a table in a specfied data file ?
any comments or suggestions ?
sorry to ask u guys a lot of questions ;)
thanks for all answers, suggestions, or comments I appreciate it v'much
best regards
Herman Susantio
-- Author: Herman INET: Sherman_at_bcsis.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You mayReceived on Fri Jul 21 2000 - 09:11:42 CDT
 |
 |