Case II has lot many problems. If you don't use database link and try to implement everything in J2EE then performance will be nightmare. All the query screens should have a message saying "Please come back next day to see the result...".

Consider a case where Query1 from Database1 returns 300K records and Query2 from Database2 returns 10000 records. The final output you want to return to user is join of both the queries which should return only 1000 records at the end. If you use DBLink then oracle will do all the optimization and return you 1000 records with shortest possible time.

If someone in your group starts writing this code then he will fetch all 300K+ records into server memory and paging will pick in. This will kill entire system performance.All users will suffer. If your application is heavy data centric then implementing at connection level is not the right choice. Implementing at J2EE connection level may make sense when any select returns not more than 5K records.

There tend to be alot more software engineers on projects than database people so they tend to win these arguments based on pure numbers. Also, I've noticed that upper level management tends to come out of the software engineering ranks alot more than out of the database ranks... so they are biased.

I've also noticed that alot of software engineers get stuck in 'cliches'. They repeat the same tired cliches, but never test them. They assume that cliches have to start somewhere and they wouldn't be a cliche if they weren't true. So they don't feel the need to test it to see if its accurate.

Anyone else notice this?

WOW!! Interesting ...

I am working with developers who think the database is a data dumping ground. Something the company paid a lot of money for.

I swear we need to enact a law whereby only a licensed database architect/developer/admin can touch a database in any way, shape, or form. Anyone without said licensing would have to follow a database professional's exact instructions regarding data access and architecture. It would save so many people so much time and money.

(In case you can't tell, I'm being sarcastic. Sort-of.)

We were evaluating a J2EE product from a vendor who implemented all security, filter, sort in J2EE architecture. Vendor also cameout with a unique datamodel whereby all objects were stored in just 4 tables as name value pair. No need for DBAs, database developers and claimed everything is transperent to development team.

The system was running great on demo machine where he had hardly 5000 records. Once I asked him to load 400K plus records and set up a security in such a way that each user gets only 100 record out of 400K plus records. This is the time, we could see the performance difference. JDBC connection was fetching all 400K plus records into server memory and then was returning 100 records per user request. The same happened with filter and sort. At the end, vendor realised his mistake and moved all these functionality back to database to improve the performance.

J2EE architecture works very well when working with less number of records and seamless integration with external applications are a must. It fails when unnecessarily, we start putting everything in J2EE.

Forutnately, I was able to convince in-house J2EE experts that our system doesn't need such integration and decided not to go with such products.

Now, we have fine tuned application whereby JDBC only gets what it needs (i.e. no more than 100 records at any request). All the optimization, performance, security, tuning are applied at database level and response time is less than 2 seconds.

Regards,

Ketan.

Let me share with Ganesh the experience we had with a big project that used your very same environment - J2EE on top of a lot of Oracle databases. I'm sure that everything may get the "Tom's Seal of Approval", since it's IMHO an example of the Best Practices He has always advocated (stress on IMHO).

We put all the sql statements in packages, something like

create package interface_pkg as

procedure get_orders (p_orders out sys_refcursor) is

open p_orders for
select ...
from customers c, orders@db_link o
where c.id = o.cust_id
and c.status ='ACTIVE';

end get_orders;

end interface_pkg;

and we had all J2EE developers calling the procedures to get the ref cursor (ResultSet in J2EE terminology) and so the rows. We did the same for inserts, updates etc.

The beauty of this arrangement is that it's perfectly database-independent even if it uses PL/SQL; say you want to migrate to SqlServer or MySql, all you have to do is modify the packages, which is a simple and mechanical task (I know because we made it, actually migrating from SqlServer to Oracle). You must examine anyway the sql since (as Tom explains beautifully in his books) different databases have different locking mechanisms, so even if the sql is syntactically compatible, it is usually semantically incompatible - so, it's much much better to have all of the sql in the same place (packages) than having it spreaded in different .java files.
This discussion should satisfy the "database independence" requirement.

