Remote DBA Plans
Remote DBA Service

Relational Database Tables

Oracle Tips by Burleson Consulting

Administration of Relational Database Tables

With Oracle8, the number and complexity of database objects increased substantially. In Oracle8i and Oracle9i, even more objects were added. However, the basic building block of the database is still the table. We now have three essential table types: the relational, the object-based, and the index-only, or IOT, table. In Oracle7 we had two types of relational tables, standard and clustered; in Oracle9i, we have standard, partitioned, object, index-only, nested, global temporary, temporary, locally managed, dictionary-managed, system-managed, clustered tables, and external tables.

The object TYPE definition was added in Oracle8 and expanded in Oracle8i. A TYPE can be just a structure of scalar datatypes, an object type, a nested table type, or a varray. In this chapter, we will cover the “standard” relational table, external tables, clusters, and triggers. In the next chapter, we will look at the object extensions to tables. Let’s get started.

The Concept of Restricted ROWIDs

Oracle used the concept of the restricted rowid in Oracle7 and earlier releases to uniquely identify each row in each table in the database. This was represented as the pseudocolumn rowid in Oracle7 and earlier releases. Hence, unknown to many Remote DBAs and developers, even non-unique identified tables that violated Third Normal Form always had a unique identifier that could be used for removal of duplicates and other unique-identifier-required operations: the rowid column. Of course, views don’t have rowids.

Note: In Oracle8, Oracle8i, and Oracle9i, the concept of rowid is still with us, but the format has been expanded. In Appendix I, on the Wiley Web site, I discuss the DBMS_ROWID set of Oracle-provided packages that provide for rowid manipulation between the old and new formats, and the piece out as well as the building of rowids.

But what does a rowid contain? To begin to answer that question, in this chapter we will examine the Oracle7 rowid concepts; in the next chapter, we will cover the expanded rowid (Oracle8, Oracle8i, and Oracle9i types, tables, and such). Though Oracle8i did not add to the concept of rowid, it added some datatypes that deal with rowids; Oracle9i did not add much to the usage of rowids.

Restricted rowid Format

The restricted rowid in Oracle is a VARCHAR2 representation of a binary value shown in hexadecimal format. You should only need to deal with restricted rowids in versions prior to Oracle8 or in systems that were upgraded from Oracle7 to Oracle8, 8i, or 9i. Restricted rowids are displayed as:

bbbbbbb.ssss.ffff

where:
   bbbbbbbb is the block ID.
   ssss is the sequence in the block.
   ffff is the file ID.

As stated in the introduction to this chapter, this rowid is a pseudocolumn (meaning the DESCRIBE command won’t show it) in each table (and cluster). The rowid is unique, except in the case of cluster tables that have values stored in the same block. This makes it handy for doing entry comparisons in tables that may not have a unique key, especially when you want to eliminate or show duplicates before creating a unique or primary key. In Oracle Performance Tuning, authors Mark Gurry and Peter Corrigan (O’Reilly & Associates, 1996) show this simple query for determining duplicates using the rowid:

DELETE FROM emp E
WHERE E.rowid > ( SELECT MIN (x.rowid)
               FROM emp X
               WHERE X.emp_no = E.emp_no );

Of course, for your table, this query would have to be modified for the proper table name and column or columns involved in the primary key. And, of course, any operation where you can do a select, update, or delete based on exact rowid values will always outperform virtually any other similar operation using standard column value logic. The file ID portion of the rowid points to the file with that number, which happens to be the same number given the datafile in the Remote DBA_DATA_FILES view. In the sections to follow, a script called ACT_SIZE.SQL makes use of the rowid to determine the actual number of blocks used by a table. The rowid pseudocolumn is one that any Remote DBA should be aware of and use to advantage. Oracle expanded the rowid in Oracle8 and continues to use the expanded ROWID in Oracle8i and Oracle9i. Chapter 5, Administration of Oracle9i Object Tables, discusses these changes and their implications.

Relational Table Administration

Tables are the primary storage division in Oracle. All data is stored in tables. Sequences and indexes support tables. Synonyms point to tables. Types and varrays are stored in tables. In short, tables make Oracle work. To create tables, you must have the permission CREATE TABLE or CREATE ANY TABLE. The CREATE TABLE privilege is granted automatically through the CONNECT role. The next subsections describe in detail the administration and maintenance of standard relational database tables.

Tables consist of attributes that are made up of datatypes at one level or another. The allowed Oracle datatypes are:

Datatype. One of the allowed SQL datatypes, which are listed in the SQL reference manual on the technet.oracle.com Web site. The remaining items in this brief list are composed of the other SQL datatypes.

CHAR(size). Character type data, max size 255. Under Oracle7, this was replaced by VARCHAR2. Under Oracle7, 8, 8i, and 9i CHAR is right-side padded to a specified length. CHAR has a maximum size of 2,000 bytes and a minimum size of 1 byte (which is the default if not specified). Note: There are plans afoot to eliminate the CHAR datatype, so begin converting any that your application may be using to VARCHAR2.

