Search the Catalog
Oracle8 Design Tips

Oracle8 Design Tips

By Dave Ensor & Ian Stevenson
1st Edition September 1997
1-56592-361-8, Order Number: 3618
130 pages, $19.95

Oracle8 Design Tips

Chapter 1

In this chapter:


1. What's New in Oracle8?

Oracle8 is finally here. What's in the new version and does it live up to expectations?

In this chapter we look at what Oracle8 is-and what it is not. We examine the message that the Oracle marketers are putting out and ponder what it means to us. We also outline what we consider to be the most significant features of Oracle8 (significant both from the perspective of wearing our designers' hats and from our expectation of what features will most heavily be used). We also believe that some features of Oracle8 won't play a major role in Oracle8 projects-only time will tell if we are right.

Oracle's Marketing Message

This past summer, one of us had the pleasure of attending the spectacular launch of Oracle8. Larry Ellison, Oracle's CEO, was at his best, giving live demonstrations and sniping at Microsoft. That part wasn't surprising. What was surprising was the fact that the event focused much more than anticipated on Oracle's vision of a new age of computing-network computing. We had expected the technical features and the much-touted object technology of Oracle8 to be on display. Instead, the network computer (NC), rather than the database, was the star.

What are we to make of Oracle's new initiative? Reading between the lines, we're seeing the Oracle database becoming something of a commodity-something that we'll simply take for granted. The analogy is simple: when you turn a tap on, you expect the water to flow; you don't want to worry about how it got there. Similarly, when you turn your computer on, you expect the data and applications to be there as a matter of course. You don't care where the data and applications physi- cally reside, and you don't care how they make their way to you.

Oracle is clearly positioning Oracle8 as more than just a database; it is now being regarded as a file server as well. The overwhelming message of the company: the cost of computing is coming down; it's more reli- able and more secure to have an Oracle8 database storing and delivering your data than it would be to use conventional methods (a mixture of local client hard drives and shared files on a server file system). This world view puts the onus on Oracle designers-as never before-to ensure that the expectations and needs of the users are satisfied.

Originally, Oracle8 was being described as a fully featured object data- base. There certainly is a move towards object support, but the emphasis has most definitely changed. Oracle has clearly listened to its existing customers and has paid attention to what they want. In subsequent chap- ters, we'll describe the many new features released with Oracle8. However, when you come right down to it, Oracle8 is primarily a robust, scalable, database management system that is capable of holding huge volumes of data and delivering it cost-effectively, reliably, and securely to large user communities. Anything more is just icing on the cake! The architecture that is Oracle's vision of the future of computing is shown in Figure 1-1.

Oracle's Network Computing Architecture
Figure 1-1 : The essentials of Oracle's Network Computing Architecture

At first glance, you might not see anything revolutionary. But there is one main difference from anything we've seen before: the lack of any local disk for the network computer. NCs are low-cost items; they may not even be conventional "computers," but instead, microprocessors that control such things as heating systems. The main point of them is this: all the data resides on the database server-sales ledgers, spreadsheets, images, pictures, video clips, or whatever. The application server contains all the executable code that can be downloaded and run on the NCs. Of course, the NCs might be the PCs you already have on your desks. However, you won't be using your hard disks for data and program storage any longer. Holding all applications software on the application server makes software maintenance much simpler-for example, an upgrade will immediately apply to all users. It will also be much easier to control who runs what soft- ware and to protect against infection from viruses

Oracle8 Features

Back to earth. Oracle8 has arrived with a good many features that will help Oracle sell its new vision and will also remedy some of the restric- tions users and developers have had in the past. This section provides a brief summary of the new technical features within Oracle8 in the context of the marketing message we've described. These features (and their claimed benefits) include:

In the following chapters, we'll expand on many of these features. In particular, we'll focus on new or improved areas where you'll need to make design decisions and choose what's best for your application. Design is all about choosing the best alternative from what's available. With the new features provided by Oracle8, Oracle Corporation is giving us a good many additional alternatives from which to choose.

Scalability

Many users of Oracle7 have hit size limitations and have had to split their data and users between servers. Oracle Corporation claims that Oracle8 provides a tenfold size improvement in Oracle8 (compared with Oracle7). Let's look at each of the specific claims and see how they stack up.

Enhancements in Parallel Server support allow Oracle to recover from the failure of one of the servers without requiring any manual intervention and with a minimal disruption to service.

In the rare event of a major failure-one in which data is lost-Oracle8's recovery is much faster than Oracle7's. Oracle8's recovery from archived redo logs has been enhanced to use multiple processors. In addition, a new GUI wizard-driven interface greatly reduces the chance of human error during the recovery process. The wizard is part of the new Recovery Manager (described in Chapter 3, Miscellaneous Oracle8 Enhancements) which makes the whole task of managing backup and recovery a lot simpler than it was in the past. Because it automates backup and recovery, the Recovery Manager is a true godsend for database administrators.

Oracle8 also provides a new utility, DB_VERIFY, which ensures the logical integrity of the data in an Oracle8 database. DB_VERIFY (also described in Chapter 3) will diagnose problems in the rare event that the database gets broken! Previously the only way to guarantee the data was by periodically exporting it.

Security

In Oracle's new vision of computing, users' PCs don't have hard disks; all data is retained on the database server. If the database is now going to be the repository for increasingly more data from many different sources, it's all the more critical that the database be secure. Both database software and design practices must be in place that will protect the integrity and the privacy of the data.

Many users like having data stored on their own hard disks and are suspicious of this new vision. They worry that their data will be less secure if it can't be stored on their own personal PCs. Is this really the case? Many users store confidential files on their PCs in the deluded belief that doing so is much more secure than putting them on a server. Others insist that their mailboxes must also reside on the good old "C" drive. They feel comforted by the fact that the data is physically close to them. These same users are shocked and disappointed when:

Oracle8 does provide better security features than Oracle7. Password management has been greatly improved within Oracle8. Oracle now offers most of the features that most operating systems have had for years (with the obvious exception of Windows!)-for example: Global user accounts are supported through distributed security domains. With these, users who are registered on one instance are authenticated to connect to other named instances within a security domain. This is useful for organizations with a mobile or fluid workforce.

More Manageable Data

We mentioned earlier that some users have been exceeding the limits of database size and have been demanding support for larger amounts of data. Large data volumes generally imply huge tables which become diffi- cult to manage. Backing up large tables or dropping or recreating indexes on large tables can take a prohibitively long time. Even loading new data into a humongous table can be very slow. In the past, designers of data warehouses have tended to get around these limitations and increase speed by partitioning large tables into lots of smaller tables and by creating views that perform a UNION ALL set operation to merge these tables together. Oracle8 now supports partitioned tables which are split on a partition key and are stored separately, but which can be accessed as a whole through partition transparency. Indexes can also be parti- tioned, where appropriate.

Another mechanism new to Oracle8 that helps keep data more manage- able is the index-only table. This is exactly what its name suggests: a table that has index blocks but no data blocks. Such tables are ideal for lookup data when you simply need to verify that a value exists and is therefore valid.

Chapter 4, Oracle8's "Big" Features, describes both partitioned tables and index-only tables.

More Copies of Data

Oracle8 has considerably improved support for data replication. Gone is Oracle7's kludge implementation using triggers. It has been replaced by code in the Oracle kernel that is far more efficient and less prone to errors. In addition, a new technology called parallel propagation allows you to achieve data replication much more quickly. Parallel propagation allows modified data on one node to be propagated to many other nodes on the network simultaneously.

Oracle7 replication did not support any tables with LONG or LONG RAW columns. In Oracle8, however, LOB data (the replacement for LONGs) can be replicated (although BFILEs cannot). (We'll describe both LOBs and BFILEs in the next section.)

More Ways to Represent Data

Oracle7 provided a fairly primitive set of scalar data types (CHAR, NUMBER, VARCHAR, DATE, RAW, LONG). Oracle8 has significantly enriched this set by offering a new type system that includes support for data collections (traditionalists can think of them as repeating groups).

Within certain limitations, users can now define their own data types, using a facility unsurprisingly called abstract data types (or ADTs). These can be simple scalar types or more complex "records," such as an address which may contain five address lines, a state, and a zip code. The biggest limitation is that there appears to be no way to specify constraints as part of a type definition, so it isn't possible to do such things as enforce an ADT for positive non-zero integers. Later in this chapter we'll show a brief example of an abstract data type (see the section "The Type System").

Those of us who have long suffered the restrictions of longs (bad pun intended) will welcome the new datatypes LOB and BFILE. LOBs (large objects) and BFILEs (binary files) have few of the restrictions of longs, and can be manipulated from within PL/SQL using the new DBMS_LOB package. The main difference between the two new datatypes is that a LOB is stored within the database and is subject to the transactional control of the database. A BFILE resides outside the database and is there- fore not subject to transactional control. We'll describe these datatypes in greater detail in Chapter 4.

NOTE The name BFILE (binary file) is rather surprising since all files held on a computer or magnetic storage are essential- ly binary. What distinguishes these files is that they are ex- ternal to the database and are pointed to from within the database. It must have been tempting to name them the X- files!

Oracle8 also no longer restricts us to scalar items (i.e., columns) within a table. Tables can contain variable arrays (known in Oracle8 parlance as VARRAYs) and nested (or embedded) tables. A VARRAY is an ordered set of built-in object types (we'll describe types under "Object Orientation" later) with implicit index variables. A nested table is a table which appears as a column of another table.

A new type of index is also available in Oracle8-the reversed key index. This feature reverses the bytes of the columns within an index, although not the columns. So, if we indexed on (LASTNAME, FIRSTNAME) the index entry for IAN STEVENSON would be NOSNEVETS\0NAI. Chapter 3 discusses why you might want to use this feature.

The view is one feature that has traditionally been used to represent data in many different ways. If, like us, you've been frustrated in the past by Oracle's restrictions on which views can be updated via SQL, then you are going to love the new INSTEAD OF triggers, which allow you to specify INSERT, UPDATE, and DELETE processing for abso- lutely any view. As you will see from the examples in Chapter 3, we do mean any view!

Faster Access to Data

Oracle8 offers significant performance enhancements over its prede- cessor. Data warehouse applications, in particular, will benefit from the new star join parallel bitmap technology. With this feature, Oracle finds qualifying rows in a fact table using bitmap indexes, and, in a second pass or phase, joins them to the relevant rows in the dimension tables. With the use of parallel processing, this type of query becomes orders of magnitude faster.

At a simpler, but no less significant, level, Oracle has introduced a new access method known as an index full scan which reads index leaf blocks from beginning to end. Because this scan is committed to reading all of the index leaf blocks, it can use the read-ahead facility; in many cases, it will be faster than a full table scan. The longer the rows and the shorter the keys, the more likely it is that an index full scan will beat a full table scan. Where a table has been analyzed and an index is available with no nullable keys, the query optimizer may elect to use an index full scan in situations where it would previously have used a full table scan.

Oracle has also introduced a server-based Advanced Queuing Facility (Oracle/AQ), available through a new PL/SQL package called DBMS_AQ. This package provides a kind of messaging system between client and server and can achieve high throughput of transactions from clients since it allows them to simply queue a message rather than directly act on the database. Clearly this approach requires careful design to guarantee data and application integrity, but in principle it is akin to middleware with the middleman cut out!

Both database recovery and DML operations (for partitioned tables) have been parallelized in Oracle8 and can therefore take full advantage of Symmetric Multiprocessing (SMP) and Massively Parallel (MPP) hardware.

Towards Object Orientation

Oracle8 is the first step on Oracle Corporation's evolutionary path toward object orientation. In its present form, the new version is by no means complete, but it does point the way toward an object-oriented database (OODBMS) which uses a relational database engine as its delivery mecha- nism. At this point in the Oracle8 life cycle, we don't believe that many projects will want to invest much effort in trying to leverage Oracle8's object support until it better supports the full object paradigm. (We'll explore the reasons why more fully in Chapter 5, Objects.) We suspect that most projects will wait for a more rounded implementation that removes many of the current restrictions.

The Type System

User-defined types are known formally as abstract data types (ADTs). They can be used in PL/SQL declarations in place of the built-in types (such as VARCHAR and NUMBER), and they can also be used in tables. You can even base an entire row definition on a type. Member functions can be called directly from SQL and are syntactically equivalent to column references. Unfortunately, as you will see in Chapter 5, the syntax used to reference ADTs means that the application program must have some awareness that ADTs are in use.

You can encapsulate structures within table definitions using VARRAYs, nested tables, and LOBs. We've introduced these datatypes briefly above, and we'll discuss them more fully in the following chapters.

One of the true object-oriented features available in Oracle8 is methods. These can be defined as part of the definition of the type on which they operate. Methods are written in PL/SQL, but they are different from stored procedures in several ways:

  1. You can invoke a method by referencing it from any occurrence of the type.
  2. Methods can access all attributes and methods of the type on which they operate.
The following SQL*Plus script gives an example of the declaration of a type containing a method (in this case, a member function), and the use of this function within PL/SQL: 
CREATE OR REPLACE TYPE circle AS OBJECT {


   x_pos  NUMBER,


   y_pos  NUMBER,


   radius NUMBER,


   MEMBER FUNCTION area RETURN NUMBER);


};






CREATE OR REPLACE TYPE BODY circle


   MEMBER FUNCTION area RETURN NUMBER IS


   BEGIN


      return(3.1417*radius*radius);


   END;


END;


/






DECLARE


   my_circle CIRCLE;


   area      NUMBER;


   radius    NUMBER;


BEGIN


   my_circle := circle(2,3,4);


   area      := my_circle.area; -- references a member function


   radius    := my_circle.radius; -- references a stored number


   DBMS_OUTPUT.PUT_LINE ( 'A circle of radius ' 


      || to_char(radius) || 'has an area of ' 


      || to_char(area));


END;


/

Object Views

Object views are designed to allow object-oriented development tools to access purely relational data as if this data were persistent objects of arbi- trary complexity. The program doesn't need to concern itself about how the data is stored in the underlying database, only with the structure with which the object is presented. Object views should help with the imple- mentation of OO applications that run against a purely relational persistent data store, though their use demands the combination of C (or C++) and embedded (OCI) calls. This is not easy stuff to implement, although Oracle Corporation has provided a service called the Object Type Translator (OTT) to generate the C and C++ structure definitions which correspond to the ADTs. We'll describe this service in Chapter 5.

Back to: Oracle8 Design Tips

O'Reilly Home | O'Reilly Bookstores | How to Order | O'Reilly Contacts
 International | About O'Reilly | Affiliated Companies

© 2001, O'Reilly & Associates, Inc.
webmaster@oreilly.com