Oracle offers a worthy contender in the database server on NT market

Over the past few years, IS managers have been evaluating Windows NT as a serious database server platform. Support has slowly grown and database servers on NT now occupy a strategic position in corporate computing.

Microsoft and Oracle have emerged as the leaders in the market for SQL databases on NT, with their respective SQL Server and Oracle server products. Both companies recently shipped significant new releases of these products. Although Microsoft SQL Server has monopolized the press lately, Oracle's Oracle8 presents a worthy contender in the battle for dominance in the market for database servers on NT.

So what does Oracle8 bring to the table? Although the fundamental architecture of Oracle8 is very similar to that of Oracle7, Oracle has added a wealth of new features to Oracle8. You can group these features into two broad categories: support for very large databases (VLDBs) and support for objects. Let's take a closer look at these two categories and the specific features that Oracle8 delivers in each.

Support for VLDBs
These days, VLDBs are the standard rather than the exception. Oracle8 contains numerous features for storing and maintaining huge amounts of data. This functionality makes Oracle8 an ideal platform for data warehouses, data marts, and e-commerce applications.

Oracle8's predecessor, Oracle7, had a maximum database size of about 4TB. Oracle8 raises the bar considerably with a theoretical limit of 512 petabytes (PB--one PB equals 1000TB). Thus, Oracle8 can handle as much as 500,000TB per database.

