Oracle8i Concepts
Release 8.1.5

A67781-01

Library

Product

Contents

Index

Prev Next

12
Built-In Datatypes

I am the voice of today, the herald of tomorrow. ... I am the leaden army that conquers the world--I am TYPE.

Frederic William Goudy: The Type Speaks

This chapter discusses the Oracle built-in datatypes, their properties, and how they map to non-Oracle datatypes. Topics include:

Overview of Oracle Datatypes

Each column value and constant in a SQL statement has a datatype, which is associated with a specific storage format, constraints, and a valid range of values. When you create a table, you must specify a datatype for each of its columns.

Oracle provides the following built-in datatypes:

Table 12-1 summarizes the characteristics of each Oracle datatype.


Table 12-1 Summary of Oracle Built-In Datatypes
Datatype  Description  Column Length and Default 

CHAR (size)  

Fixed-length character data of length size bytes.  

Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. Consider the character set (one-byte or multibyte) before setting size.  

VARCHAR2 (size)  

Variable-length character data. A maximum size must be specified.  

Variable for each row, up to 4000 bytes per row. Consider the character set (one-byte or multibyte) before setting size.  

NCHAR(size)  

Fixed-length character data of length size characters or bytes, depending on the national character set.  

Fixed for every row in the table (with trailing blanks). Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 2000 bytes per row. Default is 1 character or 1 byte, depending on the character set.  

NVARCHAR2 (size)  

Variable-length character data of length size characters or bytes, depending on national character set. A maximum size must be specified.  

Variable for each row. Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 4000 bytes per row. Default is 1 character or 1 byte, depending on the character set.  

LONG  

Variable-length character data.  

Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row.  

NUMBER (p, s)  

Variable-length numeric data. Maximum precision p and/or scale s is 38.  

Variable for each row. The maximum space required for a given column is 21 bytes per row.  

DATE  

Fixed-length date and time data, ranging from January 1, 4712 BCE to December 31, 9999 CE ("A.D.")  

Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-YY) specified by NLS_DATE_FORMAT parameter.  

RAW (size)  

Variable-length raw binary data. A maximum size must be specified.  

Variable for each row in the table, up to 2000 bytes per row.  

LONG RAW  

Variable-length raw binary data.  

Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row.  

BLOB  

Binary data.  

Up to 232 - 1 bytes, or 4 gigabytes.  

CLOB  

Single-byte character data.  

Up to 232 - 1 bytes, or 4 gigabytes.  

NCLOB  

Single-byte or fixed- or variable-width multibyte national character set (NCHAR) data.  

Up to 232 - 1 bytes, or 4 gigabytes.  

BFILE  

Binary data stored in an external file.  

Up to 232 - 1 bytes, or 4 gigabytes.  

ROWID  

Binary data representing a physical row address.  

Fixed at 10 bytes (extended rowid) or 6 bytes (restricted rowid) for each row in the table.  

UROWID  

Binary data representing any type of row address: physical, logical, or foreign.  

Up to 4000 bytes (but for a logical rowid, only 3950 bytes can be used for the primary key). The default is 4000 bytes.  

The sections that follow describe each of the built-in datatypes in more detail.

Additional Information:

See the Oracle8i Application Developer's Guide - Fundamentals for information about how to use the built-in datatypes.  

Character Datatypes

The character datatypes store character (alphanumeric) data in strings, with byte values corresponding to the character encoding scheme (generally called a character set or code page).

The database's character set is established when you create the database, and never changes. Examples of character sets are 7-bit ASCII (American Standard Code for Information Interchange), EBCDIC (Extended Binary Coded Decimal Interchange Code), Code Page 500, and Japan Extended UNIX. Oracle supports both single-byte and multibyte encoding schemes.

Additional Information:

See the Oracle8i Application Developer's Guide - Fundamentals for information about how to select a character datatype.  

CHAR Datatype

