关于 ROWID 和 UROWID

2009-11-18 18:19:52

--------------------------------------------------------------------------------

大家要尊重俺的劳动成果，转载请注明出处。本文首发自http://blog.csdn.net/erazy0/archive/2009/11/18/4829919.aspx

俺的百度空间http://hi.baidu.com/erazy0以及俺的ITPUB博客http://space.itpub.net/22666004/spacelist-blog也会同步更新

欢迎共同学习，交流，本人QQ：398467907    邮箱：zhaizhibin@hotmail.com

欢迎交换友情链接~~

=====================================华丽的分割线=====================================

      先说一下我的机器环境是 centOS4 + Oracle 11g R1。

      今天学习 Using the EXCEPTIONS Table 的时候用到一个脚本，在我的 $ORACLE_HOME/rdbms/admin 目录下有两个脚本，分别是：

 

--------------------------------------------------------------------------------
[oracle@zzb admin]$ vi utlexpt1.sql

Rem
Rem $Header: utlexpt1.sql 24-jun-99.07:59:18 echong Exp $
Rem
Rem utlexpt1.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      utlexpt1.sql -
Rem
Rem    DESCRIPTION
Rem     
Rem
Rem    NOTES
Rem     
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    echong      06/24/99 - rename
Rem    echong      06/05/98 - exceptions table with urowid type
Rem    echong      06/05/98 - Created
Rem

create table exceptions(row_id urowid,
                        owner varchar2(30),
                        table_name varchar2(30),
                        constraint varchar2(30));

 

--------------------------------------------------------------------------------

以及：

 

--------------------------------------------------------------------------------

[oracle@zzb admin]$ vi utlexcpt.sql

rem
rem $Header: utlexcpt.sql,v 1.1 1992/10/20 11:57:02 GLUMPKIN Stab $
rem
Rem  Copyright (c) 1991 by Oracle Corporation
Rem    NAME
Rem      except.sql -
Rem    DESCRIPTION
Rem     
Rem    RETURNS
Rem
Rem    NOTES
Rem     
Rem    MODIFIED   (MM/DD/YY)
Rem     glumpkin   10/20/92 -  Renamed from EXCEPT.SQL
Rem     epeeler    07/22/91 -         add comma
Rem     epeeler    04/30/91 -         Creation

create table exceptions(row_id rowid,
                        owner varchar2(30),
                        table_name varchar2(30),
                        constraint varchar2(30));
~

 

--------------------------------------------------------------------------------

注意两个文件的倒数第四行分别是 create table exceptions(row_id  urowid,  ......

                                       以及 create table exceptions(row_id  rowid,    ......

一个是 rowid 而另一个是 urowid，rowid 我知道， 那么 urowid 是什么呢？

去查了一下最新的Oracle 11g Release2 Reference，就是这个地址：

 http://download.oracle.com/docs/cd/E11882_01/server.112/e10713.pdf 

在43页的 Rowid Data Types 下面有这样一段：

Every row stored in the database has an address. Oracle Database uses a ROWID data
type to store the address (rowid) of every row in the database. Rowids fall into the
following categories:
■ Physical rowids store the addresses of rows in heap-organized tables, clustered
tables, and table and index partitions.
■ Logical rowids store the addresses of rows in index-organized tables.
■ Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed
through a gateway. They are not standard Oracle Database rowids.
A data type called the universal rowid, or UROWID, supports all kinds of rowids.

最后一句说得很清楚了，urowid适用于所有的rowid类型。

所以，很多人都建议: 只有在旧的应用程序中，为了兼容性我们才使用ROWID数据类型。对于新的应用程序，应该使用UROWID数据类型。

.

.

.

.

.

.

PostScripts：再贴一段关于rowid的东西:

Use of Rowids Oracle Database uses rowids internally for the construction of indexes.
Each key in a B-tree 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:
■ Rowids are the fastest means of accessing particular rows.
■ Rowids provide the ability to see how a table is organized.
■ Rowids are unique identifiers for rows in a given table.
You can also create tables with columns defined using the ROWID data type. For
example, you can define an exception table with a column of data type ROWID to store
the rowids of rows that violate integrity constraints. Columns defined using the ROWID
data type behave like other table columns: values can be updated, and so on.
ROWID Pseudocolumn Every table in an Oracle database has a pseudocolumn named
ROWID. A pseudocolumn behaves like a table column, but is not actually stored in the
table. You can select from pseudocolumns, but you cannot insert, update, or delete
their values. A pseudocolumn is also similar to a SQL function without arguments.
Functions without arguments typically return the same value for every row in the
result set, whereas pseudocolumns typically return a different value for each row.
Values of the ROWID pseudocolumn are strings representing the address of each row.
These strings have the data type ROWID. This pseudocolumn is not evident when
listing the structure of a table by executing SELECT or DESCRIBE, nor does the
pseudocolumn consume space. However, the rowid of each row can be retrieved with
a SQL query using the reserved word ROWID as a column name.

THAT'S ALL.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22666004/viewspace-619985/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22666004/viewspace-619985/