To conceptualize Oracle8's maximum database size, consider the data in Microsoft's TerraServer database ( TerraServer is the world's largest database of satellite images, occupying 1.01TB of database space. If Microsoft hosted TerraServer on Oracle8, instead of on SQL Server, the TerraServer data would occupy 0.000002 percent of Oracle8's theoretical upper limit. So, the TerraServer database on Oracle8 wouldn't be limited to satellite images of Earth; in fact, it could store images of most other celestial objects as well!

ROWID. A new addressing scheme for identifying rows of data in a database makes the ability to handle VLDBs possible. Oracle8 identifies each row by means of a ROWID, which you can think of as the address of a row. A ROWID for a given row points to the physical location of the row where Oracle8 stores it on disk.

In previous versions, Oracle used an 18-character hexadecimal string to represent a ROWID:


In this ROWID, 0000004A is the data block address, 0000 is the row number within the block, and 0003 is the unique file number. However, this base-16 notation posed a roadblock to future database growth. Hence, Oracle8 uses an 18-character base-64 string to represent ROWIDs:


In this string, AAAA4D is the data object number, AAC is the relative file number, AAAAC2 is the data block number, and AAB is the row number. This base-64 encoding is the key to Oracle8's support for VLDBs.

Partitioned Tables and Indexes. There is more to the story than a new ROWID. Oracle8 contains several features to help database administrators (DBAs) manage large quantities of data. Partitioned tables and indexes are among the best of the new additions.

To understand partitioned tables and indexes, you must first understand tablespaces. You use tablespaces to logically divide a database into manageable individual units. Every tablespace contains one or more disk files, each of which you use to store data, indexes, and other data structures. You can store the files that make up a tablespace on separate drives, thereby spreading the I/O load evenly across the drives.

DBAs often use tablespaces to divide data by function, such as frequently updated tables vs. read-only tables. You can also use tablespaces to improve performance. For example, if a table resides in a tablespace on drive D, and its associated index resides in a tablespace on drive E, then you can read the table and index almost simultaneously. If the disks are on separate controllers, tablespaces further improve performance.

In previous versions of Oracle, every table and index resided in one tablespace. This configuration led to problems with very large tables. A bulk data load on a million-row table, for example, can take several hours to complete. This creeping pace was a result of the fact that you had to load the entire table in one operation. You also had to perform backups, recoveries, and other maintenance at the table level. Oracle8 introduces partitions, which let you break up a large table into smaller, more manageable units, and administer each unit separately. In addition, each partition can reside in separate tablespaces, allowing greater flexibility for I/O balancing.

You implement partitions by specifying one or more columns of a table to be the partition key for that table. For example, the following SQL statement creates a table to track employee information using the table's SALARY column as the partition key:

CREATE TABLE employee (

last_name VARCHAR2(20),

first_name VARCHAR2(20),

phone_no NUMBER(7),

dept NUMBER(3),

salary NUMBER(8,2))





This statement creates three partitions, each of which resides in a separate tablespace. Depending upon the value of the SALARY column, Oracle8 places new EMPLOYEE rows into different tablespaces. For example, consider the following SQL statement to add a new employee to the company:

INSERT INTO employee VALUES ('Jane', 'Doe', 1234, 40000);

Because Jane Doe's salary is $40,000, Oracle8 places the new row in the p2 partition, which resides in tablespace ts2.

Oracle8's query optimizer recognizes partitioned tables and ignores the partitions that don't contain the desired data. For example, Oracle8 needs to look at only partition p2 to satisfy the following query:

SELECT * FROM employee WHERE salary BETWEEN 30000 AND 59999.99;

Because table partitions reside in separate tablespaces, you enjoy greater flexibility in managing the data. For instance, you can partition a very large table with millions of rows so that you can back up one partition at a time, instead of backing up the entire table at once. In addition, you can take a data file that is corrupted at the OS level offline to deal with it. If that data file contains a table partition, users can access the remainder of the table while you recover the data file that failed.

You can also partition indexes. Very large tables usually imply very large indexes: In a data warehouse environment, indexes can occupy more space than tables. Creating and maintaining partitioned indexes is similar to creating and maintaining partitioned tables--after you understand the concepts, the syntax is easy to learn.

Partitions affect the physical layout of a database and don't concern end users. Users can't tell which tables or indexes you've partitioned, although they might notice an increase in performance.

Parallel operations. In conjunction with partitioned tables and indexes, Oracle8 uses parallel operations to speed processing-intensive operations. Consider a table that you load from an ASCII flat file containing one million records. Ordinarily, this operation takes several hours, depending upon processor speeds, memory, and disk transfer rates. However, if the database server machine contains multiple processors, and if you partitioned the table, Oracle8 can use parallel operations to cut the time required to load the table with data.

Let's assume you are working with the previously mentioned EMPLOYEE table, and you're using a four-processor NT server. You can alter the table definition to specify that the system simultaneously use three NT threads when working with the EMPLOYEE table:


The three NT threads will now operate on different partitions of the table, and each thread will run on a separate processor. You can use SQL*Loader, a tool that ships with Oracle8, to perform the bulk data load on the EMPLOYEE table in a third of the normal load time.

If you alter a table so that Oracle8 operates on it in parallel, the system can perform other bulk operations in far less time. For example, a batch UPDATE of all the rows in the table can simultaneously use three CPUs; and a bulk DELETE of several hundred thousand rows takes a fraction of the usually required time.

Parallel operations are a useful tool for DBAs. They are especially attractive in a data-warehousing environment, in which you refresh large amounts of data on a regular, continuing basis.

LOBs and Other New Data Types
In addition to improved VLDB support, Oracle8 has new data types that simplify working with large objects (LOBs). Along with standard data types such as NUMBER, DATE, and VARCHAR2, the Oracle server can store large data types: The LONG data type allows up to 2GB of text, and the LONG RAW data type supports up to 2GB of sound, image, and video data.

However, the LONG RAW data type has limitations. First, Oracle8 restricts LONG RAW columns to 2GB. This amount was more than enough when Oracle introduced Oracle7, but customer requirements have changed dramatically since then. Further complicating matters is the fact that Oracle allows only one LONG RAW column per table.

Thus, Oracle is phasing out the LONG and LONG RAW data types. These data types remain in Oracle8 for backward compatibility, but Oracle encourages developers to take advantage of the new data types: character LOB (CLOB), for character data; national language support character LOB (NCLOB), for double-byte character data (e.g., Chinese); and binary LOB (BLOB), for binary data (e.g., storing sounds, images, and video). Each of these new data types can handle up to 4GB of text. Also, unlike LONG and LONG RAW, these data types have no limitation as to how many columns of each type can be in a table.

Oracle8 also introduces the BFILE data type, which is an LOB that Oracle8 stores outside the database. The BFILE data type lets Oracle8 act as an indexing mechanism that contains pointers to data outside the database.

For example, consider a document-management system that stores information about engineering documents. NT servers on the network store all the documents, which are safe from accidental deletion or corruption because you regularly back up the servers. The Oracle database needs to keep track only of information about the documents. Users can query the database for information about each document's subject, author, and last revision date. In addition, the BFILE data type lets users perform read-only operations on the contents of the documents, even though Oracle does not store the documents within the database. An application developer can use this capability to give users thumbnails (i.e., miniature versions) of each document, or let users launch the associated application. For example, users can invoke Microsoft Word to edit a document that a BFILE column within an Oracle8 table points to.

Oracle8 also lets you create user-defined types. As you'll see, this capability is crucial for Oracle8's object support.

Support for Objects
Object features are one of the biggest, and most touted, enhancements to Oracle8. Oracle has added object capabilities to its existing relational database, thereby creating the world's first object-relational hybrid.

Existing Oracle7 applications will run unmodified on Oracle8--­in fact, you can create applications that have no object features. Oracle8 lets developers ease into the world of objects, using them in their applications when appropriate. In a nutshell, Oracle8 doesn't limit data structures to traditional 2D tables of rows and columns. You can construct Oracle8 objects to reflect real-world objects by employing nested tables, variable-length arrays, and user-defined data types.

To begin, let's look at how to create a user-defined data type. You can create a user-defined data type with any combination of existing data types. For example, the following SQL statement creates a user-defined data type:


first_name VARCHAR2(20),

last_name VARCHAR2(20));

