Re: Question regarding deleting data.

floyd5432_at_yahoo.com (Floyd) wrote in message news:<277f4085.0305070835.7faca901_at_posting.google.com>...
> Hi,
>
> I've a problem with deleting data from an oracle 8i database.
> I've a java program that goes and deletes data from the database
> (typically about 20-50 rows spread accross 20 tables).
>
> Now the problem is when I delete the data, the space that is freed up
> does not show up as free space. Like after deleting 10000 rows for
> example, the amount of space in the tablespace and the amount of space
> in the index, before and after the delete is the same. Is this
> something to do with High Water Mark settings?

Probably. Whether it is important depends on how your data is distributed. Oracle will reuse the space if it can. If you have a steadily increasing primary key and delete the earlier rows, you could wind up with indexes with a lot of empty buckets. What Oracle balances in b-tree indices is the buckets that contain the keys, not generally rearranging things when the keys are deleted. Besides that, if you delete a whole lot of data and then do full table scans, you are scanning a lot of empty blocks unnecesarily.

There are a number of ways to reset the high-water mark, the simplest (if you don't have a bunch of foreign keys) is usually to export the table, truncate it, then re-import it with ignore=y. Be sure the initial and next extent settings for the table are proper before you do this, and generally you want to use compress=N on the export (compress=Y is used for pre-extending a table segment, which should only be done if you know exactly what you are doing).

>
> How do I delete the data and make the space made available count
> towards the database free space.
>
> Thanks,
>
> Anwar.

jg

--
@home.com is bogus.
"See your DBA."