Problem solve Get help with specific problems with your technologies, process and projects.

Determining instances for a data mart

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

Dig Deeper on Oracle data warehousing

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.