Notice the dimension tables around the FACT table. Some of the dimension tables contain data that is added to queries with joins. Other dimensions, such as Region Dimension, contain no data. What purpose, then, does this STAR schema achieve with huge disk space consumption and a read-only restriction?

Using the STAR schema in Figure 1.18, let’s formulate SQL queries for rapid retrieval of desired information. For example, identifying the total cost for an order is simple, as seen in the following code:

SELECT sum(total_cost) order_total
FROM FACT
WHERE
FACT.order_nbr = 123;

It is clear that the new structure makes the realtime query faster and simpler. Consider the result if the goal is to analyze information by aggregate values using this schema. Suppose that the manager needs the breakdown of regional sales. The data by region is not available, but the FACT table supplies the answer. Obtaining the sum of all orders for the western region is now simple, as seen in the following code:

SELECT sum(total_cost)
FROM FACT
WHERE
REGION = 'WEST';

The merits of the STAR schema should now be apparent, but we need to note some other ways to represent time-oriented data suitable for Online Analytical Processing (OLAP) type systems; namely, the multidimensional database architecture as seen in Oracle Express.

Summary

This chapter has hopefully demonstrated how a proper design can be critical for good performance as the database is created. While far from being an exhaustive description of all of the relational design techniques, it has focused on the basic principles that will help to ensure that your Oracle database functions as quickly as possible.

I have come to believe that response time is one of the most critical factors to the success of any database. Regardless of how well the system was analyzed or implemented—no matter now flashy the GUI interface—if the system fails to deliver data in a timely fashion, the project is doomed. Now that we have reviewed logical database design, let’s move on to the physical implementation of Oracle databases.

Of course, there is far more to performance and tuning than just design principles. The following chapter referral list should serve as a general guide for further exploration:

•  For more information on distributed database tuning, see Chapter 7, Performance And Tuning For Distributed Oracle Databases.

•  For more information on Database connectivity for client/server, see Chapter 8, Performance And Tuning For Database Connectivity Tools.

•  For more information on SQL scripts, see Appendix A, Useful SQL Scripts.

•  For more information on STAR schema design, see Chapter 10, Tuning Oracle Data Warehouse And OLAP Applications.