Re: Does index need rebuilding when table is truncated

Beg to differ.

The data segment for an index does have an HWM - it's just that nobody talks about it very much.

Nevertheless if you dump the segment header block you will see an entry for the "highwater"; and if you have an 'index fast full scan' access path occurring, then the scan goes up to the HWM. Furthermore when you truncate the table, the HWMs for the table and its indexes are reset in an identical fashion.

Thinking laterally to your Urban Legends thread - there are several statements made about Oracle's lower-level behaviour which are "incorrect by omission" because they only describe the behaviour of table data blocks, and ignore the fact that indexes consist of data blocks which are sometimes handled differently - (although as in this case they are sometimes handled identically to the table).

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 09 April 2002 23:31

|There is not a HWM indicator for an index as there is for a table.
|When a truncate occurs, the blocks are cleared in the index and the
|extents (beyond the initial extent) are deallocated (assuming you do
not
|issue the reuse storage clause).
|
|RF
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).