4.3BSD/usr/ingres/doc/quel/modify.nr
.th MODIFY QUEL 2/23/79
.sh NAME
modify \- convert the storage structure of a relation
.sh SYNOPSIS
.in +5
.ti -5
.bd modify
relname
.bd to
storage-structure
[
.bd on
key1 [ :
.it sortorder
] [ { , key2 [ :
.it sortorder
] } ] ]
[
.bd where
[
.bd "fillfactor ="
.it n
] [
.bd ", minpages ="
.it n
] [
.bd ", maxpages ="
.it n
] [
.bd ", lidn ="
.it lidname
] [
.i0
.sh DESCRIPTION
.it Relname
is modified to the specified storage structure.
Only the owner of a relation can modify that relation.
This command is used to increase performance when using large
or frequently referenced relations.
The storage structures are specified as follows:
.s3
.if n .in +10
.if t .in +1i
.de xx
.if n .ti -5
.if t .ti -0.5i
..
.xx
isam \- indexed sequential storage structure
.xx
cisam \- compressed isam
.xx
hash \- random hah storage structure
.xx
chash \- compressed hash
.xx
heap \- unkeyed and unstructured
.xx
cheap \- compressed heap
.xx
heapsort \- heap with tuples sorted and duplicates removed
.xx
cheapsort \- compressed heapsort
.xx
truncated \- heap with all tuples deleted
.xx
orderedn \- ordered relation where n is the ordering dimension
.s3
.i0
The paper ``Creating and Maintaining a Database in \*(II''
(ERL Memo M77\-71)
discusses how to select storage structures
based on how the relation is used.
.s3
The current compression algorithm only suppresses trailing
blanks in character fields.
A more effective compression scheme may be possible,
but tradeoffs between that and a larger and slower
compression algorithm are not clear.
.s3
If the
.it on
phrase is omitted when modifying to isam, cisam, hash or chash,
the relation will automatically be keyed on the first domain.
When modifying to heap or cheap the
.it on
phrase must be omitted.
When modifying to heapsort or cheapsort the
.it on
phrase is optional.
.s3
When a relation is being sorted (isam, cisam, heapsort and cheapsort),
the primary sort keys will be those specified in the
.it on
phrase (if any).
The first key after the
.it on
phrase will be the most significant sort key
and each successive key specified will be the next most significant
sort key.
Any domains not specified in the
.it on
phrase will be used as least
significant sort keys in domain number sequence.
.s3
When a relation is modified to heapsort or cheapsort,
the
.it sortorder
can be specified to be
.bd ascending
or
.bd descending.
The default is always
.bd ascending.
Each key given in the
.it on
phrase can be optionally modified
to be:
.s1
.if n .ti +5
.if t .ti +0.5i
key:descending
.s1
which will cause that key to be sorted in descending order.
For completeness,
.bd ascending
can be specified after the colon (`:'),
although this is unnecessary since
it is the default.
.bd Descending
can be abbreviated by a single `\c
.bd d\c
\&' and, correspondingly,
.bd ascending
can be abreviated by a single `\c
.bd a\c
\&'.
.s3
When modifying to
.it orderedn,
up to n ordering keys can be specified using the
.it on
clause.
Ordering keys are used to specify the ordering of tuples in the new relation.
Changes on key field values indicate the incrementing of a
lid value for the lid corresponding to the key change.
If no ordering keys are specified, only the lid corresponding
to the lowest lid level is incremented by one for every new tuple.
In this case,the order of the tuples is determined by their sort order on file.
However, note that ordering does not destroy any current storage
structures on a relation (except secondary indices).
.s3
.it Lidn
can only be specified if modifying to
.it orderedn.
Default values are
.bd lid1
,
.bd lid2
, and
.bd lid3.
.s3
.it Fillfactor
specifies the percentage
(from 1 to 100)
of each primary data page that should be filled
with tuples, under ideal conditions.
.it Fillfactor
may be used with isam, cisam, hash and chash.
Care should be taken when using large fillfactors since a non-uniform
distribution of key values could cause overflow pages to be created,
and thus degrade access performance for the relation.
.s3
.it Minpages
specifies the minimum number of primary pages a hash or chash
relation must have.
.it Maxpages
specifies the maximum number of primary pages
a hash or chash relation may have.
.it Minpages
and
.it maxpages
must be at least one.
If both
.bd minpages
and
.bd maxpages
are specified in a modify,
.bd minpages
cannot exceed
.bd maxpages.
.sp
Default values for
.bd fillfactor\c
,
.bd minpages\c
, and
.bd maxpages
are as follows:
.if n .ta 5 12 25 35
.if t .ta 0.5i 1i 3i 4.5i
.nf
.ul
.s1
FILLFACTOR MINPAGES MAXPAGES
.s2
hash 50 10 no limit
chash 75 1 no limit
isam 80 NA NA
cisam 100 NA NA
.fi
.dt
.sh EXAMPLES
.nf
/* modify the emp relation to an indexed
sequential storage structure with
"name" as the keyed domain */
.s1
modify emp to isam on name
.s1
/* if "name" is the first domain of the emp relation,
the same result can be achieved by */
.s1
modify emp to isam
.s1
/* do the same modify but request a 60% occupancy
on all primary pages */
.s1
modify emp to isam on name where fillfactor = 60
/* modify the supply relation to compressed hash
storage structure with "num" and "quan"
as keyed domains */
.s1
modify supply to chash on num, quan
.s1
/* now the same modify but also request 75% occupancy
on all primary, a minimum of 7 primary pages
pages and a maximum of 43 primary pages */
.s1
modify supply to chash on num, quan
where fillfactor = 75, minpages = 7,
maxpages = 43
.s1
/* again the same modify but only request a minimum
of 16 primary pages */
.s1
modify supply to chash on num, quan
where minpages = 16
.s1
/* modify parts to a heap storage structure */
.s1
modify parts to heap
.s1
/* modify parts to a heap again, but have tuples
sorted on "pnum" domain and have any duplicate
tuples removed */
.s1
modify parts to heapsort on pnum
.s1
/* modify employee in ascending order by manager,
descending order by salary and have any
duplicate tuples removed */
.s1
modify employee to heapsort on manager, salary:descending
.s1
/* ordered relation */
.s1
modify text to ordered1 on lid where lid1 = lidfield
.fi
.sh "SEE ALSO"
sysmod(unix)
ordered(quel)