But now that all the sql is in the database, it's also close to the data, so you can take advantage of Oracle's unmatched performance, using the DATAbase to crunch the data. Try doing the same join above as a nested loop in Java - will you (1) first get the ACTIVE customers and then the orders, or (2) the opposite ? It depends on how many ACTIVE customers and orders you have, of course ... what if today (1) is optimal and tomorrow (2) is optimal, will you change the Java code ? If the join is in sql, Oracle will automatically switch plans for you. I know because it happened to us - and i'm sure that many other times it happened without we knowing.

Or, let's say that you notice that the network is too slow - you create a local copy (snapshot) of orders and simply change 'orders@db_link o' in 'orders_local o', without touching a single line of Java. The same applies if you need to optimize/hint the sql. I know, because it happened to us.

But the most interesting thing that happened, and that was only partially predicted by us, it's that this 3-layered physical arrangement (Java using packages using the tables) fostered a division-of-labour between the Java developers, the database developers (who develop the packages), and the dbas. As an afterthought, I think that that was the most important factor that drove our project to success, since anyone played in his/her preferred technology, so:

a) the J2EE played with beans, XML parsers, Struts, etc, happy to leave the "boring sql" to the database developers;
b) the database developers enjoyed and exploited the Oracle features;
c) the dbas were more than happy to receive high-quality sql code produced by the (b) layer, and sleep at night instead of chasing the root cause of the latest inefficiency/data loss/deadlock.

Please note that i'm presenting the experience of a *real* and *successfull* project, and that is the real value of my contribution - it worked, and worked perfectly. I know the dangers of "religion wars", that can be peacefully avoided by simply looking at facts - in this case, past experience.

I wish you peace ;-)
Alberto

Folks

Thanks for the practical advise and it is very helpful. This was the kind of feedback I was waiting for.

Ganesh

> I proposed that software developers be trained and certified
> just like a civil engineer. Used the bridge analogy.

What about declaring void the "AS IS" disclaimer of sw licenses - wouldn't this achieve the same goal ? If the bridge collapses, the manufacturing company pays for the damage, hence their employing only certified civil engineers ... or, expert ones at least!

1. I just read respective section in Tom's 'Effective Oracle..' where he shows with the examples that different DBs have different semantics and therefore abstraction layers like ODBC do not help to become 'database independent'
What I discovered is that these abstraction layers not only hide (read 'disguise') the differences of say SQL Server and Oracle, they most often introduce different behavior against the same very DB.
I work with MS ADO against Oracle DB for a few years and have found numerous examples of how one and the same piece of code behaves very differently with different providers.
The same [very basic] feature can
- work fine with Oracle OLEDB provider,
- return catastrophic failure with ODBC provider
- report 'not supported' with MS provider for Oracle
So instead of DB independence you are stuck with OLE DB provider dependence

2. About certifications
It's a bit of a fun to catch Tom on contradictions.
Here he angrily downgraded soft engineers to 'coders' because they do not have certificates. In another thread he admitted that neither he nor his employees have certifications and he does not care. What is really on your mind? (Well, I guess I know, but it would be interesting to hear some confessions:)

BW

If an architect built buildings like a programmer wrote programs ...


The first woodpecker to come along would destroy civilization as we know it ... :\

Computer Science Engineers

Tom, what do you think about using a messaging option (like AQ, etc.) with request/reply as a means to link differing databases together? At one client, they abolished dblinks (they had a boatload of instances - custom and COTS) primarily due to link management concerns, and moved to a messaging solution for database interconnectivity (of course the argument can be made that the management was simply transferred to the EAI team =o). Seemed to work pretty well.... Thoughts? Cheers!

In our organization we have DB-links going to various DBs. One of the solutions we tried is J2EE. We had some success with it.

The other aspect we are looking at is one Database being the pass thru database for all tables need to be viewed by multiple apps.

Example :

