Re: How complicated could be a fragmentation expression?
On Fri, Apr 18, 2008 at 12:57 PM, Omar Muñoz <omarmun@yahoo.com> wrote: > "Performance Guide" - in the chapter on fragmentation strategies - says: "Fragmentation > expressions can be as complex as you want. However, complex expressions take > more time to evaluate and might prevent fragments from being eliminated from queries" > > I wonder if expressions like: > mod(field_x,103) between 0 and 30 > field_a=x and field_b = y or field_a=z > mod(field_x,103) in (a lot of values) > > are the kind of complex expression they are talking about. At first glance, I assumed that was one expression - I see now it is three separate ones. I'd want parentheses in the second to disambiguate it to humans; the system will be OK. The first is not dreadfully complex. The second and third are more complex, but not outrageously so. > Usually I would choose a simpler expression > (for example mod (field, one_digit) = x) but if I do > so rows are not balanced across fragments. Should I > choose the complex expression hoping performance won't > be too decreased or should I use the simple one and > deal with the uneven distribution? It depends (at least in part) on how many queries are going to benefit from fragment elimination. Unless your queries generally include a MOD(field_x, 103) term, you will not benefit from fragment elimination with that strategy, so all fragments will be scanned (first and third expressions). With the second expression, there is a far greater likelihood that your query will allow the optimizer to skip fragments (though OR'd terms are not entirely desirable). > My other alternative would be using round-robin, > but since database is an OLTP one, I should create > detached indexes elsewhere. In such case, my question > is: should I fragment indexes in order to improve > searchs (Table has 28M rows and increasing) or it's > better put entire indexes in a separate disk as guide > says? If you're not going to get fragment elimination, then maybe round robin is simpler as the system does the distribution rather than you, so things will be (more or less) evenly distributed without slowing things down by you having to think of complex fragmentation expressions and the server having to calculate them. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.
On 21 apr, 07:06, "Jonathan Leffler" <jleffler.i...@gmail.com> wrote:
> On Fri, Apr 18, 2008 at 12:57 PM, Omar Muñoz <omar...@yahoo.com> wrote:
> > "Performance Guide" - in the chapter on fragmentation strategies - says: "Fragmentation
> > expressions can be as complex as you want. However, complex expressions take
> > more time to evaluate and might prevent fragments from being eliminated from queries"
>
> > I wonder if expressions like:
> > mod(field_x,103) between 0 and 30
> > field_a=x and field_b = y or field_a=z
> > mod(field_x,103) in (a lot of values)
>
> > are the kind of complex expression they are talking about.
>
> At first glance, I assumed that was one expression - I see now it is
> three separate ones.
> I'd want parentheses in the second to disambiguate it to humans; the
> system will be OK.
>
> The first is not dreadfully complex.
> The second and third are more complex, but not outrageously so.
>
> > Usually I would choose a simpler expression
> > (for example mod (field, one_digit) = x) but if I do
> > so rows are not balanced across fragments. Should I
> > choose the complex expression hoping performance won't
> > be too decreased or should I use the simple one and
> > deal with the uneven distribution?
>
> It depends (at least in part) on how many queries are going to benefit
> from fragment elimination. Unless your queries generally include a
> MOD(field_x, 103) term, you will not benefit from fragment elimination
> with that strategy, so all fragments will be scanned (first and third
> expressions). With the second expression, there is a far greater
> likelihood that your query will allow the optimizer to skip fragments
> (though OR'd terms are not entirely desirable).
>
> > My other alternative would be using round-robin,
> > but since database is an OLTP one, I should create
> > detached indexes elsewhere. In such case, my question
> > is: should I fragment indexes in order to improve
> > searchs (Table has 28M rows and increasing) or it's
> > better put entire indexes in a separate disk as guide
> > says?
>
> If you're not going to get fragment elimination, then maybe round
> robin is simpler as the system does the distribution rather than you,
> so things will be (more or less) evenly distributed without slowing
> things down by you having to think of complex fragmentation
> expressions and the server having to calculate them.
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleff...@earthlink.net, jleff...@us.ibm.com
> Guardian of DBD::Informix v2008.0229 --http://dbi.perl.org/
> "Blessed are we who can laugh at ourselves, for we shall never cease
> to be amused."
> NB: Please do not use this email for correspondence.
> I don't necessarily read it every week, even.
i would search for a date/datetime col which is used to determine when
a record was inserted if present...
if that is not present you can potentially rule out 'not evenly'
distribution of data by running (on a testbox with production
data!!!) an update stats high <yourtable> or update stats high
<yourtable> (cols you are interested in)
then use dbschema -d db -t <yourtable> -hd to display how things look
like in your table and based on that
build a fragmentation stragegy?????
Superboer.
way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_-
_oerend_hard.html