Re: Data Warehouse allocation to Oracle instances

daveball28_at_my-deja.com wrote in message <87q6fs$83m$1_at_nnrp1.deja.com>...
>We have numerous decision support databases that we would like to pull
>together into a data warehouse. Most databases are currently in thier
>own Oracle V8 instance, but all on the same E10K.
>
>I'm new to Oracle. Does the instance assignments matter? Can we do
>cross-instance joins? Do we have a data warehouse if all databases are
>on the same box or should we move them to the same instance? Why/why
>not?

Yes, you can do cross instance (or what they call, distributed) joins. But then you can also skydive without a parachute and if the heavens smile on you, you may even survive that to lay thinking about the stupidity of that while recovering in hospital for the next 12 months. :-)

I have my own opinion of what data warehousing should be on Oracle - but then it is probably biased as I was spoiled being privilege to be part of design and implementation team using Oracle Parallel Server (OPS) on a UNIX MPP (massive parallel processing cluster) mesh.

But one thing I do know - if you want to have the Network Admin guy waiting for you in the basement after hours with a leadpipe, do distributed joins. It can put a huge load on the network. Besides, it can be excruciatingly slow from a query and end-user perspective

I would say that a "good" data warehouse requires a single physical database and should not use (without a very good technical reason), distributed databases.

As for the database instances. You have, from an architectural point of view, two basic choices for running a data warehouse on Oracle. Either use standard Oracle (on a single CPU or SMP box), or use Oracle Parallel Server (OPS) on a cluster. Standard Oracle means a single database instance for that physical database, or OPS where you have multiple database instances for that physical; database.

OK, with either config you need to have Parallel Query (PQ). Data warehousing requires a lot of full table scans of big fat fact tables and PQ is usually the best way to start skinning this cat.

OPS vs normal Oracle. Imagine OPS as having PQ, but instead of running it on a single machine, you can distributed your PQ's across several machines. And that is basically what OPS does for you. You have a single physical database, but several database engines (or instances) for that single physical database. Each instance run on it's own machine and uses a special communication facility to community with the other instances to share workload and handle database locking across instances.

To cluster or not to cluster, that is the question. You get good arguments from both sides - from the SMP supporters and the MPP supporters. I have seen both in action and both have their pros and cons. The hardware technology to use I believe is decided by the end of the day by the business requirements. MPP for example allows 24x7 processing much easier than SMP as you do not have all your eggs in a single machine. But then MPP can be a lot more expensive, but a -lot- more scalable than SMP. So it all depends...

Whatever you do decide, I do not recommend that a data warehouse be created as a set of distributed databases, without carefully considering the technical side of the decision. Impact on the network. Keeping data sets in synchronising. How daily/weekly/monthly updates are to be implemented across distributed databases. I am not saying that this can not work - but I would think that this type of architecture is the exception and not the rule when it comes to handling massive amounts of data in a warehouse environment.

regards,
Billy Received on Wed Feb 09 2000 - 04:06:56 CST