Re: Fragement by round robin

On Aug 20, 12:00 pm, mohitanch...@xxxxxxxxx wrote:
Version: IDS 10

Assuming a box has 15 CPU. And table is fragmented to go in 15
dbspaces. Now, if a table is fragmeneted by round robin and say there
are 10 inserts on the same table is going in parallel, then:

How does informix inserts them. Does it inserts them sequentially
because it's round robin or does it inserts them in parallel in 10
dbspaces ?

The access to the 'next fragment' entry is probably single threaded,
but the more intensive insert to the various fragments are made in
parallel.

Having more fragmentation is always better ? In my opinion I think
it's better when fragmented by expression, but depending on how
informix inserts/select/update it might be better for round robin.

Not a simple question. It depends on what you are optimizing for.
For insert speed round robin tends to excel. For querying and
updating specific records fragment by expression allows faster access
and more parallelism with less contention. For larger queries
accessing large subsets of the table, if fragments elimination can
reduce the scope of the search then fragment by expression is best.
For table scans under PDQPRIORITY where fragment elimination doesn't
apply all fragmentation schemes are about the same EXCEPT if
PDQPRIORITY >1 and OPTIMIZATION FIRST_ROWS and an order by matches an
index on the same key then by expression on that sort key for the
index will eliminate the sort in favor of merging the parallel index
returns which may be more responsive for interactive use (though not
neccessarily faster to return the entire query result set).

Art S. Kagel

.

Relevant Pages

  • Re: Fragement by round robin
    ... dbspaces. ... if a table is fragmeneted by round robin and say there ... The access to the 'next fragment' entry is probably single threaded, ... For table scans under PDQPRIORITY where fragment elimination doesn't ...
    (comp.databases.informix)
  • RE: Fragmented table, best way to free pages in DBspace
    ... best way to free pages in DBspace ... Certainly an alter fragment detach is not going to be happy with others ... by round robin into 8 dbspaces. ...
    (comp.databases.informix)
  • Defrag large fragmented table in 7.32
    ... detached index on a different dbspace. ... in 2 brand new separate dbspaces ... I am thinking of doing online ALTER FRAGMENT ON TABLE. ... INIT FRAGMENT BY ROUND ROBIN IN dbspace4, ...
    (comp.databases.informix)
  • Re: alter fragment question
    ... reached the page limit for a single dbspace. ... intending to fragment across four spaces. ... syntax for alter fragment only allows one dbspace at a time to be ... It would be useful to know if it is round robin or actually by expression. ...
    (comp.databases.informix)
  • Fragmented table, best way to free pages in DBspace
    ... that the best way is to run ALTER FRAGMENT ON TABLE T INIT FRAGMENT BY ... ROUND ROBIN clause. ... Snippet from oncheck -pT ...
    (comp.databases.informix)