Now let's re-create the EMPLOYEE table. Rather than using two columns for first name and last name, let's store both components of the employee name in one column (NAME), whose data type is NAME_T. For simplicity's sake, we'll omit the partitioning syntax we used earlier. To implement these changes, type

CREATE TABLE employee (

name NAME_T,

phone_no NUMBER(7),

dept NUMBER(3),

salary NUMBER(8,2))

The EMPLOYEE table is still a relational table, but it can now store objects of type NAME_T in the NAME column.

In addition to columns of object data, Oracle8 can store and manipulate object tables. An object table differs from a relational table in that Oracle treats each row in an object table as an object. Thus, an EMPLOYEE table with 500 rows represents 500 employees.

Historically, application developers manipulated SQL databases by issuing SQL statements from within application code. Instead of writing SQL code, application developers can write methods to manipulate the objects in an object table. By writing methods for Oracle objects, developers free themselves from dealing with two languages (e.g., C++ and SQL). Developers can write pure C++ code, which cleans up the database interface. For example, developers can call an EMPLOYEE.HIRE() method rather than having to INSERT a new row for each new employee. The EMPLOYEE.HIRE() method can also handle other tasks, such as calling an external program to send a welcome message to the new employee's mailbox.

Currently, you must write Oracle8 methods in PL/SQL, Oracle's procedural extension to the SQL language that allows loops and IF...THEN...ELSE constructs. You can see this requirement as a good thing or a limitation: Current PL/SQL developers will ease into using objects, while the transition might be more difficult for those familiar with Java and C++. However, object-oriented development in the Oracle environment is ever changing. (You can also access Oracle databases using Open Database Connectivity--­ODBC--­and Object Linking and Embedding Database--­OLE DB.)

Oracle8i and Objects
Objects in Oracle8 are a work in progress: Many object features are not finished, and object-oriented (OO) purists will find Oracle8 incomplete. Oracle8 doesn't offer class hierarchy, and OO concepts like encapsulation, polymorphism, and inheritance are nonexistent or not well defined.

The next version of Oracle8, smooths the rough edges of Oracle8's object implementation. Oracle has renamed Oracle 8.1 as Oracle8i (the i stands for Internet).

The big news about Oracle8i is Java: Oracle8i includes a built-in Java Virtual Machine (JVM). Thus, Java developers will immediately feel at ease with Oracle8i, and can quickly get to work writing server-side Java applications. Developers can execute Java code from within the Oracle database, much the way developers use PL/SQL stored procedures. In addition, using its Common Object Request Broker Architecture (CORBA) object-request broker, Oracle8i can host Enterprise JavaBeans (EJB).

Oracle8i also comes with SQLJ, a Java extension that lets developers manipulate SQL-based databases. Developers can embed SQL statements into SQLJ code, then use embedded Java Database Connectivity (JDBC) calls to precompile these statements to standard Java code. SQLJ also lets developers create Java data types that match SQL data types. SQLJ insulates developers from the complexities of writing database applications.

Oracle has worked closely with IBM, Sun Microsystems, and others to develop SQLJ. These companies are submitting the SQLJ specification to the International Organization for Standardization (ISO) as the official SQL specification for Java.

Perhaps the biggest Oracle8i buzz is about its built-in Internet File System. iFS lets you use the Oracle database as a file system, much the same way you currently use the NT and UNIX file systems. Oracle wants to make the underlying OS irrelevant. Rather than store files such as Word and Excel documents in a shared file system, the system stores the files as Oracle8i objects.

NT clients can use the existing Map Network Drive functionality against iFS, access their files with Windows Explorer, or view files using Internet Explorer (IE) as they've always done. However, unknown to users, these files are objects within the database. As database objects, these files benefit from Oracle8i's built-in features, such as hot backups and point-in-time recovery. iFS's functionality extends across the Internet, without administrators configuring and managing shared directories on NT file servers.

Oracle is banking on iFS to attract e-commerce and ISVs to the Oracle8i platform. Some pundits proclaim Oracle8i and Internet computing to be the end of client/server computing as we know it. Then again, as any major vendor will tell you, it is a grave mistake to count out Microsoft.