What are the points for determining database instances? Here's my situation: We are building data warehouse marts using Oracle. The marts will use conformed dimensions (some tables will be utilized across star schemas). Is it better to have one instance with all the tables in it, or would it be better to have separate instances (databases)? How does one go about making these decisions? We have one data mart in which only the date and time dimensions will be shared -- wouldn't it be "better" to put this data in its own database/instance?
First, let's make sure we're clear on the definitions of database and instance. "Database" refers to the actual, physical data files that contain the data. "Instance" refers to the memory structures and processes that allow us to access our "database". I like to use the analogy that an instance is like a doorway that you pass through to get to the data. One database can have multiple instances as is the case with Oracle Parallel Server or now, Oracle Real Application Clusters. But an instance can only refer to one database. More often that not however, one database has one instance. For example, you could have a production instance and a test instance. The production instance points you to your production data and the test instance points you to your test data. Typically, this one-to-one relationship is seen most frequently.
With those definitions, let's look at what you've asked. You asked: "Is it better to have one instance with all the tables in it or would it be better to have separate instances (databases)?" Based on our definitions, we know that the datafiles (where your tables are physically located) would be the database. Therefore, you can safely say that you need only one set of datafiles. Typically, databases are associated with the application that provides access to the data and all data that a single application needs resides in a single database. Sharing data across databases can be done in several ways (replication, dblinks...). But, if you expect to need all the data via your application, there's no reason to split data into multiple databases.
Oracle is built to handle huge amounts of data in a very robust way. The technology is there....don't fear that you need to split up data in order to help Oracle out! With proper configuration and access methods, databases of hundreds of terabytes or even pedabytes are extremely efficient.
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.