Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: May 23, 2004 - 8:20 pm UTC

Last updated: November 05, 2004 - 11:33 am UTC

Version: 9.2.0.4

Viewed 10K+ times! This question is

You Asked

tom:

This is my last question today.
I am working on a datbase and one of the table may exceed 4000 bytes, but it doesn't happen a lot.

I am thinking instead of using one clob, probably i can use two varchar2(4000) column. I think it may improve performance, what do you think?

and Tom said...

I think if it may exceed 4000 bytes, soon it may exceed 8000.

I think a clob would be the correct approach. A clob will be stored inline as a varchar2 upto 4000 bytes. When and if it exceeds that -- it will be moved out of line.

Hence, in your most common case, when the field is less than 4000 bytes, the clob will be just like a varchar2 in disguise.

In the infrequent case, it will be a clob stored out of line.

So, later when you need to index it with TEXT to search it -- you have a single field. Later when it is decided to allow for 16k of text -- you are ready to go. Later when you are not working on this application -- the coders that follow you have a simple single field to deal with.

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

jim, May 24, 2004 - 4:17 pm UTC

The reason I am asking this questin is because for this specific table, I need insert a lot of row into it. by using varchar2, I can avoid using index, but it seems to me each and every lob needs an index on it.
and for this specific table even though the length of row vary, it rarely more than 4000.
are you saying that if it is less than 4000, clob is strored EXACTLY same as varchar2? i mean no index was created on top of it and insert gives us the same performance?

Tom Kyte
May 24, 2004 - 6:50 pm UTC

benchmark it?

when less then 4000 bytes, and enable storage in row is set (default), then yes, it is stored right there in the block, no index segment, no lob pages.


create table t1 ( x int primary key, y varchar2(4000) );
create or replace procedure p1( p_n in number default 100 )
as
l_data varchar2(4000);
begin
for i in 1 .. p_n
loop
l_data := rpad( '*', mod(i,3500)+1, '*' );
insert into t1 values ( i, l_data );
end loop;
end;
/
create table t2 ( x int primary key, y clob );
create or replace procedure p2( p_n in number default 100 )
as
l_data varchar2(4000);
begin
for i in 1 .. p_n
loop
l_data := rpad( '*', mod(i,3500)+1, '*' );
insert into t2 values ( i, l_data );
end loop;
end;
/



