Re: Strange Behaviour on Index Space Usage, help please

It's something I wrote up a demo for
some time ago and can't find at
present. I don't have time to rebuild the example at the moment, but try this
as an artificial demo: I think the problem was with very large single transaction
deletes.

Create table t (n1 number, v1 varchar2(30)); create unique index on t(n1) pctfree 80; -- just to waste space

insert 100,000 rows into T with n1
ranging from 1,000,001 to 1,100,000

Delete from t where n1 < 1,090,000;
commit;

Should be sufficient to leave you with
dirty blocks in the index waiting for
delayed block cleanout (as opposed to
delayed logging block cleanout). If these blocks are never visited again (you
only query for n1 > 1,100,000, say)
then they won't go on the segment
free list.

If you insert more rows with n1 >1,100,000 the index space will grow.

(as soon as you analyze the index compute statistics, the blocks may be read, cleaned, and put on the freelist though).

You may have to play about with this
to suit your block size and db_block_buffer size.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Mark D Powell wrote in message
<1a961046.dcf9a43b_at_usw-ex0102-015.remarq.com>...



>Jonathan, I believe you, but I am interested in knowing more

>about this so if you can spare the time please explain how a

>block get into this state and how you can detect that this has

>happened?