| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Statement
In article <32EE4489.39E7_at_UWOADMIN.UWO.CA>, Paul Ferrie <DASPAF_at_UWOADMIN.UWO.CA> wrote:
> Can anyone tell me why this is happening and what can be done to rectify
> this problem...
>
> I've got the following SQL...
>
<snip...>
> When I execute this, it works just fine up to a point! If the
> sub-select returns less then ~8000 rows, the primary select excutes very
> quickly. However, if the sub-select returns more than ~8000 rows, the
> primary select goes into a sweep. The DBA's don't know why this is
> happening! Is there something at the ORACLE side that needs to be set
> (eg. buffer sizes?, temp space?, indexes?). I've also noticed that if I
> execute the sub-select on its own and return less than ~8000 rows and
> then re-execute right after, the results come back instantly. However,
> if the sub-select returns more then ~8000 rows and I re-execute it, it
> takes the same amount of time as the original select (ie. the original
> results are not held in the buffer).
You may want to take a look at your block buffers; it sounds like ~8000 rows are all you can handle before the buffers have to start aging out. Also, the join technique used in another reply would help; correlated queries tend to be more efficient than sub-queries. Also, check your sort area size; if a merge-sort join is being executed, this could have an impact.
Most of all, check out the execution plan for the query.
Hope you meet your challenge.
David Scott ...how can I keep from singing?
bumper snicker: My other guitar is a Martin (D16H)
email: dlscott_at_mindspring.com
 |
 |