Home » SQL & PL/SQL » SQL & PL/SQL » Schema v/s Database
Schema v/s Database [message #203102] Mon, 13 November 2006 22:20 Go to next message
Bijupm
Messages: 16
Registered: February 2006
Junior Member
Hai,

I have an application,which is having 3 modules.
Each modules has database objects with large amount of data.

My question is which is the best option? Should i go for 3 Schema or 3 Databases.

Thanks in advance.

Regards,
Biju.
Re: Schema v/s Database [message #203114 is a reply to message #203102] Mon, 13 November 2006 23:00 Go to previous messageGo to next message
pritika
Messages: 9
Registered: November 2006
Junior Member
Biju, You should go in for 3 schemas in place of 3 databases.
Re: Schema v/s Database [message #203116 is a reply to message #203114] Mon, 13 November 2006 23:22 Go to previous messageGo to next message
Bijupm
Messages: 16
Registered: February 2006
Junior Member
Pritika,

Can you please explain the reasons.

Data in first two databases will go to the third database (History) on a weekly and daily basis.
Re: Schema v/s Database [message #203132 is a reply to message #203116] Tue, 14 November 2006 00:34 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This comes from my experience, not the documentation (so don't take it too seriously): I think that it is better to avoid traffic over the database link (which you would have to establish if there were different databases). Having all in the same database but different schemas, no such problem - everything stays in our backyard and enables easier/faster data manipulation.

(Now I'm waiting for experienced and more educated people to spit on this and prove that this is ... oh well, what it is Smile)
Re: Schema v/s Database [message #203135 is a reply to message #203132] Tue, 14 November 2006 00:50 Go to previous messageGo to next message
pritika
Messages: 9
Registered: November 2006
Junior Member

3 database means - 1) you will have to manage 3 different instances in terms normal administration and backups etc. 2) data transfer would require db links 3) advantage is each db has resource
3 schemas - 1) easier management 2) no db links required 3) resources are shared
Re: Schema v/s Database [message #203152 is a reply to message #203132] Tue, 14 November 2006 02:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm with you on this one Littlefoot. Query parsing over DBLinks results in worse plans and slower queries in my experiance.

1 Db with 3 schemas in it will use less than 3 times the resources of 3 databases, and will be a lot less than 3 times the maintenance.

It will be easier and quicker to write cross application code.


icon6.gif  Re: Schema v/s Database [message #203158 is a reply to message #203152] Tue, 14 November 2006 02:32 Go to previous messageGo to next message
Bijupm
Messages: 16
Registered: February 2006
Junior Member
Thanks to all...

We can continue the discussion as i need more advices...

Is it a good practice to have partitions on a table which contains bulk of data?

Then how to do it?
Re: Schema v/s Database [message #203197 is a reply to message #203158] Tue, 14 November 2006 05:07 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Read on this thread:
http://www.orafaq.com/forum/m/189559/81982/?
srch=table+partition#msg_189559


or see
http://www.oracle-base.com/articles/8i/PartitionedTablesAndIndexes.php

[Updated on: Tue, 14 November 2006 05:18]

Report message to a moderator

Previous Topic: How to get values of parameters in exception block?
Next Topic: doubts in suquery
Goto Forum:
  


Current Time: Thu Dec 08 19:55:38 CST 2016

Total time taken to generate the page: 0.08451 seconds