Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Table design --ask opinion

RE: Table design --ask opinion

From: Jain, Akshay <>
Date: Fri, 21 Jul 2000 10:11:42 -0400
Message-Id: <>

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

Tel. (416) 507-5385

-----Original Message-----
From: Herman []
Sent: Thursday, July 20, 2000 11:54 PM
To: Multiple recipients of list ORACLE-L Subject: Table design --ask opinion


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

                        FileType                    = Outward, Inward,
Outward Return, Inward Return, 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


Table name : InwardFile

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

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: (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 may
Received on Fri Jul 21 2000 - 09:11:42 CDT

Original text of this message