Re: Index Fragmentation
Prasad Mahale wrote:
>
> Hi,
> I have a performance issue with a fragmented tables/indexes.
>
> My question is how the attached index, which does'nt contain fragmented
> key column, gets fragmented? What strategy it uses? What happens internally?
>
> for example -
>
> CREATE TABLE tb1(a int, b int)
> FRAGMENT BY EXPRESSION
> (a >=0 and a < 5) IN dbspace1,
> (a >=5 and a < 10) IN dbspace2> ...
> ;
> CREATE INDEX idx1 ON tb1(a);
> CREATE INDEX idx2 ON tb1(b);>
> In the above example, index idx2 also gets fragmented across both dbspaces
> whereas table is fragmented on column a.
>
> What strategy it uses to fragment such indexes?
If you don't explicitly locate the index, it will follow the
fragmentation strategy of the table. In this case that is not a good
idea. You need to either locate the whole index in a dbspace using the
IN clause or fragment it by expression on column b.
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /|
| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+