Nov 27, 2014

ROWID & ROWNUM Pseudocolumn

What is ROWID Pseudocolumns ::
For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:
  • The data object number of the object
  • The data block in the datafile in which the row resides
  • The position of the row in the data block (first row is 0)
  • The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.
Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.
Values of the ROWID pseudocolumn have the datatype ROWID or UROWID. Please refer to "ROWID Datatype" and "UROWID Datatype"for more information.
Rowid values have several important uses:
  • They are the fastest way to access a single row.
  • They can show you how the rows in a table are stored.
  • They are unique identifiers for rows in a table.
You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.
Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

Don't use ROW_ID with MIN and MAX as MAX(ROWID) won't  always return me last inserted row because that row should not  have the biggest ROWID value.
The value of ROW_ID is not in sequence but it's only unique value.

What is ROWNUM Pseudocolumns ::
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT * FROM employees WHERE ROWNUM < 10; 

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:
SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force theROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:
   (SELECT * FROM employees ORDER BY employee_id)

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.
Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM employees

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
You can also use ROWNUM to assign unique values to each row of a table, as in this example:
UPDATE my_table
    SET column1 = ROWNUM;

Please refer to the function ROW_NUMBER for an alternative method of assigning unique numbers to rows.

Sources ::

Nov 26, 2014

Oracle Business Intelligence Developers Guide (Oracle Press)


Main description

Master Oracle Business Intelligence 11g Reports and Dashboards
Deliver meaningful business information to users anytime, anywhere, on any device, using Oracle Business Intelligence 11g. Written by Oracle ACE Director Mark Rittman, Oracle Business Intelligence 11g Developers Guide fully covers the latest BI report design and distribution techniques. Find out how to execute effective queries, build accurate models, use scorecards and KPIs, create dynamic reports, set up dashboards, and publish to smartphones and wireless devices. This Oracle Press guide contains comprehensive details on Oracle Exalytics In-Memory Machine, the best-in-class, preintegrated BI platform.
  • Install or upgrade to Oracle Business Intelligence 11g
  • Develop and manage custom Oracle Business Intelligence repositories
  • Access relational, file, and multidimensional data sources
  • Design print-quality reports with Oracle Business Intelligence Publisher
  • Create web-enabled analyses, dashboards, and visualizations
  • Integrate with other applications using Oracle Business Intelligence 11g Action Framework
  • Employ authentication, authorization, and row-level security
  • Configure and deploy Oracle Exalytics In-Memory Machine

Table of contents

1. Overview and Product Architecture
2. Installations and Upgrades, for the Oracle BI Developers Handbook
3. Creating Repositories from Relational Sources
4. Creating Repositories from Oracle Essbase and Other OLAP Data Sources
5. Configuring and Maintaining the BI Server
6. Creating Reports Using Answers and Dashboards
7. Actionable Intelligence
8. KPIs and Scorecards
9. Creating Published Reports (BI Publisher)
10. Systems Management
11. High Availability, Clustering and Backup & Recovery
12. Managing Change, Versioning and Promotion Between Environments
13. Oracle Exalytics Business Intelligence Machine

Author comments

Mark Rittman, Oracle ACE Director, is Technical Director and co-founder of Rittman Mead, specializing in BI, DW and EPM solutions using Oracle Business Intelligence, Oracle Database, Oracle Data Integrator and Oracle Essbase. Mark is a strong supporter of Oracle user groups around the world, was previously an executive board member of ODTUG and speaks regularly at conferences in the UK, Europe, USA and around the world. Mark also writes for the Rittman Mead blog ( and contributes a regular column on business intelligence for Oracle Magazine.

Original Source::

OraApps Search

Custom Search

Search This Blog