IDS Forum

Re: Index strategy

Posted By: FRANK
Date: Wednesday, 15 August 2007, at 12:14 p.m.

In Response To: Re: Index strategy (vze2qjg5@verizon.net)

This question has been there for a long time.....

I think the primary purpose of fragmenting a table is to ease management!
For example, you can load off the old data ( fragment) to have more space
for the new coming data.
If both data and index are fragmented, then it would be piece of cake to
either take off or add on a fragment because all manipulations are
localized( NO impact to other fragments or indexes !).

The global index is really a contradiction to a fragmented table, it
suffers when either add or subtract a fragment. Unfortunately we do not
have a option here for the Unique index, do we?

Thanks,
Frank

On 8/14/07, vze2qjg5@verizon.net <vze2qjg5@verizon.net> wrote:
>
> It all depends.
>
> If the index is detached, then you are carrying along an extra 4 bytes for
> every row pointed to, since the index now has to point to the chunk/page
> as
> well as the "rowid". If the disk(s) in question is/are fairly active, then
> splitting the index off from the data may reduce load. I'm sure you could
> think up a dozen other things as well.
>
> I would consider that a b-tree index only has a single point of entry, so
> you
> aren't speeding up index traversal by fragmenting it, although you may
> increase throughput (#simultaneous users) by fragmenting it. I would also
> consider the data fragmentation as well. If you have a non-fragmented
> index on
> a fragmented table, then doing an 'alter fragment... detach' will work
> great
> on your data, and then spend hours having to clean up the index.
>
> Considering all that, I would benchmark.
>
> My .02
>
> j.
>
> >From: "Demeis, Tony (MOH)" <Tony.Demeis@ontario.ca>
> >Date: 2007/08/14 Tue AM 10:58:32 CDT
> >To: ids@iiug.org
> >Subject: Index strategy [9776]
>
> >Scenario:
> >IDS9.4
> >A table is fragmented (by expression) across 10 dbspaces.
> >
> >What is the best strategy for building a unique index? Should you
> >create it like a regular non-unique index, that is, where it is created
> >within the same dbspaces as the table. Or, perhaps it should be created
> >in one separate, single dbspace, different from those used by the table
> >and non-unique indexes.
> >
> >Thank you,
> >Tony
> >
> >
>
>
> >*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

Messages In This Thread

• Index strategy
  Demeis, Tony (MOH) -- Tuesday, 14 August 2007, at 11:58 a.m.
  • Re:Index strategy
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 14 August 2007, at 2:10 p.m.
  • Re: Index strategy
    vze2qjg5@verizon.net -- Tuesday, 14 August 2007, at 3:41 p.m.
    • Re: Index strategy
      FRANK -- Wednesday, 15 August 2007, at 12:14 p.m.
      • Re: Re: Index strategy
        vze2qjg5@verizon.net -- Wednesday, 15 August 2007, at 1:22 p.m.
      • Re: Re: Index strategy
        FRANK -- Wednesday, 15 August 2007, at 1:27 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.