The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes, not characters) between 1 and 2000 for the CHAR column width. (The default is 1.) Oracle then guarantees that:

Oracle compares CHAR values using blank-padded comparison semantics.

Additional Information:

See the Oracle8i SQL Reference for details about blank-padded comparison semantics.  

VARCHAR2 and VARCHAR Datatypes

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes, not characters) between 1 and 4000 for the VARCHAR2 column. For each row, Oracle stores each value in the column as a variable-length field (unless a value exceeds the column's maximum length, in which case Oracle returns an error).

For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only the 10 characters (10 bytes), not 50.

Oracle compares VARCHAR2 values using nonpadded comparison semantics.

Additional Information:

See the Oracle8i SQL Reference for details about nonpadded comparison semantics.  

VARCHAR Datatype

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. However, in a future version of Oracle, VARCHAR might store variable-length character strings compared with different comparison semantics. Therefore, to avoid possible changes in behavior you should always use the VARCHAR2 datatype to store variable-length character strings.

Column Lengths for Character Datatypes and NLS Character Sets

The Oracle National Language Support (NLS) feature allows the use of various character sets for the character datatypes. National Language Support enables you to process single-byte and multi-byte character data and convert between character sets. Client sessions can use national character sets different from the database character set.

You should consider the size of characters when you specify the column length for character datatypes. You must consider this issue when estimating space for tables with columns that contain character data.

Additional Information:

See the Oracle8i National Language Support Guide for more information about the NLS feature of Oracle.  

NCHAR and NVARCHAR2 Datatypes

The NCHAR and NVARCHAR2 datatypes store NLS character data. The NCHAR datatype stores fixed-length character strings that correspond to a fixed-length or variable-length national character set. The NVARCHAR2 datatype stores variable-length character strings.

When you create a table with an NCHAR or NVARCHAR2 column, you specify a maximum size that is either the number of characters (for a fixed-length national character set) or the number of bytes (for a variable-length national character set).

LOB Character Datatypes

The LOB datatypes for character data are CLOB and NCLOB. They can store up to four gigabytes of character data (CLOB) or national character set data (NCLOB). These datatypes are described in "LOB Datatypes".

LONG Datatype

Columns defined as LONG can store variable-length character data containing up to two gigabytes of information. LONG data is text data that is to be appropriately converted when moving among different systems.

LONG datatype columns are used in the data dictionary to store the text of view definitions. You can use LONG columns in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.


Note:

The LONG datatype is provided for backward compatibility with existing applications. In new applications, you should use CLOB and NCLOB datatypes for large amounts of character data.  


Additional Information:

The LONG datatype has many restrictions--see the Oracle8i Application Developer's Guide - Fundamentals.

Also see "RAW and LONG RAW Datatypes" for information about the LONG RAW datatype.  

NUMBER Datatype

The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision.

The following numbers can be stored in a NUMBER column:

For numeric columns you can specify the column as:

column_name NUMBER 

Optionally, you can also specify a precision (total number of digits) and scale (number of digits to the right of the decimal point):

column_name NUMBER (precision, scale) 

If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.

Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:

column_name NUMBER (*, scale) 

In this case, the precision is 38 and the specified scale is maintained.

When you specify numeric fields, it is a good idea to specify the precision and scale; this provides extra integrity checking on input.

Table 12-2 shows examples of how data would be stored using different scale factors.


Table 12-2 How Scale Factors Affect Numeric Data Storage
Input Data  Specified As  Stored As 

7,456,123.89  

NUMBER  

7456123.89  

7,456,123.89  

NUMBER(*,1)  

7456123.9  

7,456,123.89  

NUMBER(9)  

7456124  

7,456,123.89  

NUMBER(9,2)  

7456123.89  

7,456,123.89  

NUMBER(9,1)  

7456123.9  

7,456,123.89  

NUMBER(6)  

(not accepted, exceeds precision)  

7,456,123.89  

NUMBER(7,-2)  

7456100  

If you specify a negative scale, Oracle rounds the actual data to the specified number of places to the left of the decimal point. For example, specifying (7,-2) means Oracle should round to the nearest hundredths, as shown in Table 12-2.

For input and output of numbers, the standard Oracle default decimal character is a period, as in the number "1234.56". (The decimal is the character that separates the integer and decimal parts of a number.) You can change the default decimal character with the initialization parameter NLS_NUMERIC_CHARACTERS. You can also change it for the duration of a session with the ALTER SESSION statement. To enter numbers that do not use the current default decimal character, use the TO_NUMBER function.

Internal Numeric Format

Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. (The resulting value is limited to 38 digits of precision.) Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with one byte used to store the exponent (2) and two bytes used to store the three significant digits of the mantissa (4, 1, 2).

Taking this into account, the column data size for a particular numeric data value NUMBER (p), where p is the precision of a given value (scale has no effect), can be calculated using the following formula:

  1 byte              (exponent) 
+ FLOOR(p/2)+1 bytes  (mantissa) 
+ 1 byte              (only for a negative number where the number of 
                              significant digits is less than 38) 
_______________________    
number of bytes of data 

Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations: zero and negative infinity each require one byte; positive infinity requires two bytes.

DATE Datatype

The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).