We have 10 databases which publish data from 2 tables in each.

There are 20 DBs looking to DB link from One or many of the publishers.

We want to evaluate creation of a IntegrateDB. This will have DB link to the 10 Database. Now we have 20 Views in the Integrate DB.

The subscribers will link to the single IntegrateDB and depeneding on security will get access to any of some of these tables.

What is your opinion of such an architecture and what sizing impact do we need to study for the IntegrationDB.

Thanks
Siva

Tom,

you say software engineers should be as qualified/certified as bridge builders?

The Prof Eng. (PE) designation offered by www.nspe.org you refer to is just another test on some formulas commonly used in engineering. Ok, it's harder than the OCP, but still just facts. If Civ Eng can pass, it can't be all that hard. :)

And suprisingly, no other engineering discipline has the number of PE's that Civ Engs do and other disciplines construct and build things just as in important. It is just another marketing credential that the CE community adopted and requires, much like some employers look for OCP. NSPE has the credential for chem/mech/elec, but they are no where near as widely accepted/required as civ eng.

It is definitely not the proof you can be an architect of a bridge. Civil Eng have been at bridge building much longer than the word processor developers and the CE's have just as many blunders under their belt as the MSFT software engineers.

How many bridges collapsed before they got the concept right?

</code> http://www.civ.utoronto.ca/funstuff/disaster/default.htm <code>

Software has an equivalent professional designation now also. Just not widely accepted and required like the PE for CE projects.

"Professional certification is now offered by the Institute of Electrical and Electronics Engineers (IEEE) Computer Society. To be classified as a Certified Software Development Professional, individuals need a bachelor’s degree and work experience that demonstrates that they have mastered a relevant body of knowledge, and must pass a written exam."

Is that rigorous? who knows?

Tom,
I'm glad to see there is a data architecture design question. We have a database that came with a vendor application system and several our own databases for applications that the vendor system cannot provide. All these databases are not big and at certain level, they share data. We would like to consolidate these databases into one. Every database objects came from vendor remain under their schema, our own objects will be put under new schemas. Do you think this is a good idea?

Thank you very much.

A citation from a well-respected text among object-oriented designers may assist in discussions with software developers.

Quick background: Our director encouraged us (both software developers and data architects) to take IBM's Object Oriented Analysis and Design (OOAD) Exam 486. I'm always game for broadening my perspective, and understanding the developer's viewpoint.

Our test prep included reading Craig Larman's "Applying UML and Patterns" (IBSN 0-13-092569-1). Naturally, I lept to Chapter 34, "Designing a Persistence Framework with Patterns".

There Larman proposes "a single Pure Fabrication class...where all SQL operations... are consolidated." He asserts the following (familiar sounding) benefits:
(1) "Ease of maintenance and performance tuning by an expert. SQL optimization requires an SQL aficiondo, rather than an object programmer. With all the SQL embedded in the one class, it is easy for the SQL expert to find and work on it."
(2) "Encapsulation of the access method and details. For example, hard-coded SQL could be replaced by a call to a stored procedure in the RDB...".

We (the data architects) are leveraging it. We're pushing for a "call to a stored procedure" as the standard for our directorate.

I should note, however, that Larman continues next to suggest an alternative: He proposes using a "more sophisticated metadata-based approach to generating the SQL....in which SQL is dynamically generated from a metadata schema description...". SQL generated from mere table-column-primaryKey metadata (as I interpret his sentence) is generic and untunable.

hi tom,

i have been asked by (and want) to support some J2EE-developers by providing the database needed for one of their projects.

my last chance to start development from scratch dates back to 7.3.x a few years ago. there we 've introduced some useful techniques i'd like to recommend again - such as hiding all tables behind views in order to maintain access restrictions (e.g. by including ... where <some_column> = <some_packaged_function_return_value>).

