Rajesh Srivastava on

Oracle 8

New Features


(This is from a seminar organized by Oracle Education Services in Mumbai. Anand Adkohli of Oracle Server technologies addressed the seminar.) He mentioned that most of the features of Oracle8 have been released in Oracle 7.3 but are much publicized in Oracle8. Among other things included in this article is the new ROWID format, table and index partitioning and object support offered by Oracle8.

Partitioning


Table and Index partitioning is Oracle8's solution to the demands of very large databases (VLDBS).
VLDBS require
  • Tens to hundreds of terabytes of data: Typically they owe their size to a few large tables rather than many objects.
  • Large user communities
  • Scalable parallel performance


Oracle8 can cater to databases in the range of petabytes (10-15). In VLDB environments, the tables and indexes become increasingly vulnerable to failures. Partitioning is one such solution provided by Oracle8.

Tables and indexes can be decomposed into smaller units called partitions. The table is partitioned on the basis of a range of key values. The key can be anything that logically partitions the table.

Table Partitions

Example:
Table SALES is partitioned by week numbers of a year divided into 12 partitions, each holding information of 4 weeks of sales. It is expected that there will be more sales in December and January. So the partitions 1 and 12 will require more storage than the others. So these two partitions can be further divided into smaller partitions based on 1 or 2 weeks.
CREATE TABLE sales ( acct_no NUMBER(5) NOT NULL),
person VARCHAR2(30),
sales_amount NUMBER(8) NOT NULL,
week_no NUMBER(2) NOT NULL)
PARTITION BY RANGE(week_no)
( PARTITION P1 VALUES LESS THAN(4),-- 1st 4 weeks
TABLESPACE data0,
PARTITION P2 VALUES LESS THAN(8)
TABLESPACE data1,.......
PARTITION P13 VALUES LESS THAN(53)
TABLESPACE data12);
  • The partition key - Defines the columns and range to be used. The key can be a composite of up to 16 columns. You cannot have gaps in the ranges, for example 1-3, 5-6.
  • VALUES LESS THAN CLAUSE - Defines the non-inclusive upper bound. This must be specified as a literal or as MAXVALUE. NULL cannot be specified as a value. However Null values can be inserted for the partitioned key value. NULL sorts greater than all other values except MAXVALUE.
  • Physical Attributes - They can set as per partition. So one can no longer say that a table cannot span across tablespaces. You can create partitions to reside in different tablespaces or to contain varying physical storage parameter values like PCTFREE, PCTUSED.
  • Logical attributes: The partition must contain the same columns, datatypes and constraints.
  • A partition can be moved from one tablespace to another, dropped, added or truncated. DML operations can be applied on a partition level instead of on a table level.
  • The partition key column cannot be updated if updating causes rows to be moved from one partition to another.
  • A full table scan would imply reading through all the partitions.
  • Exchange Partition
    Tables can be split into partitions and partitions can be integrated into 1 single table.

Index Partitions

Indexes can either be partitioned or non-partitioned. A non-partitioned index is a standard Oracle7 index. Partitioned indexes are built as separate B-tree structures. One index partition is created for each table partition. So only one index partition is affected by any operation on the underlying table partition.
  • You can point multiple table partitions to multiple indexes. Table partition P1 and P2 collectively point to indexes I1 and I2.
  • By using a GLOBAL keyword in the CREATE INDEX statement you can point many table partitions to a single index partition.
  • A local index would mean one table partition to one index partition.
  • Anytime the partition is modified the index is destroyed so we can rebuild the index using the ALTER INDEX REBUILD command.
  • Indexes (partitions or nonpartitioned) can be marked as index unusable(IU). You can do this in the event of importing a partition, partition maintenance operations like alter table to move truncate or split a partition.The index can be made usable only by rebuilding or the drop and re-create commands.

Operations Supported for Partitioning:

ALTER TABLE
ALTER INDEX
Drop partition Drop partition
Add partition Rename partition
Rename partition Rebuild partition
Modify partition Modify partition
Truncate partition Split partition
Split partition Parallel partition
Move partition Unusable partition
Exchange partition
It is interesting to note that due to the partitioning concept being introduced, around 206 new data dictionary views have been appended!!! Among them are user_tab_partitions, user_ind_partitions etc.

Benefits of Partitioning

  • The large tables and indexes which were utterly unmanageble can now be judiciously partitioned into smaller and manageable pieces.
  • Partitioning improves data availablity, one partition fails but still the other partitions can be accessed as normal. Administrators can perform piecewise maintenance operations on the system without bringing the whole database down.
  • It speeds up time for searching.
  • It improves performance.The optimizer eliminates partitions that do not need to be scanned. Partitions can be scanned in parallel. DBA's can load balance partitions across physical devices.

