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

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 Received on Sat Nov 06 2004 - 15:01:04 CST