Re: Why is sort segment growing extremely big on simple query?

Don Vaillancourt wrote:

> Are you serious?
>
> I can't retrieve from any CLOBs larger than 32KB? That's not good. I
> remember reading the docs and well this is what I found:
>
> FUNCTION DBMS_LOB.SUBSTR
> (lob_loc IN BLOB,
> amount IN INTEGER := 32767,
> offset IN INTEGER := 1)
> RETURN RAW;
>
> For amount I definitely couldn't go up to 32767 bytes or anything that
> big. I was even told that 4000 bytes was the max which ia about right.
>
> I think that we'll have to write a java class that just reads the record
> through streams.
>
> DA Morgan wrote:
>

>> Don Vaillancourt wrote:
>>
>>> Thank you.
>>>
>>> Will the package function that you specify, dbms_lob.substr, populate 
>>> the temporary tablespace just like the substr, or is it a bit more 
>>> optimized.
>>
>>
>>
>> Hopefully optimized but there is a far more important consideration.
>>
>> You won't be using SUBSTR on anything larger than 32K.

You didn't write DBMS_LOB.SUBSTR ... you wrote SUBSTR which is why I recommended the DBMS_LOB package.

And if I misread what you wrote my apologies.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)