what is psudocolumn, Rowdepandancy and Norowdepandency in oracle
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
Used to get system related data
There are many number of psudocolumns, few of them listed here.
ROWID
A rowid is assigned to a row upon insert and is imutable (never changing) unless the row is deleted and re-inserted
ROWID which store and return row address in HEXADECIMAL format with database tables.ROWID is the permanent unique identifiers for each row in the database
ROWID consists of 18 character string with the format.
Data Object Number: This identifies the data file in which the row resides.
Relative File Number: This specifies the relative datafile number within the tablespace.
Block Number: This indicates the data block within the datafile where the row is stored.
Row Number: This represents the location of the row within the block.
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
The ROWID is typically used internally by the Oracle database to efficiently locate and access rows. It can be particularly useful when you need to perform low-level operations or optimizations, such as fine-tuning queries or debugging performance issues. However, it’s important to note that the ROWID values are subject to change, especially during maintenance operations like table reorganization or export/import processes.
ROWNUM
The ROW_NUMBER built-in SQL function provides superior support for limiting the number of rows returned by a query or to retrieve a specific subset of rows.
In where clause
rownum <=10
ORA_ROWSCN
ORA_ROWSCN is a pseudocolumn in Oracle databases that provides information about the system change number (SCN) associated with each row in a table
The SCN is a unique identifier that represents a specific point in time when a change was made to the database.
Ora_scn can be converted to timestamp using function scn_to_timestamp
Ora_scn velues always in incremaental
OBJECT_ID
OBJECT_ID is a function that returns a unique identifier for a specified object. This identifier is commonly used to reference and identify various database objects, such as tables, views, indexes, sequences, and other database schema elements.
The OBJECT_ID function takes an object name and an optional object type as parameters and returns the corresponding object identifier
Rowdepandancy and norowdepandency
Row Dependency:
Row dependency refers to the situation where a query depends on specific rows being consistent throughout its execution. If a row being accessed by a query is updated or deleted by another transaction before the query completes, it could lead to inconsistencies in the query results.
Oracle default No Row Dependency when table is created if RowDependency required need to create table with RowDependency keyword
create table t (x var number) rowdependencies;No Row Dependency:
No row dependency means that a query’s execution does not depend on maintaining consistent data in the presence of concurrent modifications by other transactions. When using the SERIALIZABLE isolation level or when explicitly using the FOR UPDATE clause in a query, Oracle employs “no row dependency” behavior. This implies that Oracle may employ locks to ensure that the data being read by one transaction is not modified by another transaction until the first transaction completes.
By specifying NO ROWDEPENDENCIES on a table, you are indicating to Oracle that you do not want it to track row dependencies. This can help reduce overhead in certain situations, especially when you know that the queries against the table won’t rely on row dependencies.
create table t (x var number);
Rowdepandancy and Norowdepandency in concept of ora_rowscn
In the context of ORA_ROWSCN, the concepts of row dependency and no row dependency are related to the way Oracle tracks and manages System Change Numbers (SCNs) for rows, and how these concepts interact with the ORA_ROWSCN pseudocolumn.
The SCN Value changes in block level if it norowdependency (dublicat SCN value is listed), and if it rowdependency (No dublicat SCN values listeed it unique SCN for each row)
Row Dependency with ORA_ROWSCN:
When a row is updated in an Oracle database, its SCN is updated to reflect the time of the modification. If a query uses ORA_ROWSCN to access the SCN of a row, it creates a dependency on that row. This means that if another transaction updates or deletes the row after the SCN is read by the query but before the transaction is committed, it could lead to a row dependency error when the query’s transaction is committed.
This is a form of optimistic locking where Oracle allows transactions to proceed without holding locks on the rows. If the SCN changes before the committing transaction, Oracle detects the row dependency and raises an error, allowing the application to handle the conflict.
No Row Dependency with ORA_ROWSCN:
Using the NO ROWDEPENDENCIES clause when creating a table indicates to Oracle that you don’t want to track row dependencies for that table. In this context, it means that ORA_ROWSCN values are not tracked and queries using ORA_ROWSCN won’t create dependencies on rows. This can be useful when you know that the queries you’re performing won’t rely on SCN values for row consistency.
In summary, the concepts of row dependency and no row dependency with ORA_ROWSCN revolve around whether or not queries that use ORA_ROWSCN create dependencies on rows, potentially leading to conflicts with concurrent updates. By specifying NO ROWDEPENDENCIES, you’re indicating that you don’t need these dependencies for your specific use case, which can help reduce overhead and avoid potential conflicts.
