Home » Developer & Programmer » Designer » Database Design
Database Design [message #90672] Wed, 21 July 2004 05:51 Go to next message
Messages: 108
Registered: February 2000
Senior Member
I wanted to just re-confirm few performance related issues in Oracle.

(1) Suppose if I have 2 tables with the same primary key but other column informations in both tables are different, is it better to go for a single table instead of two tables -as the primary key is the same. Or are there any performance issues in having a single table and multiple tables are preferred?

Just to illustrate, (a) ApplicationRequestTable has columns StartDate, ApplicationName, NumberOfRequests and (b) ApplicationResponseTable has columns StartDate, ApplicationName, MinTime, MaxTime of which StartDate and ApplicationName are the Primary key.

Is it better to have a single table ApplicationTable with fields as StartDate, ApplicationName, NumberOfRequests, MinTime and MaxTime.

(2) For a reporting tool, will it be better to have a consolidated information in a single table and define materialised views on whatever information we are interested OR is it preferred to have multiple tables for each and every dimension we are interested in.

Going by the same example, let us consider UserName is also recorded in the ApplicationTable. Hence, the fields are StartDate, ApplicationName, UserName, NumberOfRequests, MinTime and MaxTime where keyfield is StartDate, ApplicationName and UserName.

Would it be better to have materialised view on UserName and StartDate independent of the applications used OR are separate tables needed for ApplicationUsers with fields as StartDate, UserName, NumberOfRequests and Applications with fields as StartDate, ApplicationName, NumberOfRequests, MinTime and MaxTime.
Re: Database Design [message #90674 is a reply to message #90672] Wed, 21 July 2004 07:36 Go to previous messageGo to next message
Harish Shanbhag
Messages: 19
Registered: July 2004
Junior Member
thats the trade off.
u can go for cluster indexing.
which improives the performance, as the PK is strored in a separete table and is shared by tow tables.
Re: Database Design [message #90675 is a reply to message #90672] Wed, 21 July 2004 08:26 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
What you have are two sub-types of your entity. The ways to implement it would be:
1.) a single table having all the fields (the mutually exclusive ones would be nullable)
2.) 2 separate tables - one for each sub-type. The problem here would be enforcing your PK as unique across both sub-types.
3.) Keep the PK and any common attributes in one table. Create one child table for each subtype (3 tables total).

I wouldn't over-design your solution by considering MV's unless you are convinced that you really need them. They will use more space and could end up being slower. You could consider partitioned table. Just create a flag indicating sub-type 1 or 2 and partition on that.
Re: Database Design [message #90676 is a reply to message #90675] Wed, 21 July 2004 23:40 Go to previous messageGo to next message
Priya Sundar
Messages: 1
Registered: July 2004
Junior Member
In reply to mail from Andrew......

(1) There would be no Mutually exclusive data at all. For the given primary key, there would be Number of requests information and also Min, Max Response times. Basically, for the Application (say) Trade on 22-Jul-2004 08:00:00 AM to 9:00:00 AM the Number of queries happened is 100 and the Min, Max response times are 0.003 seconds and 0.005 seconds respectively.

Just wanted to know,(a) Is there a difference in performance if data is selected from a table based on the primary key - having about 10 columns or 12 columns? (b) Basically wanted to know, if there are any disadvantages in using a single table for storing both Number of Request and MinMax Times.

(2) I looked at Partitioned tables. But for a reporting utility where the user can choose any application and grouping, it does not seem to be appropriate. My idea is to create two or more MVs based on the main table. Say - (a) one for Application Name, Sum(Number of Requests) per day independent of the User (As this is a daily report) (b) one for User, Sum(Number of Requests) per month etc (As this is a monthly report).

Can there be many MVs on the same table based on different groupings and aggregations ?
Re: Database Design [message #90677 is a reply to message #90676] Thu, 22 July 2004 10:47 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
If you are selecting based on the PK, then the response time of your query is virtually independent of the number of rows in the table (even if you have 100 million rows in the table - response time to retrieve a record based on the PK will be virtually instantaneous).

You can have as many MV's as you like pointing to the table. There are restrictions on refreshing the MV's though based on how they relate to the source table. i.e. you may need to use full refreshes of MV's if the MV represents agregated results (if the MV doesn't preserver the PK of the source table). Of course your MV's will never be 100% consistent with your source table because changes to it need time to propogate to the MV or wait for the complete refresh to occur. An MV is simply a table which gets populated by a query which runs against the source table. The "trickle" feed of changes to the source table uses log tables tied to the source table. You will still need to index the MV tables if you choose to use them.
Previous Topic: differences between oracle7.3 ,8i,9i
Next Topic: creation of database
Goto Forum:

Current Time: Wed Jul 08 17:52:11 CDT 2020