performance compare -> insert select and insert select using cursor [message #445602] |
Tue, 02 March 2010 22:23 |
yurkoz
Messages: 26 Registered: September 2009 Location: bronx
|
Junior Member |
|
|
Hi everyone.
I make insert into my_table_1 select <some calc> > from my_table_2
select consume a lot of resources and takes time.
1 th question-> performance will be improved if I use cursor?
2 nd question
if I use cursor and during insert my_table_2 will be
updated . my_table_1 will notice this change or it
depends where cursor was during cursor execution
Thanks
|
|
|
|
|
|
|
|
Re: performance compare -> insert select and insert select using cursor [message #445610 is a reply to message #445608] |
Tue, 02 March 2010 23:02 |
yurkoz
Messages: 26 Registered: September 2009 Location: bronx
|
Junior Member |
|
|
black swan
as I know when sql run it takes all related data into the memory
from the table.
because if update happend on that data, sql did not notice it.
It bring data relly on memory data.
About cursor. I am not sure. It is possible ( I do not know )
that cursor scaning real table, row after row. And after it finish work with particular row it reads next row from real table.
unix I am working with is overload. And I am interesting toknow what happend inside ORACLE in cursor cases.
Thanks
|
|
|
|
|
|
|
|
Re: performance compare -> insert select and insert select using cursor [message #445628 is a reply to message #445610] |
Wed, 03 March 2010 00:55 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
yurkoz wrote on Wed, 03 March 2010 06:02black swan
as I know when sql run it takes all related data into the memory
from the table.
because if update happend on that data, sql did not notice it.
It bring data relly on memory data.
I agree with Michel: you should read the concepts guide. Looks like you have a wrong idea on how stuff works.
|
|
|
Re: performance compare -> insert select and insert select using cursor [message #445650 is a reply to message #445610] |
Wed, 03 March 2010 03:02 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:black swan
as I know when sql run it takes all related data into the memory
from the table.
because if update happend on that data, sql did not notice it.
It bring data relly on memory data.
As has been pointed out, this is not what happens.
What actually happens is that Oracle takes a note of the state of all the tables involved in the SQL when it is executed. When the query returns rows, Oracle will internally reverse any changes committed to the tables after that point in order to return all the data to you as it was when you started the query.
Quote:About cursor. I am not sure. It is possible ( I do not know )
that cursor scaning real table, row after row. And after it finish work with particular row it reads next row from real table.
Cursors are just pieces of SQL - the same rules apply. If your cursor is open for a long period, and there are lots of other transactions being committed, then it is possible to get an ORA-01555 error where Oracle loses the data neccessary to reconstruct the state for your query, but this is fairly rare.
|
|
|
Re: performance compare -> insert select and insert select using cursor [message #445717 is a reply to message #445650] |
Wed, 03 March 2010 09:43 |
yurkoz
Messages: 26 Registered: September 2009 Location: bronx
|
Junior Member |
|
|
JRowbottom Thank you for answer.
As I understand your message ,ther is no difference in execution
when we make insert -> select and insert select using cursor.
But what do you mean oracle makes note of state?
if some record has been update during sql execution , oracle
should find way to bring previous record before update happend.
Or update will not happen , before oracle save somewhere the record that should be updated by another update statement during execution
do you mean Server use UNDO tablespace?
[Updated on: Wed, 03 March 2010 09:50] Report message to a moderator
|
|
|
|
|
|