Re: fragmentation and sqexplain
> The Question : what does fragments: 0 vs. fragments: 1 mean ?
This indicates that only one fragment will be searched in executing this query. If it
required multiple fragments, you would see something like "fragments: ALL" or "fragments:
0,1". The number of the fragment that will be searched is 0 in the first case, 1 in the
second case. Fragment numbers are determined by the order in which the fragmentation
expression is specified. In other words, rows that satisfy the first expression are placed
in fragment 0, the second expression in fragment 1, and so on. If you don't have the sql
that created the table, you can do:
select evalpos, exprtext, dbspace from sysfragments
where tabid = (select tabid from systables
where tabname = "your_table")
and fragtype = "T"
order by evalpos;
The value in evalpos corresponds to the number shown in "fragments: 0" in sqexplain.out.
Note that in the first test case, the fragmentation expression you give would place a row
with a date of 1997-02-15 in sub_space1. Since this is the first expression in your
fragmentation scheme, it is referred to as fragment 0. The second test case uses the
month() function, and rows with a date anytime in either February or August will be placed
in sub_space2. It is the second fragmentation expression, so it is fragment 1. The names
of the dbspaces have nothing to do with fragment numbering, only the order of the "fragment
by ... in ..." expression.
> Has it something to do with eliminating fragments (maybe a problem by
> using a function in the fragment expression) ?
Yes, it is exactly related to fragment elimination. I doubt that it is a problem, but just
that the two expressions result in different fragmentation schemes. For performance
reasons, you probably want to minimize the complexity of the fragmentation expression, but
I don't think you'll encounter any major problems with the example you gave.
Mark Collins
mcollins@us.dhl.com
The problem lies in how easily and dangerously we forget that
manipulating things is not the same as understanding them.