rownum和rowid的概念
来源:互联网 发布:不在江湖知天下 编辑:程序博客网 时间:2024/04/30 02:01
ROWNUM returns a number indicating the sequence in which a row was selected from a table. The first row selected has a ROWNUM
of 1, the second row has a ROWNUM
of 2, and so on. If a SELECT
statement includes an ORDER
BY
clause, ROWNUM
s are assigned to the selected rows before the sort is done.
You can use ROWNUM
to limit the number of rows returned by a SELECT
statement. Also, you can use ROWNUM
in an UPDATE
statement to assign unique values to each row in a table. Using ROWNUM
in the WHERE
clause does not stop the processing of a SELECT
statement; it just limits the number of rows retrieved. The only meaningful use of ROWNUM
in a WHERE clause is
... WHERE ROWNUM < constant;
because the value of ROWNUM
increases only when a row is retrieved. The following search condition can never be met because the first four rows are not retrieved:
... WHERE ROWNUM = 5;
-----------------------------------------------------------------------------------------
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 aSELECT
*FROM
... statement, or aDESCRIBE
... 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 wordROWID
as a column name, for example:SELECT ROWID, last_name FROM employees;You cannot set the value of the pseudocolumn
ROWID
inINSERT
orUPDATE
statements, and you cannot delete aROWID
value. Oracle uses theROWID
values in the pseudocolumnROWID
internally for the construction of indexes.You can reference rowids in the pseudocolumn
ROWID
like other table columns (used inSELECT
lists andWHERE
clauses), but rowids are not stored in the database, nor are they database data. However, you can create tables that contain columns having theROWID
datatype, although Oracle does not guarantee that the values of such columns are valid rowids. The user must ensure that the data stored in theROWID
column truly is a validROWID
.
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:
Rowids are the fastest means of accessing particular rows.
Rowids can be used to see how a table is organized.
Rowids are unique identifiers for rows in a given table.
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.
- rownum和rowid的概念
- rownum和rowid的概念
- RowID和RowNum概念
- rowid 和 rownum 的区别
- Rowid和Rownum的区别
- Rowid和Rownum的区别
- ROWID和ROWNUM的区别
- Rowid和Rownum的区别
- Oracle的rownum和rowid
- rowid 和 rownum的使用
- rownum和rowid的区别
- rowid 和 rownum 的区别
- oracle的rowid和rownum
- Oracle 的ROWID 和 ROWNUM
- Rowid和Rownum的区别
- Rowid和Rownum的区别
- rownum和rowid 的区别
- Oracle查询 rownum和rowid的区别
- isos系统工作笔记6
- 教程:VB6写的32位dll插件升级支持64位Inventor
- spring控制文件,范例
- 开源软件与自由软件的区别
- 静态代码检查工具-FindBugs
- rownum和rowid的概念
- Java基础学习中必须清楚的一些问题
- BlackBerry dev notes:
- 任意不自相交的凹多边形实现三角剖分
- 笔记本与投影仪切换的问题
- exe4j中"this executable was created with an evaluation version exe4j"的解决
- oracle基础
- Ext.tree.TreeLoader异步加载数据
- Openfire配置需求