since we've now 9i(R2) i wouldn't use that approach anymore to implement such features. i would go for contexts and FGAC/VPD instead to acchieve the same. based on experiences we had i would establish appropriate PL/SQL-interfaces (non-TAPI-approach) between the application server's routines and the database too.

i think we 've to bridge a gap to be successful (e.g. how to design an efficient J2EE-PL/SQL-interface): the J2EE-developers asked for assistance, but i've no J2EE-knowledge apart from reading some books and trying some samples.

therefore i would appreciate it very much, if you could point out any additional hints or comments, things you would focus on, you would do and - more important - you would never do, bugs, side effects to be aware of, most important sources of information and so on - as some kind of summarized overview or roadmap to follow.

thanks in advance.

I think this is along the same vein:

Lets pretend the original poster has 3 applications on 3 databases on 3 machines. Lets also assume all the computers and databases are homogenous. Which do you think would be better, performance-wise (purely database perspective)?

1) DB-links to access the database on the different databases

2) Creating a RAC out of the three machines and running one database instance for each.

Database_2
---------
User_B------------------->User_A

____________________________________________

Database_1
----------
User_C------------------->User_D



Background:

Front end team has connection object pointing to Database_2/User_A.
Very frequently we need to access the tables
in User_D from User_A.

Since they are in different
databases; we used Dblinks. But, since we wanted
some access control on the User_D objects, we came
up with above solution.

1.User_C (schema User_C) has synonyms for all the
tables in User_D.

2.User_B has views/synonyms for all the synonyms
in User_C using a Dblink.

3.User_A then accesses views/synonyms of User_B.



Problem:
------

Sometimes we have to copy data from Database_1 to Database_2.
For this I have created some SQL level objects and used them
in my procedure in User_D.

When I was trying to call this procedure, I was not able to access
those objects.

I had to create local/package level object in User_C and create a package
which copies data from User_D to User_C. And then used the package in User_C
in User_A using DBlink.

To me it looks like additional work. Is there a better approach for this?

Thanks in advance!!!

Yes it is a proxy link.

I wanted to know if there is a better way of doing the same.
In such an environment with 2 databases and frequent need of data in one database to other, what approach do you suggest?

User_B is accessing objects of User_C using a DBLink.
So identity is User_C.

Thanks for your response!

I am getting following error:
PLS-00201: identifier 'obj1@LINK1' must be declared.

obj is User_D and I am trying to use it in User_A.

Hello Tom, thanks for your directions in system architecture. We have some issues with db links and I would like to make sure with you. We recently built data warehouse which is a backend for vendor application, hence we do not have much control over the versions + it has different recovery requirements compared to applications and application database which use data from DW, we split databases into two. Applications need to use data (some not so big <100000 rows, lookup tables) from DW and we created db link to it. So as you see we now have problems with performance. Applications need to have quick response time, but this architecture does not give us needed performance. What would you suggest?
We have considered some options, such as:
1. Replication of data to local db (then we have to store the same data in two places);
2. Re-write sql to place driving_site or other hints;
3. use stored outlines (which is basically the same as above).
What else could we use? Is there any Oracle global parameter which set driving_site like hint on instance level?
Additionally, you have big experience with all kind of systems. What is the valid architecture for systems which are distributed and have some applications which need to have quick response time.

Tom,
We are currently transferring data between 2 oracle db's through dbLinks and triggers. Triggers present on base tables in database DB1 would perform the similar dml operation of the base tables in DB2. If an insert is made in talble1 in database 1, an after insert trigger on this table would do an insert in table1 in database2 through a dbLink. A similar procedure is being followed for updates and deletes.
There is a plan to re-write this using Java messaging.
Source DB -->Topic Publisher-->JMS BUS (MQ, SEEBeyond..)-->Topic subscriber-->Destination DB
Topic publisher and subscriber are java messaging objects.
The way they plan on doing this to read data from database1 into the java messaging objects. Publish the message to the JMS bus as queues/topic. Topic subscriber would then pick up the message on the JMS BUS and modify the data in the destination DB.

