OraFAQ Forum: SQL & PL/SQL » Schema v/s Database

Home » SQL & PL/SQL » SQL & PL/SQL » Schema v/s Database

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Schema v/s Database [message #203102]

Mon, 13 November 2006 22:20

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.

Report message to a moderator

Re: Schema v/s Database [message #203114 is a reply to message #203102]

Mon, 13 November 2006 23:00

pritika
Messages: 9
Registered: November 2006

Junior Member

Biju, You should go in for 3 schemas in place of 3 databases.

Report message to a moderator

Re: Schema v/s Database [message #203116 is a reply to message #203114]

Mon, 13 November 2006 23:22

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.

Report message to a moderator

Re: Schema v/s Database [message #203132 is a reply to message #203116]

Tue, 14 November 2006 00:34

Littlefoot
Messages: 21823
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

)

Report message to a moderator

Re: Schema v/s Database [message #203135 is a reply to message #203132]

Tue, 14 November 2006 00:50

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

Report message to a moderator

Re: Schema v/s Database [message #203152 is a reply to message #203132]

Tue, 14 November 2006 02:07

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.

Report message to a moderator

Re: Schema v/s Database [message #203158 is a reply to message #203152]

Tue, 14 November 2006 02:32

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?

Report message to a moderator

Re: Schema v/s Database [message #203197 is a reply to message #203158]

Tue, 14 November 2006 05:07

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 07:31:28 CST 2024