Oracle 8.1.7 : Problem deleting rows with long raws

[derek_botfield]

I am deleting from a table containing
a long raw (contains jpeg images) and the
records are being deleted but the space
is not being reclaimed.

I am told that large entries in long raw
fields can span blocks that mingh have additional information in them and since
they are partially used, these blocks
do not get reclaimed. Can anyone confirm
or deny this?

Anyway, is there any way/strategy for
getting the space back without significant
(any!!??) schema changes.

TIA

Derek

[jkoopmann]

yes, if you are deleting rows that span blocks (of any datatype), if there are other rows that are in those same blocks, those blocks will not go on the free list unless the pctused boundary is crossed.

the question of space not being reclaimed, i would have to ask what you mean by reclaimed. the space is available for updates and inserts if on the free list for that object. it is not given back to the tablespace to be used by other objects unless you "re-org" the object.

as far as a strategy for this object, it is really a determination of how big your block_size is and how big your typicall row size is with the long raw, and then adjusting your pctfree and pctused to hopefully get one row to be contained in full blocks by itself so that when you do have to delete, full blocks are available for the next inserts. this might take a bit more space for your object, but performance will be improved and the issues of inserts, deletes, and updates will be cleaner.

[derek_botfield]

I've got a block size of 8192

pctused = 40 (default)
pctfree = 10 (default)

long raw sizes are between 1.5K and 50K.
Quite a bit of variation here, which I'm
sure makes parameter choice a little
more tricky.

There are 10 other columns in the table:
NUMBER(38) * 2
NUMBER(1) * 2
INTEGER * 2
FLOAT * 4

Any suggestions? I'm not an oracle
expert so be gentle!

[derek_botfield]

I learned enough about Oracle to begin to understand more about space allocation and deallocation.

I now know that once a table has obtained
some space, it does not give that space
up without some administration being
required.

After I performed an
ALTER TABLE XYZ DEALOOCATE UNUSED
I still had thousands of blocks on
the free list and few had been
deallocated.

I figured that fragmentation must be
the issue here; even though the blocks
were on the free list, the still
contained some current information.

I tried the
ALTER TABLE XYZ MOVE NOLOGGING
command but since my table contains
a LONG RAW, that command would not
work.

Ultimately, my band-aid solution was
to use the TRUNCATE command which served
to give back the space. Sacraficed the
images but that was OK. export, delete,
import will work too. But since image
loss turend OK, that was not necessary.

A good thing appears to be to use
BLOB rather than LONG RAW. Then the
ALTER TABLE command can be used.