DIFF ORCL

ROWNUM Vs. ROWID

What is the difference between ROWNUM and ROWID? Does Oracle make a distinction between a ROWID and

 ROWNUM?  

Rownum (Numeric) = Generated Sequence Number of your output. For each row returned by a query, 

the ROWNUM pseudo column 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. 

So you can limit the number of rows in your result.

Rowid (Hexadecimal) = Generated automatically at the time of insertion of row. For each row in the database,

the ROWID pseudo column returns the address of the row.

ROWID	ROWNUM
1.Physical address of the rows.	1.Rownum is the sequential number, allocated to each returned row during query execution.
2.Rowid is permanent	2.Rownum is temporary.
3.Rowid is 16-bit hexadecimal	3.Rownum is numeric
4.Rowid gives address of rows or records	4.Rownum gives count of records
5.Rowid is automatically generated unique id of a row and it is generated at the time of insertion of row.	5.Rownum is a dynamic value automatically
6.ROWID is the fastest means of accessing data.	6.Retrieved along with select statement output.
7.They are unique identifiers for the any row in a table.	7.It represents the sequential order in which Oracle has retrieved the row.

Check the below Query displaying placeholder and physical location of the row.

SQL> SELECT ID, ROWID, ROWNUM From EMP_TEST;




        ID ROWID                  ROWNUM

       102 AAASd8AAEAAAAIcAAB          1

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

       104 AAASd8AAEAAAAIcAAD          2

       110 AAASd8AAEAAAAIcAAJ          5

       105 AAASd8AAEAAAAIcAAE          3

       107 AAASd8AAEAAAAIcAAG          4




       103 AAASd8AAEAAAAIfAAA          6

       106 AAASd8AAEAAAAIfAAB          7

       109 AAASd8AAEAAAAIfAAD          9

       108 AAASd8AAEAAAAIfAAC          8

9 rows selected.

When a ROWID changes?

A ROWID is assigned to a row upon insert and is immutable (never changing). Changes happens in the following situations-

• When you update a partition key and rows moves the partition.
• Flashback a table, since the flashback table command really issues a DELETE+INSERT to put the data back the way it was. 
• If you use alter table test shrink space compact, the ROWID could change.
• When you move table with Alter command- would change the ROWID’s.

SQL> CREATE TABLE MyTab As

  2  SELECT ID, NAME From EMP_TEST Where ROWNUM<=4;




Table created.




ROWID                  ROWNUM         ID NAME

SQL> SELECT ROWID, ROWNUM, ID, NAME From MyTab;




AAAS7GAAEAAAARjAAA          1        102 Ankit

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

AAAS7GAAEAAAARjAAB          2        104 Nikhil

SQL> ALTER Table MyTab MOVE;

AAAS7GAAEAAAARjAAC          3        105 Rajan

AAAS7GAAEAAAARjAAD          4        107 Karan

AAAS7HAAEAAAARrAAA          1        102 Ankit





Table altered.

SQL> SELECT ROWID, ROWNUM, ID, NAME From MyTab;




ROWID                  ROWNUM         ID NAME

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

AAAS7HAAEAAAARrAAD          4        107 Karan

AAAS7HAAEAAAARrAAB          2        104 Nikhil

AAAS7HAAEAAAARrAAC          3        105 Rajan




ORACLE 11g Features

What are the new features added in ORACLE 11g?

Following are the features added in ORACLE 11g for SQL and PL/SQL. 

PL/SQL "CONTINUE" keyword - This will allow a "C-Like" continue in a loop, skipping an iteration to bypass any "else" Boolean conditions.  A nasty PL/SQL GOTO statement is no longer required to exit a Boolean within a loop.

New "PIVOT" SQL clause - The new "pivot" SQL clause will allow quick ROLLUP, similar to an MS-Excel pivot table, where you can display multiple rows on one column with SQL. The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. The UNPIVOT operator converts column-based data into separate rows.

Using Compound Triggers:  In Oracle 11g, the concept of compound trigger was introduced. A compound trigger is a single trigger on a table that enables you to specify actions for each of four timing points:

1.        Before the firing statement

2.       Before each row that the firing statement affects

3.       After each row that the firing statement affects

4.       After the firing statement

With the compound trigger, both the statement-level and row-level action can be put up in a single trigger. Plus, there is an added advantage: it allows sharing of common state between all the trigger-points using variable.

Two popular reasons to use compound trigger are:

1.        To accumulate rows for bulk-insertion.

2.       To avoid the infamous ORA-04091: mutating-table error.

Change Trigger Execution using Follows Clause: 

The Oracle 11g trigger syntax now includes the Follows/Precedes clause to guarantee execution order for triggers defined with the same timing point.  Use FOLLOWS to indicate that the trigger being created should fire after the specified triggers. Follows clause in a trigger specifies that the current trigger would follow the execution of specified triggers. The specified triggers must already exist, they must be defined on the same table as the trigger being created, and they must have been successfully compiled. They need not be enabled.

Creating Trigger in DISABLED mode: 

Oracle 11g provides flexibility to create a Trigger in DISABLED mode also. They remain deactivated until they are enabled. Prior to Oracle 11g, a trigger can be created in ENABLED mode only.

DDL Wait Option: Oracle Database 11g, using DDL_LOCK_TIMEOUT parameter, when a DDL statement in the session does not get the exclusive lock, it will not error out. Instead, it will wait for specified seconds in DDL_LOCK_TIMEOUT parameter. In those seconds, Oracle continually re-tries the DDL operation until it's successful or the time expires.

Read Only Tables: 

In Oracle 11g, a table can be set READ ONLY mode to restrict write operations on the table. A table can be altered to toggle over READ ONLY and READ WRITE modes.

NEW Data Types and Invisible Indexes: Oracle 11g has designed a new data type SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE keeping in view the hardware requirements and expectations with an Integer value. They are compatible with the native compilation feature of Oracle 11g, which makes supports their faster implementation.

An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE or using INDEX hint. Making an index invisible is an alternative to making it unusable. It can be converted to VISIBLE mode for auto consideration by the optimizer

Using Virtual Columns: 

Oracle 11g allows you to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary). They show same behaviour as other columns in the table in terms of indexing and statistics. 

LISTAGG Analytic Functions and REGEXP_COUNT: Oracle 11g has introduced. LISTAGG analytic function to aggregate the result set in multiple rows into one single column.
 Oracle 11g introduced REGEXP_COUNT in regular expressions, used to count the occurrence of a character or string expression in another string.

SGA Result Cache: Oracle 11g has introduced a new component in SGA - Shared Pool as Result Cache to retain result-sets of SQL queries and PL/SQL functions results. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the result set. 

Pragma Inline and Direct Sequence Assignment: A new pragma PRAGMA INLINE has been introduced to specify whether a subprogram call has to be inclines’ or not. IN lining replaces a subprogram call with a copy of the called subprogram
Prior to Oracle 11g, sequence assignment to a number variable could be done through a SELECT statement only. From oracle 11g we can directly assign a sequence value to a PL/SQL variable providing greater simplicity.

Calling Functions and Recursive Subquery Factoring: In Oracle 11g, functions can now be called using Named, Positional and Mixed notation while calling from SQL SELECT statement. Version 11g release 2 introduced recursive subquery factoring or the recursive with clause. This is an extension to the SQL syntax with which you can do recursive/hierarchical queries.