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
How to Merge the Columns from Two Tables into One Output
Using Group by and Having Clause
In General, Should Every Table in a Database Have an Identity Field to Use as a Pk
From a Sybase Database, How to Get Table Description ( Field Names and Types)
SQL Query for Today's Date Minus Two Months
Create a One to Many Relationship Using SQL Server
How to Avoid Nulls in My Database, While Also Representing Missing Data
SQL Query Where Field Does Not Contain $X
How to Have Multiple "With As" in Single SQL - Oracle SQL
Best Practice on Users/Roles on SQL Server for a Web Application
SQL Statement Using Where Clause with Multiple Values
How to Get a Plain Text Postgres Database Dump on Heroku
Generate a Random Number in the Range 1 - 10
Sqlite - Replace Part of a String
Grant Privileges on Future Tables in Postgresql