INSERT into t1 values ( :b2, :b1 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 1.37 1.67 4 1214 50656 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 1.37 1.67 4 1214 50656 10000

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 8 0.00 0.00
control file sequential read 24 0.00 0.00
async disk IO 2 0.00 0.00
db file single write 2 0.00 0.00
control file parallel write 4 0.00 0.00
********************************************************************************
INSERT into t2 values ( :b2, :b1 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 1.63 1.73 0 1545 51038 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 1.63 1.73 0 1545 51038 10000

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 12 0.00 0.00
db file sequential read 2 0.00 0.00
async disk IO 1 0.00 0.00
db file single write 1 0.00 0.00
control file parallel write 2 0.00 0.00
********************************************************************************


confused

A reader, May 24, 2004 - 7:53 pm UTC

Here is my output, it seems it confirm my thought that varchar2 do better than clob in terms of performance.
how come my result is so different from yours
I run it twice , here is the result.

first run
====================
********************************************************************************

INSERT into t2
values
( :b2, :b1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 4.93 30.55 7 6175 79436 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 4.93 30.55 7 6175 79436 10000

Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
********************************************************************************

INSERT into t1
values
( :b2, :b1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 10000 1.77 5.65 5 1249 55098 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 1.78 5.65 5 1249 55098 10000

Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)

second run
==============================
INSERT into t1
values
( :b2, :b1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 2 0 0
Execute 10000 2.11 4.89 0 1315 55085 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 2.11 4.96 0 1317 55085 10000

Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
********************************************************************************


INSERT into t2
values
( :b2, :b1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 10000 5.06 30.57 0 6164 79423 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 5.07 30.58 0 6164 79423 10000

Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
********************************************************************************

Tom Kyte
May 24, 2004 - 11:47 pm UTC

you would need to provide all relevant information, db version being somewhat "the most important". OS being relevant.

i used 9204 on linux - as is, pretty much "out of the box". 8k blocksize. 3meg redo log buffer. 64m block buffers.

run it with a 10046 level 12 trace like I did, post the results with waits.

You used my code "as is, no variations" right?

report

A reader, May 25, 2004 - 2:19 pm UTC

I used your code "as is"

Then I issue
exec p1(10000) and exec p2(10000)

db is 9204 on linux,8k block size, 10m redo log buffer, 20000 db_block_bufer, here is the result.

********************************************************************************

BEGIN p1(10000); END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 1 0
Execute 1 1.88 2.14 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.89 2.17 0 0 1 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.21 0.21
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 6.80 6.80
********************************************************************************

INSERT into t1
values
( :b2, :b1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 2.67 2.59 0 1043 34555 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 2.67 2.59 0 1043 34555 10000

Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)
********************************************************************************
********************************************************************************

BEGIN p2(10000); END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 1 0
Execute 1 7.47 20.52 0 6195 50939 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.49 20.54 0 6195 50940 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.10 0.10
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 1.95 1.95
********************************************************************************

INSERT into t2
values
( :b2, :b1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 5.62 18.67 0 9867 59179 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 5.62 18.67 0 9867 59179 10000

Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write (lob) 4055 0.00 0.04
********************************************************************************

Tom Kyte
May 25, 2004 - 2:47 pm UTC 

what linux build?  I mean, my numbers were from a *laptop* for linux -- and I got comparable ones on windows running in vmware on the same.

you see to be getting wildly varying response times.


also, I see direct path write(lob) -- that would indicate to me that the lob was created with "disable storage in row", that is a write to the lob segment which won't happen until the lob exceeds about 4000 bytes.

do this, 

ops$tkyte@ORA9IR2> select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual;
 
DBMS_METADATA.GET_DDL('TABLE','T2')
------------------------------------------------------------------------
 
  CREATE TABLE "OPS$TKYTE"."T2"
   (    "X" NUMBER(*,0),
        "Y" CLOB,
         PRIMARY KEY ("X")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 LOB ("Y") STORE AS (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))


and 

ops$tkyte@ORA9IR2> select min(dbms_lob.getlength(y)), max(dbms_lob.getlength(y)), sum(dbms_lob.getlength(y))
  2  from t2;
 
MIN(DBMS_LOB.GETLENGTH(Y)) MAX(DBMS_LOB.GETLENGTH(Y))
-------------------------- --------------------------
SUM(DBMS_LOB.GETLENGTH(Y))
--------------------------
                         1                       3500
                  16758000

A reader, May 25, 2004 - 3:21 pm UTC 

SQL>  select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3430
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4259
ORA-06512: at "SYS.DBMS_METADATA", line 458
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1



no rows selected

SQL>  select min(dbms_lob.getlength(y)), max(dbms_lob.getlength(y)), sum(dbms_lob.getlength(y)) from t2  2  /

MIN(DBMS_LOB.GETLENGTH(Y)) MAX(DBMS_LOB.GETLENGTH(Y)) SUM(DBMS_LOB.GETLENGTH(Y))
-------------------------- -------------------------- --------------------------
                         1                       3500                   16758000

Tom Kyte
May 25, 2004 - 3:56 pm UTC

Lpx, i know that guy -- what is your character set -- i'll betcha that's the culprit.

just for grins:


select dump('*') from dual;


for me.

Ian Matyssik, May 26, 2004 - 1:44 am UTC

Tom,

I have same problem with DBMS_METADATA.GET_DDL, my database is 9.2.0.5 with AL32UTF8 Character set. Could you please let me know how can that be fixed if possible. And also if you know what problems can happen when using UTF8 as you database character set. I think I also noticed some strange things with dbms_obfuscation_toolkit.

Thank you.

Tom Kyte
May 26, 2004 - 8:09 am UTC

with dbms_obfuscation_toolkit you'll get "strange" things period if you use the varchar2 api (wish they didn't do that). you want to use ONLY raw, only RAW. else character set conversion will kill you every time.


for dbms_metadata throwing errors, please contact support.

here it is

A reader, May 26, 2004 - 2:13 pm UTC 

SQL> select dump('*') from dual;

DUMP('*')
----------------
Typ=96 Len=1: 42

Tom Kyte
May 26, 2004 - 4:37 pm UTC

what is your character set -- i'll betcha that's the
culprit.

A reader, May 27, 2004 - 1:02 pm UTC

it is UTF8, why?

Tom Kyte
May 27, 2004 - 8:30 pm UTC

it must be multi-byte causing the bytes stored for this test to exceed 4000 (else the lob would *not* overflow). Interesting.

do you need utf8 or is a single byte characterset more than good enough?

I would still go with LOB personally.

A reader, July 23, 2004 - 1:22 pm UTC


Update a varchar2 column

Robert, November 03, 2004 - 8:45 pm UTC

Table1(id number, info_target varchar2(2000))
Table2(id number, info_source CLOB)

How can I join-update Table1 so that 2000 bytes of
Table2.info_source is written to Table1.info_target ?

thanks

Tom Kyte
November 05, 2004 - 11:33 am UTC 

assuming a primary/unique constraint on table2(id)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 (id number, info_target varchar2(2000)) ;
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2(id number primary key, info_source CLOB);
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 1, null );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 'hello world....' || rpad( '*', 3000, '*' ) );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update (select info_target, dbms_lob.substr( info_source, 2000, 1 ) data
  2            from t1, t2
  3           where t1.id = t2.id)
  4    set info_target = data;
 
1 row updated.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here