Oracle can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era). Unless BCE ('BC' in the format mask) is specifically used, CE date entries are the default.

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

For input and output of dates, the standard Oracle default date format is DD-MON-YY, as below:

'13-NOV-92' 

You can change this default date format for an instance with the parameter NLS_DATE_FORMAT. You can also change it during a user session with the ALTER SESSION statement. To enter dates that are not in standard Oracle date format, use the TO_DATE function with a format mask:

TO_DATE ('November 13, 1992', 'MONTH DD, YYYY') 


Note:

If you use the standard date format DD-MON-YY, YY gives the year in the 20th century (for example, 31-DEC-92 is December 31, 1992). If you want to indicate years in any century other than the 20th century, use a different format mask, as shown above.  


Oracle stores time in 24-hour format--HH:MI:SS. By default, the time in a date field is 00:00:00 A.M. (midnight) if no time portion is entered. In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, use the TO_DATE function with a format mask indicating the time portion, as in

INSERT INTO birthdays (bname, bday) VALUES 
    ('ANDY',TO_DATE('13-AUG-66 12:56 A.M.','DD-MON-YY HH:MI A.M.')); 

Using Julian Dates

Julian dates allow continuous dating by the number of days from a common reference. (The reference is 01-01-4712 years BCE, so current dates are somewhere in the 2.4 million range.) A Julian date is nominally a noninteger, the fractional part being a portion of a day. Oracle uses a simplified approach that results in integer values. Julian dates can be calculated and interpreted differently; the calculation method used by Oracle results in a seven-digit number (for dates most often used), such as 2449086 for 08-APR-93.


Note:

Oracle Julian dates might not be compatible with Julian dates generated by other date algorithms.  


The format mask "J" can be used with date functions (TO_DATE or TO_CHAR) to convert date data into Julian dates. For example, the following query returns all dates in Julian date format:

SELECT TO_CHAR (hiredate, 'J') FROM emp; 

You must use the TO_NUMBER function if you want to use Julian dates in calculations. You can use the TO_DATE function to enter Julian dates:

INSERT INTO emp (hiredate) VALUES (TO_DATE(2448921, 'J')); 

Date Arithmetic

Oracle date arithmetic takes into account the anomalies of the calendars used throughout history. For example, the switch from the Julian to the Gregorian calendar, 15-10-1582, eliminated the previous 10 days (05-10-1582 through 14-10-1582). The year 0 does not exist.

You can enter missing dates into the database, but they are ignored in date arithmetic and treated as the next "real" date. For example, the next day after 04-10-1582 is 15-10-1582, and the day following 05-10-1582 is also 15-10-1582.


Note:

