Question:
I want to understand how to use dbms_lob.getlength to
estimate the size of a BLOB column in my table. Are there
issues with using dbms_lob.getlength?
Answer: First, there are several methods
for getting the
length of a BLOB, CLOB table column.
Yes, there is one issue with dbms_lob.getlength where
dbms_lob.getlength gives an incorrect answer, where
dbms_lob.getlength does
not give the correct BLOB size. In
this case, you need to pass the argument within double quotes:
Below is a WRONG
invocation of dbms_lob.getlength, using single quotes which
will give you an incorrect answer:
select
dbms_lob.getlength('XML_OUT_BEFORE_XSL')
from
XDB_TEST1.SSZLOB
where
XML_OUT_BEFORE_XSL is not
null;
DBMS_LOB.GETLENGTH('XML_OUT_BEFORE_XSL')
---------------------------------------------
18 ---> This gives
the total number of characters
The actual way is to
call dbms_lob_getlength is by using double quote marks:
select
dbms_lob.getlength("XML_OUT_BEFORE_XSL")
from
XDB_TEST1.SSZLOB
where
XML_OUT_BEFORE_XSL is not
null;
DBMS_LOB.GETLENGTH(XML_OUT_BEFORE_XSL)
---------------------------------------------
8250
There are a few ways to compute the actual data length,
but I prefer the dbms_lob package:
select
dbms_lob.getlength("BLOB_TABLE")
from
blob_mytab
order by
dbms_lob.getlength("BOB_TABLE") desc ;
Here is a working example of computing the row length in a case
where the name and datatype of each row are known in advance:
select
nvl(dbms_lob.getlength("BLOB_TABLE"),0)+1 +
nvl(vsize(CASE_NUMBER
),0)+1 +
nvl(vsize(CASE_DATA_NAME),0)+1 +
nvl(vsize(LASTMOD_TIME_T),0)+1
"Total bytes per row"
from
mytab
where
case = 123;
Some people also try using the dba_lobs
view as an alternative to dbms_lob.getlength:
select
table_name,
column_name,
segment_name,
a.bytes
from
dba_segments a
join
dba_lobs b
using (owner,
segment_name)
where
b.table_name = 'BLOB_TABLE';
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
