Re: How to tune SQL to avoid ORA-03232 ?

Hi Kenneth,

<kenneth.koenraadt_at_no-spam.hotmail.com> schrieb im Newsbeitrag news:3dad12b3.601765_at_news.mobilixnet.dk...
> Hi Jan,
>
> Please post your execution plan. Hard to find your way through the
> jungle if you don't know where the jungle is....
Sure You are right, and I would liek to post it, but what I saw after EXPLAIN PLAN was the most strange plan I have ever seen: it consisted of 5000++ lines (so, useless to post - it was bad anyway ...).
>
> 2 things, though, are obvious, even without the execution plan :
>
> a)
>
> The predicate :
>
> To_Number(Substr(To_Char(P.Special_Nr),2,4)).
>
> Effectively hinders usage of indexes on the column, unless you have a
> FBI (Function-based index).

Ack. And BTW, I did not know _that_ abbreviation yet. >D
>
> b) The extent size of 32k is very small, certainly too small for such
> a huge sort.

So, what would You suggest ? 100k ? 1M ?
>
>
> - Kenneth Koenraadt

In the meantime, my partner began to rewrite the query allover (he is better in SQL than me ...),
in order to have smaller temporary tables.

But it seems to me a problem that is more general than depending on this query. I observed the same
error tow times in the past, and I donīt know what else than rewriting SQL could be done to avoid ORA-03232.

Is this a common parameter setting error ?

Yours, Jan Received on Wed Oct 16 2002 - 03:14:50 CDT