IDS Forum

RE: compressing indexes

Posted By: Uri Haham
Date: Thursday, 30 December 2004, at 3:51 a.m.

In Response To: Re: compressing indexes (Khaled Bentebal)

Hi

You can consider to partition the index, use range or expression on the first keys of the index.
A good expression function for numeric and date can be mod function.
By partition the index you divide the index into several smaller indexes.
If you partition using the first keys of the index the optimizer use only the relevant partition of the index.

Uri

-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Khaled Bentebal
Sent: Wednesday, December 29, 2004 5:16 PM
To: ids@iiug.org
Subject: Re: compressing indexes [3935]


Hi,

The numbre of levels in a B-TREE depends on the number of items in the
index, the size of the key, and the life of the index (B-TREE).

There is no specific parameter except may be for the FILLFACTOR onconfig
parameter.

If you want to have the most optimized B-TREE structure, set FILLFACTOR to
100 (but this won't be great for indexes that are very volatile), use the
minimum size fields for your index, and recreate (drop the index and create
it again) the index whenever you can. As the index lives, its depth might
increase to keep it balanced at all times.

So the best thing you can do it to recreate the index whenever possible.

You should realize that for a single access through an index that is 4 or 5,
you won't notice the difference, however for a batch that does a lot
(thousands to millions ) of accesses, you will notice a difference.

I hope that this helps.

Khaled Bentebal
Tél: 33 (0) 1 39 72 17 00
Fax: 33 (0) 1 39 72 17 01
Mobile: 33 (0) 6 07 78 41 97
Email: khaled.bentebal@consult-ix.fr
Site Web: http://www.consult-ix.fr

----- Original Message -----
From: "Floyd Welle...." <fwellers@yahoo.com>
To: <ids@iiug.org>
Sent: Wednesday, December 29, 2004 12:45 PM
Subject: compressing indexes [3929]


> Does anybody know how to lower the number of levels in a btree index ?
> We have some that have 4 and 5 levels, and I read that is a performance
hit.
>
> Thanks,
>
>
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
> email: fwellers@yahoo.com
> Work: 703-733-4126
> Pager: 703-705-9241
> Email Pager: 7037059241@my2way.com
> Home: 703-430-0805
> Cell: 703-477-6045
> ========================
>
>
>
>



***********************************************
This Mail Was Scanned By Mail-seCure System in
Matrix Herzeliya
***********************************************

Messages In This Thread

• compressing indexes
  Floyd Wellershaus -- Wednesday, 29 December 2004, at 6:45 a.m.
  • Re: compressing indexes
    Khaled Bentebal -- Wednesday, 29 December 2004, at 10:15 a.m.
    • RE: compressing indexes
      Uri Haham -- Thursday, 30 December 2004, at 3:51 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.