Re: Select max fro a table!

In a b-tree, an index block has a directory listing which orders the index entries. The following fragment of an Oracle 8 block dump illustrates the idea:

row#0[1848] flag: ----, lock: 2, data:(6): 01 40 09 90 00 01 col 0; len 3; (3): 41 41 41
row#1[1860] flag: ----, lock: 2, data:(6): 01 40 09 90 00 00 col 0; len 3; (3): 43 43 43
row#2[1836] flag: ----, lock: 2, data:(6): 01 40 09 90 00 02 col 0; len 3; (3): 45 45 45

The table was created with a varchar2() index. I inserted 3 rows, with index values 'CCC','AAA','EEE' in that order.

As you can see from the block dump, the

    'CCC' row starts at the top of the block - byte 1860
    'AAA' starts just below is at byte 1848
    'EEE' starts below that at byte 1836

The entries are packed into clean blocks at the next available space, and space is made wherever possible in dirty blocks.

However, the directory, basically a list of pointers at the start of the block
is constantly shuffled so that an ordered walk through the directory returns the rows in index order: as you can see

    row #0 is 'AAA'
    row #1 is 'CCC'
    row #2 is 'EEE'

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

Markz_at_starnet.lenfest.com wrote in message <36b0be81.527617523_at_news1.fast.net>...
>Most likely, the index is unordered. Given a key, it will return the
>physical location of the record via some hash function or tree
>traversal, however it need not be ordered in the way you
>might expect.
>
Received on Thu Jan 28 1999 - 15:04:39 CST