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

From: Don Vaillancourt <donv_at_webimpact.com>
Date: Mon, 08 Nov 2004 09:54:46 -0500
Message-ID: <aNLjd.83177$vO1.482802@nnrp1.uunet.ca>

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.

Alberto Dell'Era wrote:
> Don Vaillancourt <donv_at_webimpact.com> wrote:
>
>

>>SELECT SUBSTR (si_large_content, 1, 4290) AS txt
>>   FROM search_index
>>  WHERE si_id = 1002020

>
>
> You are using the "SQL semantics support for LOBs" feature (first
> available in 9iR2 afaik) as described in the "Oracle9i Application
> Developer's Guide - Large Objects (LOBs)". So, a temporary clob is
> created under the covers to contain the result of "SUBSTR
> (si_large_content, 1, 4290)" and returned to the caller.
>
> Since temporary CLOBs live in the temporary tablespace, you need more
> space in the temporary tablespace (the "sort segment" should really be
> called "temporary tablespace segment", it's not used for sorting only
> - it's just a big segment covering all the used-so-far temporary
> tablespace).
>
> I'm not an expert here so i cannot suggest a way to lower the impact
> on the temp tablespace; anyway in the aforementioned doc i've noticed
> some suggestions (check the "Performance Attributes When Using SQL
> Semantics with LOBs" first).
>
> If i needed at most 4000 chars, i would use dbms_lob.substr (that
> returns a varchar2, not a clob) instead of plain substr - but i fear
> that you need more than 4000.
>
> hth
> Alberto Dell'Era

-- 
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv_at_web-impact.com <mailto:donv_at_webimpact.com>
web: http://www.web-impact.com



/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

Received on Mon Nov 08 2004 - 08:54:46 CST