Home |
Previous Page | Next Page System Catalog Tables >
The sysfragments system catalog
table stores fragmentation information for tables and indexes. One
row exists for each table or index fragment.
The sysfragments table has the following
columns.
| Column |
Type |
Explanation |
| fragtype |
CHAR(1) |
Code indicating the type of fragmented object:
I = Original index fragment
i = Duplicated index fragment (XPS)
T = Original table fragment
t = Duplicated table fragment (XPS)
B = TEXT or BYTE data (XPS)
i = Index fragments of a duplicated table (XPS)
d = Data fragments of a duplicated table (XPS) |
| tabid |
INTEGER |
Unique identifying code of table |
| indexname |
VARCHAR(128) |
Name of index |
| colno |
INTEGER |
Identifying code of TEXT or BYTE column (IDS) Identifying
code of replica identifier (XPS) |
| partn |
INTEGER |
Identifying code of physical location |
| strategy |
CHAR(1) |
Code
for type of fragment distribution strategy:
R = Round-robin fragmentation strategy
E = Expression-based fragmentation strategy
I = IN DBSPACE clause specifies a specific location as
part of fragmentation strategy
T = Table-based fragmentation strategy
H = Table is a subtable witin a table hierarchy (IDS)
Hash-based fragmentation strategy (XPS) |
| location |
CHAR(1) |
Reserved for future use; shows L for local |
| servername |
VARCHAR(128) |
Reserved for future use |
| evalpos |
INTEGER |
Position of fragment in the fragmentation list |
| exprtext |
TEXT |
Expression for fragmentation strategy (IDS)
Contains names of the columns that are hashed and composite information
for hybrid fragmentation strategies; shows hashed columns followed
by the fragmentation expression of the dbslice. (XPS) |
| exprbin |
BYTE |
Binary version of expression |
| exprarr |
BYTE |
Range-partitioning data to optimize expression
in range-expression fragmentation strategy |
| flags |
INTEGER |
Used internally (IDS)
Bitmap indicating a hybrid fragmentation strategy (value = 0x00000010)
(XPS)
Also, an additional flag (value = 0x00000020) is set on the first
fragment of a globally detached index. (XPS) |
| dbspace |
VARCHAR(128) |
Name of dbspace for fragment |
| levels |
SMALLINT |
Number of B+ tree index levels |
| npused |
INTEGER |
For table-fragmentation strategy, npused is
the number of data pages; for index-fragmentation strategy, npused is
the number of leaf pages. |
| nrows |
INTEGER |
For tables, nrows represents
the number of rows in the fragment; for indexes, nrows represents
the number of unique keys. |
| clust |
INTEGER |
Degree of index clustering; smaller numbers correspond
to greater clustering. |
| hybdpos |
INTEGER |
Contains the relative position of the hybrid
fragment within a dbslice or list of dbspaces associated with a particular
expression. The hybrid fragmentation strategy (and the set of fragments
against which the hybrid strategy is applied) determine the relative position.
The first fragment has a hybdpos value of zero
(0). (XPS) |
| partname |
VARCHAR(128) |
Name of partition within dbspace for
fragment (IDS) |
In certain situations, you can duplicate selected tables across
coservers to improve performance. If you have a duplicate copy of
a small table on each coserver, then the database server can execute
some small queries (queries that do not need rows from a table fragment
on any other coserver and that do not require more than 128 kilobytes
of memory per operator) as serial plans instead of as parallel plans
that the Resource Grant Manager (RGM) manages.
This performance feature applies only to OLTP-type
transactions.
The following query returns the owner and name for each of the
duplicated tables in the current database:
SELECT DISTINCT st.owner, st.tabname
FROM systables st, sysfragments sf
WHERE st.tabid = sf.tabid AND sf.fragtype = 't'
For more information about duplicating tables, refer to the description
of the CREATE DUPLICATE statement
in IBM Informix: Guide to SQL Syntax.
The strategy type T is used for attached indexes. (This is a fragmented index whose
fragmentation is the same as the table fragmentation.)
In Dynamic Server, a composite index on the fragtype, tabid, indexname,
and evalpos columns allows duplicate values.
In Extended Parallel Server, a composite index on the fragtype, tabid, indexname, evalpos,
and hybdpos columns allows duplicate values.
Home |
[ Top of Page | Previous Page | Next Page | Contents |
Index ]