| 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
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;
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:231414051079 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 CorpReceived on Tue May 22 2001 - 20:27:43 CDT
 |
 |