Data Management

Decision Support: The business strengths of Oracle

Even if Oracle isnt used by your organization, you need to be familiar with it

Oracle is an expanding universe, and if it hasn’t penetrated your environment yet, you may soon see it heading your direction via the system of some partner company. Oracle is more than a platform—it is a bona fide technology—and the prudent IT professional should have some familiarity with it, even if it isn’t your house platform.

The business strengths of Oracle, like most ERP/database environments, include some fundamentals that no company should be without. But Oracle also has many unique qualities that merit special notice. It’s wise to be aware of these, whether you’re considering an Oracle conversion or planning to integrate with an existing Oracle system.

Unparalleled transaction management
Oracle passed through infancy and to its early stages of maturity as a technology in the days before enterprise resource planning (ERP) became the driving force of large-scale information systems. It entrenched itself in high-end LAN and WAN environments by tooling for high efficiency in high-volume processing. The idea was to create a large-scale processing platform that permitted large numbers of users to interact with multiple databases. A few issues Oracle had to contend with are:
  • Concurrency: You run into lots of problems when creating a system with such traditional relational database technology. Why? Well, if you permitted many users at once to access a wide spectrum of data, you must somehow allow for concurrent use. That is, an open-ended number of users may need to be reading certain common data, and an open-ended number of users will need to be maintaining that data in real time. In a traditional relational database management system (RDBMS), this is a major headache. Oracle was cured of that headache before anyone ever thought up the acronym ERP. Concurrency with Oracle is all that you could want it to be.
  • Transactional continuity: The marvelous thing about Oracle is that it is robust against a high update volume, and this volume does not impact extended queries. If you are doing an extended read of an exceptionally large data set, it will be uninterrupted, even if other users are hitting it hard with updates in the process.
  • Recovery: If there is a more recoverable high-volume platform than Oracle, I’m unaware of it. There is almost no way to lose data. What is astonishing about the robustness of Oracle technology in this area is that the RDBMS allows for complexity yet provides individual tables to be recoverable even in cascade updates. You can roll back to the original data at the most local level, no matter how integrated your application.

Rolling with the changes
When Oracle is asked to apply updates to a batch of records, it puts the originals in a rollback area and stores the necessary steps for restoring them if necessary. The capacity of this feature is configurable by your DBA, depending on the degree to which your organization values the capability. It’s important for you as an IT manager to be fully aware of this feature and how it works and to allow for this high degree of recoverability in designing applications.

This powerful capability enables you to write tremendously robust applications. You are given two phases of data management with which to work, and Oracle handles the overhead. There’s an update phase, which puts Oracle to work on the rollback process with whatever data your application is crunching, and a commit phase, which makes changes permanent.

Think about the control this gives you in your applications. You can basically be a high-level traffic manager and set whatever physical or logical conditions you please on permanent database update. You can prevent most of the major data corruption snags that can occur in an RDBMS environment and, with Oracle’s full cooperation, sidestep the phantom data changes that can plague a high-volume update system. The days of writing abstract applications and implementing them through linear programming are long gone. The real-time demands of the business environment have swept them away. However, you must have a solid understanding of your database system’s architecture in order to write effective applications.

Features that you need to know
With the advent of Oracle 9i, there are important features that you need to be aware of as an IT manager. These are all extended features rooted in the architecture described above:
  • List partitioning: This is an object-handling feature that allows you to locally handle columns of values from blocks of records and handle them as separate objects. You can imagine the power of this feature if you’re familiar with the overhead involved in manually indexing and tabling data items into arrays. Oracle now gives you a powerful shortcut, and it is especially well suited to the grab-and-drop mentality of today’s typical real-time user.
  • Decision support systems, data warehousing: These powerful new database applications are becoming increasingly prevalent in information systems of large organizations. Oracle has added components that all IT managers should be familiar with to support these applications and facilitate convenient access. The central new feature is called extraction transformation loading (ETL).
    ETL lets you move data into data warehousing tables for high-level business purposes and aids you in performing all the necessary chores that make the data valuable to senior management, such as validation, transformation, and so on. Usually this is a series of processes that are time-consuming and easily botched, especially if you’re pulling decision support data together from multiple sources. Oracle ETL cuts the overhead and gives you a direct path to implementing this kind of application.
  • External tables: In modern distributed applications, it is important that you have some means of pulling external data in and loading it into formats that facilitate its integration with system-internal data. SQL*Loader was your old mechanism. Now you can create read-only tables within Oracle that can contain data virtually. That is, the data itself is external to Oracle, but Oracle sees it and treats it as internal. You achieve the same thing you’d achieve by pulling the external data in with SQL*Loader, as you once did, but without the overhead. This feature is especially important to know in this day of highly integrated applications and processes that are distributed across systems, where data sharing between apps is increasingly frequent.

You can’t update or delete this data, since it’s not really in Oracle. This mechanism is for pulling data in, not for doing operations outside of Oracle.

Only the beginning
There are other important features that are central to Oracle RDBMS design and application work that you should look into. The ones that I’ve discussed here are a starting point, and they are probably the ones that will impact your thinking the most from a design standpoint. It’s important to realize that you aren’t simply dealing with a different make and model. When considering database platforms in today’s IT environment, you’re choosing between planes, trains, and automobiles, and need to equip yourself accordingly.

About Scott Robinson

Scott Robinson is a 20-year IT veteran with extensive experience in business intelligence and systems integration. An enterprise architect with a background in social psychology, he frequently consults and lectures on analytics, business intelligence...

Editor's Picks