rollback issues. Any ideas

[marist89]

Originally posted by clinton
I mean selecting from a table e.g the EMP table. Does that use rollback segments?

It may, depending on whether data is changing in the EMP table or not.

I guess the big question is how do you use "USE"? [insert obvious Bill Clinton joke here...]

Do you mean is information WRITTEN to the RBS? No.

Do you mean the RBS has some involvement with the transaction? Yes.

[clinton]

The feedbacks are not straight forward.
I need a yes or no answer.

The select is only a select(e.g. sql query to return an out output). Data is not changing, no insert, no update, no delete,etc. Just a plain select statement. There is no modification to the table
EXAMPLE:Users select through a web application. There is no modification to the table. Just a plain select statement.

[santoshym]

Hi

No !!! I dont think it will use any rollback segments for a

select statement

Regards,

[marist89]

Originally posted by santoshym
Hi

No !!! I dont think it will use any rollback segments for a

select statement

Regards,

YES it will. If there are ANY changes, your select might use a rollback segment. Surely, you won't have a database where the data never changes.

[santoshym]

Hi

But Clinton specifically is said there are no updates,inserts or deletes going on. Just a simple SQL statement

Regards,

[marist89]

For this one scenario, a very unlikely event in the real world, no a RBS will not be used. However, I think it is irresponsible to say that SELECT statements don't use RBS. It's simply not true.

[santoshym]

Hi Jeff

I agree with you very unlikely event that there is no update or insert or data change going on in a database but I was just looking at the last post and answering.

No offense meant!!!

Regards,

[jmodic]

Theoreticaly speaking:
Not that SELECT may only READ from rollback segments to get a consistent view of the data, it can also WRITE to RBS.

If you call a PL/SQL function among the column list in your select statement, this function can easily write to some database table (for auditing puprposes, for example), and this will of course be recorded in RBS. And your function must use autonomus transaction for this.

[jrpm]

Unless the statement 'Set Transaction Read Only' is issued prior to the select, the select is presumed to be a select for update. This means the retrieved blocks are stored for a 'before' image during the life of the transaction.

Also, I believe Jeff Hunter is right in that it is Rollbacks that keep the read consistant data for long running queries.

Net Result: Most of the time a query will use rollbacks, but quick queries may not be noticable for monitoring.

Anyway, isn't this becoming moot as 9i has the undo tablespaces replacing rollbacks? At least as an architectural direction.