IDS Forum

Re: Re: Index strategy

Posted By: FRANK
Date: Wednesday, 15 August 2007, at 1:27 p.m.

In Response To: Re: Index strategy (FRANK)

Unfortunately, this is not always possible......

On 8/15/07, vze2qjg5@verizon.net <vze2qjg5@verizon.net> wrote:
>
> For a unique index, you would have to add the column involved in the
> fragment expression so that you could fragment the index.
>
> j.
>
> >From: FRANK <yunyaoqu@gmail.com>
> >Date: 2007/08/15 Wed AM 11:14:12 CDT
> >To: ids@iiug.org
> >Subject: Re: Index strategy [9789]
>
> >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.
> >>
> >>
> >
> >
>
> >*******************************************************************************
> > 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.