This discussion of date arithmetic may not apply to all countries' date standards (such as those in Asia).  


Centuries and the Year 2000

Oracle stores year data with the century information. For example, the Oracle database stores 1996 or 2001, and not just 96 or 01. The DATE datatype always stores a four-digit year internally, and all other dates stored internally in the database have four digit years. Oracle utilities such as import, export, and recovery also deal properly with four-digit years.

However, some applications might be written with an assumption about the year (such as assuming that everything is 19xx). Application programmers should therefore review and test their code with regard to the year 2000.

Additional Information:

For more information about centuries and date format masks, see the Oracle8i Application Developer's Guide - Fundamentals. For general information about date format codes, see the Oracle8i SQL Reference.  

LOB Datatypes

The LOB datatypes BLOB, CLOB, NCLOB, and BFILE enable you to store large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size. They provide efficient, random, piece-wise access to the data.

You can perform parallel queries (but not parallel DML or DDL) on LOB columns.

LOB datatypes differ from LONG and LONG RAW datatypes in several ways. For example:

SQL statements define LOB columns in a table and LOB attributes in a user-defined object type. See "Default Logging Mode" for information about the LOB attributes LOGGING and NOLOGGING. When defining LOBs in a table, you can explicitly specify the tablespace and storage characteristics for each LOB.

LOB datatypes can be stored inline (within a table), out-of-line (within a tablespace, using a LOB locator), or in an external file (BFILE datatypes).

Additional Information:

See the Oracle8i Application Developer's Guide - Fundamentals for more information about LOB storage and LOB locators.  

BLOB Datatype

The BLOB datatype stores unstructured binary data in the database. BLOBs can store up to four gigabytes of binary data.

BLOBs participate fully in transactions. Changes made to a BLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, BLOB locators cannot span transactions or sessions.

CLOB and NCLOB Datatypes

The CLOB and NCLOB datatypes store up to four gigabytes of character data in the database. CLOBs store single-byte character set data and NCLOBs store fixed-width and varying-width multibyte national character set data (NCHAR data).

CLOBs and NCLOBs participate fully in transactions. Changes made to a CLOB or NCLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, CLOB and NCLOB locators cannot span transactions or sessions.

The CLOB or NCLOB value is stored in the database using the two-byte Unicode character set, which has a fixed width. Oracle translates the stored Unicode value to the character set requested on the client or on the server, which can be fixed-width or varying-width. When you insert data into a CLOB or NCLOB column using a varying-width character set, Oracle converts the data into Unicode before storing it in the database.

You cannot create an object type with NCLOB attributes, but you can specify NCLOB parameters in a method for an object type.

Additional Information:

See the Oracle8i National Language Support Guide for more information about national character set data and the Unicode character set.  

BFILE Datatype

The BFILE datatype stores unstructured binary data in operating-system files outside the database. A BFILE column or attribute stores a file locator that points to an external file containing the data. BFILEs can store up to four gigabytes of data.

BFILEs are read-only; you cannot modify them. They support only random (not sequential) reads, and they do not participate in transactions. The underlying operating system must maintain the file integrity and durability for BFILEs. The database administrator must ensure that the file exists and that Oracle processes have operating-system read permissions on the file.

RAW and LONG RAW Datatypes


Note:

The RAW and LONG RAW datatypes are provided for backward compatibility with existing applications. For new applications, you should use the BLOB and BFILE datatypes for large amounts of binary data.  


The RAW and LONG RAW datatypes are used for data that is not to be interpreted (not converted when moving data between different systems) by Oracle. These datatypes are intended for binary data or byte strings. For example, LONG RAW can be used to store graphics, sound, documents, or arrays of binary data; the interpretation is dependent on the use.

