I have a follow-up to your Oct. 28 answer to a question about how many Oracle instances for data marts should there be. My questions would have been:
Should unrelated data marts be on the same instance? i.e. would there be issues related to tuning that would make it difficult to deal with disparate sets of users? Perhaps one data mart/star schema might be lightly accessed while an unrelated data mart could have heavier access.
One more question: If users have an HR business area consisting of normalized (non-star) tables as well as star schemas should they all be on the same instance?
Again, let's make sure we're talking about the same thing. When you ask about having unrelated data marts on the "same instance," I'll assume that you mean should your unrelated data be stored in the "same database" files. The answer here is really more of a management question. Ideally (i.e. in the perfect world), if you've got the hardware, put all databases on their own server and then they have no opportunities for contention with one another. The same idea applies to instances ... since instances are the memory and processes that allow us access to the data, if you put multiple instances pointing to multiple databases on the same hardware, then they have to share resources. Therefore, you have the opportunity for more contention than if each database/instance lived on its own hardware.
The idea of disparate sets of users and lower vs. higher access patterns isn't necessarily an issue. Data marts are typically there for users to access (query) data. It's not like an OLTP system where users are needing to execute lots and lots of insert/update/delete activity. In this case, if you have one database that is designed for OLTP type use and one that's designed for a data mart/datawarehouse, then it IS a good idea to put them in different databases. The configuration of everything from block size to memory should be different for OLTP vs. data warehouse type systems.
Finally, the fact that you might have "normalized" tables and "star schemas" in the same place is fine. Just use the OLTP vs. data mart concept as your yardstick to measure if you should have data stored in one database or multiples.
A last caveat ... there are nifty features in 9i (particularly multiple block sizes) that give you the ability to put OLTP and data warehouse type data together in the same database. Your ultimate solution will need to be based on what version of Oracle you're running in addition to all else we've discussed.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle data warehousing
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.