I would like your opinion on the best approach in general for transferring data between 2 databases.
1. When should db links be used?
2. When should a messaging system be used?
3. Is there a better approach than db links and java messaging?
4. Is there anything in oracle which provides functionality similar to JMS?

Would really appreciate your comments on this subject.


Thank you

Tom,

1) If we go with the option of replication, would the hardware (space and CPU) usage be double the current usage?

2) Can you please list or point me to a URL that would give me top level picture of the different methods of data transfer within Oracle - like Streams, Advanced replication and other available methods.


Thank you

or

Tom,

We want to build a static data centric application. We havge fairly large data. I don't know which type of application architecture we should select. we have about 20 users. It is an important app. but not critical, meaning it will not stop the business we must have the application very fast and can not frustrate users with slowness. all data is in oracle 10 R2 databses. please share your ideas.

1.) Desktop application ?
* (2-tier) client server ? --
clients may be in US,Europe or Asia will it perform ?

* (3-tier desktop) I have not seen many application like this.

2.) web application ?
The only thing is, the application has significant amount of data and I am not too sure that the web-browser (IE as users will use it) will be able to handle it.

- the stuff I used to build this site)


I would not even give a moments consideration to a client server application.

The volume of data is not relevant.  Amazon has a lot of data, Yahoo has a lot of data, Ebay has a lot of data, Google has a lot of data....

But they are reputed to be fairly successful.  Even using this web thing :)


You are limited mostly by the PEBKAC issue 

I also HATE the term "Software Engineer"....also I think the term/title "Consultant" has been misused and "abused" for too long (since like Oracle8.0)

Is HTML db free ? or included with oracle 10g R2 ?

I went to HTML db oracle website, those samples they have, previews are not production Quality, Your site is totally different than those samples/previews there.

So, How much experties do we need to get some real world work done with this product ?

By the way on your web-site, on upper-right side what does the Home tab ?/lable do ? it is not clickable !!

Tom,

I don't seem to have a clear picture on the approach to take on the subject topic even after going through all the excellent discussions above.

We have about 5 different Oracle transcational DBs running different J2EE applications.

What is the optimal method of intercommunication between these 5 different databases/applications. Different groups are giving different suggestions. I have put in the options being considered. I would appreciate your detailed comments or rule of the thumb (if any) on the approach to be taken.

1) Create dblinks, synonyms and triggers between the databases.
2) Use SeeBeyond to publish and subscribe messages.
3) Create web services


Thank you

Tom,

>>purchased AQ and JMS you own in the database

Can you please provide additional details on the above statement. I know that there is AQ in the DB.

How and where is JMS present in the DB?

Thank you

Our development team are planning to integrate the data on our management applications database A with another database B for Portal development.
They are planning to create materialized views on database B based on tables on database A. As they are going to implement triggers on their materialized views they need to create also snapshot logs on the tables in database A that they use for the materialized views. They say that this way the triggers will be fired properly when the views will be refreshed.
I personally don't like this very much because the snapshot logs on database A involve a lot of tables and will affect the performance of the write operations. As they are the development team they don't want to wait to do tests in production before apply this changes to see how much will affect.
The data model in database A is designed for a third party and can change.
Is there a better way to do this.
Thanks.

Tom,

We are buinding a new J2EE applications and I want to know the best way to access the database, it should be direct DML or use the object oriented concept, create a API to hide the access implementation, and which options have better performance.

Tom,

Could you please give me some example code,how you implement the transactional API, for example if the J2EE applications need the result for the following query, SELECT X,Y,Z FROM t1,t2 WHERE t1.a=t2.a .... How will look like the transactional API.

If we use transactional API the PL/SQL engine could impact on the performance of the query

create or replace package my_pgk
as

   procedure some_api( p_parm1 in <datatype>,
                       p_parm2 in <datatype>,
                       ....
                       p_resultSet in out sys_refcursor );
