Oracle Unleashed, 2E
Oracle8 is much more than an upgrade to Oracle's existing relational database management system (RDBMS). For the first time, Oracle has combined object and relational technologies to produce an incredibly powerful and flexible object-relational database management system (ORDBMS). In addition to the new object-oriented extensions, Oracle8 provides significant scalability, extensibility, and performance improvements. This chapter reviews the basic Oracle architecture and presents an overview of the most significant new features of the Oracle8 server.
Despite the many new features of Oracle8, the core of the RDBMS remains intact to support backward compatibility. Backward compatibility protects investments in current Oracle implementations and enables existing application and database designs to exploit the new features of Oracle8. This section provides a high-level overview of the basic Oracle server architecture and some its most popular options.
The basic architecture of the Oracle8 server can be expressed in terms of physical storage, memory structures, and server processes.
An Oracle database uses three types of file structures:
TIP: Ideally, control files and redo log files are spread across at least two disks to enable full recovery from a media failure.
The primary memory structure used by an Oracle instance is the system global area (SGA). The SGA is made up of the following components:
Each of the components of the SGA can be tuned to optimize performance using initialization file parameters. You can set the number of database blocks and the size of each block, for example, to increase the size of the cache and optimize I/O between the cache and data files. As the name implies, the SGA is shared by all processes associated with the Oracle instance.
Except when using the multithreaded server option (which is discussed later in this section), a separate server process is dedicated to processing an individual client's requests. The program global area (PGA) is the memory structure used by these individual processes. The PGA contains data for a specific process and includes stack space, session information, and sort space. The size of each PGA is set for the instance, depending on the operating system and a handful of initialization parameters. The sort area of the PGA can be tuned based on these parameters, which can set the minimum and maximum size of the sort area and indicate how I/O between the sort area and temporary data segments should be handled.
A number of background processes are used by each Oracle instance to control physical I/O and to manage the instance and dedicated server processes:
Figure 7.1 illustrates the interaction between Oracle processes, memory structures, and file structures in a standard (dedicated server) configuration.
The basic architecture is extended by a number of popular Oracle server options, including the following:
NOTE: The term fail-over refers to redundancy in servers. When two computers are accessing a common disk subsystem, the second is available in the event the first computer fails. This minimizes downtime.
FIGURE 7.1. Oracle dedicated server architecture.
This brief overview of the Oracle architecture was provided as a review and a frame of reference. The following section presents the new features of Oracle8 and assumes that you are familiar with Oracle7.
The new object-relational features of Oracle8 have been highly publicized, but a number of significant enhancements to the core RBMS deserve attention as well. This section presents some of the most significant Oracle8 enhancements in terms of physical storage improvements, SQL and PL/SQL extensions (including object-oriented features), and optimizer improvements.
Oracle8 offers a number of new physical storage options you can use to optimize performance for specific types of applications, including table partitioning, index partitioning, and several new index types.
Table partitioning divides table data between two or more tablespaces and physical data files, ideally on separate disks. You can use table partitioning to improve transaction throughput and certain types of queries for large tables. Currently, you can partition a table only horizontally, based on the value of one or more columns.
Suppose that a national sales organization wants to partition historical sales data by date and regional sales office. Listing 7.1 shows what the DDL to create the partitioned table might look like.
){kind=link}
CREATE TABLE region_sales_hist (
invoice_date DATE,
region_id NUMBER(1),
invoice_num NUMBER(10),
customer_id NUMBER(10),
total_amt NUMBER(10, 2),
status_code NUMBER(1) )
PARTITION BY RANGE (invoice_date, region_id)
(PARTITION inv96r1 VALUES LESS THAN (`01-JAN-96', 2)
TABLESPACE ts_inv96r1,
PARTITION inv96r2 VALUES LESS THAN (`01-JAN-96', 3)
TABLESPACE ts_inv96r2,
PARTITION inv96r3 VALUES LESS THAN (`01-JAN-96', MAXVALUE)
TABLESPACE ts_inv96r3,
PARTITION inv97r1 VALUES LESS THAN (`01-JAN-97', 2)
TABLESPACE ts_inv97r1,
PARTITION inv97r2 VALUES LESS THAN (`01-JAN-97', 3)
TABLESPACE ts_inv97r2,
PARTITION inv97r3 VALUES LESS THAN (`01-JAN-97', MAXVALUE)
TABLESPACE ts_inv97r3);
Note that because MAXVALUE was never specified in the VALUES LESS THAN clause for invoice date, an implicit check constraint on invoice_date < `01-JAN-97' will be enforced. You can add a partition by using the ALTER TABLE command to accommodate additional years.
You can define partitions in separate tablespaces, each of which may have different default storage parameters. If no storage specification is provided for the table, each partition uses the default storage parameters of its underlying tablespace. This enables you to size partitions independently. If the data files for each tablespace are on separate physical devices, this partitioning strategy can maximize transaction throughput and improve performance for queries based on data and region code. DML statements and SQL queries can reference the table as a logical unit, or they can operate against a specific partition. For example, the following query selects all rows from a specific partition of the region_sales_hist table:
SELECT * FROM region_sales_hist PARTITION (inv96r1);
Partitioning also can improve availability and simplify maintenance. In Listing 7.1, the 1997 data could be loaded using SQL*Loader without affecting the availability of 1996 data. Partitioning can provide increased availability, depending on how the data files are organized. Disk striping, in particular, can affect availability. Using Listing 7.1 as an example, suppose that each tablespace contains a single data file that is striped across two disks, using 12 disks total. In this scenario, the failure of a single disk would affect only one partition. On the other hand, if each data file is striped across all 12 disks, the failure of a single disk affects all partitions. This requires a design decision involving a common trade-off: performance versus availability.
Certain restrictions apply to table partitioning:
Index partitioning is related closely to table partitioning. Three distinct types of partitioned indexes are supported by Oracle8: local prefixed, local non-prefixed, and global prefixed.
Local Prefixed Partitions Local prefixed indexes are partitioned on the same columns as the underlying partitioned table, and the partition columns must be a left prefix of the index. Using Listing 7.1 as an example, the index defined here is a local prefixed index:
CREATE INDEX ix_region_sales_h ON region_sales_hist (invoice_date, region_id) ÂLOCAL;
Here, LOCAL is required to ensure that Oracle automatically generates the partitions based on the table definition and maintains index partitioning as the table is repartitioned. Local prefixed indexes provide the same performance, maintenance, and availability benefits as partitioned tables. The limitations inherent in partitioned tables also are present in local prefixed partitions.
Local Non-Prefixed Partitions Local non-prefixed partitions use the same partitioning columns and ranges as the underlying table, but the partition columns are not in the leftmost positions of the index. For example, the following index based on Listing 7.1 uses a local non-prefixed partition:
CREATE INDEX ix2_region_sales_h ON region_sales_hist (customer_id) LOCAL;
Local non-prefixed indexes are useful particularly in decision-support systems (DSS). In many cases, a date range is the best means of partitioning historical DSS data, but fast access is required for queries based on other columns. Local non-prefixed indexes can be slower to search than local prefixed indexes, however, because the query cannot always be isolated to a specific partition or partitions in advance.
All indexes defined as LOCAL are not maintained directly; instead, they are maintained through operations on the underlying table. For example, consider this statement:
ALTER TABLE region_sales_hist DROP PARTITION inv96r1;
This statement drops the partition from the table and corresponding partitions from all LOCAL partitioned indexes based on the table.
Global Prefixed Indexes Oracle also supports global prefixed indexes, which contain the partition range column(s) in the leftmost positions of the index but do not use the same partitioning range as the underlying table. Global prefixes can be used to spread index data over additional disks not used by table partitions to improve performance. The partitions of global indexes must be maintained manually, however.
In addition to partitioned indexes, Oracle8 offers several new types of indexes that you can use to improve performance for particular types of applications.
Reverse Key Indexes Reverse key indexes can be useful in high-volume OLTP applications. As the name implies, these indexes reverse the bits of each column value in the index, but column order is maintained. Reverse key indexes are useful in situations in which the natural order of the key would cause inserts and updates to be concentrated in a small number of leaf blocks.
Suppose that a sales application is used to generate a large number of invoices, and that it inserts sequentially numbered invoice data into a table where the invoice number column has a unique index to maintain integrity. Invoice numbers 100000 to 100999, for example, would be concentrated in a small number of index leaf blocks. Reversing the key causes insertions to be distributed across a wider range of leaf keys, making the index more selective. The REVERSE keyword is used to define a reverse key index, as shown here:
CREATE INDEX ix_invoice_num ON invoice_master (invoice_num) REVERSE;
NOTE: The disadvantage of using reverse key indexes is that you cannot perform a range scan against them, because the true key values no longer are adjacent.
Cooperative Indexes Oracle8 also provides a cooperative index, implemented as an index-only table. You can use index-only tables to build indexes for large or complex datatypes, such as video and audio clips. You access these tables as if they were regular tables--you can apply all DML and SQL operations to them. Index-only tables are stored as indexes, though, and you cannot build additional indexes on them. The CREATE TABLE syntax has been extended in Oracle8 to provide the mechanism for creating index-only tables. The following statement creates an index-only table that could be used to store MPEG video clips:
CREATE TABLE press_conferences (
date_recorded DATE,
topic VARCHAR2(20),
video_data BLOB,
CONSTRAINT pk_press_conf PRIMARY KEY (date_recorded, topic))
ORGANIZATION INDEX TABLESPACE ts_pc_index
PCTTHRESHOLD 40
OVERFLOW TABLESPACE ts_pc_video;
Note that the ORGANIZATION INDEX clause informs Oracle that this is an index-only table. The overflow area is used to prevent leaf nodes of the index from being used up with pieces of video data, and the PCTTHRESHOLD keyword indicates the maximum percentage of the block size a row can use before non-key column values are placed in the overflow area. In the preceding example, the video data always should be larger than one block, so the data for that column should always overflow. This table can be queried as the following:
SELECT video_data FROM press_conferences WHERE date_recorded = `16-JUN-97' AND Âtopic = `Oracle8 Release';
The B-tree structure of the index-only table can provide considerable performance gains if the table contains a large number of rows. The structure also eliminates the data redundancy that would result from the creation of a table as defined in the preceding code line, with a separate index for the primary key. These characteristics make index-only tables particularly well-suited for very large databases that store full documents or multimedia data.
Bitmap Indexes The bitmap index is an index type that was introduced in version 7.3. Unlike the standard B*-tree Oracle indexes, bitmap indexes do not store row IDs with corresponding key values. Instead, the index is a list of possible key values and a bitmap, with each bit corresponding to an actual row in the table. If the bit is set for a particular row, the row contains the key value. An internal mapping function converts the bitmap representation to actual row IDs as needed. Bitmap indexes are particularly efficient (in terms of space use and performance) for indexes that have a low degree of cardinality. You can create these indexes by using the BITMAP keyword, as shown here:
CREATE BITMAP INDEX ix_inv_region ON invoices(region_code);
TIP: Data warehousing applications will derive the most benefit from bitmap indexes, because they often query very large tables based on a column(s) with a relatively low number of distinct values, such as an indicator, or a Boolean value. These indexes are not particularly useful in OLTP applications, though, because they take longer to update and do not provide the same advantages for queries based on values with a high degree of cardinality. Obviously, there is no benefit in creating a bitmap index for a unique key.
Oracle8 provides new built-in datatypes, object datatypes, nested tables, and a number of other features that require new DDL extensions. In this section, the DDL extensions that have less to do with physical storage and more to do with representation are discussed.
Three new built-in types are introduced in Oracle8: VARRAY, REF, and large objects (LOBs). These new types provide powerful capabilities for dealing with objects and arrays of objects. The new LOBs accommodate improved multimedia storage. VARRAY VARRAYs are one example of an Oracle8 collection that correspond roughly to an array. Before a VARRAY type can be used in a DDL statement, it must be declared. The following statement declares a VARRAY type consisting of numbers:
CREATE TYPE invoice_details AS VARRAY(5) OF NUMBER (10);
After you define a type, you can use it in a DDL statement directly, as shown here:
CREATE TABLE invoice (
invoice_num NUMBER(10),
invoice_date DATE,
customer_id NUMBER(10),
total_amt NUMBER(10, 2),
status_code NUMBER(1),
details invoice_details);
In this example, the invoice_details type is used to store a collection of foreign keys to invoice detail records. This method eliminates the need for a purely associative entity to relate an invoice to its detail records.
The individual elements of a VARRAY cannot be referenced by index in DML or SQL statements. The following DML statement inserts a row into the invoice table:
INSERT INTO invoice (invoice_num, invoice_date, customer_id, total_amt, Âstatus_code, details)
VALUES (100292, '03-JUL-97', 23123, 33500, 1, invoice_details(2122939, 2122940,
null, null, null));
NOTE: Note that all values for details must be supplied. Indexes can be used to reference specific elements of VARRAYs in PL/SQL blocks.
Listing 7.2 creates a procedure that displays the detail keys for any invoice.
CREATE OR REPLACE PROCEDURE ShowDetails(InvoiceNum IN NUMBER) AS
BEGIN
DECLARE
test invoice_details;
i NUMBER;
BEGIN
i := 1;
SELECT details INTO test FROM invoice WHERE invoice_num = InvoiceNum;
WHILE (i <= test.count) LOOP
dbms_output.put_line(test(i));
i := i + 1;
END LOOP;
END;
END ShowDetails;
Note that indexes are 1-based and that each VARRAY has two properties: count and size. The count property returns the number of non-NULL items, and size returns the maximum number of items the VARRAY can contain.
VARRAYs are useful in processing small collections in PL/SQL code. Restrictions limit their usefulness, though. As mentioned previously, array indexes cannot be referenced in DML or SQL statements. The types of VARRAYs that can be defined also are limited. A VARRAY type cannot be based on a type that also contains a VARRAY or any other aggregate type, such as a PL/SQL table, for example. REF A second built-in datatype introduced in Oracle8 is the REF. A REF is, in essence, a pointer to a user-defined object type. Suppose an object of type customer_type and a table to store the type customers are defined as the following:
CREATE TYPE customer_type (
ID NUMBER(10),
LastName VARCHAR2(20),
FirstName VARCHAR2(20));
CREATE TABLE customers OF customer_type;
Object types are discussed in greater detail in the section titled "Object Types." For now, the object type was defined to demonstrate that a customer REF can be stored in a table, as shown here:
CREATE TABLE invoice (
invoice_num NUMBER(10) PRIMARY KEY,
invoice_date DATE,
custref REF customer_type,
total_amt NUMBER(10, 2),
status_code NUMBER(1),
details invoice_details);
A select statement against the invoice table can be issued to retrieve a customer REF, but to access the actual customer data, the DEREF operator is used to return a customer object. The simple procedure that follows demonstrates these concepts:
CREATE OR REPLACE PROCEDURE ShowCust(InvNum IN NUMBER) AS
BEGIN
DECLARE
cust customer_type;
BEGIN
SELECT DEREF(custref) INTO cust FROM invoice WHERE invoice_num = InvNum;
dbms_output.put_line(cust.ID);
dbms_output.put_line(cust.LastName);
dbms_output.put_line(cust.FirstName);
END;
END ShowCust;.
Using REFs can provide performance improvements in PL/SQL programs, particularly when the REFs reference large objects and are used as parameters to functions and procedures. LOBs Oracle8 introduces several new built-in types for dealing with large objects (LOBs), such as full documents, video images, digital audio, and so on. The four types of LOBs follow:
LOBs are not stored with other tables' data, and you can use a different tablespace and storage specification for each LOB column in a table definition. Oracle8, unlike its predecessors, enables you to define multiple LOB columns for a single table, as shown here:
CREATE TABLE emp_data (
empno NUMBER(10) PRIMARY KEY,
picture BLOB,
bio CLOB,
hr_file BFILE)
TABLESPACE ts_emp_data
LOB (picture) STORE AS (TABLESPACE ts_pics storage (initial 200MB next 200M) CHUNK Â16K),
LOB (bio) STORE AS (TABLESPACE ts_large_text storage (initial 2MB next 2M) CHUNK Â2K);
Note that BFILE data is not stored in Oracle data files, so no storage specification can be defined for this type. The CHUNK keyword is used to specify the amount of data to be read or written for piece-wise operations on the column.
The special EMPTY_BLOB(), EMPTY_CLOB(), and EMPTY_NCLOB() functions are provided to initialize internal LOB types to empty, because LOB columns cannot be written to if they contain NULL values. BFILE data is stored externally, so columns of this type can safely be initialized to NULL. The BFILENAME() function is used to initialize a BFILE column to point to a specific file in the external file system. The following inserts into the emp_data table illustrate the use of these functions:
INSERT INTO emp_data VALUES (1001, EMPTY_BLOB(), EMPTY_CLOB(), NULL);
INSERT INTO emp_data VALUES (1002, EMPTY_BLOB(), EMPTY_CLOB(),
BFILENAME(`F:\hrfiles', `1002.doc'));
Internal LOB types are accessed through locators and cannot be manipulated directly through SQL or DML. The DBMS_LOB package, which is discussed in the section titled "DBMS_LOB Package," contains routines for manipulating LOB columns.
Deferred constraint checking is new in Oracle8; it provides increased flexibility in managing referential integrity for complex transactions. Deferred constraint checking allows referential integrity to be broken by individual DML statements, assuming that the transaction as a whole will resolve any integrity problems caused by individual statements. This capability minimizes the importance of the order in which DML statements are executed for a particular transaction. Consider the emp and dept tables of the famous Scott schema--these come standard with Oracle as practice tables. The deptno column of emp has a foreign key constraint referencing deptno in the dept table. In previous versions of Oracle, the following transaction would fail on the very first statement:
INSERT INTO emp VALUES(9001, `SMITH', `MANAGER', 7839, '15-AUG-90', 4000, null, Â50); INSERT INTO dept VALUES(50, `IS', `PORTLAND');
In Oracle8, foreign key constraint checking can be deferred, allowing this transaction to complete successfully. Constraints are defined by using the keyword DEFERRABLE or NONDEFERRABLE (the default). Deferrable constraints are INITIALLY DEFERRED or INITIALLY IMMEDIATE, allowing the behavior of deferred constraints to be controlled at the session level. Constraints cannot be modified, so to redefine the behavior of the emp table foreign key constraint, the following commands are issued:
ALTER TABLE emp DROP CONSTRAINT fk_deptno;
ALTER TABLE emp ADD CONSTRAINT fk_deptno
FOREIGN KEY (deptno) REFERENCES dept(deptno) DEFERRABLE INITIALLY IMMEDIATE;
The sample transaction then could be applied using the following statements:
ALTER SESSION SET CONSTRAINTS=DEFERRED; INSERT INTO emp VALUES(9001, `SMITH', `MANAGER', 7839, '15-AUG-90', 4000, null, Â50); INSERT INTO dept VALUES(50, `IS', `PORTLAND');
Note that the ALTER SESSION statement in the example sets the mode for all deferrable constraints. Keep in mind that deferred constraint checking changes the timing of Oracle Call Interface (OCI) errors and PL/SQL exceptions. Extending this example, the following transaction does not report any errors until a commit is issued:
INSERT INTO emp VALUES(9002, `JONES', `P/A', 9001, '15-AUG-90', 4000, null, 60); INSERT INTO emp VALUES(9003, `DILBERT', `P/A', 9001, '15-AUG-90', 4000, null, 60);
Deferred constraint checking can simplify the development of complex transactions with numerous dependencies and can increase transaction throughput in some cases. The disadvantage of this approach is that it allows processing to continue until a commit is issued. This method can waste processing time for long transactions that fail because of problems in early statements and can make it difficult to isolate the offending statement(s).
Object types are the basis for the new object-relational features incorporated in Oracle8. An object type is an extension of the user-defined type, allowing methods to be encapsulated with data elements into a single logical unit. The definition of an object type serves as a template but does not allocate any storage. Objects are stored physically as rows or columns of a table. Consider the following simple type declaration:
CREATE TYPE Address_Type (
StreetAddr1 VARCHAR2(80),
StreetAddr2 VARCHAR2(80),
City VARCHAR2(30),
State CHAR(2),
Zip NUMBER(9));
This type can be used as a row object or a column object. It is used as a row object in the following statement, which creates a table of Address_Type objects:
CREATE TABLE Addresses OF Address_Type;
A unique identifier is assigned to each row object. This ID enables row objects to be accessed by reference using the REF operator. Column objects, on the other hand, are not assigned unique identifiers. They exist only in the context of the table in which they are stored and cannot be accessed by reference. The following DDL creates a table that uses Address_Type to store column objects:
CREATE TABLE Persons (
ID NUMBER(10) PRIMARY KEY,
LastName VARCHAR2(30),
FirstName VARCHAR2(20),
Address Address_Type);
In this context, the data for each address column object is stored in the Persons table. There is no unique identifier for an Address_Type object and no way to access an Address_Type object by reference.
Alternatively, an application could use the previous declaration of the Addresses table and define the Persons table as the following:
CREATE TABLE Persons (
ID NUMBER(10) PRIMARY KEY,
LastName VARCHAR2(30),
FirstName VARCHAR2(20),
Address REF Address_Type);
The only difference between this version of the Persons table and the previous version of the Persons table is that only references to Address_Type objects are stored. The actual data for the Address_Type objects is stored externally in the Addresses table. This method allows multiple rows of Persons to reference the same address. It also provides an implied integrity constraint, because only references to valid Address_Types (stored in Addresses or some other object table) can be inserted.
Object types may be nested. Instead of declaring a Persons table containing Address_Type objects, for example, a Person_Type object type containing an Address_Type object can be declared, as shown here:
CREATE TYPE Person_Type (
ID NUMBER(10) PRIMARY KEY,
LastName VARCHAR2(30) NOT NULL,
FirstName VARCHAR2(20),
Address Address_Type);
Note that the attributes of an object type can contain constraints as if they were table columns. If a table of row objects will be created, though, it is preferable to add constraints to the table in which the objects will be stored, as shown here:
CREATE TABLE Persons OF Person_Type (ID PRIMARY KEY, LastName NOT NULL);
Even though a primary key is defined for the Persons table, a unique ID will be generated for each object stored in the Persons table to be used by Oracle internally to resolve object references. The use of primary or foreign key constraints in object tables is not necessary if the entire design of the system is based on object types. Such constraints can be used to provide relational access paths to ease integration with existing structures migrated from previous versions of Oracle, however. Primary keys on object tables also can be useful in providing an object-comparison routine.
Object types may define methods in addition to attributes. Methods can be used to perform complex calculations, operate on nested objects, and so on. Object methods are defined in the type specification using the MEMBER keyword. A special type of member function used in object comparisons is designated using the MAP keyword. MAP functions must return a scalar datatype, and their use implies an order. Oracle can compare objects for equality by performing a field-by-field comparison, so MAP functions are not required to support this comparison. The following type declaration defines a MAP method and methods to operate on a nested object:
CREATE TYPE Person_Type (
ID NUMBER(10),
LastName VARCHAR2(30),
FirstName VARCHAR2(20),
Address REF Address_Type,
MAP MEMBER FUNCTION get_int_ID RETURN NUMBER,
MEMBER FUNCTION get_address RETURN Address_Type,
MEMBER PROCEDURE add_address(NewAddr IN Address_Type),
MEMBER PROCEDURE change_address(NewAddr IN Address_Type),
MEMBER PROCEDURE remove_address(OldAddr IN Address_Type));
Given this declaration, the MAP function might return the integer ID. Assuming that the ID is generated by an Oracle sequence, this would order the objects based on when they were created. Note that the declaration holds a nested REF to another object type. Additional member functions were provided to operate on the nested object, which provides a layer of abstraction between the developers using Person_Type and the physical location of the address data. These methods also eliminate the need to use the REF and DEREF operators in the development of client applications.
Similar to the Oracle package supported in previous versions, the type declaration is independent of its implementation. Type bodies, like package bodies, are implemented in PL/SQL and may include private variables and methods. For types that have no members, a body does not need to be implemented. The Person_Type, declared in the previous example, might be implemented as shown in Listing 7.3.
CREATE OR REPLACE TYPE BODY Person_Type (
MAP MEMBER FUNCTION get_int_ID RETURN NUMBER IS
BEGIN
RETURN SELF.ID;
END;
MEMBER FUNCTION get_address RETURN Address_Type IS
BEGIN
DECLARE
AddrOut Address_Type;
BEGIN
SELECT DEREF(Address) INTO AddrOut FROM Persons WHERE ID = SELF.ID;
RETURN AddrOut;
END;
END get_address;
MEMBER PROCEDURE add_address(NewAddr IN Address_Type) IS
BEGIN
INSERT INTO Addresses VALUES (NewAddr);
UPDATE Persons SET Address =
(SELECT REF(a) FROM Addresses a WHERE ID = NewAddr.ID)
WHERE ID = SELF.ID;
COMMIT;
END;
.
.
.
END;
This partial implementation demonstrates some of the new operators for working with Oracle8 object types. REF and DEREF, as mentioned previously, are used to create a reference to an object type and to dereference an object reference, respectively. The SELF keyword works similar to the C++ or Java this pointer; it references the current instance of the object. Note that in the add_address implementation, a row must be inserted into the Addresses table before a valid reference to it can be obtained. The following PL/SQL block demonstrates the use of this object type:
DECLARE
NewPerson Person_Type;
NewAddr Address_Type;
NewID NUMBER;
BEGIN
SELECT PersonIDs.NEXTVAL INTO NewID FROM dual;
NewPerson := Person_Type(NewID, `DOE', `JOHN', NULL);
INSERT INTO Persons VALUES (NewPerson);
SELECT AddressIDs.NEXTVAL INTO NewID FROM dual;
NewAddr := Address_Type(NewID, `1 S. Main', null, `ANYWHERE', `NY', 10203);
NewPerson.add_address(NewAddr);
END;
This PL/SQL block constructs a new Person_Type object with the Address attribute initially set to NULL and inserts it into the Persons table. It then constructs a new Address_Type object and passes it to the add_address method of the Person_Type instance. This PL/SQL block highlights the use of constructors, which are generated automatically for every object type. Constructors must be passed a value for each attribute of the object type, ordered as in the type declaration. NULL can be used to initialize any object attribute, including attributes that are objects themselves. The methods of an object are invoked in the same manner as packaged procedures, except that they are accessed through a particular instance of the object instead of through the object type itself. You can use the PL/SQL block shown in Listing 7.4 to display the data that was just inserted.
DECLARE
ThePerson Person_Type;
TheAddr Address_Type;
BEGIN
SELECT VALUE(p) INTO ThePerson FROM Persons p
WHERE ID = (SELECT MAX(ID) FROM Persons);
DBMS_OUTPUT.PUT_LINE(ThePerson.LastName||', `||ThePerson.FirstName);
TheAddr := ThePerson.get_address();
DBMS_OUTPUT.PUT_LINE(TheAddr.StreetAddr1);
DBMS_OUTPUT.PUT_LINE(TheAddr.City||', `||TheAddr.State||' `||TheAddr.Zip);
END;
Notice the use of the VALUE operator. As the name implies, it is used to access an object by value. This example also shows how an object's attributes are accessed. You also can use this notation for assignment, as in this code:
TheAddr.City := `NEW YORK';
Depending on the nature of the application, you might want to present a completely object-oriented interface. Using the example in Listing 7.4, you could have accomplished this by providing additional Person_Type methods to encapsulate DML operations. In addition to providing better encapsulation, this also provides an additional layer of abstraction between client applications and the physical structure of the database. Rights to the Persons table would be revoked, and an object would be created to allow client applications to retrieve lists of objects through simple SQL queries. Only select rights would be granted to users of this view. All changes to individual objects would be handled by methods of the object, to which developers and users would be granted the EXECUTE privilege. This design simplifies the client application and provides greater flexibility in the physical design of the database, because it hides the physical structure and the complexity of the SQL and DML statements required to access it. This approach provides object views to retrieve lists and individual objects as needed.
Oracle8 Object views are extensions of the standard views provided in previous versions of Oracle. In addition to column data, Object views expose object types, object REFs, VARRAYs, and nested tables. Similar to standard views, DML operations can be performed against Object views, provided that INSTEAD OF triggers are provided.
An Object view exposes at least one object and must contain an object identifier. If the view is based on an object table, the WITH OBJECT OID clause is not necessary, because a unique identifier is generated automatically for the objects in an object table. You can use the WITH OBJECT OID clause to create an Object view from a relational table or to specify a unique ID other than the generated key for object tables. Using the Persons table as an example, you could specify a view against this table as the following:
CREATE VIEW ov_persons (ThePerson, City, State) AS
SELECT VALUE(p),
City,
State
FROM Persons p, Addresses
WHERE Address = (SELECT REF(a) FROM Addresses a);
This view returns a Person_Type object and some basic address information using the nested Address_Type reference in Persons to join to the Addresses table. Note that no OID is specified, so DML statements against this view are not possible. The Person_Type object returned includes all columns of the Persons table, including the Address_Type reference. This reference is not particularly useful unless accessed in a PL/SQL block where it can be manipulated or dereferenced in a SELECT statement. You can use SELECT statements, such as the following statement, to access individual attributes of Person_Type in the first column position:
SELECT ThePerson.LastName||', `||ThePerson.FirstName "Full Name" FROM ov_persons WHERE ThePerson.LastName LIKE `S%';
The following PL/SQL block displays the full street address from the view:
DECLARE TheAddr Address_Type; BEGIN SELECT DEREF(ThePerson.Address) INTO TheAddr FROM ov_persons; DBMS_OUTPUT.PUT_LINE(TheAddr.StreetAddr1); DBMS_OUTPUT.PUT_LINE(TheAddr.StreetAddr2); DBMS_OUTPUT.PUT_LINE(TheAddr.City||', `||TheAddr.State||' `||TheAddr.Zip); END;
You can use a combination of user-defined types and Object views to build an object-oriented interface to access relational tables. Using the Person_Type and Address_Type example, suppose that the Addresses and Persons tables are not object tables but relational tables. Each row in the Persons table contains a foreign key (AddrID) to the Addresses table, and Person_Type has a nested Address_Type (instead of a nested REF Address_Type, as in previous examples).
You could use an Object view to expose Person_Type objects from the relational structures, as demonstrated here:
CREATE VIEW ov_person OF Person_Type
WITH OBJECT OID(ID) AS
SELECT p.ID ID,
LastName,
FirstName,
Address_Type(a.ID, StreetAddr1, StreetAddr2, City, State, Zip)
FROM Persons p,
Addresses a
WHERE p.AddrID = a.ID;
Note that the nested Address_Type must be constructed from the relational table within the view. The WITH OBJECT OID clause is required and must be based on a unique ID.
TIP: Relational tables without primary keys or unique indexes are not suitable for this technique. Using Object views and object types to "hide" relational structures can simplify the process of porting an existing Oracle relational database to an Oracle8 object-relational database.
Object views also can be updateable through the use of INSTEAD OF triggers. Consider the ov_person view defined in the previous example. Only the data stored in the Persons relational table can be modified directly through the view. However, you could use INSTEAD OF triggers to allow DML operations on the full view, including the nested Address_Type. You could use the trigger shown in Listing 7.5 to handle updates on this view.
CREATE TRIGGER tr_ov_person_upd
INSTEAD OF UPDATE ON ov_person
FOR EACH ROW
BEGIN
UPDATE Address SET StreetAddr1 = :new.Address.StreetAddr1,
StreetAddr2 = :new.Address.StreetAddr2,
City = :new.Address.City,
State = :new.Address.State,
Zip = :new.Address.Zip
WHERE ID = :old.Address.ID;
UPDATE Persons SET LastName = :new.LastName,
FirstName = :new.FirstName
WHERE ID = :old.ID;
END;
This trigger is invoked whenever an UPDATE statement is issued against the ov_person view. Unlike table triggers, INSTEAD OF triggers are executed instead of the DML statement. In most other respects, the syntax of INSTEAD OF triggers is similar to table triggers. Both use the correlation names :old and :new and may perform DML transactions on other tables and views, including object tables and views. Note that in Listing 7.5, it is assumed that the IDs of the relational tables will not be updated. The trigger is designed to accommodate DML UPDATEs such as this one:
UPDATE ov_person SET Address = Address_Type(1, '22 E. WASHINGTON', null, `CHICAGO', `IL', 60605) WHERE ID=1;
Additional INSTEAD OF triggers would be provided to handle INSERTs and DELETEs. The advantage of using Object views and INSTEAD OF triggers is their flexibility. Depending on their design and the design of the objects they expose, they can be used to present a more "flat" interface to underlying object tables or an object interface to underlying relational structures. In either case, Object views and INSTEAD OF triggers provide a layer of abstraction between applications and the physical structure of the database.
CAUTION: A potential disadvantage of using INSTEAD OF triggers (as opposed to using object methods) for transaction support is that they do not provide the same degree of encapsulation. It requires greater knowledge of Oracle8 object-relational SQL syntax to apply DML transactions to object views, and the triggers are maintained independently. An object that provides insert(), update(), and delete() methods can encapsulate all DML transactions in the type body, which provides a more object-oriented interface and can simplify maintenance.
Nested tables are an Oracle8 extension of PL/SQL tables supported in previous versions. A nested table can be created only from a named type. Earlier sections in this chapter presented nested objects and VARRAYs in the context of built-in and object types. This section focuses on the built-in TABLE type and issues related to nested tables. To illustrate the use of nested tables, the following named types are used in this section:
CREATE TYPE invoice_detail (
item_number NUMBER(10),
item_desc VARCHAR2(40),
quantity NUMBER(10),
price NUMBER(10, 2);
CREATE TYPE invoice_details AS TABLE OF invoice_detail;
Note that a TABLE type can be defined based on an object type, a VARRAY, or standard types. Based on these type declarations, an invoice_details table can be nested within another table, as shown here:
CREATE TABLE invoice (
invoice_num NUMBER(10) PRIMARY KEY,
invoice_date DATE,
customer_id NUMBER(10),
total_amt NUMBER(10, 2),
status_code NUMBER(1),
details invoice_details);
NOTE: The data for nested tables is not stored inline with the outer table. Instead, it is stored in a system-generated table that inherits the storage parameters of the outer table. This is an important consideration in designing storage parameters for tables containing nested tables.
This invoice table definition is almost identical to the invoice table containing a nested VARRAY defined previously. These tables differ in that a nested TABLE can contain other aggregate types. Another advantage of using nested table types is that they contain a number of built-in methods for operating on members of nested tables. For example, COUNT identifies the number of items in the nested table, EXISTS(i) tests for the existence of the ith element, FIRST and LAST can be used to access the first and last elements, and so on.
NOTE: Currently, the EXISTS, FIRST, and LAST functions are only available within PL/SQL code blocks. Host language interfaces are not supported.
Oracle8 defines a new operator, THE, for direct access to nested tables for SQL and DML operations. For example, the following INSERT adds a detail item to invoice 123:
INSERT INTO THE(SELECT details FROM invoice WHERE invoice_num = 123) VALUES (999, `ACME WIDGETS', 10, 49.99);
The following SELECT statement retrieves the same row from the nested table:
SELECT item_number, item_desc, quantity, price FROM THE(SELECT details FROM invoice WHERE invoice_num = 123) AS p WHERE p.item_number = 999;
An invoice_details TABLE type is returned by the following SELECT statement:
SELECT details FROM invoice WHERE invoice_num = 123;
Unlike other nested types, indexes can be created on nested tables. For example, you can create an index on item_number with this code:
CREATE INDEX ix_inv_item_num ON invoice.details(item_number);
A storage specification can be provided for indexes on nested tables.
Nested tables are ideal for inlining one-to-many relationships. Although the nested table data is not physically stored with other column data, the logical representation is clearer. Nested tables also can eliminate redundancy, particularly when multiple columns would be needed to maintain the relationship in standard relational tables. This lack of redundancy can improve performance when the internal Oracle set identifier is smaller than the key that would be required to join the two relational tables.
The primary disadvantage of using nested tables is the lack of flexibility in defining the storage tables for nested table data. Unless the size and transaction volume of the outer table are nearly identical to the size and transaction volume for the inner table, a compromise must be made in the storage specification. It is likely that this limitation will be resolved in minor versions of Oracle8, however.
Aside from the PL/SQL extensions required to support the new object-relational features, a number of features and packages are introduced in Oracle8. These features include a new and simplified interface for accessing external procedures, new packages for accessing the new advanced queuing capabilities of Oracle8, and a package for manipulating LOB data.
In previous versions of Oracle, external procedures could be accessed only by using the DBMS_PIPE package. Oracle8 greatly simplifies this process by providing a means of declaring an external procedure in any PL/SQL block and eliminating the dependency on DBMS_PIPE.
Currently, external procedures can be written only in C (or a language that supports the C calling convention). A second requirement is that external procedures must be implemented in a shared library specific to the operating system (a Windows DLL, for example). Before you can call an external procedure, you must create the library and register the external procedure with Oracle. The following statement defines an external library to Oracle:
CREATE LIBRARY calc AS `/mylib/calc.so' ;
You can register and call external procedures from anonymous PL/SQL blocks, stand-alone functions, object member functions, and so on. An external procedure can be called only from the program unit that registered it, however. The simple function shown in Listing 7.6 registers and wraps an external function.
CREATE FUNCTION calc_score ( val1 IN BINARY_INTEGER, val2 IN BINARY_INTEGER, val3 IN BINARY_INTEGER) RETURN REAL AS EXTERNAL LIBRARY calc NAME "calc_score" LANGUAGE C;
Datatypes specified as parameters or return values for external functions are mapped to OCI C types. Given the PL/SQL definition in Listing 7.6, the C prototype might look like this:
float calc_score(int val1, int val2, int val3);
The PL/SQL function created in Listing 7.6 can be called by other program units as if it were implemented entirely in PL/SQL. When invoked, Oracle spawns a new process, extproc, which handles interprocess communications between PL/SQL and the host language. extproc remains active for the rest of the session. This listener receives the library name and call information from PL/SQL and passes it to extproc. The session-specific extproc process loads the shared library, executes the external procedure, and returns any output parameter data, exception information, or return values to PL/SQL via the listener.
NOTE: At this time, you should note two important points.
The first point is that a dedicated listener process must be available to handle external function calls. This listener must be configured in tnsnames.ora and listener.ora. The protocol should be specified as IPC, and in listener.ora, the PROGRAM_NAME must be extproc.
The second point is related to the actual implementation of the shared library. This is probably an obvious point, but any functions or procedures that will be accessed by Oracle must be thread-safe. Access to shared resources, such as static data or files, must be synchronized properly.
External procedures are a powerful tool; they provide the means to extend the capabilities of PL/SQL. External procedures also can provide improved performance for complex calculations or other resource-intensive tasks. Because they can be called from any PL/SQL program unit (including triggers), implement callbacks, and perform any tasks allowable under the operating system, the possible uses of external procedures are almost unlimited. Users familiar with the DBMS_PIPE package will recognize that these capabilities are not entirely new, but the Oracle8 implementation is vastly improved.
A second powerful new feature of Oracle8 that is accessed through PL/SQL packages is advanced queuing (or Oracle/AQ). This is more than an extension of the Oracle job queue. Oracle/AQ furnishes the messaging, scheduling, and management capabilities that typically are reserved for proprietary messaging middleware and transaction-processing monitors.
Oracle/AQ messages are stored in tables and provide built-in persistence and recovery capabilities. These messages are implemented as object types. Custom application-specific queues are created to handle message- and object-specific data. Messages are added to queues by using the DBMS_AQ.ENQUEUE procedure. The parameters of this procedure enable users to specify the following:
A single output parameter supplies the caller with a globally unique message identifier.
Messages are removed from a queue by using the DBMS_AQ.DEQUEUE procedure. This procedure retrieves the message information supplied by the sender's call to DBMS_AQ.ENQUEUE. If no custom ordering was specified, first in, first out (FIFO) ordering is assumed. Input parameters are supplied to specify the following:
DBMS_AQ.DEQUEUE can specify that the message should be destroyed after it is read, that it should be left in the queue, or that it should be locked and left in the queue. The sender of a message can attempt to cancel it by dequeuing it or by sending an application-defined cancel message.
A number of factors determine the order in which messages are read from the queue, including message priority and correlation information. Note that the application calling DBMS_AQ.DEQUEUE must supply the same object type to retrieve message parameters as the caller supplied. The process of queuing and dequeuing can be used to perform complex transactions. The queuing application can specify that a message is part of an ongoing transaction by suppyling the transactional parameter and setting it to true. It is up to the dequeuing application to determine the proper ordering of messages that participate in a single transaction. The corr_id and user_data parameters, which are used to supply application-specific data as a VARCHAR2 or an object, respectively, can be used to provide ordering information for transactions that involve multiple messages. A more general (but also more complex and error-prone) approach to ordering messages within a transaction could be devised using the priority, delay, seq_deviation, and relative_msgid parameters.
The DBMS_AQADM package contains the functions and procedures for managing queues. This package enables administrators to perform the following tasks:
A number of new System views are provided to monitor queue activities. These views provide information on the contents of the queue tables, queue parameters, and the ordering attributes of queues. The database administrator (DBA) versions of these views also provide timing information, user IDs of senders and receivers, and so on.
TIP: Because messages and replies are object types, they can contain almost anything. A message could start a batch process or request a complex query. The reply could contain a simple acknowledgment or a collection type (making up a result set). Oracle/AQ is completely flexible in this regard.
The capability to store multiple LOB columns in a single table is new in Oracle8; it provides improved performance and storage flexibility. The new LOB types and features are useful particularly for multimedia applications that integrate large amounts text, audio, and video.
The Oracle8 DBMS_LOB package provides the methods for manipulating LOB types. As mentioned previously, Oracle8 defines four LOB_TYPES: BLOB, CLOB, NCLOB, and BFILE. Of these types, only BFILEs are stored outside the database, so a different set of DBMS_LOB routines applies to them. All DBMS_LOB procedures operate on locators. This means that internal LOB objects must exist in the database before you can use the DBMS_LOB package to manipulate them.
The DBMS_LOB routines can be grouped into three categories. Table 7.1 lists the routines in each category and provides brief descriptions. Note that all functions and procedures that operate on two LOBs require that they be of the same type.
| Routine | Function |
|
Routines That Apply to All LOBs |
|
| COMPARE | Compares specified sections of two LOBs |
| GETLENGTH | Gets the length of the LOB in bytes (for BLOBs) or characters |
| INSTR | Searches for the source pattern of bytes (BLOBs) or characters in a specified section of the target LOB |
| READ | Reads a specified section of a LOB into a supplied VARCHAR2 (CLOB, NCLOB) or RAW buffer (BLOB, BFILE) |
| SUBSTR | Returns the data instead of populating an OUT parameter buffer (essentially, a function version of the READ procedure) |
|
Routines That Apply to Internal LOBs |
|
|
APPEND |
Appends a source LOB to the end of the destination LOB |
|
COPY |
Copies a specified section of the source LOB to a specified section of the destination LOB (overwriting the section in the destination) |
|
ERASE |
Erases a specified section of a LOB, zero-byte (BLOB), or space (CLOB, NCLOB) filling, if the section erased is in the middle |
|
TRIM |
Truncates LOB data to a specified number of bytes |
|
WRITE |
Writes data to a LOB column from an input buffer of RAW (BLOB) or VARCHAR2 data |
|
Routines That Apply to BFILEs Only |
|
|
FILECLOSE |
Closes an open BFILE |
|
FILECLOSEALL |
Closes all open BFILEs for the current session |
|
FILEEXISTS |
Checks the file system for the existence of a BFILE |
|
FILEGETNAME |
Determines the path and filename of a BFILE, given a locator |
|
FILEISOPEN |
Checks to see whether a given BFILE already is open |
|
FILEOPEN |
Opens a BFILE (read-only) |
NOTE: It is important to understand the snapshot processes involved in LOB selection and LOB updates. These are the primary rules that apply to LOB locators:
- A selected LOB locator's snapshot will not be refreshed if the LOB is updated by another session.
- A LOB locator is refreshed only when updated through the DBMS_LOB package or corresponding OCI routines (locators are not refreshed on DML updates).
- Deleting a BFILE only deletes the database reference to it. (The file is not deleted from the external file system.)
- The net result of these rules is that applications always should lock the row containing the LOB before accessing a locator and should use only the DBMS_LOB package to update the LOB. This ensures that the locator is current at all times.
In addition to incremental improvements, the Oracle8 cost-based optimizer contains many enhancements to support the new physical storage options and object-relational features. This section focuses on three of the most significant improvements in the cost-based optimizer, including improvements to hash joins, histograms, and star query processing.
Hash joins improve the performance of equijoins, particularly when used with the Parallel Query option. A hash join starts by performing full table scans of the two tables being joined, splitting them into as many partitions as possible (based on available memory). Partition by partition, the hash join generates a hash key for the rows of the smaller table and searches for matching hash keys in the corresponding partition of the larger table. With the Parallel Query option, separate query processes can be used to process separate partition pairs simultaneously. Three initialization parameters are used to control the behavior of hash joins:
TIP: You can use ALTER SESSION to override these settings for a particular session.
Oracle8 allows the creation of histograms for a particular column to provide additional selectivity information. Histograms are used when the data for a column that is used frequently in a WHERE clause is not distributed evenly. University exam scores may range from 0 to 100, for example, but are likely to have a large concentration in a particular range, such as 70 to 90. The histogram represents the distribution of values in terms of evenly sized buckets. Consider the following example:
ANALYZE TABLE exam_scores COMPUTE STATISTICS FOR COLUMNS score SIZE 5;
The SIZE parameter indicates the number of buckets into which the values are divided. If there were 10,000 rows in the table, each bucket would represent 2,000 rows. A larger number of buckets provides a more detailed histogram. Increasing SIZE to 100 would result in 100 rows per bucket. You may need to experiment to find the best size for a particular column. Histograms are not useful for columns that do not appear in WHERE clauses or that are distributed evenly. Even distribution is assumed when a histogram is not present.
The new Oracle8 star-transformation capabilities will be of great interest to designers and developers of data warehousing applications. Bitmap indexes, as discussed earlier, can provide dramatic performance increases, particularly for indexes containing a small number of distinct column values. Using star transformation, the Oracle8 optimizer capitalizes on the bitmap index representation. Suppose that the query in Listing 7.7 is executed against a very large fact table and three of its dimensions.
SELECT sum(fact_t.measure) FROM fact_t, dim_t1, dim_t2, dim_t3 WHERE fact_t.fk1 = dim_t1.code AND fact_t.fk2 = dim_t2.code AND fact_t.fk3 = Âdim_t3.code AND dim_t1.description BETWEEN `01-JAN-96' AND `31-MAR-96' AND dim_t2.description = `NORTHEAST REGION' AND dim_t3.description IN (`CONSUMER', `SMALL BUSINESS');
Assuming that there are bitmap indexes on each of the fact table columns used in the join, star transformation first performs subqueries on the dimension tables to retrieve the code values associated with the descriptions specified in the WHERE clause, retrieving the portions of the bitmap indexes corresponding to the specific code values returned. These three bitmap images then are merged and ANDed, which results in a bitmap image that corresponds to the exact rows in the fact table that meet all three conditions. These rows are retrieved, and the value of measure is summed to produce the result.
NOTE: Note that in Listing 7.7, the join never actually occurred. Standard star query optimizations may have produced the Cartesian product of the rows selected from dimensions and joined this result to the fact table. This join approach will almost always be considerably slower than bitmap indexes, consuming more memory in the process.
You enable star transformation for an instance by setting the STAR_TRANSFORMATION_ENABLED initialization parameter to true. Keep in mind that bitmap indexes must be available to perform the transformation. This approach is particularly well-optimized for sparse fact tables that can be constrained optionally by a large number of dimensions. For dense fact tables with a few required dimensional constraints, the more traditional approach using an aggregate index and standard star-optimization techniques may be more appropriate.
Oracle8 introduces a number of administration and security enhancements, many of which apply to specific server options or object-oriented features. This section focuses on the major improvements related to the core server, including server-managed recovery, improved password management, and data dictionary protection.
Server-managed recovery is implemented as a command-line utility similar to SQL*DBA that accesses the new backup and recovery PL/SQL package. The new Oracle8 Recovery Manager, which can be used only with Oracle8 servers, enables you to perform the following tasks:
Although recovery catalogs are not required, there are several advantages to using them. Recovery catalogs, which are maintained in the database rather than in the file system, contain additional information that is not in the control files, including information to support point-in-time recovery and stored command scripts. More important, you can use a catalog to recover a database even if the control files have been damaged or are missing. Obviously, to take advantage of this feature, you should store recovery catalogs in an external database on another machine. You can register and refresh the recovery catalog destination database through the Recovery Manager interface.
The Recovery Manager supports full and incremental backups using backup sets, as well as image copying of specific files or groups of files. You can schedule individual backup operations or the execution of stored backup sets through the Recovery Manager. All activity is logged, and you can generate a report to view the results of operations in a readable format.
NOTE: A full discussion of the Recovery Manager's capabilities is far beyond the scope of this chapter. You can use literally hundreds of commands to configure and perform backup, restore, and recovery operations. In addition to the added capabilities, the real benefit of this new utility is that it is simply the administrative interface. The actual tasks are performed by separate server-based processes, which can be run in parallel, completely unattended.
The Oracle profile has been enhanced in Version 8 to support better security and password management. You now can use the profile to perform these tasks:
The following example demonstrates the use of the parameters that enforce these restrictions:
CREATE PROFILE remote_user
LIMIT FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFETIME DEFAULT
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME 365
ACCOUNT_LOCK_TIME 30
PASSWORD_GRACE_TIME 30
PASSWORD_VERIFY_FUNCTION DEFAULT;
Note that Oracle supplies defaults for these values, including a verification function.
Data dictionary protection is another Oracle8 security enhancement. The ANY system privileges (SELECT ANY TABLE, for example) no longer include the SYS schema. Also, the SYS user ID now is functionally equivalent to internal. Users must belong to the DBA operating system group to connect to SYS, and the password provided must match the password for SYS in the password file. These new restrictions are designed to prevent unauthorized access from the network.
The Oracle Call Interface is at the core of all Oracle development tools, and the Oracle8 version of the OCI is almost entirely new. Backward compatibility is maintained for many of the Version 7 functions, but applications written to the Oracle7 OCI will be unable to take advantage of the new features of Oracle8.
Version 8 of the OCI is a much larger and more flexible library than Version 7. The changes and enhancements can be classified into the following categories:
As mentioned previously, backward compatibility is provided for the vast majority of relational functions. However, new versions of these functions are supplied in Version 8. Consider the following Version 7 function prototypes to parse and execute a SQL statement or PL/SQL block:
sword oparse(struct cda_def *cursor, text *sqlstm, sb4 sqllen, sword defflg, ub4 Âlngflg); sword oexec(struct cda_def *cursor);
Version 8 of the OCI provides these replacements:
sword OCIStmtPrepare(OCIStmt *stmtp, OCIError *errhp, CONST text *stmt, ub4 Âstmt_len, ub4 language, ub4 mode); sword OCIStmtExecute (OCISvcCtx *svchp, OCIStmt *stmtp, OCIError *errhp, ub4 iters, Âub4 rowoff, CONST OCISnapshot snap_in, OCISnapshot *snap_out, ub4 mode);
These prototypes are provided only to illustrate the point that OCI8 is a completely new API rather than an extension of OCI7. It is highly recommended that OCI7 applications be fully migrated to the new API unless Oracle7 databases must be supported.
To access the object-relational features of Oracle8 from OCI programs, C language object representations are required. Fortunately, you can use a new utility called the Oracle Type Translator (OTT) to generate C structure declarations directly from Oracle -named datatypes. Suppose that the following type, defined in the Scott schema, must be accessed from an OCI program:
CREATE TYPE department (
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
The C structure to represent this type could be generated by using OTT from the command line, as shown in this example:
ott scott/tiger intype=objin.typ outtype=objout.typ code=c hfile=dept.h
The intype, outtype, and hfile parameters specify the input parameter file, the output log file, and the target file, respectively. The code parameter specifies the target language. In its simplest form, the intype file is a text file containing the names of the types to be translated. You also can use intype to specify the case and name of the target structure.
Suppose that the contents of objin.typ follow:
CASE=lower TRANSLATE department AS dept_struct
Here, the outtype file is a log that will contain the resulting C structure, version information, and target header file. The hfile is the target C source file. The simple department type is translated by OTT to the following C structure:
struct dept_struct
{
OCINumber deptno;
OCIString *dname;
OCIString *loc;
}
OTT also generates a corresponding structure to hold NULL indicators for the dept_struct, as shown in this code:
struct dept_struct_ind
{
OCIInd _atomic
OCIInd deptno;
OCIInd *dname;
OCIInd *loc;
}
Obviously, it probably would simpler to perform the mapping of this single object by hand. Many objects can be generated from a single input file, however. OTT saves a considerable amount of time and eliminates errors when mapping numerous complex objects.
This chapter demonstrated that, although the basic architecture of the server is not new, Oracle8 is much more than a new version of the relational database. The brief overviews of the physical storage improvements, SQL language extensions, management and security enhancements, and core development tool changes gave you the big picture.
In addition to the powerful new object-relational features, numerous other improvements to the core server and server options make the Oracle server more scalable, reliable, extensible, and robust than ever, positioning Oracle8 as the universal data server that will meet the distributed computing needs of the future.
© Copyright, Macmillan Publishing. All rights reserved.