Re: LOB segment space usage

On Aug 17, 5:29 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> andreik schrieb:
>
> > So why did it need to use 3 blocks (that's 6144 bytes) instead of just
> > 1 to store the poor 2048 bytes?
>
> As yourself mentioned, the data will be stored in the fixed width
> UCS2(like) character set, that means, you store not 2048 bytes, but 2048
> characters, each of them requires 2 bytes of storage.
> You can probably see it if you follow the advice from Sybrand and dump
> your blocks, or may be you would like to read a nice white paper from
> Tanel Põderhttp://integrid.info/Poder_LOB_Internals.pdf
> about LOB internals.
>
> Best regards
>
> Maxim

this is just not right. I have UTF8 as national character set and I store ascii characters, so why does it have to use two bytes for that. But ok, consider another test. This time I used CLOB as the LOB type.

SCOTT_at_andrkydb> truncate table t1;

Table truncated.

SCOTT_at_andrkydb> insert into t1 values (1, rpad('*',2000,'*'));

1 row created.

SCOTT_at_andrkydb> exec show_space('T1_LOB','SCOTT','LOB');

Unformatted Blocks .....................              26
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               2
Total Blocks............................              32
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................              33
Last Used Block.........................              32

It has used 2 blocks to store 2000 characters.

Now when I store 1800, then it needs only 1 block:

SCOTT_at_andrkydb> truncate table t1;

Table truncated.

SCOTT_at_andrkydb> insert into t1 values (1, rpad('*',1800,'*'));

1 row created.

SCOTT_at_andrkydb> exec show_space('T1_LOB','SCOTT','LOB');

Unformatted Blocks .....................              27
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               1
Total Blocks............................              32
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................              33
Last Used Block.........................              32

This way the 2-byte unicode explanation stops to make sence. Since 2048/2 != 1800
In other words it would not be possible to store 1800 bytes in a single block if 2 bytes were used for each character.

Regarding the block dump, what am I supposed to do will all those hex values from the dump file? I am not ready to get THAT deep ;) Received on Fri Aug 17 2007 - 10:26:46 CDT