.....

Tom,
can you comment on their implementation, thanks:

move cpu-intensive work moved out of the database layer to applications applications layer: referential integrity, joins, sorting done in the application layer

http://www.addsimplicity.com/downloads/eBaySDForum2006-11-29.pdf

hi Tom, you may not be interested, but I'm curious how they could achieve this. Are they doing joins in the "nested loop" way?

Hi Tom

How do you tune an SQL running on a Remote DB (via DB Link) ?

Explain Plan mentions only "Remote Execution", but doesn't say how the data was access on the Remote DB.

To: A reader

Hard to say if that was an achievement.

Look, they started with a concoction of files and GDBM until they maxed out ... then they migrated "the items" to an Oracle database ... they did not re-develop their platform for a relational database. They never did ... they remained at GDBM functionality against Oracle! That was the opportunity ... it would've taken longer to re-write the code than to just insert "items" into tables and they took the quick route. After that, they got really big and doing it properly could never have been a viable option.

They were always in catch up mode ... business came pouring in (apparently people like buying and selling ____) and the only way they could scale, without re-development, was to scale out: buy more h/w. The only way they could keep adding h/w was by functional decomposition of their application. And so on ... they always had to respond to the scalability issue ... and apparently they were able to respond successfully; meaning they managed not to collapse under sheer wait. That was the achievement.

Them presenting "Architectural Lessons", just because they got away with it, is ...
And how many didn't get away with shabby architectures? Do you think they're presenting at conferences?

"Striking a balance between site stability, feature velocity, performance, and cost."

How do they know what the costs and performance would've been had they re-developed for a RDBMS in 1998?

If someone raises a building and keeps adding props to it as new floors are added on top ... would you call that an architecture? That paper describes how they evolved, the challenges they encountered and a description of their particular system ... but otherwise it is hardly an architectural blueprint of how one should build against a relational database.

Not all real-estate developers, successful at expanding the suburbia, should be in charge of city planning. And as for them deserving a Pritzker prize ... of well.

Somewhere in my comment there is a "sheer wait" ... tuning that gives way to "sheer weight".

At least they said:

"Extensive use of prepared statements and bind variables"

There has been a long running debate at work about how to 'join databases together'. We have a number of different databases (for historical reasons) each supporting their own application. However, they share certain information e.g. user account data. Currently the databases are 'joined' by the PHP application software. I have long argued for using db links as a way to 'join' the data together, arguing that the CBO will do it in a far more efficient manner than any application ever could. A quick search on the asktom website came up with the answer..i.e this article!

The discussions in this article have taken place over a number of years and I needed a quick summary of the conclusion for my work colleagues.

My summary of it's conclusions goes as follows:-
1) should I use the application to 'join' different database together?
Answer: definitely not , no way, nicht, never.

2) Should I use db links as part of my real time application code?
Answer: not unless you can absolutely avoid it, by all mean use DB links to copy data via materialized views or even in pl/sql that copies data from one db to another but anything that relies on both databases being up at the same time as part of a real time application should be avoided.

3) Should I use one of Oracle replication technologies to copy and sync data between different databases, so that real time applications are depend on one and only one database?
Answer: yes, were possible

4) Should I strive to keep everything on one database and use technologies like RAC to provide both processing power and resilience?
Answer: yes , yes and yes.

Comments ?

Hi Tom,

In regards to DB Links, we have a policy set in place that discourages the use of them. This was put into effect before my time and I have since been responsible for enforcing this "DB Link" policy against my better judgement.
We have a large number of shared database servers (245 to be exact) with dozens of applications residing on each and we continually get requests to have DB Links created between databases either to read/write data (in realtime) and push/pull data (e.g. batch jobs) from one DB to the other. The DB's are housed locally and in remote locations (over the WAN).

