select statement

[Sonia]

hi

Is there any situation where a SELECT statement uses a roll back segment.

If so when ?

Regards
sonia

[julian]

It is possible, example:

select customer_id, function_insert_info(customer_id) from customer;

where function_insert_info inserts some info about that customer somewhere.

[ales]

Originally posted by Sonia
Is there any situation where a SELECT statement uses a roll back segment.

Rollback segment is also used for ensuring read consistency.
A transaction can only see data that were commited before the transaction began.

1. You start a transaction with "update emp set sal=1000 where empno=1"
2. Somebody else updates another salary from 1500 to 2000: "update emp set sal=2000 where empno=2"
3. You select the both employees: "select empno,sal from emp where empno in(1,2)"
You get
1 1000
2 1500
The salary 1500 is taken from rollback segment.

[Sonia]

oh ales.....i know that.....


probably u didn't understood my question.....


Actually i had been for an interview today and they asked me the above question....

But i said.....NO....only DML statemets make use of Rollback segments....

But then i thought when he is asking that question...there must be something behind that.....

Regards
sonia

[oravijay]

Hi,

Insert into table select....
will use a rollback segment..


Vijay.

[dave777]

select from remote database will always use rollback segment.

dave777

[sanjay92]

Julian,
Here is my little test case, Could you give a working example of your statement.

create or replace function f_emp(empno number)
return number
is
begin
insert into tmp values (empno);
return 0;
end;
/


select empno, f_emp(empno) from emp;
select empno, f_emp(empno) from emp
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SANJAY.F_EMP", line 5

Thanks
Sanjay

Originally posted by julian
It is possible, example:

select customer_id, function_insert_info(customer_id) from customer;

where function_insert_info inserts some info about that customer somewhere.

[Edited by sanjay92 on 05-13-2002 at 02:31 PM]

[ales]

Sonia,
I think I understand your question well.
If I had to answer I'd say yes, there is.
When? When the statement attempts to read some data and detects that the data has been modified after actual transaction had started. To get a consistent read, the data must be reconstructed and that's the rollback segment is used for, because the before-change-image of the data is (hopefully still) stored in it.

Ales

[virajvk]

hi soniya

I am not clear with ur question itself

You want ask when Select Statement uses rollback segment or
u want to ask when Select Statement is write into Rollback Segment.


bye
viraj

[julian]

Originally posted by sanjay92
Julian,
Here is my little test case, Could you give a working example of your statement.

Use autonomous transaction to make the insert work.