The Index Utility Ixutil
The ixutil utility supports reorganizing an SPD Server
hybrid index to improve query performance and minimize disk space.
The utility also prints the disk usage statistics or the contents
of indexes.
Ixutil Usage
ixutil -crejidx
<data set1,column1>
< data set2,column2> ...
<data set_n,column_n>-libpath
<physical path>
-joinparts
< number of parallel
join work units > ;
Create a join index
for a pair of data sets in the same domain that can be used by the
SPD Server Parallel Range Join optimization. The columns must already be indexed. The recommended number of parallel
join work units is two times the number of processors.
ixutil -deljidx
<data set1,column1>
< data set2,column2> ...
<data set_n,column_n>-libpath
<physical path>
;
ixutil -lstjidx -libpath
<physical
path> [
-verbose] ;
List the join indexes
in a domain.
ixutil -statjidx
<data set1,column1>
< data set2,column2> ...
<data set_n,column_n>-libpath
<physical path>
;
Gather statistics about
the join index parts. Pay attention to the average join row percentage,
which indicates the average number of rows that are read by a parallel
join work unit. For example, a percentage of 75 indicates the parallel
join work unit will use 75 percent of the rows it must read. The closer
the percentage is to 100, the better the performance. The percentage
will increase as the distribution of the data for the join column
becomes more sorted.
ixutil -stats
< indx1,indx2,...>
-dsn
<data set>
-libpath
<physical path> [-dist
] ;
For a specified set
of indexes that belong to a given table, print the disk usage statistics
and segment list fragmentation statistics. Each value in the index
has a segment list. A value's segment list can become fragmented when
the index is updated. An index that is highly fragmented can degrade
query performance and waste disk space. To improve performance and
reclaim the wasted disk space, the index should be reorganized using
the -reorg
option of ixutil.
ixutil -runstats
<indx1,indx2,...>
-dsn
< data set name>
-libpath
<physical path> [-maxruns
<number>] ;
For a specified set
of indexes that belong to a given table, print the run statistics
for each index. Run statistics give an indication of how the values
of a particular index are sorted in relation to their observation
numbers. By default, ixutil run stats display the ten longest runs
in the data set. A run is defined as the number of successive observations
that contain the same index value. The optional [-maxruns<number>] argument can be used to change from the default setting
of 10 runs to any integer between 1 and 100. Ixutil run stats can
be useful in constructing more efficient BY and WHERE clause constructs
for the data set.
ixutil -reorg
<index1,index2,...>
-dsn
< data set name>
-libpath
<physical path> ;
Reorganize the specified
set of indexes in a table to reclaim wasted disk space and to aggregate
the per-value segment lists. Reorganizing an index results in optimal
disk usage and query performance.
ixutil -fixclustmem -dsn
<data set
name>
-libpath
< physical path>
Make cluster member
tables accessible, if the CDF metadata is corrupted or deleted. Run
this command for each member table in the cluster, and then remove
the .cdf file from the directory path.
Ixutil Options
-crejidx
<data set1,column1>
< data set2,column2> ... <data set_n,column_n>
Create a join index
for SPD Parallel Join use.
-deljidx
<data set1,column1>
< data set2,column2> ... <data set_n,column_n>
Include the distribution
statistics with the index statistics.
-lstjidx
-libpath
<library path>
List the join indexes
for a domain.
-statjidx
< data set1,column1>
<data set2,column2> ... < data set_n,column_n>
Get statistics about
a join index.
For each specified
index, prints the index disk usage statistics and value segment list
statistics.
-runstats
< index,index...> [
For each specified
index, print the "run" statistics. "Runs" are defined as successive
observations in a table that contain the same index value
For each index, reorganize
the index to reclaim any unused disk space and coalesce any fragmented
value segment lists.
-joinparts
<number of parallel join
work units>
Specifies the number
of parallel join work units for a join index. Parallel join threads
will join the work units concurrently and then merge their partial
results into the final result.
The SPD Server table
that contains the index.
The physical path of
the domain containing the table.
Prints the ixutil help
menu.
Note: Index names are case sensitive
and MUST be specified exactly as listed in the PROC CONTENTS output.
Copyright © SAS Institute Inc. All rights reserved.