What are some recommended architecture best practices that I should recommend to my developer community for these different senarios? Which technologies would you suggest be used in these circumstances (i.e. seebeyond (now JCAPS), Business Objects Data Integrater (ETL tools), DB Links, AQ, Streams, etc.)?

1) Reading / writing data between databases locally (both realtime & adhoc)
2) Reading / writing data between databases remotely (over the WAN) (both realtime & adhoc)?
3) Doing batch loads from local DBs to remote DBs?

do you know how long architectural drafters work on the weeks and the weekends.

One pretty good laugh in this rainy day.
Thanks!

Regards
Michel

Forgive this long question - I am working on a project to integrate several applications and would be grateful to receive any comments / suggestions on one of our design ideas;

We have a master Personnel/HR app (highly normalised data). We need a lot of data from this HR app integrated in a couple of other apps. This includes the core personnel record as well as master/lookup data. For example the master HR app has;

create table job_title (
 job_id    number
,job_title varchar2(25)
,constraint job_title_pk primary key (emp_id)
)

create table emp (
 emp_id number
,name   varchar2(25)
,job_id number
,...    ...
,constraint emp_pk primary key (emp_id)
,constraint emp_job_id_fk foreign key (job_id) references job_title(job_id)
)

As new job titles are added we wish to integrate the data into the target application. As emps are added, updated integrate across. There are lots of look up tables (job titles, grades, depts, locations, ethnicity descriptions etc.)

Our target app exposes web services: load_job_title(), load_emp(), etc. The target app has a similar data model.


Our idea is to build Mapping tables in an integration server whereby a call to the web service load_job_title() returns the target application's primary key of the new job title.

So we build a mapping table

create table map_job_title (
 source_job_id number
,target_job_id number
,job_title     varchar2(25)
,constraint map_job_title_pk primary key (source_job_id)
)

When a new job title is captured on the source app we;

l_target_pk = call_web_service('Aspiring Software Engineer!');
insert into map_job_title (l_source_job_id, l_target_job_id, 'Aspiring Software Engineer!');

Then when we capture a new EMP record on the source;

{ emp_id:2, name:John, job_id:205, grade_id:450, deptno:10, location_id:101 }

for each target application we can construct a record using the target application's foreign key values;

select target_job_id
  into l_target_job_id
  from map_job_title
 where source_job_id = l_source_job_id;

select target_deptno into ...;

call_load_emp_webservice (emp_id, l_target_job_id, l_target_depno, ..., ...);

BENEFITS
- We dont have to get each third party vendor to build the same sort of logic internally in their API.
- We build the lookup mapping data tables once on creation and then the data is there for re-use;
Alternatively, the target app's API could receive strings ('Developer', 'IT Department', '...') and have to lookup the relevant foreign key value to load into its main emp table.

NEGATIVES
- Must be many?

Thanks for reading, if you got this far. And many thanks for any comment / critique / suggestion.
Craig

The values all come from a source app which is trusted as the 'owner' of the data.

For the master/lookup data we capture newly inserted and updated source data and integrate into the target - in many cases the target app will have a unique constraint on the real data (i.e. in the Job Title table there will be a unique constraint on the job_title attribute).

But many apps are third party. The services will have their internal validation. In other cases maybe there will not be any unique constraints on the target apps so we will need to manage data integrity in the integration server.

Is this what you were getting at in your question?

many thanks

Hai Tom,

Oracle official database 11gR2 architecture diagram is having a pointer running from DBWn to Redo log buffers. As per oracle concepts and architecture manual concerned I have never come across the relationship between DBWn and redo log buffers. What do you say? Is this needs to be address in the diagram as it is Oracle official database architecture diagram. Appreciated your comments.

Diagram Link: http://www.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=9575302&src=7027600&Act=54&msgid=3-3138049431

Thanks,
Prasad Mynumpati.

Here is the link shows oracle architecture diagram and my question about DBWR having pointer to REDO LOG Buffers.

https://www.youtube.com/watch?v=fmOLYrDkQ_Q