![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to modify.nr CVS log ![[TXT]](/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvsweb/icons/dir.gif){kind=icon}
Up to [CSRG BSD Unix] / 42BSD / ingres / doc / quel|
Return to modify.nr CVS log | Up to [CSRG BSD Unix] / 42BSD / ingres / doc / quel |
1.1 root 1: .th MODIFY QUEL 2/23/79
2: .sh NAME
3: modify \- convert the storage structure of a relation
4: .sh SYNOPSIS
5: .in +5
6: .ti -5
7: .bd modify
8: relname
9: .bd to
10: storage-structure
11: [
12: .bd on
13: key1 [ :
14: .it sortorder
15: ] [ { , key2 [ :
16: .it sortorder
17: ] } ] ]
18: [
19: .bd where
20: [
21: .bd "fillfactor ="
22: .it n
23: ] [
24: .bd ", minpages ="
25: .it n
26: ] [
27: .bd ", maxpages ="
28: .it n
29: ] ]
30: .i0
31: .sh DESCRIPTION
32: .it Relname
33: is modified to the specified storage structure.
34: Only the owner of a relation can modify that relation.
35: This command is used to increase performance when using large
36: or frequently referenced relations.
37: The storage structures are specified as follows:
38: .s3
39: .if n .in +10
40: .if t .in +1i
41: .de xx
42: .if n .ti -5
43: .if t .ti -0.5i
44: ..
45: .xx
46: isam \- indexed sequential storage structure
47: .xx
48: cisam \- compressed isam
49: .xx
50: hash \- random hah storage structure
51: .xx
52: chash \- compressed hash
53: .xx
54: heap \- unkeyed and unstructured
55: .xx
56: cheap \- compressed heap
57: .xx
58: heapsort \- heap with tuples sorted and duplicates removed
59: .xx
60: cheapsort \- compressed heapsort
61: .xx
62: truncated \- heap with all tuples deleted
63: .s3
64: .i0
65: The paper ``Creating and Maintaining a Database in \*(II''
66: (ERL Memo M77\-71)
67: discusses how to select storage structures
68: based on how the relation is used.
69: .s3
70: The current compression algorithm only suppresses trailing
71: blanks in character fields.
72: A more effective compression scheme may be possible,
73: but tradeoffs between that and a larger and slower
74: compression algorithm are not clear.
75: .s3
76: If the
77: .it on
78: phrase is omitted when modifying to isam, cisam, hash or chash,
79: the relation will automatically be keyed on the first domain.
80: When modifying to heap or cheap the
81: .it on
82: phrase must be omitted.
83: When modifying to heapsort or cheapsort the
84: .it on
85: phrase is optional.
86: .s3
87: When a relation is being sorted (isam, cisam, heapsort and cheapsort),
88: the primary sort keys will be those specified in the
89: .it on
90: phrase (if any).
91: The first key after the
92: .it on
93: phrase will be the most significant sort key
94: and each successive key specified will be the next most significant
95: sort key.
96: Any domains not specified in the
97: .it on
98: phrase will be used as least
99: significant sort keys in domain number sequence.
100: .s3
101: When a relation is modified to heapsort or cheapsort,
102: the
103: .it sortorder
104: can be specified to be
105: .bd ascending
106: or
107: .bd descending.
108: The default is always
109: .bd ascending.
110: Each key given in the
111: .it on
112: phrase can be optionally modified
113: to be:
114: .s1
115: .if n .ti +5
116: .if t .ti +0.5i
117: key:descending
118: .s1
119: which will cause that key to be sorted in descending order.
120: For completeness,
121: .bd ascending
122: can be specified after the colon (`:'),
123: although this is unnecessary since
124: it is the default.
125: .bd Descending
126: can be abbreviated by a single `\c
127: .bd d\c
128: \&' and, correspondingly,
129: .bd ascending
130: can be abreviated by a single `\c
131: .bd a\c
132: \&'.
133: .s3
134: .it Fillfactor
135: specifies the percentage
136: (from 1 to 100)
137: of each primary data page that should be filled
138: with tuples, under ideal conditions.
139: .it Fillfactor
140: may be used with isam, cisam, hash and chash.
141: Care should be taken when using large fillfactors since a non-uniform
142: distribution of key values could cause overflow pages to be created,
143: and thus degrade access performance for the relation.
144: .s3
145: .it Minpages
146: specifies the minimum number of primary pages a hash or chash
147: relation must have.
148: .it Maxpages
149: specifies the maximum number of primary pages
150: a hash or chash relation may have.
151: .it Minpages
152: and
153: .it maxpages
154: must be at least one.
155: If both
156: .bd minpages
157: and
158: .bd maxpages
159: are specified in a modify,
160: .bd minpages
161: cannot exceed
162: .bd maxpages.
163: .sp
164: Default values for
165: .bd fillfactor\c
166: ,
167: .bd minpages\c
168: , and
169: .bd maxpages
170: are as follows:
171: .if n .ta 5 12 25 35
172: .if t .ta 0.5i 1i 3i 4.5i
173: .nf
174: .ul
175: .s1
176: FILLFACTOR MINPAGES MAXPAGES
177: .s2
178: hash 50 10 no limit
179: chash 75 1 no limit
180: isam 80 NA NA
181: cisam 100 NA NA
182: .fi
183: .dt
184: .sh EXAMPLES
185: .nf
186: /* modify the emp relation to an indexed
187: sequential storage structure with
188: "name" as the keyed domain */
189: .s1
190: modify emp to isam on name
191: .s1
192: /* if "name" is the first domain of the emp relation,
193: the same result can be achieved by */
194: .s1
195: modify emp to isam
196: .s1
197: /* do the same modify but request a 60% occupancy
198: on all primary pages */
199: .s1
200: modify emp to isam on name where fillfactor = 60
201:
202: /* modify the supply relation to compressed hash
203: storage structure with "num" and "quan"
204: as keyed domains */
205: .s1
206: modify supply to chash on num, quan
207: .s1
208: /* now the same modify but also request 75% occupancy
209: on all primary, a minimum of 7 primary pages
210: pages and a maximum of 43 primary pages */
211: .s1
212: modify supply to chash on num, quan
213: where fillfactor = 75, minpages = 7,
214: maxpages = 43
215: .s1
216: /* again the same modify but only request a minimum
217: of 16 primary pages */
218: .s1
219: modify supply to chash on num, quan
220: where minpages = 16
221: .s1
222: /* modify parts to a heap storage structure */
223: .s1
224: modify parts to heap
225: .s1
226: /* modify parts to a heap again, but have tuples
227: sorted on "pnum" domain and have any duplicate
228: tuples removed */
229: .s1
230: modify parts to heapsort on pnum
231: .s1
232: /* modify employee in ascending order by manager,
233: descending order by salary and have any
234: duplicate tuples removed */
235: .s1
236: modify employee to heapsort on manager, salary:descending
237: .fi
238: .sh "SEE ALSO"
239: sysmod(unix)
This archive runs on limited infrastructure. Preserving old code on modern bandwidth. Automated agents are requested to crawl responsibly.