| Multiple Instances [message #253808] |
Tue, 24 July 2007 21:43  |
vsharmac
Messages: 16
Registered: July 2005
|
Junior Member |
|
|
Hi all,
I have a bit complex scenario here.
There are 2 separate business which have their own
separate networks. Business X uses a oracle 9i
database and it wants Business Y to have access
to part of its data(read-only).
However creating a read only view is one of a
solution here. Business Y has a requirement that
the data they access should be a live one that
Business X has.There will be a lease line across
this 2 businesses through which the application
will connect to this view.
Since addition of this view and addition of the
users from business Y might have some performance
issue with the server at the Business X end.
Business X wants a separate server for that view
to be held in so that the Business Y users access
only this new server. This view is suppose to
contain same data as it is in the live system at
any given time.
My question here is what are the ways of doing
this? Which other products or tools will be used
here?Need some help urgently
Thanks
vsharma
|
|
|
|
| Re: Multiple Instances [message #253811 is a reply to message #253808] |
Tue, 24 July 2007 22:10  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>creating a read only view
Please post the DDL that creates a "read only view".
If a user does NOT have INSERT, UPDATE or DELETE privs,
how does a "read only" view differ from a regular view?
How does creating a view increase the load on the DB or system?
>Since addition of this view and addition of the users from business Y
>might have some performance issue with the server at the Business X end.
^^^^^ Then again the performance might not change at all
Without some ball park numbers, it is anyone's guess what viable solution is appropriate.
How many new users as compared to existing users?
How loaded is the current database?
How much data is changing within the "view"?
How big a pipe (network speed?) will exist between 2 sites?
[Updated on: Tue, 24 July 2007 22:11] by Moderator Report message to a moderator |
|
|
|
| Re: Multiple Instances [message #253821 is a reply to message #253811] |
Tue, 24 July 2007 23:36 |
vsharmac
Messages: 16
Registered: July 2005
|
Junior Member |
|
|
Hi,
This is how i write the read-only view
CREATE OR REPLACE VIEW myview2
AS SELECT empno, ename, sal
FROM emp
WHERE deptno=10
WITH READ ONLY OPTION;
Currently there are 100 users from Business X and there will be another 30 that will come from Business Y side.
Well this current machine is not a very powerful machine. Business X wants another machine to balance the load.
The table for which this view will be created currently has about 2,000,000 records and there are about 4,000 to 5,000 changes everyday to that table.
We are thinking of getting 1M Line.
The extra 30 Business Y users will only query the data so iam thinking this query will have some impact on the performance of the DB. It is etimated that one new user will atleast run 100 queries per day.
Was reading about Materialized Views. Don't know if that can be a solution here.
Thanks & Regrds,
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
