| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: BLOBS in the database
Thomas, it would help in this particular case if you read the original post and (rather more importantly) one of the replies. The 106Mb is not "outrageous", but a horrible fact, and the reason it was so is simply because my front end (the Access OLE Bound object control) has an interesting habit of DE-compressing the JPEGs that I was trying to store. And, perfectly reasonably, an 80K JPEG turns into a meg or two of uncompressed image file when that happens. 60 pictures of about the same size, and 106Mb bites the dust.
The issue is therefore the front-end tool, together with the type of files I was loading, and now that I've got that fixed, I get results very similar to yours: ie, the images take up very little extra space than they do on disk.
Thank you for you piece of PL/SQL however, since that's a handy little technique for loading 8000 images without dislocating my mousing finger.
Regards
HJR
-- =============================!!============================= The views expressed are my own only, and definitely NOT those of Oracle Corporation =============================!!============================= "Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:4e3mgt4eopolk80je06m8kblm95v1rt5be_at_4ax.com...Received on Tue May 22 2001 - 21:34:07 CDT
> A copy of this was sent to "George Barbour" <george.barbour_at_gecm.com>
> (if that email address didn't require changing)
> On Tue, 22 May 2001 13:24:09 +0100, you wrote:
>
> >I hope Oracle have an answer to this one Howard, an awful lot depends on
it.
> >Lots of folks are looking for an answer that doesn't involve purchasing a
> >third party tool,
> >with all respect to Ammara's excellent product.
> >
> >George Barbour.
>
> I did not see the original post, or i would have followed up to that
one --
> simply because it is somewhat outrageous.
>
> Any way -- here we go:
>
> 1 create tablespace test datafile 'c:\oracle\oradata\tkyte816\test.dbf'
> 2 size 256k
> 3 autoextend on next 256k
> 4 extent management local
> 5* uniform size 32k
> tkyte_at_TKYTE816> /
>
>
> I have a tablespace, starts very very small -- nothing in it. let it
autoextend
> -- we want to see how much space is really being used. we let it
autoextend
> just a little bit at a time.
>
> Then I:
>
>
> CREATE TABLE "TKYTE"."T"
> ("X" NUMBER(*,0), "Y" BLOB)
> PCTFREE 10 PCTUSED 40
> INITRANS 1 MAXTRANS 255
> LOGGING STORAGE(INITIAL 32768)
> TABLESPACE "TEST" LOB ("Y") STORE AS
> (TABLESPACE "TEST" ENABLE STORAGE IN ROW CHUNK 8192
> PCTVERSION 10 NOCACHE STORAGE(INITIAL 32768)
> ) ;
>
>
> both the table and the blob are in that tablespace.... Now, I have a
datafile:
>
> tkyte_at_TKYTE816> host dir \temp\test.dat
> Volume in drive C has no label.
> Volume Serial Number is F455-B3C3
>
> Directory of C:\temp
>
> 05/22/2001 09:12p 81,371 test.dat
> 1 File(s) 81,371 bytes
> 0 Dir(s) 13,725,458,432 bytes free
>
> that is about 80k in size on my file system. I'll load 50 copies of it
into the
> table:
>
>
> tkyte_at_TKYTE816> create or replace directory my_files as 'c:\temp\'
> 2 /
>
> Directory created.
>
> tkyte_at_TKYTE816>
> tkyte_at_TKYTE816> declare
> 2 l_bfile bfile;
> 3 l_blob blob;
> 4 begin
> 5 l_bfile := bfilename( 'MY_FILES', 'test.dat' );
> 6
> 7 dbms_lob.fileopen( l_bfile );
> 8
> 9 for i in 1 .. 50
> 10 loop
> 11 insert into t values ( i, empty_blob() )
> 12 returning y into l_blob;
> 13
> 14 dbms_lob.loadfromfile( l_blob, l_bfile,
> dbms_lob.getlength(l_bfile) );
> 15 commit;
> 16 end loop;
> 17
> 18 dbms_lob.fileclose(l_bfile);
> 19 end;
> 20 /
>
> PL/SQL procedure successfully completed.
>
> After all is said an done, I find my tablespace is now:
>
>
> Name KBytes Used Free Used
> ----- ------ ------------ ------------ ------
> TEST 4,608 4,576 32 99.3
>
>
> about 4.5meg. Not 106meg -- 4.5 meg. Now I look at the sum of the
lengths of
> my blobs (expecting about 4meg here -- 50 * 80k
>
>
> tkyte_at_TKYTE816> select sum(dbms_lob.getlength(y)) from t;
>
> SUM(DBMS_LOB.GETLENGTH(Y))
> --------------------------
> 4068550
>
> dead on. Looking at the segments themselves (see
>
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:231414051 079
> for a defintion of the show_space routine used below)
>
>
> tkyte_at_TKYTE816> exec show_space( 'T' );
> Free Blocks.............................1
> Total Blocks............................4
> Total Bytes.............................32768
> Unused Blocks...........................2
> Unused Bytes............................16384
> Last Used Ext FileId....................8
> Last Used Ext BlockId...................9
> Last Used Block.........................2
>
> PL/SQL procedure successfully completed.
>
> so the table is using 1 block (total blocks = 4, 1 for overhead by Oracle,
2
> unused -- that leaves 1 block)
>
>
> tkyte_at_TKYTE816> exec show_space( 'SYS_LOB0000032064C00002$$', user,
'LOB' );
> Free Blocks.............................3
> Total Blocks............................556
> Total Bytes.............................4554752
> Unused Blocks...........................0
> Unused Bytes............................0
> Last Used Ext FileId....................8
> Last Used Ext BlockId...................569
> Last Used Block.........................4
>
> PL/SQL procedure successfully completed.
>
>
> and here we see that 556 blocks are used -- with 3 on the freelist (so we
are
> really using 552 8k blocks for the lob data itself)....
>
>
> >
> >"Howard J. Rogers" <howardjr_at_www.com> wrote in message
> >news:3b09e49a_at_news.iprimus.com.au...
> >> Am I doing something horribly wrong here? I have around 100 JPEGs I
want
> >> stored within the database (ie, as a BLOB, not merely a BFILE). Not
one of
> >> these pictures is larger than around 80K. I've just loaded 50 of them,
and
> >> I'm up to 106 *MEGS* of storage.
> >>
>
> you've either totally mucked up your storage parameters or...
>
> >> I haven't done much work with BLOBS before, and I expected a bit of
> >> fluffiness with these things, but not quite so fluffy that I need to
buy a
> >> new hard disk to complete the project, thanks very much!
> >>
> >> 8.1.7 on W2K, graphics are being loaded via an OLE Linked object frame
in
> >> Access, via ODBC. I'm using an 8K block.
> >>
>
> you haven't accounted for the fact that MS wraps GOBS of data about OLE
embedded
> objects -- what does dbms_lob.getlength tell you -- do you have 50 80k
objects
> or (more likely) 50 1meg or more objects.
>
> >> I've seen this same behaviour on, >cough<, SQL Server 2000, so I
suspect
> >> it's an intrinsic feature of the way these things are stored
internally, but
> >> I'm surprised if so that it appears so *very* inefficient. Any
suggestions
> >> for different ways to go about this, gratefully received. The thing
is,
> >> after this I'm scaling up to around 8000 graphics, some rather larger,
and
> >> the whole point is that I do not want 8000 separate JPEG files floating
> >> around on my hard disk.
> >>
> >> Where are the good developers when you need one, huh??!
>
> you don't need a developer, you need a DBA for this one. Unless of course
the
> OLE objects are huge (which they very well could be) then the answer is to
use
> different programming techniques.
>
>
>
> >>
> >> Regards
> >> HJR
> >> --
> >> =============================!!=============================
> >> The views expressed are my own only, and definitely NOT those of Oracle
> >> Corporation
> >> =============================!!=============================
> >>
> >>
> >>
> >>
> >
>
> --
> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> Howtos and such: http://asktom.oracle.com/
> Oracle Magazine: http://www.oracle.com/oramag
> Opinions are mine and do not necessarily reflect those of Oracle Corp
 |
 |