Re: -- advantage BLOB or file on file system --

From: Gary Menchen <gary.e.menchen_at_dartmouth.edu>
Date: Mon, 26 Jan 2004 08:12:18 -0500
Message-ID: <bv33pn$d76$1@merrimack.Dartmouth.EDU>

REM wrote:
> I have some problems. It's dilemma between BLOB, BFILE and takes care of
> file on file system. What it's advantage??
> I import some PDF document in BLOB filed in my database. It's about 1000
> documents on day.
>
> My first question is: if I have a PDF on file system about 100 kb and if I
> import this in BLOB field in database. How much it's take more recourse.
>
> How I can measure largeness of BLOB field or how I can measure spacious of
> the table?
>
>
> --
> Thanks,
>
> ilica
>
> _________
>
> "If A is a success in life, then A equals x plus y plus z. Work is x; y is
> play; and z is keeping your mouth shut."
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.515 / Virus Database: 313 - Release Date: 1.9.2003
>
>

LOBS are written in blocks of "chunksize", which I think is configurable on a table by table basis but for which there is also a default value. Typical sizes range from 8 to 32K, so there may be some surplus space allocated for blobs - this is true for files in a file system as well.

It is also possible to store blobs and clobs inefficiently if your writes are not size to match some multiple of the chunksize - although this doesn't seem to be as bad in 9i as in earlier versions it is still worthwhile doing writes in whatever multiple of chunksize is less than or equal to the largest raw you can write.

This query ought to give you information about storage.

select table_name, column_name,ul.segment_name,chunk,us.tablespace_name,

         us.bytes,us.blocks,us.extents,us.initial_extent,
         us.next_extent,us.min_extents,us.max_extents

    from user_segments us, user_lobs ul
   where ul.segment_name = us.segment_name      and ul.table_name = <<< your table name here >>>

I prefer blobs and clobs over bfiles - they get exported, backed up with database, etc etc Received on Mon Jan 26 2004 - 07:12:18 CST