On 7/7/06, Ben Thompson <ben@nomonitorsoftspam.com> wrote:
>
> Quman wrote:
>
> > The table is not new and the primary key can not be
> > redefined(referenced by other tables already).
>
> You would need to drop the foreign keys that reference it and rebuild
> the primary key or its underlying unique index with a fragmentation
> strategy and then put everything else back. This may be impractical on
> your system.


This is not possible for us now.


> datatype_name is the only possible column to be used as fragmenting
> > key(column), so a global unique index on "inventory_id" can not be
> > fragmented by informix.
> > So, my question is,
> > Is it possible or reasonable to have a fragmented unique index( index
> > keys do not include fragmentation keys) in future Informix?
>
> What version of Informix are you running? I think you're using v10 as
> you're using the partition syntax. From v9 onwards indices and unique
> indices can be fragmented in a way which is independent of the data
> which they point to as data and indices are stored separately. You could
> fragment inventory_id using a schema like:
>
> create unique index "informix".ds_head_ipk on "informix".ds_head
> (inventory_id) using btree fragment by expression
> partition no1 (mod (inventory_id, 4) = 0) in part1,
> partition no2 (mod (inventory_id, 4) = 1) in part2,
> partition no3 (mod (inventory_id, 4) = 2) in part3,
> partition no4 (mod (inventory_id, 4) = 3) in part4;
>
> Of course you can have as many fragments/partitions as you want by
> changing the 4 passed to the mod function and adding more partitions.
> (Mod divides by the number given and gives the remainder.)



Fragmenting a index independantly looks not making much sense to us, since
every time you maintain (drop, recreate, ...) some piece of data, the
corresponding index must also be maintanined. you can not do the data and
its index independantly, right? For example, if I detach or attach a
fragment, the corresponding index must also be deatched or attached. I mean
if the index is fragmented with its data, the maintenance could be faster,
since you need not touch other fragments or index( every thing could be
done locally!).

Thanks,
Frank


Ben.
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>

Quman wrote:
> On 7/7/06, Ben Thompson <ben@nomonitorsoftspam.com> wrote:
> >
> > Quman wrote:
> >
> > > The table is not new and the primary key can not be
> > > redefined(referenced by other tables already).
> >
> > You would need to drop the foreign keys that reference it and rebuild
> > the primary key or its underlying unique index with a fragmentation
> > strategy and then put everything else back. This may be impractical on
> > your system.
>
>
> This is not possible for us now.

> Fragmenting a index independantly looks not making much sense to us, since
> every time you maintain (drop, recreate, ...) some piece of data, the
> corresponding index must also be maintanined. you can not do the data and
> its index independantly, right?

No, this isn't correct. The index isn't attached to the table it is
just fragmented with the same scheme.

create index "informix".ds_head_idx2 on "informix".ds_head
(datatype_name,
inventory_id) using btree ;

To fragment the above index with the same scheme as the table, which
looks like what you are trying to accomplish.

drop index ds_head_idx2 ;
create index ds_head_idx2 on ds_head(datatype_name, inventory_id)
fragment by expression
partition pt1 (datatype_name LIKE 'GVAR%' ) in idx01,
partition pt2 (datatype_name LIKE 'GOES%' ) in idx01,
partition pt3 (datatype_name LIKE 'CW%' ) in idx01,
partition rmd remainder in idx01
;

So, when you run your query part of the index will be eliminated from
consideration.

Hello Quman,

sounds reasonable to me however i am not ibm/informix; you need to
contact them
to get a feature request in.

for what it's worth, it may be very difficult to implement...


see you

Superboer.