-1

In Repeatable Read, we can guarantee the same value can be retrieved from same record even the record is queried multiple times. However, setting isolation level is not allowed in defining view. Currently, I am trying to create a view and the SQL statement as follow SELECT * FROM TBL t1 INNER JOIN TBL t2 ON t1.SEQ = t2.SEQ + 1.

As you see, I am querying a table twice within a session. This query can be fast, but I cannot guarantee there isn't any change in this table between these 2 query. How can I keep data consistency in this self-joining?

mkrieger1
  • 19,194
  • 5
  • 54
  • 65
mannok
  • 1,712
  • 1
  • 20
  • 30

1 Answers1

3

Oracle does this automatically.

From the documentation:

Statement-Level Read Consistency

Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent for a single point in time.

The point in time to which a single SQL statement is consistent depends on the transaction isolation level and the nature of the query:

  • In the read committed isolation level, this point is the time at which the statement was opened. For example, if a SELECT statement opens at SCN 1000, then this statement is consistent to SCN 1000.
  • In a serializable or read-only transaction, this point is the time the transaction began. For example, if a transaction begins at SCN 1000, and if multiple SELECT statements occur in this transaction, then each statement is consistent to SCN 1000.
  • In a Flashback Query operation (SELECT ... AS OF), the SELECT statement explicitly specifies the point in time. For example, you can query a table as it appeared last Thursday at 2 p.m.
MT0
  • 143,790
  • 11
  • 59
  • 117