Types of Databases

In many organizations, as a DBA you can face on a daily basis the possibility of working with different types of databases and, as a result, different types of data and management requirements. You may find yourself working on simple SQL queries with users and simultaneously wrestling with decision-support systems for management. Databases perform a variety of functions, but you can group all of those functions into two broad categories: online transaction processing (OLTP) and decision support system (DSS) (which is sometimes also called online analytical processing [OLAP]). Let's take a quick look at some of the basic classifications of Oracle databases.

Online Transaction Processing and Decision Support System Databases

The online transaction processing (OLTP) category of databases represents the bread and butter of most consumer- and supplier-oriented databases. This category includes order entry, billing, customer, supplier, and supply-chain databases. These databases are characterized by rapid transactions and a need to be online continuously, which today (given the use of the Internet to access such systems) means 24/7/365 availability, short maintenance intervals, and low tolerance for breakdowns in the system.

Decision support systems (DSSs) range from small databases to large data warehouses. These are typically not 24/7 operations and they can easily manage with regularly scheduled downtime and maintenance windows. The extremely large size of some of these data warehouses necessitates the use of special techniques both to load and to use the data.

There isn't a whole lot of difference between the administration of a DSS-oriented data warehouse and a transaction-oriented OLTP system from the DBA's perspective. The backup and recovery methodology is essentially the same, and database security and other related issues are also very similar. The big difference between the two types of databases occurs at the design and implementation stages. DSS systems usually involve a different optimization strategy for queries and different physical storage strategies. Oracle9i provides you a choice between using an OLTP database and a DSS database using the same database server software.

Performance design considerations that may work well with one type of database may be entirely inappropriate for another type of database. For example, a large number of indexes can help you query a typical data warehouse efficiently while you are getting some reports out of that database. If you have the same number of indexes on a live OLTP system with a large number of concurrent users, you may see a substantial slowing down of the database, because the many updates, inserts, and deletes on the OLTP system require more work on the part of the database. So, know what you are trying to achieve with your new system and design accordingly in terms of choosing indexes, index types, and space parameters for the various objects in your database.

Development, Test, and Production Databases

Oracle databases can generally be grouped into three categories: development, test, and production. Applications are developed, tested, and put into production. A firm usually has all three database versions in use at any given time, although for smaller companies, the test and development versions of the database may be integrated in one database.

Development databases are usually owned by the development team, which has full privileges to access and modify data and objects in those databases. The test databases are designed to simulate actual production databases and are used to test the functionality of code after it comes out of the development databases. No new code is usually implemented in the production or the "real" databases of the company unless it has been successfully tested in the test databases.

When a new application is developed, tested, and put into actual business use (production), the development and production cycle does not end. Application software is always being modified, for two reasons: to fix bugs and to improve the functionality of the application. Although most applications go through several layers of testing before they move into production, coding errors and the pressure to meet deadlines contribute to actual errors in software, which are sometimes caught later when the application is already in use. In addition, users continually request (or, more appropriately, demand) modifications in the software to improve the functionality of the application. Consequently, application code does not remain static; rather, developers and testers are always working on it.