Relational Database Tables
Oracle Tips by Burleson Consulting
Administration of Relational Database
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
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
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:
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:
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
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.
1 2 3 4 5 6 7
Meaning: Century Year Month Day Hour Minute
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
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
SMALLINT. Same as INTEGER.
FLOAT. Same as NUMBER(38).
FLOAT(b). NUMBER with precision of 1 to
REAL. defaults to NUMBER(63).
DOUBLE PRECISION. Same as NUMBER(38).
Note: No scale specification means
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
* 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
* 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
* 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
* 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
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.
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.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.