Note: There is downside to this as space management becomes a vital activity due to the flexibility offered and therefore can be disastrous if not implemented with care and planning.

Networking

Net8
Net8 is a glorified name for SQL*Net3.0. It supports client-server, server-server and N-tier connectivity. It provides 3 key areas of focus:
Scalability
New features enable thousands of concurrent users to access a single server, as required by a OLTP (Online Transaction Processing), Data Warehousing and messaging applications. These features are
  • Connection Pooling - It is implemented for Net8 clients and dispatchers for Multi-threaded Server(MTS) configurations. Idle network sessions time out releasing the transport connections used by that session for use by other network sessions.
  • Multiplexing - Multiple network sessions simultaneously share a single transport connection.
  • Link concentration - The connection manager enables a large client population to connect to a common server. Multiplexing is funneling many TNS sessions across one transport connection, while the Connection Manager funnels many clients which may have many sessions per client using only one physical connection. This results in the server using it's resources on processing requests from clients rather than opening and closing network connections.

Network Manageability

  • Configuration-Free Installation Option - In workgroup environments Net8 enables users in a TCP/IP environment to resolve Oracle service names using their existing name-resolution services thereby avoiding the trouble of configuring service names to connect to the database.
  • Centralized Client Administration - The administrator profiles all the clients through the Oracle Names server which is nothing but a server storing all the SQL*Net aliases. So on installation the appropriate client profile data is loaded from the Oracle Names server.

Security

Net8 and Oracle8 support the Oracle Security Server. This server facilitates security and ease of administration in distributed environments. The features of this server are:

Password Management

Below is the command syntax to create profile in Oracle 8 :

CREATE PROFILE grace_5 LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFETIME 30>br> PASSWORD_REUSE_TIME 30
PASSWORD_VERIFICATION_FUNCTION my_passfunc
PASSWORD_GRACE_TIME 5;

CREATE USER rajs
IDENTIFIED BY 4heh3ii
...
PASSWORD EXPIRE
PROFILE grace_5
As you can see above the password management has been greatly improved in Oracle8.

New features in password management are:
  • Account Locking - This allows the account to be locked when a user fails to log in to the system in specified number of attempts. The security officer can also lock an account manually.
  • Password Ageing and Expiration - You can establish a lifetime for the password after which it must be changed in order to access the account.
  • Password History - You can check a new password to ensure that it is not reused for a specified amount of time or specified number of password changes.
  • Verification of Password Complexity - This checks the password to verify that it is complex enough for intruders to guess passwords.

Additional New Features in Oracle8

Introduction of index-only tables
An index-only table keeps all data within a B-tree. There are no separate data blocks, only indexes blocks. Instead of the ROWID of the index key, the column values of the table are stored. This greatly reduces storage.

Deferred Constraints
Before Oracle8 the constraints on tables were always enforced with immediate effect after the DML (Data Manipulation Language) is issued except for discrete transactions. But Oracle8 conforming to SQL92 enables you to defer the enforcement of the constraints until you COMMIT your transaction.

Example:
ALTER SESSION SET CONSTRAINTS=DEFERRED;
UPDATE emp SET deptno=27
WHERE deptno=20;
UPDATE dept SET deptno=27
WHERE deptno=20;
COMMIT;

Here the foreign key is updated prior to the key being changed. Enhanced National Language Support Oracle uses the national character set for data stored in columns like:
  • NCHAR Fixed length character data of the length size of characters, or bytes, depending on the choice of national character set
  • NVARCHAR2 Variable length character string having the maximum length size of characters or bytes depending on national character set
  • NCLOB Large object containg fixed-width multibyte characters.

Parallel DML

It is used for DML operations against large databases such as DSS (Decision Support Systems) and data warehouses. You can improve performance dramatically without multiple sessions. Use the PARALLEL clause in the CREATE TABLE statement to specify the degree of parallelism of DML statements as well as queries. Enforced Constraints.
Constraints can be:
  • Enabled - It is operational.
  • Disabled - New rows may be invalid.
  • Enforced - Subsequent inserts are checked.

Example:
A load operation is to be performed on a data warehouse. The currently enforced constraint is disabled. Then the new rows are loaded. Some of the rows maybe invalid against the disabled constraint, but no violation is observed. When the load is complete, you can set the constraint to enforced now, which allows the existing invalid data to exist but checks subsequent additions for validity. They are useful in bulk operations.

Size limits
Size Type
Oracle 7
Oracle 8
Database 2-32 terabytes 512 petabytes
Data files per database 1000-4000 64-256 million
Columns per table 254 1000
Extents per table Unlimited (7.3) Unlimited
LOB Columns per table 1 1000
CHAR Column 254 bytes 2000 bytes
VARCHAR2 2000 bytes 4000 bytes

