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