NCHAR(size). Same as CHAR except it is used for multibyte characters. The maximum length that can be stored depends on the number of bytes per character.

VARCHAR2. Variable-length character data of up to 2,000 characters in Oracle7; 4,000 characters in Oracle8, 8iand 9i.

NVARCHAR2(size). Same as VARCHAR2, except for multi-byte character sets. Maximum length that can be stored is dependent on length of individual character representations.

DATE. Date format, from 1/1/4712 BC to 12/31/4712 AD. Standard Oracle format is (10-APR-93), fixed 7 bytes of internal storage. The DATE datatype can update, insert, or retrieve a date value using the Oracle internal date binary format. A date in binary format contains 7 bytes, as shown here.

Byte:       1        2 3     4     5     6     7
Meaning:    Century     Year      Month Day   Hour Minute      Second
   Example, for 30-NOV-1992, 3:17 PM:
Byte 1     2     3     4     5     6     7
Value 119   192   11    30    16    18    1

The century and year bytes (bytes 1 and 2) are in excess-100 notation. The first byte stores the value of the year, which is 1992, as an integer, divided by 100, giving 119 in excess-100 notation. The second byte stores year modulo 100, giving 192. Dates before common era (BCE) are less than 100. The era begins on 01-JAN-4712 BCE, which is Julian day 1. For this date, the century byte is 53, and the year byte is 88. The hour, minute, and second bytes are in excess-1 notation. The hour byte ranges from 1 to 24, the minute and second bytes from 1 to 60. If no time was specified when the date was created, the time defaults to midnight (1, 1, 1).

LONG. Only one LONG per table; 2 gig under Oracle7; 4 gig under Oracle8 and 8i. Oracle intends to do away with LONG and LONG RAW in favor of LOB datatypes, so it would be wise to begin converting the LONG and LONG RAW columns in your application to LOB datatypes.

RAW(size). Raw binary data, maximum of 2,000 bytes under Oracle7; 4,000 under Oracle8, 8i and 9i.

LONG RAW. Raw binary data in hexadecimal format, 2 gig under Oracle7; 4 gig under Oracle8, 8i and 9i.

ROWID. Internal datatype, not user-definable; used to uniquely identify table rows. Length of 6 in Oracle7, 10 in Oracle8, 8i and 9i.

UROWID[(size)]. Hexadecimal string that represents the unique address of a row in an index-organized table. The optional size parameter is the size of a column of type UROWID. The maximum size is 4,000 bytes; this is also the default size.

NUMBER(p, s). Numeric data, with p being precision and s being scale. Defaults to 38 p, null s.

DECIMAL(p, s). Same as numeric.

INTEGER. Defaults to NUMBER(38), no scale.

SMALLINT. Same as INTEGER.

FLOAT. Same as NUMBER(38).

FLOAT(b). NUMBER with precision of 1 to 126.

REAL. defaults to NUMBER(63).

DOUBLE PRECISION. Same as NUMBER(38).

Note: No scale specification means floating point.

Large Object Datatypes

LOBs are similar to LONG and LONG RAW types, but differ in the following ways:

* Multiple LOBs are allowed in a single row.

* LOBs can be attributes of a user-defined datatype (object).

* The LOB locator is stored in the table column, either with or without the actual LOB value; BLOB, NCLOB, and CLOB values can be stored in separate tablespaces

* BFILE data is stored in an external file on the server.

* When you access a LOB column, it is the locator that is returned.

* A LOB can be up to 4 GB in size. BFILE maximum size is operating system-dependent, but cannot exceed 4 GB.

* LOBs permit efficient, random, piecewise access to, and manipulation of, data.

* You can define one or more LOB datatype columns in a table.

* With the exception of NCLOB, you can define one or more LOB attributes in an object.

* You can declare LOB bind variables.

* You can select LOB columns and LOB attributes.

* You can insert a new row or update an existing row that contains one or more LOB columns, and/or an object with one or more LOB attributes. (You can set the internal LOB value to NULL or empty, or replace the entire LOB with data. You can set the BFILE to NULL or so that it points to a different file.)

* You can update a LOB row/column intersection or a LOB attribute with another LOB row/column intersection or LOB attribute.

* You can delete a row containing a LOB column or LOB attribute and thereby also delete the LOB value. Note that for BFILEs, the actual operating system file is not deleted.

The LOB datatypes themselves are defined as follows:

BLOB. Binary large object, usually used to store graphics, video, or audio data. Maximum length of 4 GB under Oracle8, 8i, and 9i.

CLOB. Character large object, usually used to store single-byte character objects such as large text files. Maximum length of 4 GB under Oracle8, 8i, and 9i.

NCLOB. National character large object, usually used to store multi-byte character data. Maximum length of 4 GB under Oracle8, 8i, and 9i.

BFILE. Binary external file locator. This probably varies in size depending on the value of the directory and filename placed into it. In empirical testing, with a directory specification of ‘GIF_FILES’, which contained the directory value ‘e:\Oracle3\Ortest1\Bfiles’ and an average name length of 10 for the actual external files, this column showed a length of 40.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.