0172-0174

Previous Table of Contents Next

Page 172

New Features in Oracle7 Server 7.3

Oracle7 Server 7.3 provides several new features to improve the database performance and functionality. This section discusses the following major features that are new in Oracle7 Server 7.3: standby database, bitmap index, hash joins, and Partition views.

Standby Database

The Standby Database feature enables users to maintain a duplicate copy of a database at a remote site. A standby database runs on a standby system with duplicate hardware as the primary system. It is kept in Recovery mode by applying the archived log files from the primary database. This feature expedites the disaster recovery. In the event of a primary database failure, users quickly can switch from the primary database to the standby database with minimum recovery. For information on creating, maintaining, and activating a standby database, refer to your Oracle7 Server Administrator's Guide.

Bitmap Index

The bitmap index provides performance improvement for data warehouse applications with ad hoc queries and a low number of concurrent transactions updating the data. A bitmap index is most useful for tables with low cardinality columns .

NOTE

Low cardinality columns are columns that have a relatively small number of distinct values compared to the number of rows in the table. For example, a gender column with a male or female value is a candidate for a bitmap index.

Before you can create a bitmap index, you must set the COMPATIBLE parameter to 7.3.2.0 or higher in your initialization parameter file. To verify that the COMPATIBLE parameter is set to 7.3.2.0 or higher, you can issue the following command at the Server Manager prompt:

 CONNECT INTERNAL SHOW PARAMETER COMPATIBLE 

To create a bitmap index, you can use the BITMAP keyword in the CREATE INDEX command. A sample CREATE BITMAP INDEX command follows :

 CREATE BITMAP INDEX JOBMAP ON EMP (JOB); 

The NOSORT parameter is not applicable when creating a bitmap index. Also, the ANALYZE INDEX VALIDATE STRUCTURE command is not supported for bitmap indexes.

Page 173

Hash Joins

The hash-join algorithm can produce better performance for complex queries than sort -merge join algorithms and nested- loops join algorithms. The hash-join algorithm is considered only by the cost-based optimizer, not by the rule-based optimizer. Thus, if you set the OPTIMIZER_MODE parameter to RULE in the initialization parameter file, the hash-join algorithm is not used. The default value for the OPTIMIZER_MODE parameter is CHOOSE, which means that if there are statistics in the data dictionary for at least one table accessed in a SQL statement, Oracle uses the cost-based optimizer. To enable a hash join, the HASH_JOIN_ENABLED initialization parameter must be set to TRUE, which is the default value.

TIP

You might receive the following error message:

 ORA-6580 Hash Join ran out of memory while keeping large rows in memory 

If you do see this message, you may need to increase the HASH_MULTIBLOCK_IO_COUNT and the HASH_AREA_SIZE initialization parameters. Hash join reserves three slots for a row (each slot size = DB_BLOCK_SIZE * HASH_JOIN_MULTIBLOCK_IO_COUNT). By increasing HASH_JOIN_MULTIBLOCK_IO_COUNT, the slots' sizes are increased for a row.

TIP

When the cost-based optimizer uses the hash-join method for your query and the join key column type is CHAR, it might return the wrong result. The same problem might happen on certain SQL statements that use UNION ALL or UNION. This problem is fixed in Oracle7 Server Patch Set 7.3.3.1. A workaround is to disable hash the join by setting the parameter HASH_JOIN_ENABLED to FALSE in the initialization parameter file.

Partition Views

The Partition View feature enables users to divide a large table into multiple smaller partitions. Users and applications can access the Partition views as a single object by using the UNION ALL option in a query. This new feature provides performance, administration, and availability improvements. You can assign key ranges by using CHECK constraints on the tables to the Partition views. When you use a key range in your query to select from a Partition view, your query accesses only the partitions within the key range.

Partition views are useful in data warehouse environments because users need to store and access large amount of data. You can partition a large sales table by quarter for each fiscal year,

Page 174

for example, such as S1_Y96, S2_Y96, , S3_Y97, and S4_Y97. In this case, if your query only involves the data in S1_Y96 and S2_Y96, the database needs to access only those two partitions, and it does not access the rest of the partitions. For more information about the Partition views, refer to the Oracle7 Server Concepts Manual, Release 7.3.

TIP

When you issue a query with a complex predicate, such as WHERE...IN, the optimizer might access all the partitions when a query is issued against the Partitioned view. If the WHERE clause of a SELECT statement provides only one matching criterion, the optimizer accesses only the appropriate partitions. The following query, for example, causes the optimizer to access all partitions regardless of the key ranges assigned to each partition:

 SELECT * FROM sales WHERE sales_date IN ('15-JAN-96','15-FEB-96'); 

Here, sales is the name of the Partition view and sales_date is a column.
To produce the expected result, you need to use certain workarounds by rewriting your query with a simple predicate, such as EQUAL (=) or BETWEEN against literals. Also, you need to set PARTITION_VIEW_ENABLED=TRUE in your parameter file (initSID.ora).

How Oracle7 Server Differs from Other Oracle
Packages

Oracle7 Server is designed for developers and organizations that require distributed database systems to develop and deploy client/server applications in an enterprise-wide environment. This section highlights the differences between Oracle7 Server and other Oracle packages, including Personal Oracle7 and Oracle7 Workgroup Server.

The primary difference between Oracle7 Server and Personal Oracle7 is that Oracle7 Server is a multiuser database system, whereas Personal Oracle7 is a single- user /developer database system. Although Oracle7 Server and Oracle7 Workgroup Server share many similarities, Oracle7 Server is targeted toward users who require real-time data access and the advanced replication functionality across distributed databases in an enterprise environment, whereas Oracle7 Workgroup Server is geared toward users who need data and read-only snapshots to reside on one or multiple servers in a workgroup or a small-to-medium sized business environment.

Operating System Integration Support

Similar to Oracle7 Workgroup Server, Oracle7 Server is integrated tightly with the server operating system. Oracle7 Server can function beyond a workgroup or local area network (LAN) environment, however, because it supports the enterprise network environment. Table 8.2 shows three types of operating system integration support.

Previous Table of Contents Next