Hannu Krosing wrote:
Ühel kenal päeval, K, 2007-03-14 kell 10:22, kirjutas Heikki
Linnakangas:
Clustered indexes have roughly the same performance effect and use cases
as clustered indexes on MS SQL Server, and Index-Organized-Tables on
Oracle, but the way I've implemented them is significantly different. On
other DBMSs, the index and heap are combined to a single b-tree
structure. The way I've implemented them is less invasive, there's no
changes to the heap for example, and it doesn't require moving live tuples.
Do you keep visibility info in the index ?
No.
If there is no visibility data in index, then I can't see, how it gets
the same performance effect as Index-Organized-Tables, as lot of random
heap access is still needed.
Let me illustrate the effect in the best case, with a table that
consists of just the key:
Normal b-tree:
Root -> leaf -> heap
aaa -> aaa -> aaa
bbb -> bbb
ccc -> ccc
ddd -> ddd -> ddd
eee -> eee
fff -> fff
ggg -> ggg -> ggg
hhh -> hhh
iii -> iii
Clustered b-tree:
Root -> heap
aaa -> aaa
bbb
ccc
ddd -> ddd
eee
fff
ggg -> ggg
hhh
iii
The index is much smaller, one level shallower in the best case. A
smaller index means that more of it fits in cache. If you're doing
random access through the index, that means that you need to do less I/O
because you don't need to fetch so many index pages. You need to access
the heap anyway for the visibility information, as you pointed out, but
the savings are coming from having to do less index I/O.
How close to the best case do you get in practice? It depends on your
schema, narrow tables or tables with wide keys gain the most, and on the
clusteredness of the table.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