New ROWID format :


The Oracle7 ROWID format is:
BBBBBBBB.		RRRR.			FFFF

Block			Row 			File
No.			No.			No.

Storage is 6 bytes.


The Oracle8 ROWID format is:

OOOOOO		        FFF	                BBBBBB          SSS


Data Object		Relative		Block No.	Slot No. 
No.			File No.		in File		in Block

Storage is 10 bytes


Benefits of the New ROWID format

  • Supports partitions, objects and increased database limits. Provides efficient access to partitions. Before Oracle8, ROWID identified the file, block and row number.
  • In Oracle8, a datafile has two associated numbers:
    • an absolute number that is unique within the database
    • a relative number that is unique within a tablespace
ROWID includes the relative file number as an unique identification of the segment itself. It includes the data object number which identifies a table or partition. So Oracle8 knows the data object number then it knows the tablespace that contains the object. This ROWID has been changed to include the fact that a table can exist in more than one tablespace.
  • Returns the same ROWID length value
  • Allows more files per databases
  • Indexes do not have to be rebuilt while migrating from Oracle7 to Oracle8. You do not have to recode your applications if you have not used ROWIDs. If you have used ROWIDs in your applications then the DBMS_ROWID package has to be used.

New Datatypes

VARRAY
A VARRAY contains zero or more elements with a unique position of the same type in an ordered collection. Data in a VARRAY is stored in-line with other column data. They are referenced or retrieved as a single unit from SQL.
Example:
Consider a customer who has a list of telephone numbers like business numbers, mobile etc. A VARRAY type would be best suited for such a requirement.
CREATE TYPE phone_no_type as OBJECT
(
description VARCHAR2(10),
phone_no VARCHAR2(12)>br> );
CREATE TYPE phone_list_typ AS VARRAY(5) OF phone_no_type;
CREATE TYPE customer_typ AS OBJECT
(
custid NUMBER,
name VARCHAR2(50),
CONTACT VARCHAR2(50),
phone_nos phone_list_typ
);
CREATE TABLE customers OF customer_typ;

Nested tables
A nested table is a table within a table and useful for models requiring referential integrity.

Example:
CREATE TYPE item_typ AS OBJECT
(
prodid NUMBER(5),
price NUMBER(7,2)
);
CREATE TYPE item_nst_typ AS TABLE OF item_typ;
CREATE TABLE porder
(
ordid NUMBER(5),
custid NUMBER(5),
salesrep NUMBER(5),
orderdt DATE,
items item_nst_typ)
NESTED TABLE items STORE AS porder_item_nst;

where porder_item_nst is the name of the storage table in which the rows of all the values of the nested table reside.

Migration to Oracle8

As with all its previous releases, Oracle promises a relatively painless migration. It is required that each Oracle version upgrades to its terminal release before migrating to Oracle8. Terminal releases are Oracle 7.1.6, Oracle 7.2.3, Oracle 7.3.3. So Oracle 7.1.x has to be upgraded to 7.1.6 before migrating to Oracle8.

A migration assistant has been provided for migration to Oracle8. Using this
  • No database rebuild is required
  • No application conversion
There is no code rewriting. Care will have to be taken to see that code which uses ROWID now interprets the new ROWID using the DBMS_ROWID package
  • One way conversion to Oracle8. One cannot shift back to their previous version.
Migration process options are:
  • Use the migration utility, which rebuilds the data dictionary .
  • Export the data from the existing database, then import the data into Oracle8.
  • Copy the data between databases.

Oracle 8 as an Object Relational database

Before Oracle8, applications required mapping their data model to the Oracle database design. The developer had the responsibility to decompose application structures into a form that the database would understand. Oracle reduces this mismatch between the application and the database by implementing new and improved datatypes. Oracle8 can interact with the developer's application objects and integrate more closely with the application.

Oracle's definition of an object

An object is an instance of an object type, which is a model for something in the real world. It has two components:
Attributes: Built-in types or other object types. Attributes model the structure of the real world entity. Methods: Functions or procedures written in PL/SQL or an external language like C and stored in the database. Methods implement the operations that the application can perform on the real-world entity.

Oracle's definition of an object-relational database

  • Allows creation of user-defined datatypes. Users can create their own datatypes that can contain methods and can be used where a predefined datatype is allowed. This type of system makes it possible for member functions of user-defined datatypes to be flexible and secure.
  • Supports multimedia and large data objects. Direct access to large objects stored inside and outside the database.
  • Includes compatibility with SQL object standards. User defined DataTypes
    Users can create their own datatypes called object types. This allows easy interaction with object-oriented applications.
