RE: what are leaf blocks and table high water marks?

>
>can anyone explain what are leaf blocks and table
>high water marks?
>thanks.
>

Leaf blocks refer to indexes, and, more generally speaking, trees (I can only advise you to read Donald Knuth's 'The Art of Computer Programming' volume 3 (Addison-Wesley) if you want to know all about trees - one of the very few really useful computer books IMHO). In the case of Oracle, these are the blocks where you find the rowids which are the physical addresses of the data associated with keys. The other index blocks are 'nodes', basically key values and pointers allowing you to manage your way from the top of the tree down to the leaf block.

The high-water mark refers, as its name implies, to the last block (among those allocated to a table) to have ever contained data. Oracle keeps a track of this block for two reasons at least : 1) when doing a full scan it knows it can stop there, since there is nothing afterwards. It makes quite a difference when you have allocated a huge storage in prevision of future growth but your table still is relatively small. 2) when appending data in a fast mode it can store everything there without having to look for 'holes' resulting from row deletions in the blocks which already contain data - losing space but saving time (the usual trade-off).

Of course, if you delete rows (some of them or all of them) you will be able (all right, there's also PCTUSED but let's keep things simple) to insert data below the high-water mark. The high-water mark is reset (to 0) by TRUNCATE.

HTH, SF

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).