Oracle9i Database Concepts Release 1 (9.0.1) Part Number A88856-02 |
|
This chapter discusses the Oracle built-in datatypes, their properties, and how they map to non-Oracle datatypes. Topics include:
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:
See Also:
The following sections that describe each of the built-in datatypes in more detail.
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. Examples of character sets are 7-bit ASCII (American Standard Code for Information Interchange), EBCDIC (Extended Binary Coded Decimal Interchange Code), Code Page 500, Japan Extended UNIX, and Unicode UTF-8. Oracle supports both single-byte and multibyte encoding schemes.
See Also:
|
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 or characters) between 1 and 2000 bytes for the CHAR
column width. The default is 1 byte. Oracle then guarantees that:
CHAR
column has the fixed length.
Oracle compares CHAR
values using blank-padded comparison semantics.
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 or characters) between 1 and 4000 bytes 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. Using VARCHAR2
and VARCHAR
saves on space used by the table.
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.
The VARCHAR
datatype is synonymous with the VARCHAR2
datatype. To avoid possible changes in behavior, always use the VARCHAR2
datatype to store variable-length character strings.
The Oracle9i Globalization Support feature allows the use of various character sets for the character datatypes. Globalization Support enables you to process single-byte and multibyte character data and convert between character sets. Client sessions can use client character sets that are different from the database character set.
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.
The length semantics of character datatypes can be measured in bytes or characters.
For single byte character sets, columns defined in character semantics are basically the same as those defined in byte semantics. Character semantics are useful for defining varying-width multibyte strings; it reduces the complexity when defining the actual length requirements for data storage. For example, in a Unicode database (UTF8),
you need to define a VARCHAR2
column that can store up to five Chinese characters together with five English characters. In byte semantics, this would require (5*3 bytes) + (1*5 bytes) = 20 bytes; in character semantics, the column would require 10 characters.
VARCHAR2(20 BYTE)
and SUBSTRB(
<string>, 1, 20)
use byte semantics. VARCHAR2(10 CHAR)
and SUBSTR(
<string>, 1, 10)
use character semantics.
The parameter NLS_LENGTH_SEMANTICS
decides whether a new column of character datatype uses byte or character semantics. The default length semantic is byte. If all character datatype columns in a database use byte semantics (or all use character semantics) then users do not have to worry about which columns use which semantics. The BYTE
and CHAR
qualifiers shown above should be avoided when possible, because they lead to mixed-semantics databases. Instead, the NLS_LENGTH_SEMANTICS
initialization parameter should be set appropriately in INIT.ORA, and columns should use the default semantics.
See Also:
|
NCHAR
and NVARCHAR2
are Unicode data types that store Unicode character data. The character set of NCHAR
and NVARCHAR2
datatypes can only be either AL16UTF16
or UTF8
and is specified at database creation time as the national character set. AL16UTF16
and UTF8
are both Unicode encoding.
NCHAR
datatype stores fixed-length character strings that correspond to the national character set.
NVARCHAR2
datatype stores variable length character strings.
When you create a table with an NCHAR
or NVARCHAR2
column, the maximum size specified is always in character length semantics. Character length semantics is the default and only length semantics for NCHAR
or NVARCHAR2
.
If national character set is UTF8,
the following statement defines the maximum byte length of 90 bytes:
CREATE TABLE tab1 (col1 NCHAR(30));
This statement creates a column with maximum character length of 30. The maximum byte length is the multiple of the maximum character length and the maximum number of bytes in each character.
The maximum length of an NCHAR
column is 2000 bytes. It can hold up to 2000 characters. The actual data is subject to the maximum byte limit of 2000. The two size constraints must be satisfied simultaneously at run time.
The maximum length of an NVARCHAR2
column is 4000 bytes. It can hold up to 4000 characters. The actual data is subject to the maximum byte limit of 4000. The two size constraints must be satisfied simultaneously at run time.
See Also:
Oracle9i Globalization Support Guide for more information about the |
Unicode is an effort to have a unified encoding of every character in every language known to man. It also provides a way to represent privately-defined characters. A database column that stores Unicode can store text written in any language.
Oracle users deploying globalized applications have a strong need to store Unicode data in Oracle databases. They need a datatype which is guaranteed to be Unicode regardless of the database character set.
Oracle9i, Release 1 (9.0.1), supports a reliable Unicode data type through NCHAR, NVARCHAR2,
and NCLOB.
These data types are guaranteed to be Unicode encoding and always use character length semantics. The character sets used by NCHAR/NVARCHAR2
can be either UTF8
or AL16UTF16,
depending on the setting of the national character set when the database is created. These data types allow character data in Unicode to be stored in a database that may or may not use Unicode as database character set.
To all the implicit conversions for CHAR/VARCHAR2,
Oracle9i, Release 1 (9.0.1), adds support for implicit conversion for NCHAR/NVARCHAR2.
Implicit conversion between CHAR/VARCHAR2
and NCHAR/NVARCHAR2
is also supported.
The LOB
datatypes for character data are CLOB
and NCLOB
.
They can store up to 4 gigabytes of character data (CLOB)
or national character set data (NCLOB)
. LOB
datatypes are intended to replace the LONG
datatype functionality.
Columns defined as LONG
can store variable-length character data containing up to 2 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.
See Also:
|
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 13-1 shows examples of how data would be stored using different scale factors.
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 rounds to the nearest hundredths, as shown in Table 13-1.
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.
Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 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 1 byte used to store the exponent(2)
and 2 bytes used to store the three significant digits of the mantissa(4,1,2)
.
Negative numbers include the sign in their length.
Taking this into account, the column size in bytes for a particular numeric data value NUMBER(
p
),
where p
is the precision of a given value, can be calculated using the following formula:
ROUND((length(p
)+s
)/2))+1
where s
equals zero if the number is positive, and s
equals 1 if the number is negative.
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 1 byte; positive infinity requires 2 bytes.
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 follows:
'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')
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.'));
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.
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'));
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.
Oracle stores year data with the century information. For example, the Oracle database stores 1996 or 2001, and not simply 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 with four-digit years.
Oracle9i, Release 1 (9.0.1), provides daylight savings support for DATETIME
datatypes in the server. You can insert and query DATETIME
values based on local time in a specific region. The DATETIME
datatypes TIMESTAMP WITH TIME ZONE
and TIMESTAMP WITH LOCAL TIME ZONE
are time-zone aware.
See Also:
|
Oracle9i, Release 1 (9.0.1), lets you include the time zone in your date/time data and provides support for fractional seconds. Three new datatypes are added to DATE,
with the following differences:
Datatype | Time Zone | Fractional Seconds |
---|---|---|
|
No |
No |
|
No |
Yes |
|
Explicit |
Yes |
|
Relative |
Yes |
TIMESTAMP WITH LOCAL TIME ZONE
is stored in the database time zone. When a user selects the data, the value is adjusted to the user's session time zone.
A San Francisco database has system time zone = -8:00. When a New York client (session time zone = -5:00) inserts into or selects from the San Francisco database, TIMESTAMP WITH LOCAL TIME ZONE
data is adjusted as follows:
TIMESTAMP'1998-1-23 6:00:00-5:00'
into a TIMESTAMP WITH LOCAL TIME ZONE
column in the San Francisco database. The inserted data is stored in San Francisco as binary value 1998-1-23 3:00:00
.
`1998-1-23 6:00:00'
.
'1998-1-23 3:00:00'
.
Oracle9i SQL Reference for details about the syntax of creating and entering data in time stamp columns
See Also:
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 4 gigabytes in size. They provide efficient, random, piece-wise access to the data. Oracle Corporation recommends that you always use LOB
datatypes over LONG
datatypes.
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:
LOB
columns but only one LONG
column.
LOB
columns can be partitioned, but a table containing a LONG
column cannot be partitioned.
LOB
is 4 gigabytes, but the maximum size of a LONG
is 2 gigabytes.
LOB
s support random access to data, but LONG
s support only sequential access.
LOB
datatypes (except NCLOB)
can be attributes of a user-defined object type but LONG
datatypes cannot.
LOB
s that act like local variables can be used to perform transformations on LOB
data. Temporary internal LOB
s (BLOBs, CLOBs,
and NCLOB
s)
are created in the user's temporary tablespace and are independent of tables. For LONG
datatypes, however, no temporary structures are available.
LOB
columns can be replicated, but tables with LONG
columns cannot.
SQL statements define LOB
columns in a table and LOB
attributes in a user-defined object type. When defining LOB
s 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).
With compatibility set to Oracle9i, Release 1 (9.0.1), or higher, you can use LOB
s with SQL VARCHAR
operators and functions.
See Also:
|
The BLOB
datatype stores unstructured binary data in the database. BLOB
s can store up to 4 gigabytes of binary data.
BLOB
s 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.
The CLOB
and NCLOB
datatypes store up to 4 gigabytes of character data in the database. CLOB
s store database character set data and NCLOB
s store Unicode national character set data. For varying-width database character sets, the CLOB
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
column using a varying-width character set, Oracle converts the data into Unicode before storing it in the database.
CLOB
s and NCLOB
s 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.
You cannot create an object type with NCLOB
attributes, but you can specify NCLOB
parameters in a method for an object type.
See Also:
Oracle9i Globalization Support Guide for more information about national character set data and the Unicode character set |
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. BFILE
s can store up to 4 gigabytes of data.
BFILE
s 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, security, and durability for BFILE
s. The database administrator must ensure that the file exists and that Oracle processes have operating-system read permissions on the file.
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 depends on the use.
RAW
is a variable-length datatype like the VARCHAR2
character datatype, except Oracle Net Services (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, Oracle Net Services 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
statement), 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.
See Also:
Oracle9i Application Developer's Guide - Fundamentals for information about other restrictions on the |
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.
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.
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, nor does the pseudocolumn take up space in the table. 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.
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. The user must ensure that the data stored in the ROWID
column truly is a valid ROWID.
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 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 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;
can return the following row information:
ROWID ENAME ------------------ ---------- AAAAaoAATAAABrXAAA BORTINS AAAAaoAATAAABrXAAE RUGGLES AAAAaoAATAAABrXAAG CHEN AAAAaoAATAAABrXAAN BLUMBERG
An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:
OOOOOO:
The data object number that identifies the database segment (AAAAao
in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.
FFF:
The tablespace-relative datafile number of the datafile that contains the row (file AAT
in the example).
BBBBBB:
The data block that contains the row (block AAABrX
in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.
RRR:
The row in the block.
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).
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;
can return the following row information:
ROWID ENAME ------------------ ---------- 00000DD5.0000.0001 KRISHNAN 00000DD5.0001.0001 ARBUCKLE 00000DD5.0002.0001 NGUYEN
As shown, a restricted rowid's VARCHAR2/
hexadecimal representation is in a three-piece format, block.row.file:
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
See Also:
for more examples using rowids |
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.
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 can 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.
Logical rowids are similar to the physical rowids in the following ways:
ROWID
pseudocolumn.
You can use the ROWID
pseudocolumn to select logical rowids from an index-organized table. The SELECT ROWID
statement returns an opaque structure, which internally consists of the table's primary key and the physical guess (if any) for the row, along with some control information.
You can access a row using predicates of the form WHERE ROWID =
value
,
where value
is the opaque structure returned by SELECT ROWID.
UROWID
datatype
One difference between physical and logical rowids is that logical rowids cannot be used to see how a table is organized.
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.
Collect index statistics with the DBMS_STATS
package or ANALYZE
statement to keep track of the staleness of guesses, so 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
statement, 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), collect index statistics again.
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.
See Also:
Oracle9i Database Performance Guide and Reference for more information about collecting statistics |
Oracle database applications can be executed against non-Oracle database servers using SQL*Connect or the Oracle Transparent 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 Also:
|
The ANSI
datatype conversions to Oracle datatypes are shown in Table 13-2. The ANSI/ISO
datatypes NUMERIC, DECIMAL,
and DEC
can specify only fixed-point numbers. For these datatypes, s
(scale) defaults to 0.
The IBM products SQL/DS, and DB2 datatypes TIME, GRAPHIC, VARGRAPHIC,
and LONG VARGRAPHIC
have no corresponding Oracle datatype and cannot be used. The TIME
datatype is a subcomponent of the Oracle datatype DATE.
Table 13-3 shows the DB2 and SQL/DS conversions.
DB2 or SQL/DS Datatype | Oracle Datatype |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Oracle9i, Release 1 (9.0.1), provides two new datatypes to handle XML data.
NCLOB
or structured object types.
SYSXML,
automatically indexes the XML schema associated with each XML document.
XMLType is an opaque type which supports storage of XML documents in NCLOB
or structured object types.
To use XMLType
with NCLOB
storage, you define a column of XML
type. You can then insert any XML document in this column. The XML data is stored in a NCLOB;
no extra columns are needed.
This XMLType
without any indexes can store any XML document. Because the XMLType
is a LOB
column, it can be imported, exported, and loaded in the same way as regular LOB
columns.
To use XMLType
with structured storage, you specify the following when you define a column of XMLType:
The template specifies the mapping between the XML schema and the object type. The template can be either created by the user or generated by default by the system. This template is registered in the template registry.
The XML
type itself is made into a virtual column and a hidden object type column is created to store the object type instances. A dependency is created on the template and the schema.
When you insert an XMLType
instance into this column, an object instance is created from the XML document, using the template. This object instance is inserted into the hidden column. You can also choose to keep the NCLOB
as real instead of virtual. This is useful where the whole document needs to be kept intact for auditing or verification later. In this case, the data is written to the LOB
and broken into the object instance.
A column defined as an XMLType
mapped to an ADT, can contain only XML documents which conform to that particular XML schema.
In Oracle9i, Release 1 (9.0.1), an XMLIndex
index type is installed under user SYSXML. XMLIndex
automatically indexes the XML schema associated with each XML document, provided the documents conform to existing registered XML schemas.
You can also use XMLIndex
to constrain the list of schemas to which the documents are allowed conform. You specify this in the parameters clause when you create the index type.
In addition to XMLIndex,
you can also create Intermedia Text indexes on the XML data. This lets you use the CONTAINS
operator for more complex searches.
See Also:
|
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. These are some of the functions used:
TO_NUMBER()
TO_CHAR()
TO_NCHAR()
TO_DATE()
TO_CLOB()
TO_NCLOB()
CHARTOROWID()
ROWIDTOCHAR()
ROWIDTONCHAR()
HEXTORAW()
RAWTOHEX()
RAWTONHEX()
REFTOHEX()
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|