| performance of cursor embedded in select [message #466808] |
Wed, 21 July 2010 04:40  |
akj_dk
Messages: 2
Registered: July 2010
Location: DK
|
Junior Member |
|
|
I've been examining som old queries in an existing db due to more and more problems regarding performance.
The sql is used as backend for a java/jboss webapplication with the possibility for users to enter data. With more and more data, theres starting to come complaints about the performance.
I stumbled upon a select query with an embedded cursor similar to this :
select id, name ...,
cursor(select id, sequence.... from table2),
cursor(select id, name.... from table3)
from table1
join table4 on (table1.id = table4.id)
where .....
The javacode is a prepared statement with the actual sql as a string and the content of the cursors saved in conjunction with each row.
when i use sqldeveloper to show the explain plan without the cursors, the cost is 2428
when i use sqldeveloper to show the explain plan with just 1 of the cursors, the cost is ~165000
Is there a better way to do this instead of cursors ?
|
|
|
|
|
|
| Re: performance of cursor embedded in select [message #467036 is a reply to message #466808] |
Thu, 22 July 2010 01:50  |
akj_dk
Messages: 2
Registered: July 2010
Location: DK
|
Junior Member |
|
|
less than 20000 rows is selected from the main select
The first cursor selects less than 100 rows
The second cursor selects ~8000 rows
The third cursor selects ~47000 rows
But even if i only use the first cursor
the cost is ~165000
and stays at this level if i insert the second and third
cursor
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
