Home » SQL & PL/SQL » SQL & PL/SQL » Where the cursor's (implicit/explicit) result is stored...?
Where the cursor's (implicit/explicit) result is stored...? [message #353978] Wed, 15 October 2008 23:43 Go to next message
patil.vishwa
Messages: 7
Registered: December 2006
Location: Bangalore,India
Junior Member
Hi,

I understand,

Opening a cursor,
1.Executes the query.
2.Identifies the result set.
3.Positions the pointer to first row in result set.

Fetching from a cursor,
1.Fethces a record from result set to which pointer is
pointing.
2.Advances the pointer to next row.

My question is where does the result set is being stored by
the open statement and from where the fetch statement retrieves.

Look at the below example,

Declare
cursor cl is
select empno from emp where empno='7788';
b number;
begin
open cl; 
delete from emp where empno='7788'; /* Deletes the record(one which is selected from cursor) 
                                   from table.*/
commit;
dbms_lock.sleep(20); /*Sleep for sometime, so that the committed blocks from rollback segments 
                     released (delayed clean out).*/
fetch cl into b; /*Fetch operation shows the result even then there is no actual record in table.*/
dbms_output.put_line('Record :'||b);
end;


By this, I guess result is not directly fetched from the table or rollback segment, might be somewhere else.

Is it PGA (cursor memory area)..?


Could anyone explain, where the result is coming from?

Any guidance's are appreciated.

Regards,
Vishwa.






Re: Where the cursor's (implicit/explicit) result is stored...? [message #353987 is a reply to message #353978] Thu, 16 October 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My question is where does the result set is being stored by
the open statement and from where the fetch statement retrieves.

Nowhere.

Quote:
By this, I guess result is not directly fetched from the table or rollback segment, might be somewhere else.

You're wrong.
First you have to know that in ISO/ANSI "commit" close all cursors, so you should not be able to fetch after this. However Oracle allows it but does not guarantee the result (you may have an ORA-1555 error).
Now assuming that you didn't make the delete in this session but in another, there you can safely commit.
Oracle then fetches the row from the rollback segment (actually reconstruct it from rollback segment information).

Regards
Michel
Re: Where the cursor's (implicit/explicit) result is stored...? [message #353988 is a reply to message #353978] Thu, 16 October 2008 00:46 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
As far as i know oracle store result of cursor in memory.

But i don't know the limit upto which it will store the result
in memory.If the resultset is very large then what i think oracle will not fetch whole data in memory because that going
to be inefficient.

Please try on a large dataset and share the results.

Regards,
Rajat Ratewal



Re: Where the cursor's (implicit/explicit) result is stored...? [message #353991 is a reply to message #353988] Thu, 16 October 2008 00:49 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Thanks michel for clarification.

patil.vishwa please ignore my previous post.

Regards,
Rajat Ratewal
Re: Where the cursor's (implicit/explicit) result is stored...? [message #353993 is a reply to message #353988] Thu, 16 October 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Rajat Ratewal

You're wrong, Oracle does not store the result set anywhere unless there is an operation implying sorting the whole result set (like "order by") then it uses temporary tablespace to sort the result and keep it.

Regards
Michel
Re: Where the cursor's (implicit/explicit) result is stored...? [message #354002 is a reply to message #353993] Thu, 16 October 2008 01:02 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

@Rajat Ratewal

You're wrong, Oracle does not store the result set anywhere unless there is an operation implying sorting the whole result set (like "order by") then it uses temporary tablespace to sort the result and keep it.

Regards
Michel



I know Michel that's why i asked OP to ignore my post../fa/1587/0/

Regards,
Rajat Ratewal
Re: Where the cursor's (implicit/explicit) result is stored...? [message #354007 is a reply to message #354002] Thu, 16 October 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I saw your answer, we posted them at the same time.

Regards
Michel
Re: Where the cursor's (implicit/explicit) result is stored...? [message #354010 is a reply to message #353987] Thu, 16 October 2008 01:28 Go to previous messageGo to next message
patil.vishwa
Messages: 7
Registered: December 2006
Location: Bangalore,India
Junior Member
Hi Michel,

Quote:
Now assuming that you didn't make the delete in this session but in another, there you can safely commit.
Oracle then fetches the row from the rollback segment (actually reconstruct it from rollback segment information).


Look at this analysis,

Time T1:
Session 1:Opens the cursor
a. Execute query and identify the result.
Time T2:
Session 2:Issues a DML (deletes the record which is selected from cursor)
a.Affected blocks are copied to RB segm.
Time T3:
Session 2:Issues a commit
a.Changes are applied back to table(deleted the record).
b.Blocks are cleaned from RB segm immediately or
sometime later(delay clean out).
Time T4: (Note: I'm fetching after sleepin for sometime).
Session 1:fetch from the cursor
a. Gets the record from table,which is already
deleted by session 2.

At T4 no blocks are in table/RB segm,
so where does this record from...?

Correct me if my ananlysis is wrong...!

Regards,
Vishwa

[Updated on: Thu, 16 October 2008 01:29]

Report message to a moderator

Re: Where the cursor's (implicit/explicit) result is stored...? [message #354014 is a reply to message #354010] Thu, 16 October 2008 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
At T4 no blocks are in table/RB segm,
so where does this record from...?

In this case, if Oracle is unable to reconstruct the row, you get an error ORA-01555.

Regards
Michel
Re: Where the cursor's (implicit/explicit) result is stored...? [message #354028 is a reply to message #354010] Thu, 16 October 2008 02:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think your misunderstanding is here:
 Session 2:Issues a commit
a.Changes are applied back to table(deleted the record).
b.Blocks are cleaned from RB segm immediately or
sometime later(delay clean out).


Blocks aren't cleared out of Rollback segments - they stay there until another process needs the space, at which point they're overwritten. Thus the data to reconstruct the previous position can remain available for quite a long time.
But, sooner or later something will overwrite them, and you'll get an ORA-1555
Previous Topic: Issue with Trigger
Next Topic: View for total length in bytes for all columns in tables
Goto Forum:
  


Current Time: Mon Dec 05 08:44:16 CST 2016

Total time taken to generate the page: 0.10728 seconds