Example:
CREATE TYPE person_type AS OBJECT (
name VARCHAR2(30),
phone VARCHAR2(12),
address VARCHAR2(50),..
);

User-defined object types can be used to:
Create a relational table as a column of an object type
CREATE TABLE student
(
st_no CHAR(5) PRIMARY KEY,
st person_typ
);

INSERT INTO student VALUES
(385,person_typ('Ketan','835-0635,''SDF-IV UNIT..');
Create another object type as an attribute
CREATE TYPE employee_typ AS OBJECT
(
empno NUMBER,
emp_name VARCHAR2(30)
);
CREATE TYPE DEPARTMENT_TYP AS OBJECT>br> (
DEPTNO NUMBER,
MANAGER REF EMPLOYEE_TYP
);
where REF means another datatype is created referencing
employee_typ type
Create an object table based on that type
CREATE TABLE person_tab of person_typ;
The user has to create a member method to define the actions that can be performed on the object. Member methods can be functions or procedures.
Create Object Views
You can create object views that allow relational and object applications to coexist on the same dataset.

Extending the Database with Objects

To take full advantage of object functionality, the Oracle8 supports object extensions in the following areas
  • PL/SQL
  • Programmatic interfaces
PL/SQL
  • Execution of DML extensions for object types inside of PL/SQL procedures.
  • Declaration of object types as parameters or bind variables.
  • PL/SQL server-side methods and stored procedures can operate on the objects.

External Procedures

An external procedure is a third-generation language (3GL) routine stored in a shared library, registered with PL/SQL and called by you to do special-purpose processing. At runtime, PL/SQL loads the library dynamically and calls the routine as if it were a PL/SQL function or procedure.
Advantages are:
  • Reusage of code. Shared libraries already written and available in other languages can be directly called from PL/SQL programs. For example, on the Windows NT, a PL/SQL program can call dynamic linked libraries(DLLs).
  • Callouts to C functions. Support for Java, http, C++ and CORBA are expected.
  • Provides an interface between the database and external systems.

Programmatic support for objects

Pro*C, Pro*C++
Support objects including user-defined types.
Pro*COBOL
Support migration from DB2.

Object Type Translator

It translates object-based schemas or information into declarations of C structs.

Client-side Object Cache

A client-side object cache is provided to manage object-oriented datatype interaction locally. It provides high-performance navigation access of objects and reduction in the number of round-trips to the server through complex object retrieval among other things. Enhancements and object support exists in OCI (Oracle Call Interface) and Open Database connectivity (ODBC) driver.

Support for Multimedia and large data objects

Large Objects (LOBs) You can store and manipulate large objects up to 4GB in size. There are two categories of LOBs:
Internal LOBs : They are stored in the database.
  • A BLOB is a large object that contains unstructured binary (raw) data. Examples of BLOBs are compressed files, images and audio.
  • A CLOB is a large object that contains single-byte character data. Examples of CLOBs are resumes and recipes.
  • A NCLOB is a large object that contains fixed-width multibyte character data. An example of NCLOB are a recipe for making sushi written in Japanese.
Each type has an intended use and the data is not transferrable between columns of the above types.
External LOBs
  • A BFILE is a large object that contains raw data that is stored out of the database tablespaces and in an operating system file. Examples of BFILES are multimedia images or video. BFILES are read-only in Oracle8 release.
LOB contains two parts:
  • Data - what the object is made of
  • Locator - An indicator of the LOB data location stored in the database. An in-memory programmatic pointer to the locator is called the handle.
Creation of a LOB by a user causes Oracle8 to create and place a locator to the out-of-line LOB data in the LOB column of a particular row in the table. Programmatic interfaces operate on LOBs using these locators.
LOB index
It is creates implicitly and is used to find the LOB data. It contains the ROWID from the table containing LOB column, the LOB offset and the starting data block address of the LOB chunk.
Chunk It is the number of contiguous blocks stored with contiguous LOB information.
LOBs are seen as a replacement for data types LONG and LONG RAW eventually.

Comparison between LOB and LONG

LONG and LONG RAW
LOB
Single column per table Multiple columns per table
Upto 2 GB Upto 4 GB
SELECT returns data SELECT returns locator
Data stored in-line Data stored in-line or out-of-line
No object type support Object type support
Sequential access to data Random access to data
Can not be replicated Can be replicated
Note: Columns of datatype LOB, LONG, LONG RAW are not supported in partitioned tables.

Conclusion

As you can see that Oracle8 has come out with various kinds of new features. It is in our best interest to judge whether Oracle8 is the database for our needs. The performance of Oracle8 greatly improves as the database size it operates on increases.
Rajesh Srivastava
Mumbai,
India

Back to Nalin's Page