RAW is a variable-length datatype like the VARCHAR2 character datatype, except that Net8 (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, Net8 and Import/Export automatically convert CHAR, VARCHAR2, and LONG data between the database character set and the user session character set (set by the NLS_LANGUAGE parameter of the ALTER SESSION command), if the two character sets are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB'.

LONG RAW data cannot be indexed, but RAW data can be indexed.

Additional Information:

See the Oracle8i Application Developer's Guide - Fundamentals for information about other restrictions on the LONG RAW datatype.  

ROWID and UROWID Datatypes

Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.

A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables (such as non-Oracle tables accessed through a gateway--see "Rowids in Non-Oracle Databases").

A column of the UROWID datatype can store all kinds of rowids. The value of the COMPATIBLE initialization parameter must be set to 8.1 or higher to use UROWID columns.

The ROWID Pseudocolumn

Each table in an Oracle database internally has a pseudocolumn named ROWID. This pseudocolumn is not evident when listing the structure of a table by executing a SELECT * FROM . . . statement, or a DESCRIBE . . . statement using SQL*Plus. However, each row's address can be retrieved with a SQL query using the reserved word ROWID as a column name, for example:

SELECT ROWID, ename FROM emp; 

You cannot set the value of the pseudocolumn ROWID in INSERT or UPDATE statements, and you cannot delete a ROWID value. Oracle uses the ROWID values in the pseudocolumn ROWID internally for the construction of indexes (see "How Rowids Are Used").

You can reference rowids in the pseudocolumn ROWID like other table columns (used in SELECT lists and WHERE clauses), but rowids are not stored in the database, nor are they database data. However, you can create tables that contain columns having the ROWID datatype, although Oracle does not guarantee that the values of such columns are valid rowids.

Physical Rowids

Physical rowids provide the fastest possible access to a row of a given table. They contain the physical address of a row (down to the specific block), and essentially allow you to retrieve the row in a single block access. Oracle guarantees that as long as the row exists, its rowid does not change. These performance and stability qualities make rowids useful for applications that select a set of rows, perform some operations on them, and then access some of the selected rows again, perhaps with the purpose of updating them.

Every row in a nonclustered table is assigned a unique rowid that corresponds to the physical address of a row's row piece (or the initial row piece if the row is chained among multiple row pieces). In the case of clustered tables, rows in different tables that are in the same data block can have the same rowid.

A row's assigned rowid remains unchanged unless the row is exported and imported (using the IMPORT and EXPORT utilities). When you delete a row from a table (and then commit the encompassing transaction), the deleted row's associated rowid can be assigned to a row inserted in a subsequent transaction.

A physical rowid datatype has one of two formats:

Extended Rowids

Extended rowids use a base 64 encoding of the physical address for each row selected. The encoding characters are A-Z, a-z, 0-9, +, and /. For example, the following query

SELECT ROWID, ename FROM emp WHERE deptno = 20; 

might return the following row information:

ROWID              ENAME 
------------------ ---------- 
AAAAaoAATAAABrXAAA BORTINS 
AAAAaoAATAAABrXAAE RUGGLES 
AAAAaoAATAAABrXAAG CHEN 
AAAAaoAATAAABrXAAN BLUMBERG 

An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

You can retrieve the data object number from data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. For example, the following query returns the data object number for the EMP table in the SCOTT schema:

SELECT DATA_OBJECT_ID FROM DBA_OBJECTS 
    WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'EMP'; 

You can also use the DBMS_ROWID package to extract information from an extended rowid or to convert a rowid from extended format to restricted format (or vice versa).

Additional Information:

See the Oracle8i Application Developer's Guide - Fundamentals for information about the DBMS_ROWID package.  

Restricted Rowids

Restricted rowids use a binary representation of the physical address for each row selected. When queried using SQL*Plus, the binary representation is converted to a VARCHAR2/hexadecimal representation. The following query

SELECT ROWID, ename FROM emp 
    WHERE deptno = 30; 

might return the following row information:

ROWID              ENAME 
------------------ ---------- 
00000DD5.0000.0001 KRISHNAN 
00000DD5.0001.0001 ARBUCKLE 
00000DD5.0002.0001 NGUYEN 

As shown above, a restricted rowid's VARCHAR2/hexadecimal representation is in a three-piece format, block.row.file:

Examples of Using Rowids

You can use the function SUBSTR to break the data in a rowid into its components. For example, you can use SUBSTR to break an extended rowid into its four components (database object, file, block, and row):

SELECT ROWID, 
       SUBSTR(ROWID,1,6) "OBJECT", 
       SUBSTR(ROWID,7,3) "FIL", 
       SUBSTR(ROWID,10,6) "BLOCK", 
       SUBSTR(ROWID,16,3) "ROW" 
       FROM products; 

ROWID               OBJECT  FIL  BLOCK   ROW 
------------------  ------  ---  ------  ---- 
AAAA8mAALAAAAQkAAA  AAAA8m  AAL  AAAAQk  AAA 
AAAA8mAALAAAAQkAAF  AAAA8m  AAL  AAAAQk  AAF 
AAAA8mAALAAAAQkAAI  AAAA8m  AAL  AAAAQk  AAI 

Or you can use SUBSTR to break a restricted rowid into its three components (block, row, and file):

SELECT ROWID, SUBSTR(ROWID,15,4) "FILE", 
       SUBSTR(ROWID,1,8) "BLOCK", 
       SUBSTR(ROWID,10,4) "ROW" 
       FROM products; 

ROWID               FILE  BLOCK     ROW 
------------------  ----  --------  ---- 
00000DD5.0000.0001  0001  00000DD5  0000 
00000DD5.0001.0001  0001  00000DD5  0001 
00000DD5.0002.0001  0001  00000DD5  0002 

Rowids can be useful for revealing information about the physical storage of a table's data. For example, if you are interested in the physical location of a table's rows (such as for table striping), the following query of an extended rowid tells how many datafiles contain rows of a given table:

SELECT COUNT(DISTINCT(SUBSTR(ROWID,7,3))) "FILES" FROM tablename; 

    FILES 
-------- 
        2 

Additional Information:

See the Oracle8i SQL Reference, PL/SQL User's Guide and Reference, Oracle8i Tuning, and other books about Oracle tools and utilities for more examples of using rowids.  

How Rowids Are Used

Oracle uses rowids internally for the construction of indexes. Each key in an index is associated with a rowid that points to the associated row's address for fast access. End users and application developers can also use rowids for several important functions:

Before you use rowids in DML statements, they should be verified and guaranteed not to change; the intended rows should be locked so they cannot be deleted. Under some circumstances, requesting data with an invalid rowid could cause a statement to fail.

You can also create tables with columns defined using the ROWID datatype. For example, you can define an exception table with a column of datatype ROWID to store the rowids of rows in the database that violate integrity constraints. Columns defined using the ROWID datatype behave like other table columns; values can be updated, and so on. Each value in a column defined as datatype ROWID requires six bytes to store pertinent column data.

Logical Rowids

Rows in index-organized tables do not have permanent physical addresses--they are stored in the index leaves and can move within the block or to a different block as a result of insertions--therefore their row identifiers cannot be based on physical addresses. Instead, Oracle provides index-organized tables with logical row identifiers, called logical rowids, that are based on the table's primary key. Oracle uses these logical rowids for the construction of secondary indexes on index-organized tables.

Each logical rowid used in a secondary index can include a physical guess, which identifies the block location of the row in the index-organized table at the time the guess was made (that is, when the secondary index was created or rebuilt).

Oracle can use guesses to probe into the leaf block directly, bypassing the full key search. This ensures that rowid access of nonvolatile index-organized tables gives comparable performance to the physical rowid access of ordinary tables. In a volatile table, however, if the guess becomes stale the probe may fail, in which case a primary key search must be performed.

The values of two logical rowids are considered equal if they have the same primary key values but different guesses.

Comparison of Logical Rowids with Physical Rowids

Logical rowids are similar to the physical rowids in the following ways:

One difference between physical and logical rowids is that logical rowids cannot be used to see how a table is organized.

Guesses in Logical Rowids

When a row's physical location changes, the logical rowid remains valid even if it contains a guess, although the guess could become stale and slow down access to the row. Guess information cannot be updated dynamically. For secondary indexes on index-organized tables, however, you can rebuild the index to obtain fresh guesses. Note that rebuilding a secondary index on an index-organized table involves reading the base table, unlike rebuilding an index on an ordinary table.

You should collect index statistics with the DBMS_STATS package or ANALYZE command to keep track of the staleness of guesses, so that Oracle does not use them unnecessarily. This is particularly important for applications that store rowids with guesses persistently in a UROWID column, then retrieve the rowids later and use them to fetch rows.

When you collect index statistics with the DBMS_STATS package or ANALYZE command, Oracle checks whether the existing guesses are still valid and records the percentage of stale/valid guesses in the data dictionary. After you rebuild a secondary index (recomputing the guesses), you should collect index statistics again. See "Statistics for Cost-Based Optimization" for more information about collecting statistics.

In general, logical rowids without guesses provide the fastest possible access for a highly volatile table. If a table is static or if the time between getting a rowid and using it is sufficiently short to make row movement unlikely, logical rowids with guesses provide the fastest access.

Rowids in Non-Oracle Databases

Oracle database applications can be executed against non-Oracle database servers using SQL*Connect or the Oracle Open Gateway. In such cases, the format of rowids varies according to the characteristics of the non-Oracle system. Furthermore, no standard translation to VARCHAR2/hexadecimal format is available. Programs can still use the ROWID datatype; however, they must use a nonstandard translation to hexadecimal format of length up to 256 bytes.

Rowids of a non-Oracle database can be stored in a column of the UROWID datatype (see "ROWID and UROWID Datatypes").

Additional Information:

Refer to the relevant manual for OCIs or precompilers for further details on handling rowids with non-Oracle systems.  

ANSI, DB2, and SQL/DS Datatypes

The ANSI datatype conversions to Oracle datatypes are shown in Table 12-3. The ANSI/ISO datatypes NUMERIC, DECIMAL, and DEC can specify only fixed-point numbers. For these datatypes, s (scale) defaults to 0.


Table 12-3 ANSI Datatype Conversions to Oracle Datatypes
ANSI SQL Datatype  Oracle Datatype 

CHARACTER (n), CHAR (n)  

CHAR (n)  

NUMERIC (p,s), DECIMAL (p,s), DEC (p,s)  

NUMBER (p,s)  

INTEGER, INT, SMALLINT  

NUMBER (38)  

FLOAT (p)  

FLOAT (p)  

REAL  

FLOAT (63)  

DOUBLE PRECISION  

FLOAT (126)  

CHARACTER VARYING(n), CHAR VARYING(n)  

VARCHAR2 (n)  

The IBM products SQL/DS, and DB2 datatypes TIME, TIMESTAMP, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC have no corresponding Oracle datatype and cannot be used. The TIME and TIMESTAMP datatypes are subcomponents of the Oracle datatype DATE.

Table 12-4 shows the DB2 and SQL/DS conversions.


Table 12-4 SQL/DS, DB2 Datatype Conversions to Oracle Datatypes
DB2 or SQL/DS Datatype  Oracle Datatype 

CHARACTER (n)  

CHAR (n)  

VARCHAR (n)  

VARCHAR2 (n)  

LONG VARCHAR  

LONG  

DECIMAL (p,s)  

NUMBER (p,s)  

INTEGER, SMALLINT  

NUMBER (38)  

FLOAT (p)  

FLOAT (p)  

DATE  

DATE  

Data Conversion

In some cases, Oracle supplies data of one datatype where it expects data of a different datatype. This is allowed when Oracle can automatically convert the data to the expected datatype using one of the following functions:




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index