Performance of Substr on Clob

clob datatype is causing performace issue

put this code between BEGIN and OPEN p_resultset FOR : this might have some performance issue though.INSERT into GlobalTemp_EMP with inputs ( str ) as (         select to_clob(emp_refno )       from dual     ),     prep ( s, n, token, st_pos, end_pos ) as (       select ',' || str || ',', -1, null, null, 1         from inputs       union all       select s, n+1, substr(s, st_pos, end_pos - st_pos),              end_pos + 1, instr(s, ',', 1, n+3)         from prep         where end_pos != 0     )  select token from prep where token is not NULL;

Substr more than one value in a Oracle CLOB column

If you have XML then treat is as XML, don't try to extract data from it using substrings. Presumably this is a fragment of a larger document that has parent nodes and declares the tns namespace; so you can do:

select x.description
from your_table t
cross apply xmltable(
xmlnamespaces('https://some/url' as "tns"),
'//tns:ExceptionList/tns:Exception'
passing xmltype(t.your_clob)
columns description path 'tns:Description'
) x
























DESCRIPTION
Both HPI Make and HPI Category have to be entered - one cannot exist without the other.<br>
Exception List must be empty
AgreementNumber must not be supplied
IntroducerCode or Name is invalid
SupplierCode or Name invalid. Must exist on pancredit.

Oracle PL/SQL: Performance of CLOB data type in PL/SQL

CLOBs are variable in length, yes. The upper limit varies according to which version of Oracle you are on and your database block size. For 11G the limit is "4G * value of DB_BLOCK_SIZE parameter" (from 11G PL/SQL Language Reference). VARCHAR2 values are limited to 32767 bytes in PL/SQL.

I don't have any definitive information on the relative perforance of CLOB vs. VARCHAR2 in PL/SQL, and a brief Google didn't return anything useful either. However I would strongly suspect that VARCHAR2 performs better than CLOB in general (for data that can be stored in either), since it is simpler. You could easily set up a test to prove this right or wrong by writing the same simple program once with VARCHAR2 and once with CLOB, and running each 1000s of times and comparing the total elapsed time. Tom Kyte's Runstats utility is a great help here, and shows other differences between 2 approaches in terms of Oracle resources.

My advice would be: if your data will exceed 32K then you must use CLOB; if not then don't use CLOB, use VARCHAR2.

NB: if CLOB were better than VARCHAR2 for all sizes of character data, Oracle would probably deprecate VARCHAR2, as they did with the LONG datatype - but they haven't.

oracle, xmltype/clob substr loop

Your arguments to dbms_lob.substr() are wrong. Firstly you probably have the offset and amount the wrong way round - it's the reverse of plain substr() for some reason. And then you're using rec*1000 as the amount, when really you just want 1000 - otherwise each chunk gets longer, which isn't what you want, and would continue to overlap.

So:

    dbms_output.put_line('update test_clob set val = val || ''' ||
dbms_lob.substr(cl, 1000, 1 + ((rec-1) * 1000)) ||''';');

Depending on the tools you are using, there may be built-in tools to do this; SQL Developer and SQLcl can generate insert statements that split CLOB up into manageable chunks, for instance, with:

select /*insert*/ id, val from test_clob;

or

set sqlformat insert;
select id, val from test_clob;

Read more about that client functionality. Other clients probably have similar tricks.

Selecting from clobs

You said you're doing this in PL/SQL, but didn't show any PL/SQL so some of this is guesswork.

That error happens when you are selecting into a variable that is too small for your selection. DBMS_LOB.SUBSTR returns a VARCHAR2, which has a maximum length of 4000 bytes in SQL (by default, extensible to 32767) and 32767 bytes in PL/SQL. I emphasize bytes there because that can be relevant if you are working in a database with a multibyte character set like AL32UTF8.

Even if you weren't hitting the absolute max limit, you can still get that error if the variable you are selecting INTO is too small:

declare
v varchar2(10 char);
begin
select dbms_lob.substr( '12345678901', 11, 1 ) into v from dual;
end;

If you're going to manipulate large CLOBs and break them into pieces, which means always going through an intermediate VARCHAR2, you must account for this. Usually, this means breaking up the CLOB into chunks of manageable size.

How to optimize performance of appending to CLOB in UPDATE?

When you need to update EVERY record in the table it is always faster to recreate table as select (CTAS). Regardless which method you are updating LOB column.

Example:

create table temp
as
select id, first, second||' some_string' as second
from test_tab;

rename test_tab to old_test_tab; -- you can drop it later
rename temp to test_tab;

-- then you need to move all indexes, grants and etc from old_test_tab to test_tab;

Search for a particular string in Oracle clob column

Use dbms_lob.instr and dbms_lob.substr, just like regular InStr and SubstStr functions.

Look at simple example:

SQL> create table t_clob(
2 id number,
3 cl clob
4 );

Tabela zosta│a utworzona.

SQL> insert into t_clob values ( 1, ' xxxx abcd xyz qwerty 354657 [] ' );

1 wiersz zosta│ utworzony.

SQL> declare
2 i number;
3 begin
4 for i in 1..400 loop
5 update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';
6 end loop;
7 update t_clob set cl = cl || ' CALCULATION=[N]NEW.PRODUCT_NO=[T9856] OLD.PRODUCT_NO=[T9852].... -- with other text ';
8 for i in 1..400 loop
9 update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';
10 end loop;
11 end;
12 /

Procedura PL/SQL zosta│a zako˝czona pomyťlnie.

SQL> commit;

Zatwierdzanie zosta│o uko˝czone.
SQL> select length( cl ) from t_clob;

LENGTH(CL)
----------
25717

SQL> select dbms_lob.instr( cl, 'NEW.PRODUCT_NO=[' ) from t_clob;

DBMS_LOB.INSTR(CL,'NEW.PRODUCT_NO=[')
-------------------------------------
12849

SQL> select dbms_lob.substr( cl, 5,dbms_lob.instr( cl, 'NEW.PRODUCT_NO=[' ) + length( 'NEW.PRODUCT_NO=[') ) new_product
2 from t_clob;

NEW_PRODUCT
--------------------------------------------------------------------------------
T9856


Related Topics



Leave a reply



Submit