Btree Vs. ISAM

Btree Vs. ISAM

Post by vivekc.m.. » Wed, 14 Jan 1998 04:00:00



     I have been told by someone, that the number of Disk i/o is higher in
     btree as compared to isam. is this true..? If yes, why..?

     Vivek

 
 
 

Btree Vs. ISAM

Post by Karl & Betty Schende » Wed, 14 Jan 1998 04:00:00



Quote:>     I have been told by someone, that the number of Disk i/o is higher in
>     btree as compared to isam. is this true..? If yes, why..?

It can be, yes.  RTFM the Database Administrator's guide.  Basically,
ISAM stores the row in the tree, while BTREE stores keys down to a dense
leaf level, and then has to read a data page for the row.  Thus an ISAM
table that is not in overflow can be read with less I/O.  It's keeping
an ISAM table out of overflow that's the trick, basically it has to be
pretty static or it has to be re-modified all the time.

Karl

 
 
 

Btree Vs. ISAM

Post by Alan Spiller » Wed, 14 Jan 1998 04:00:00


BTREE indices will have two or more levels of index above the data;  ISAM
will have one, therefore with perfect (freshly modified) indices the ISAM
will be faster.  But ISAM will pace inserted records in an  overflow area,
requiring a sequential scan of the overflow area if the record was not found
in the indexed area.  In benchmark testing I did a few years ago with a
100,000 row table, a unique prime key of an integer, ISAM and HASH were
equally fast when freshly modified, and BTREE used about twice the elapsed
time for a select.  But after inserting 10% more rows, ISAM was slower then
BTREE.  (No guarantees on the technical details.)


Quote:>     I have been told by someone, that the number of Disk i/o is higher in
>     btree as compared to isam. is this true..? If yes, why..?

>     Vivek

 
 
 

Btree Vs. ISAM

Post by Chip Nickolet » Wed, 14 Jan 1998 04:00:00




Quote:>      I have been told by someone, that the number of Disk i/o is higher in
>      btree as compared to isam. is this true..? If yes, why..?

>      Vivek

Vivek,

The BTREE structure has an extra level of indexing (the leaf pages)
compared to ISAM.  That is generally insignificant when you consider
some of the benefits of BTREE compared to ISAM (e.g., the index is
dynamic, data is guaranteed to be returned in sorted order (allowing
Ingres to eliminate certain sorts during query execution), no overflow
in the data pages, etc.).


Caribou Lake Software  http://www.CaribouLake.com  Java/RDBMS Solutions
Phone:  414-544-9954

When you need the best don't settle for less than Caribou Lake!

 
 
 

Btree Vs. ISAM

Post by Roy Han » Thu, 15 Jan 1998 04:00:00


Quote:>      I have been told by someone, that the number of Disk i/o is higher
in
>      btree as compared to isam. is this true..? If yes, why..?

B-trees have an extra layer of so-called "leaf" pages that you must access
in order to reach your data.  The cost of the leaf page access is greatly
exaggerated IMO though.  Also, the index of a B-tree is maintained
dynamically, so as you insert data you will have to do additional I/O to
make the necessary changes to the leaf pages, and sometimes to the index
pages too.  In contrast the index of an ISAM table is static after it is
constructed, so there is no I/O associated with maintaining it.  

What you were told is true provided the ISAM table has no overflow, but
whether it is tremendously important is another matter.

=================================================================

BKB Client/Server (UK) Ltd.         http://www.bkbcs.com
Tel: +44 (0)411 071844              "OpenIngres & Java Solutions"

 
 
 

Btree Vs. ISAM

Post by Rod Brunke » Wed, 21 Jan 1998 04:00:00


The reasons given in this thread explain the situation very
well.  The question raises a very good point.  I've seen a number
of sites who exclusively use btree as their storage structure.
As another person mentioned benchmarks show for a non-volatile table
ISAM can produce much faster results.

If you know your table wont be added to often, then
ISAM is a far better choice.  Most databases I've seen have a number
of lookup tables of static data. These are ideal candidates.

As the manual says, and I've found this to be very true, a good
guide is:
Static Data, always exact matching on key       - Hash
Static Data, pattern matching etc               - ISAM
Volatile Data                                   - Btree

One other thing, for what it's worth, the number of times I've
seen tables which never hold more than 8 pages of data, defined
as ISAM or btree, is amazing.  Make them heap.

Cheers
Rod
Cynos Consulting


>      I have been told by someone, that the number of Disk i/o is higher in
>      btree as compared to isam. is this true..? If yes, why..?

>      Vivek

 
 
 

1. BTree/ISAM from Softfocus ?

Hello ,

About a year ago I ordered the BTree/ISAM package from Softfocus
Oakville , Ontario , Canada. I received the version 3.1 (multi-user).
In the mean time I developped some applications around it (some for
SCO unix , some for DOS). Recently I have send 2 faxes to Softfocus
and also 1 letter by normal post ... but till now no reaction.
I want to know if the firm still exists (can they be reached via e-mail
?) and is the product still supported (is there a more recent version
and what's the update price ?).

Thanks.

Willy

2. Ingres + Opal

3. Want [ng]dbm/avl/btree/isam type library

4. Services

5. Btree (Isam ?) Library wanted

6. Urgent Help!! AIX user = D3/AIX user ?

7. btree or isam tables

8. square brackets in store procs cause crash - workaround

9. Reports vs ESQL vs C-ISAM

10. RTREE vs BTREE

11. Primary Key vs Btree

12. cBTREE vs BTREE w/ compression=(data,key)

13. cBTREE vs BTREE w/ compression=(data,key) (fwd)