![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to maintain.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 / other|
Return to maintain.nr CVS log | Up to [CSRG BSD Unix] / 42BSD / ingres / doc / other |
1.1 root 1: .ds HE 'DATA BASE'INGRES'Page %'
2: .so nmacs
3: .hx
4: .rs
5: .sp 8
6: .ce
7: CREATING AND MAINTAINING A DATABASE USING INGRES
8: .sp 15
9: .ce
10: by
11: .ce
12: Robert Epstein
13: .sp 18
14: .ce 2
15: Memorandum No. ERL - M77-71
16: December 16, 1977
17: .sp 3
18: .ce 4
19: Electronics Research Laboratory
20: College of Engineering
21: University of California, Berkeley
22: 94720
23: .bp 1
24: .ce
25: .bl + 5
26: CREATING AND MAINTAINING A DATABASE USING INGRES
27: .sp 3
28: 1. INTRODUCTION
29: .sp
30: In this paper we describe how to create,
31: structure and maintain relations
32: in INGRES.
33: It is assumed that the reader
34: is familiar with INGRES
35: and understands QUEL, the INGRES
36: query language.
37: It is strongly suggested that the
38: document "A Tutorial on INGRES"
39: (ERL M77/25) be read first.
40: .sp 1
41: This paper is divided into six sections
42: .in +5
43: .sp 1
44: 1. Introduction
45: .sp 1
46: 2. Creating a Relation
47: .sp 1
48: 3. Using Copy
49: .sp 1
50: 4. Storage Structures
51: .sp 1
52: 5. Secondary Indices
53: .sp 1
54: 6. Recovery and Data Update
55: .in -5
56: .sp 1
57: To create a new data base you must be a valid
58: INGRES user and have "create data base"
59: permission.
60: These permissions are granted by the
61: "ingres" superuser.
62: If you pass those two requirements you can create
63: a data base using the command to the
64: Unix shell:
65: .sp 1
66: % creatdb mydata
67: .sp 1
68: where "mydata" is the name of the data base.
69: You become the "data base administrator" (DBA) for
70: mydata.
71: As the DBA you have certain special
72: powers.
73:
74: .in +4
75: .ti -5
76: 1. Any relation created by you can be accessed
77: by anyone else using "mydata".
78: If any other user creates a relation it is
79: strictly private and cannot
80: be accessed by the DBA or any other user.
81:
82: .ti -5
83: 2. You can use the "-u" flag in ingres
84: and printr.
85: This enables you to use ingres on "mydata"
86: with someone else's id.
87: Refer to the INGRES reference
88: manual under sections
89: ingres(unix) and users(files)
90: for details.
91:
92: .ti -5
93: 3. You can run sysmod, restore and purge
94: on "mydata".
95:
96: .ti -5
97: 4. The data base by default is created to
98: allow for multiple concurrent users.
99: If only one user will ever use the
100: data base at a time,
101: the data base administrator can
102: turn off the concurrency control.
103: Refer to creatdb(unix) in the INGRES
104: reference manual.
105: .in -4
106: .sp 1
107: Once a data base has been created you should
108: immediately run
109: .sp 1
110: % sysmod mydata
111: .sp 1
112: This program will convert
113: the system relations to their
114: "best" structure for use in INGRES.
115: Sysmod will be explained further in
116: section 4.
117: .sp 1
118: As a DBA or as a user you can create and
119: structure new relations in any data
120: base to which you have access.
121: The remainder of this paper describes how this is done.
122: .bp
123: 2. CREATING NEW RELATIONS IN INGRES
124: .sp
125: There are two ways to create new relations in INGRES.
126: .sp 1
127: .ti +5
128: create
129: .br
130: .ti +5
131: retrieve into
132: .sp 1
133: "Retrieve into" is used to form a new relation from one or
134: more existing relations.
135: "Create" is used to create
136: a new relation with no tuples in it.
137: .sp 1
138: example 1:
139: .sp 1
140: .ti +5
141: range of p is parts
142: .ti +5
143: range of s is supply
144: .ti +5
145: retrieve into newsupply(
146: .ti +19
147: number = s.snum,
148: .ti +19
149: p.pname,
150: .ti +19
151: s.shipdate)
152: .ti +5
153: where s.pnum = p.pnum
154: .sp 1
155: example 2:
156: .sp 1
157: .ti +5
158: create newsupply(
159: .ti +12
160: number = i2,
161: .ti +12
162: pname = c20,
163: .ti +12
164: shipdate = c8)
165: .sp 1
166: In example 1 INGRES creates a new relation called
167: "newsupply", computing what the format
168: of each domain should be.
169: The query is then run and newsupply is
170: modified to "cheapsort".
171: (This will be covered in more detail in section 4.)
172: .sp 1
173: In example 2 "newsupply"
174: is created and the name and
175: format for each domain is given.
176: The format types which are
177: allowed are:
178: .sp 1
179: .in +5
180: .nf
181: i1 1 byte integer
182: i2 2 " "
183: i4 4 " "
184: f4 4 byte floating point number
185: f8 8 " " " "
186: c1,c2,..,c255 1,2,..,255 byte character
187: .in -5
188: .fi
189: .sp 1
190: In example 2, the width of an individual
191: tuple is 30 bytes
192: (2 + 20 + 8), and the
193: relation has three domains.
194: Beware that INGRES
195: has limits.
196: A relation cannot have more
197: than 49 domains and the tuple width
198: cannot exceed 498 bytes.
199: .sp 1
200: UNIX allocates space on a disk
201: in units of 512 byte pages.
202: INGRES gets a performance advantage
203: by doing I/O in one block units.
204: Therefore relations are divided into 512 byte pages.
205: INGRES never splits a tuple
206: between two pages.
207: Thus some space can be wasted.
208: There is an overhead of 12 bytes per page plus
209: 2 bytes for every tuple on the page.
210: The formulas are:
211: .sp 1
212: .ti +5
213: number tuples per page = 500/(tuple width + 2)
214: .sp 1
215: .ti +5
216: wasted space = 500 - number of tuples per page
217: .ti +5
218: *(tuple width +2)
219: .sp 1
220: For our example there are
221: .sp 1
222: .ti +5
223: 22 = 500/(20 + 2)
224: .sp 1
225: .ti +5
226: 16 = 500 - 22 * (20 + 2)
227: .sp 1
228: 22 tuples per page and 16 bytes
229: wasted per page.
230: These computations are valid
231: only for uncompressed relations.
232: We will return to this subject
233: in section 4 when we discuss compression.
234: .sp 1
235: If you forget a domain name or
236: format, use the "help" command.
237: For example if you gave the INGRES
238: command:
239: .sp 1
240: .ti +5
241: help newsupply
242: .sp 1
243: the following would be printed:
244: .nf
245:
246: Relation: newsupply
247: Owner: bob
248: Tuple width: 30
249: Saved until: Thu Nov 10 16:17:06 1977
250: Number of tuples: 0
251: Storage structure: paged heap
252: Relation type: user relation
253:
254: attribute name type length keyno.
255:
256: number i 2
257: pname c 20
258: shipdate c 8
259:
260: .fi
261: Notice that every relation has an expiration
262: date.
263: This is set to be one week
264: from the time when it was
265: created.
266: The "save" command
267: can be used to save the relation longer.
268: See "save(quel)" and "purge(unix)" in the
269: INGRES reference manual.
270:
271: .bp
272: 3. COPYING DATA TO AND FROM INGRES
273: .sp
274: Once a relation is created, there are two mechanisms for
275: inserting new data:
276: .sp
277: .in +5
278: append command
279: .br
280: copy command
281: .in -5
282: .sp
283: Append is used to insert tuples one at a time,
284: or for filling one relation from other relations.
285: .sp
286: Copy is used for copying data from a UNIX file into
287: a relation.
288: It is used for copying data from another program, or for copying
289: data from another system.
290: It is also the most convenient way to copy any data
291: larger than a few tuples.
292: .sp
293: Let's begin by creating a simple relation
294: and loading data into it.
295: .sp
296: Example:
297: .sp
298: .ti +5
299: .nf
300: create donation (name = c10, amount = f4, ext = i2)
301: .sp
302: .fi
303: Now suppose we have two people to enter.
304: The simplest procedure is probably
305: to run the two queries in INGRES using
306: the append command.
307: .sp
308: .ti 5
309: .nf
310: append to donation (name="frank",amount = 5,ext = 204)
311: .sp 1
312: .ti 5
313: append to donation (name="harry",ext = 209,amount = 4.50)
314: .fi
315: .sp
316: Note that the order in which the domains are given
317: does not matter.
318: INGRES matches by recognizing attribute names and
319: does not care in what order attributes
320: are listed.
321: Here is what the relation "donation" looks like now:
322: .nf
323:
324: donation relation
325:
326: |name |amount |ext |
327: |----------------------------|
328: |frank |5.000 |204 |
329: |harry |4.500 |209 |
330: |----------------------------|
331: .fi
332: .sp
333: We now have two people entered into
334: the donation relation.
335: Suppose we had fifty more to enter.
336: Using the append command is far too tedious
337: since so much typing is involved for each tuple.
338: The copy command will better suit our purposes.
339: .sp
340: Copy can take data from a regular
341: Unix file in a variety of formats and
342: append it to a relation.
343: To use the copy command first create
344: a Unix file (typically using "ed") containing
345: the data.
346: .sp
347: For example, let's put five new names in a file
348: using the editor.
349: .sp
350: .nf
351: .tr Z.
352: % ed
353: a
354: bill,3.50,302
355: sam,10.00,410
356: susan,,100
357: sally,.5,305
358: george,4.00,302
359: Z
360: w newdom
361: 68
362: q
363: %
364: .tr ZZ
365: .sp
366: .fi
367: The format of the above file is a
368: name followed by a comma, followed
369: by the amount, then a comma, then the extension,
370: and finally a newline.
371: Null entries, for example the amount
372: for susan, are perfectly
373: legal and default to zero
374: for numerical domains and
375: blanks for character domains.
376: .sp
377: To use copy we enter INGRES and give the copy command.
378: .sp
379: .in +5
380: .nf
381: copy donation (name = c0, amount = c0, ext = c0)
382: from "/mnt/bob/newdom"
383: .fi
384: .sp
385: .in -5
386: Here is how the copy command works:
387: .sp
388: .ti +5
389: copy relname (list of what to copy) from "full pathname"
390: .sp
391: In the case above we wrote:
392: .sp
393: .ti +5
394: copy donation (. . .) from "/mnt/bob/newdom"
395: .sp
396: Although amount and ext are stored in the relation
397: as f4 (floating point) and i2 (integer), in the
398: Unix file they were entered as characters.
399: In specifying the format of the domain,
400: copy accepts:
401: .sp
402: .ti +5
403: domain = format
404: .sp
405: where domain is the domain name and
406: the format in the UNIX file is one of
407: .sp
408: .in +5
409: .nf
410: i1, i2, i4 (true binary integer of size 1, 2, or 4)
411: .br
412: f4, f8 (true binary float point of size 4 or 8)
413: .br
414: c1, c2, c3,...c255 (a fixed length character string)
415: .br
416: c0 (a variable length character string de-
417: limited by a comma, tab or new line)
418: .sp
419: .in -5
420: .fi
421: In the example we use
422: .sp
423: .ti +5
424: name = c0, amount = c0, extension = c0
425: .sp
426: This means that each of the domains
427: was stored in the Unix file as
428: variable length character
429: strings.
430: Copy takes the first comma,
431: tab, or new line character
432: as the end of the string.
433: This by far is the most
434: common use of copy
435: when the data is being entered
436: into a relation
437: for the first time.
438: .sp
439: Copy can also be used to copy data from a relation
440: into a Unix file.
441: For example:
442: .sp
443: .in +5
444: .nf
445: copy donation (name = c10, amount = c10, ext = c5)
446: into "/mnt/bob/data"
447: .fi
448: .in -5
449: .sp
450: This will cause the following to happen:
451: .sp 1
452: .in +4
453: .ti -5
454: 1. If the file /mnt/bob/data already exists it will
455: be destroyed.
456: .ti -5
457: .sp 1
458: 2. The file is created in mode 600 (read/write by you only)
459: .sp 1
460: .ti -5
461: 3. Name will be copied as a 10 character field,
462: immediately followed by amount,
463: immediately followed by ext.
464: Amount will be converted to a character
465: field 10 characters wide.
466: Ext will be converted to a character
467: field 5 characters wide.
468: .in -4
469: .sp 1
470: The file "/mnt/bob/data" would be a stream of characters
471: looking like this:
472:
473: .tr Z
474: .nf
475: frankZZZZZZZZZZ5.000ZZ204harryZZZZZZZZZZ4.500ZZ209bill
476: ZZZZZZZZZZZ3.500ZZ302samZZZZZZZZZZZ10.000ZZ410susanZZZ
477: ZZZZZZZ0.000ZZ100sallyZZZZZZZZZZ0.500ZZ305georgeZZZZZZ
478: ZZZ4.000ZZ302
479: .fi
480: .tr ZZ
481:
482: .sp
483: The output was broken into four lines to
484: make it fit on this page.
485: In actuality the file
486: is a single line.
487: Another example:
488: .sp
489: .in +5
490: .nf
491: copy (name = c0, colon = d1, ext = c0, comma = d1
492: amt = c0, nl = d1) into "/mnt/bob/data"
493: .fi
494: .in -5
495: .sp
496: In this example "c0" is interpreted to mean "use
497: the appropriate character format".
498: For character domains it is the
499: width of the domain.
500: Numeric domains are converted to characters
501: according to the INGRES defaults
502: (see ingres(unix)).
503: .sp
504: The statements:
505: .sp
506: .in +5
507: colon = d1
508: .br
509: comma = d1
510: .br
511: nl = d1
512: .in -5
513: .sp
514: are used to insert one colon,
515: comma, and newline into the file.
516: The format "d1" is interpreted to mean
517: one dummy character.
518: When copying into a Unix file,
519: a selected set of characters can be inserted into
520: the file using this
521: "dummy domain" specification.
522: Here is what the file "/mnt/bob/data" would look like:
523:
524: .nf
525: frank : 204, 5.000
526: harry : 209, 4.500
527: bill : 302, 3.500
528: sam : 410, 10.000
529: susan : 100, 0.000
530: sally : 305, 0.500
531: george : 302, 4.000
532:
533: .fi
534: .sp
535: If you wanted a file with the true binary representation
536: of the numbers you would use:
537: .sp
538: .ti +5
539: copy (name = c10, amount = f4, ext = i2)
540: .sp
541: This would create a file with the exact
542: copy of each tuple,
543: one after the other.
544: This is frequently desireable for
545: temporary backup purposes
546: and it guarantees that floating
547: point domains will be exact.
548: .sp 2
549: TYPICAL ERRORS
550: .sp 1
551: There are 17 different errors
552: that can occur in copy.
553: We will go through the most
554: common ones.
555: .sp 1
556: Suppose you have a file with
557: .sp 1
558: bill,3.5,302
559: .br
560: sam,10,410,
561: .br
562: susan,3,100
563: .sp 1
564: and run the copy command
565: .sp 1
566: .in +5
567: .nf
568: copy donation (name = c0, amount = c0, ext = c0)
569: from "/mnt/bob/data"
570: .fi
571: .in -5
572: .sp 1
573: You would get the error message
574: .sp 1
575: .nf
576: 5809: COPY: bad input string for domain amount. Input was "susan".
577: There were 2 tuples sucessfully copied from /mnt/bob/data into
578: donation.
579: .fi
580: .sp 1
581: What happened is that line 2 had an extra
582: comma.
583: The first two tuples were copied correctly.
584: For the next tuple, name = "" (blank), amount =
585: "susan", and ext = "3".
586: Since "susan" is not a proper floating point
587: number, an error was generated and
588: processing was stopped after two tuples.
589: .sp 1
590: If you tried to copy the file with a file
591: such as
592: .sp 1
593: nancy,5.0,35000
594: .sp 1
595: you would get the error message
596: .sp 1
597: .nf
598: 5809: COPY: bad input string for domain ext. Input was "35000".
599: There were 0 tuples successfully copied from /mnt/bob/data into
600: donation.
601: .fi
602: .sp 1
603: Here, since ext is an i2 (integer) domain,
604: it cannot exceed the value 32767.
605: .sp 1
606: There are numerous other error messages,
607: most of which are self-explanatory.
608: .sp 1
609: In addition there are three, non-fatal warnings
610: which may appear on a copy "from".
611: .sp 1
612: If you are copying from
613: a file into a relation which
614: is ISAM or hash, a count
615: of the number of duplicate
616: tuples will appear, (if there were
617: any).
618: This will never appear on a "heap"
619: because no duplicate checking
620: is performed.
621: .sp 1
622: INGRES does not allow
623: control characters
624: (such as "bell" etc.)
625: to be stored.
626: If copy reads any control characters, it converts them
627: to blanks and reports the number
628: of domains that had control characters in them.
629: .sp 1
630: If you are copying using the c0
631: option, copy will
632: report if any character strings were
633: longer than their domains
634: and had to be truncated.
635: .sp 2
636: SPECIAL FEATURES
637: .sp 1
638: .ti +3
639: There are a few special functions that
640: make copy a little
641: easier to use
642: .sp 1
643: .nr in 6n
644: .ti -4
645: 1. Bulk copy
646: .sp 1
647: If you ask for:
648: .sp 1
649: .ti +4
650: copy relname () from "file"
651: .ti +8
652: or
653: .ti +4
654: copy relname () into "file"
655: .sp 1
656: copy expands the statement to mean:
657: .sp 1
658: .in +5
659: copy each domain in its proper
660: order according to its proper
661: format.
662: .sp 1
663: .in -5
664: So, if you said
665: .sp 1
666: .ti +4
667: copy donation () into "/mnt/bob/donation"
668: .sp 1
669: it would be the same as asking for:
670: .sp 1
671: .ti +4
672: .nf
673: copy donation (name = c10, amount = f4, ext = i2)
674: into "/mnt/bob/donation"
675: .fi
676: .sp 1
677: This provides a convenient way to copy
678: whole relations to and from INGRES.
679: .sp 1
680: .ti -4
681: 2. Dummy Domains
682: .sp 1
683: If you are copying data
684: from another computer or program,
685: frequently there will be
686: a portion of data that you will want to
687: ignore.
688: This can be done using the
689: dummy domain specifications
690: d0, d1, d2 ... d511.
691: For example
692: .sp 1
693: .ti +4
694: .nf
695: copy rel (dom1 = c5, dummy = d2, dom2 = i4,
696: dumb = d0) from "/mnt/me/data"
697: .fi
698: .sp 1
699: The first five characters
700: are put in dom1,
701: the next two characters are ignored.
702: The next four bytes are
703: an i4 (integer) and go in dom2,
704: and the remaining delimited string
705: is ignored.
706: The name given to a dummy specifier is
707: ignored.
708: .sp 1
709: As mentioned previously,
710: dummy domains can be used on a copy
711: "into" a Unix file for inserting
712: special characters.
713: The list of recognizable names includes:
714: .sp 1
715: .in +5
716: .nf
717: nl newline
718: tab tab character
719: sp space
720: nul a zero byte
721: null a zero byte
722: comma ,
723: dash -
724: colon :
725: lparen (
726: rparen )
727: .fi
728: .in -5
729: .sp 1
730: .ti -4
731: 3. Truncation
732: .sp 1
733: It is not uncommon to have a mistake occur
734: and need to start over.
735: The simplest way to do that
736: is to "truncate" the relation.
737: This is done by the command:
738: .sp 1
739: .ti +4
740: modify relname to truncated
741: .sp 1
742: This has the effect of removing
743: all tuples in relname,
744: releasing all disk space,
745: and making relname a heap again.
746: It is the logical equivalent of
747: a destroy followed by a create
748: (but with a lot less typing).
749: .sp 1
750: Since formatting mistakes are possible
751: with copy,
752: it is not generally a good idea to
753: copy data into a relation that already
754: has valid data in it.
755: The best procedure is to create a
756: temporary relation with the same domains
757: as the existing relation.
758: Copy data into the temporary relation
759: and then append it to the real relation.
760: For example:
761:
762: .in +8
763: .nf
764: create tempdom(name=c10,amount=f4,ext=i2)
765:
766: copy tempdom(name=c0,amount=c0,ext=c0)
767: from "/mnt/bob/data"
768:
769: range of td is tempdom
770: append to donation(td.all)
771: .fi
772: .in -8
773: .sp 1
774: 4. Specifing Delimitors.
775: .sp 1
776: Sometimes it is desirable to specify
777: what the delimiting character should be
778: on a copy "from" a file.
779: This can be done by specifing:
780:
781: .ti +8
782: domain = c0delim
783:
784: where "delim" is a valid delimitor
785: taken from the list of recognizable names.
786: This list was summarized on the
787: previous page under "dummy domains".
788: For example:
789:
790: .ti +8
791: copy donation (name = c0nl) from "/mnt/me/data"
792:
793: will copy names from the file to the relation.
794: Only a new line will delimit the names so
795: any commas or tabs will be passed along as
796: part of the name.
797:
798: When copying "into" a Unix file,
799: the "delim" is actually written into the
800: file,
801: so on a copy "into" the specification:
802:
803: .ti +8
804: copy donation (name = c0nl) into "/mnt/me/file"
805:
806: will cause "name" to be written followed by a new line
807: character.
808: .nr in 0
809: .bp
810: 4. CHOOSING THE BEST STORAGE STRUCTURES
811: .sp 1
812: .sp
813: We now turn to the issue of efficiency.
814: Once you have created a relation
815: and inserted your
816: data using either copy or append,
817: INGRES can process any query
818: on the relation.
819: There are several things you can do
820: to improve the speed at which INGRES
821: can process a query.
822: .sp
823: INGRES can store a relation in three different
824: internal
825: structures.
826: These are called "heap",
827: "isam", and "hash".
828: First we will briefly describe each
829: structure and then later expand our
830: discussion.
831: .sp
832: HEAP
833: .sp 1
834: When a relation is first created, it is
835: created as a "heap".
836: There are two important properties about a heap:
837: duplicate tuples are not removed,
838: and nothing is known about the location of the tuples.
839: If you ran the query:
840: .sp 1
841: .ti +5
842: range of d is donation
843: .br
844: .ti +5
845: retrieve (d.amount) where d.name = "bill"
846: .sp 1
847: INGRES would have to read every tuple in the
848: relation looking for those with name "bill".
849: If the relation is small this isn't a
850: serious matter.
851: But if the relation is very large, this can take
852: minutes (or even hours!).
853: .sp 1
854: HASH
855: .sp 1
856: A relation whose structure is "hash" can give fast
857: access to searches on certain domains.
858: (Those domains are usually referred to as
859: "keyed domains".)
860: In addition, a "hashed" relation contains
861: no duplicate tuples.
862: For example, suppose the donation relation is stored hashed on
863: domain "name".
864: Then the query:
865: .sp 1
866: .ti +5
867: retrieve (d.amount) where d.name = "bill"
868: .sp 1
869: will run quickly
870: since INGRES knows approximately where on
871: disk the tuple is stored.
872: If the relation contains only a few tuples you
873: won't notice the difference between a "heap"
874: and a "hash" structure.
875: But as the relation becomes larger, the
876: difference in speed becomes
877: much more noticeable.
878: .sp 1
879: ISAM
880: .sp 1
881: An isam structure is one where the relation is
882: sorted on one or more domains,
883: (also called keyed domains).
884: Duplicates are also removed on "isam relations".
885: When new tuples are appended they are
886: placed "approximately" in their sorted position in the
887: relation.
888: (The "approximately" will be explained a bit
889: later.)
890: .sp 1
891: Suppose donation is isam on name.
892: To process the query
893: .sp 1
894: .ti +5
895: retrieve (d.amount) where d.name = "bill"
896: .sp 1
897: INGRES will determine where in the sorted order
898: the name "bill" would be and read only
899: those portions of the relation.
900: .sp 1
901: Since the relation is approximately sorted,
902: an isam structure is also efficient for
903: processing the query:
904: .ti +5
905: .sp 1
906: retrieve (d.amount) where d.name >= "b" and d.name < "g"
907: .sp 1
908: This query would retrieve all names beginning
909: with "b" through "f".
910: The entire relation would not have to be
911: searched since it is isam on name.
912: .sp 2
913: SPECIFYING THE STORAGE STRUCTURE
914: .sp
915: Any user created relation can be converted
916: to any storage structure using the
917: "modify" command.
918: For example
919: .sp
920: .ti 5
921: modify donation to hash on name
922: .br
923: or
924: .br
925: .ti 5
926: modify donation to isam on name
927: .sp
928: or even
929: .sp
930: .ti 5
931: modify donation to heap
932: .sp 2
933: PRIMARY AND OVERFLOW PAGES
934: .sp
935: At this point it is necessary to introduce the
936: concepts of primary and overflow pages on
937: hash and isam structures.
938: Both hash and isam are techniques for assigning
939: specific tuples to specific pages of a relation
940: based on the tuple's keyed domains.
941: Thus each page will contain only a certain
942: specified subset of the relation.
943:
944: When a new tuple is appended to a hash or isam
945: relation, INGRES
946: first determines what page it belongs to,
947: and then looks for room on that page.
948: If there is space then the tuple
949: is placed on that page.
950: If not,
951: then an "overflow" page is created and
952: the tuple is placed there.
953:
954: The overflow page is linked to the
955: original page.
956: The original page is called the "primary"
957: page.
958: If the overflow page became full,
959: then INGRES
960: would connect an overflow page to it.
961: We would then have one primary page
962: linked to an overflow page,
963: linked to another overflow page.
964: Overflow pages are dynamically added as
965: needed.
966: .sp 2
967: SPECIFYING FREE SPACE
968: .sp
969: The modify command also lets you specify how much
970: room to leave for the relation to grow.
971: As was mentiond in "create",
972: relations are divided into pages.
973: A "fillfactor" can be used to specify how
974: full to make each primary page.
975: This decision should be based
976: only on whether more tuples
977: will be appended to the relation.
978: For example:
979: .sp
980: .ti 5
981: .nf
982: modify donation to isam on name where fillfactor = 100
983: .fi
984: .sp
985: This tells modify to make each page 100% full
986: if at all possible.
987: .sp
988: .ti 5
989: .nf
990: modify donation to isam on name where fillfactor = 25
991: .fi
992: .sp
993: This will leave each page 25% full or, in other words,
994: 75% empty.
995: We would do this if we had roughly 1/4 of the
996: data already loaded and it was fairly well distributed
997: about the alphabet.
998: .sp
999: Keep in mind that if you don't specify the fillfactor,
1000: INGRES will typically default to a reasonable choice.
1001: Also when a page becomes full, INGRES
1002: automatically creates an "overflow"
1003: page so it is never the case that a relation
1004: will be unable to expand.
1005: .sp
1006: When modifying a relation
1007: to hash, an additional
1008: parameter "minpages" can
1009: be specified.
1010: Modify will guarantee
1011: that at least "minpage" primary pages will be allocated
1012: for the relation.
1013: .sp
1014: Modify computes how may primary pages will be
1015: needed to store the existing tuples at
1016: the specified fillfactor
1017: assuming that no overflow pages will be necessary originally.
1018: If that number is less than
1019: minpages, then minpages is used instead.
1020: .sp
1021: For example:
1022: .sp
1023: .ti 5
1024: .nf
1025: modify donation to hash on name where fillfactor = 50,
1026: .ti 10
1027: minpages = 1
1028: .sp 1
1029: .ti 5
1030: modify donation to hash on name where minpages = 150
1031: .fi
1032: .sp
1033: In the first case we guarantee that no more
1034: pages than are necessary will be
1035: used for 50% occupancy.
1036: The second case is typically
1037: used for modifying an empty or near
1038: empty relation.
1039: If the approximate maximum
1040: size of the relation is known in advance,
1041: minpages
1042: can be used to guarantee that the relation will
1043: have its expected maximum size.
1044: .sp
1045: There is one other option available for hash called
1046: "maxpages".
1047: Its syntax is the same as minpages.
1048: It can be used to specify the maximum
1049: number of primary pages to use.
1050: .sp
1051: COMPRESSION
1052: .sp 1
1053: The three storage structures
1054: (heap, hash, isam) can optionally
1055: have "compression" applied
1056: to them.
1057: To do this, refer to the
1058: storage structures as cheap, chash, and cisam.
1059: Compression reduces
1060: the amount of space needed to store each tuple
1061: internally.
1062: The current compression technique is to
1063: suppress trailing blanks in
1064: character domains.
1065: Using compression will never
1066: require more space and typically
1067: it can save disk space and improve
1068: performance.
1069: Here is an example:
1070: .sp 1
1071: .nf
1072: .ti +5
1073: modify donation to cisam on name where fillfactor = 100
1074: .fi
1075: .sp 1
1076: This will make donation a compressed isam
1077: structure and fill every page as
1078: full as possible.
1079: With compression, each tuple
1080: can have a different compressed
1081: length.
1082: Thus the number of tuples
1083: that can fit on one page will
1084: depend on how successfully
1085: they can be compressed.
1086:
1087: Compressed relations can be more expensive to update.
1088: In particular if a replace is done on one or
1089: more domains and the compressed tuple is no
1090: longer the same length,
1091: then INGRES must look for a new place to put the tuple.
1092: .sp 2
1093: TWO VARIATIONS ON A THEME
1094: .sp
1095: As mentioned, duplicates are not removed
1096: from a relation stored
1097: as a heap.
1098: Frequently it is desirable
1099: to remove duplicates and sort
1100: a heap relation.
1101: One way of doing this is to modify the
1102: relation to isam specifying
1103: the order in which to sort
1104: the relation.
1105: An alternative to this is to use either
1106: "heapsort" or "cheapsort".
1107: For example
1108: .sp
1109: .ti 5
1110: .nf
1111: modify donation to heapsort on name, ext
1112: .fi
1113: .sp
1114: This will sort the relation by
1115: name then ext.
1116: The tuples are further sorted on the
1117: remaining domains,
1118: in the order they were listed in the
1119: original create statement.
1120: So in this case the relation will be
1121: sorted on name then ext and then amount.
1122: Duplicate tuples are always removed.
1123: The relation will be left
1124: as a heap.
1125: Heapsort and cheapsort are intended
1126: for sorting a temporary relation before printing and
1127: destroying it.
1128: It is more efficient than modifying
1129: to isam because
1130: with isam INGRES creates a
1131: "directory" containing
1132: key information about each page.
1133: The relation will NOT be kept sorted
1134: when further updates occur.
1135: .sp
1136: Examples:
1137: .sp
1138: .nr in 2n
1139: Here are a collection of examples
1140: and comments as to the efficiency of
1141: each query.
1142: The queries are based on the
1143: relations:
1144: .(l
1145: parts(pnum, pname, color, weight, qoh)
1146: .br
1147: supply(snum, pnum, jnum, shipdate, quan)
1148: .sp 1
1149: range of p is parts
1150: .br
1151: range of s is supply
1152: .sp 1
1153: modify parts to hash on pnum
1154: .br
1155: modify supply to hash on snum,jnum
1156: .)l
1157: .ti +5
1158: .sp 1
1159: retrieve (p.all) where p.pnum = 10
1160: .sp 1
1161: INGRES will recognize that parts is
1162: hashed on pnum and go directly to the
1163: page where parts with number 10 would be stored.
1164: .sp 1
1165: .ti +5
1166: retrieve (p.all) where p.pname = "tape drive"
1167: .sp 1
1168: INGRES will read the entire relation
1169: looking for matching pnames.
1170: .sp 1
1171: .ti +5
1172: retrieve (p.all) where p.pnum < 10 and p.pnum > 5
1173: .sp 1
1174: INGRES will read the entire relation
1175: because no exact value for pnum
1176: was given.
1177: .sp 1
1178: .ti +5
1179: retrieve (s.shipdate) where s.snum = 471 and s.jnum = 1008
1180: .sp 1
1181: INGRES will recognize that supply is hashed on the
1182: combination of snum and jnum and will go directly
1183: to the correct page.
1184: .ti +5
1185: .sp 1
1186: retrieve (s.shipdate) where s.snum = 471
1187: .sp 1
1188: INGRES will read the entire
1189: relation.
1190: Supply is hashed on the
1191: combination of snum and jnum.
1192: Unless INGRES is given a unique
1193: value for both, it cannot
1194: take advantage of the storage
1195: structure.
1196: .sp 1
1197: .ti +5
1198: retrieve (p.pname, s.shipdate) where
1199: .ti +5
1200: .br
1201: p.pnum = s.pnum and s.snum = 471 and s.jnum = 1008
1202: .sp 1
1203: INGRES will take advantage of both
1204: storage structures.
1205: It will first find all
1206: s.pnum and s.shipdate
1207: where s.snum = 471 and
1208: s.jnum = 1008.
1209: After that it will look for all
1210: p.pname where p.pnum is equal to
1211: the correct value.
1212: .sp 1
1213: This example illustrates the idea that it is
1214: frequently a good idea to hash a
1215: relation on the domains where it is
1216: "joined" with another relation.
1217: For example, in this
1218: case it is very common to ask
1219: for p.pnum = s.pnum
1220: .sp 1
1221: To summarize:
1222: .sp 1
1223: To take advantage of a hash
1224: structure,
1225: INGRES needs an exact value
1226: for each key domain.
1227: An exact value is anything
1228: such as:
1229: .ti +5
1230: .sp 1
1231: s.snum = 471
1232: .br
1233: .ti +5
1234: s.pnum = p.pnum
1235: .sp 1
1236: An exact value is not
1237: .sp 1
1238: .ti +5
1239: s.snum >= 471
1240: .br
1241: .ti +5
1242: (s.snum = 10 or s.snum = 20)
1243: .sp 1
1244: Now let's consider some
1245: cases using isam
1246: .sp 1
1247: .in +5
1248: modify supply to isam on snum,shipdate
1249: .br
1250: retrieve (s.all) where s.snum = 471
1251: .br
1252: and s.shipdate > "75-12-31"
1253: .br
1254: and s.shipdate < "77-01-01"
1255: .sp 1
1256: .in -5
1257: Since supply is sorted first on snum and then
1258: on shipdate, INGRES
1259: can take full advantage of the
1260: isam structure to locate the
1261: portions of supply which satisfy
1262: the query.
1263: .sp 1
1264: .ti +5
1265: retrieve (s.all) where s.snum = 47l
1266: .sp 1
1267: Unlike hash, an isam structure
1268: can still be used if only the first key is
1269: provided.
1270: .sp 1
1271: .ti +5
1272: retrieve (s.all) where s.snum > 400 and s.snum < 500
1273: .sp 1
1274: Again INGRES will take advantage of the structure.
1275: .sp 1
1276: .ti +5
1277: retrieve (s.all) where s.shipdate >= "75-12-31" and
1278: .ti +5
1279: s.shipdate <= "77-01-01"
1280: .sp 1
1281: Here INGRES will read the entire relation.
1282: This is because the first key (snum) is not
1283: provided in the query.
1284: .sp 1
1285: To summarize:
1286: .sp 1
1287: Isam can provide improved access
1288: on either exact values or ranges of
1289: values.
1290: It is useful as long as at least
1291: the first key is provided.
1292: .sp 1
1293: To locate where the tuples are
1294: in an isam relation,
1295: INGRES searches the isam directory for that
1296: relation.
1297: When a relation is modified to isam,
1298: the tuples are first sorted and duplicates
1299: are removed.
1300: Next, the relation is
1301: filled (according to the fillfactor) starting
1302: at page 0, 1, 2... for as many
1303: pages as are needed.
1304: .sp 1
1305: Now the directory is built.
1306: The key domains from the first
1307: tuple on each page are collected and
1308: organized into a directory (stored in the relation
1309: on disk).
1310: The directory is never changed
1311: until the next time a modify is done.
1312: .sp 1
1313: Whenever a tuple is added to the relation,
1314: the directory is searched to find
1315: which page the new tuple belongs on.
1316: Within that page, the individual
1317: tuples are NOT kept sorted.
1318: This is what is meant by "approximately" sorted.
1319: .sp 2
1320: .nr in 0
1321: HEAP v. HASH v. ISAM
1322: .sp 1
1323: Let's now compare the relative advantages and disadvantages
1324: of each option.
1325: A relation is always created as a heap.
1326: A heap is the most efficient
1327: structure to use to initially
1328: fill a relation using copy or append.
1329: .sp 1
1330: Space from deleted tuples of a heap
1331: is only reused on the last page.
1332: No duplicate checking is done on
1333: a heap relation.
1334: .sp 1
1335: Hash is advantageous for locating tuples
1336: referenced in a qualification by an exact
1337: value.
1338: The primary page for tuples with a specific
1339: value can be easily computed.
1340: .sp 1
1341: Isam is useful for both exact values and ranges of values.
1342: Since the isam directory must be searched to
1343: locate tuples, it is never as efficient as hash.
1344: .sp 2
1345: OVERFLOW PAGES
1346: .sp 1
1347: When a tuple is to be inserted
1348: and there is no more room on the
1349: primary page of a relation, then an
1350: overflow page is created.
1351: As more tuples are inserted, additional overflow
1352: pages are added as needed.
1353: Overflow pages, while necessary, decrease
1354: the system performance for
1355: retrieves and updates.
1356: .sp 1
1357: For example, let's suppose that supply
1358: is hashed on snum and has 10 primary pages.
1359: Suppose the value snum = 3 falls on page 7.
1360: To find all snum = 3 requires INGRES to search
1361: primary page 7 and all overflow pages of page 7
1362: (if any).
1363: As more overflow pages are added the time
1364: needed to search for
1365: snum = 3 will increase.
1366: Since duplicates are removed on isam and hash,
1367: this search must be performed on appends and
1368: replaces also.
1369: .sp 1
1370: When a hash or isam relation has too many overflow pages
1371: it should be remodified to hash
1372: or isam again.
1373: This will clear up the relation
1374: and eliminate as many overflow pages as possible.
1375: .sp 2
1376: UNIQUE KEYS
1377: .sp 1
1378: When choosing key domains for a relation
1379: it is desirable to have each set of
1380: key domains
1381: as unique as possible.
1382: For example, employee id numbers
1383: typically have no
1384: duplicate values, while
1385: something like color
1386: is likely to have only a few distinct
1387: values, and something like
1388: sex, to the best of our knowledge, has only two
1389: values.
1390: .sp 1
1391: If a relation is hashed on domain sex then you can expect to have all
1392: males on one primary page and all its
1393: overflow pages and a corresponding
1394: situation with females.
1395: With a hash relation there is no solution to this
1396: problem.
1397: A trade-off must be made between the
1398: most desirable key domains to use in a
1399: qualification versus the uniqueness of the
1400: key values.
1401: .sp 1
1402: Since isam structure can be used if at least
1403: the first key is provided, extra
1404: key domains can sometimes be added to increase uniqueness.
1405: For example, suppose the supply
1406: relation has only 10 unique supplier numbers
1407: but thousands of tuples.
1408: Choosing an isam structure with the keys snum and jnum
1409: will probably give many more unique keys.
1410: However, the directory size will
1411: be larger and consequently it will
1412: take longer to search.
1413: When providing additional keys
1414: just for the sake of increasing
1415: uniqueness,
1416: try to use the smallest possible domains.
1417: .sp 2
1418: SYSTEM RELATIONS
1419: .sp 1
1420: INGRES uses three relations
1421: ("relation", "attribute", and "indexes") to maintain
1422: and organize a data base.
1423: The "relation" relation has one tuple for
1424: each relation in the data base.
1425: The "attribute" relation has one tuple
1426: for each attribute in each
1427: relation.
1428: The "indexes" relation
1429: has one tuple for each secondary
1430: index.
1431: .sp 1
1432: INGRES accesses these relations
1433: in a very well defined manner.
1434: A program called "sysmod" should be used
1435: to modify these relations to hash on the
1436: appropriate domains.
1437: To use sysmod the data base
1438: administrator types
1439: .sp 1
1440: % sysmod data-base-name
1441: .sp 1
1442: Sysmod should be run
1443: initially after the data base is created and subsequently
1444: as relations are created and the data
1445: base grows.
1446: It is insufficient to run
1447: sysmod only once and forget about it.
1448: Rerunning sysmod will cause the
1449: system relations to be remodified.
1450: This will typically remove
1451: most overflow pages and improve
1452: system response time
1453: for everything.
1454: .bp
1455: 5. SECONDARY INDICES
1456: .sp 1
1457: Using an isam or hash structure
1458: provides a fast way to find
1459: tuples in a relation given values for the key
1460: domains.
1461: Sometimes this is not enough.
1462: For example, suppose we have
1463: the donation relation
1464: .sp 1
1465: .ti +5
1466: donation(name, amount, ext)
1467: .sp 1
1468: hashed on name.
1469: This will provide fast access
1470: to queries where the qualification has
1471: an exact value for name.
1472: What if we also will be doing
1473: queries giving exact values for ext?
1474: .sp 1
1475: Donation can be hashed either on name
1476: or ext, so we would have to choose which is more common
1477: and hash donation on that domain.
1478: The other domain (say ext) can have
1479: a secondary index.
1480: A secondary index is a relation which contains
1481: each "ext" together with the exact
1482: location of where the tuple is in the relation
1483: donation.
1484: .sp 1
1485: The command to create a secondary
1486: index is:
1487: .sp 1
1488: .ti +5
1489: index on donation is donext (ext)
1490: .sp 1
1491: The general format is:
1492: .sp 1
1493: .ti +5
1494: index on relation_name is secondary_index_name (domains)
1495: .sp 1
1496: Here we are asking INGRES
1497: to create a secondary index on the relation
1498: donation.
1499: The domain being indexed is "ext".
1500: Indices are formed in three steps:
1501: .sp 1
1502: .in +4
1503: .ti -5
1504: 1. "Donext" is created as a heap.
1505: .br
1506: .ti -5
1507: 2. For each
1508: tuple in donation, a tuple is inserted
1509: in "donext" with the value for ext and the
1510: exact location of the corresponding tuple in
1511: donation.
1512: .br
1513: .ti -5
1514: 3. By default "donext" is modified to isam.
1515: .in -4
1516: .sp 1
1517: Now if you run the query
1518: .sp 1
1519: .ti +5
1520: range of d is donation
1521: .ti +5
1522: retrieve(d.amount) where d.ext = 207
1523: .sp 1
1524: INGRES will automatically look first in
1525: "donext" to find ext = 207.
1526: When it finds one it then goes directly
1527: to the tuple in the donation relation.
1528: Since "donext" is isam on ext, search for
1529: ext = 207 can typically be
1530: done rapidly.
1531: .sp 1
1532: If you run the query
1533: .sp 1
1534: .ti 5
1535: retrieve(d.amount) where d.name = "frank"
1536: .sp 1
1537: then INGRES will continue to use the hash
1538: structure of the relation "donation"
1539: to locate the qualifying tuples.
1540: .sp 1
1541: Since secondary indices are themselves relations,
1542: they also can be either hash, isam, chash or cisam.
1543: It never makes sense to a secondary index a heap.
1544: .sp 1
1545: The decision as to what structure to make
1546: them on involves the same issues
1547: as were discussed before:
1548: .sp 1
1549: Will the domains be referenced by exact value?
1550: .br
1551: Will they be referenced by ranges of value?
1552: .br
1553: etc.
1554: .sp 1
1555: In this case the "ext" domain
1556: will be referenced by exact values, and
1557: since the relation is nearly full we will do:
1558: .sp 1
1559: .ti +5
1560: modify donext to hash on ext where fillfactor = 100
1561: .ti +5
1562: and minpages = 1
1563: .sp 1
1564: Secondary indices provide a way for INGRES
1565: to access tuples based on domains
1566: that are not key domains.
1567: A relation can have any number of secondary
1568: indices and in addition
1569: each secondary index can be an index
1570: on up to six domains of the primary relation.
1571: .sp 1
1572: Whenever a tuple is replaced, deleted
1573: or appended to a primary relation,
1574: all secondary indices must
1575: also be updated.
1576: Thus secondary indices
1577: are "not free".
1578: They increase
1579: the cost of updating the
1580: primary relation, but
1581: can decrease the cost of finding tuples
1582: in the primary relation.
1583: .sp 1
1584: Whether a secondary index will improve
1585: performance or not strongly
1586: depends on the uniqueness of the
1587: values of the domains being
1588: indexed.
1589: The primary concern is whether searching
1590: through the secondary index is
1591: more efficient than simply
1592: reading the entire primary relation.
1593: In general it is if the
1594: number of tuples which satisfy the
1595: qualification is less than the number of total pages
1596: (both primary and overflow) in the primary
1597: relation.
1598: .sp 1
1599: For example if we frequently want to find
1600: all people who donated less than
1601: five dollars, consider creating
1602: .sp 1
1603: .ti +5
1604: index on donation is donamount (amount)
1605: .sp 1
1606: By default donamount will be isam
1607: on amount.
1608: IF INGRES processes the query:
1609: .sp 1
1610: .ti +5
1611: retrieve(d.name) where d.amount < 5.0
1612: .sp 1
1613: it will locate d.amount < 5.0 in the secondary
1614: index and for each tuple it
1615: finds will fetch the corresponding
1616: tuple in donation.
1617: The tuples in donamount are sorted by
1618: amount but the tuples
1619: in donation are not.
1620: Thus in general each tuple fetch from
1621: donation via donamount will be on a
1622: different page.
1623: Retrieval using the secondary index can then cause more page
1624: reads than simply reading all of donation sequentially!
1625: So in this example it would
1626: be a bad idea to create the secondary
1627: index.
1628: .bp
1629: 6. RECOVERY AND DATA UPDATE
1630: .sp 1
1631: INGRES has been carefully designed
1632: to protect the integrity of a data base
1633: against certain classes
1634: of system failures.
1635: To do this INGRES
1636: processes changes to a relation
1637: using what we call "deferred
1638: update" or "batch file update".
1639: In addition there are two INGRES
1640: programs "restore" and "purge" that can be used to check
1641: out a data base after a system failure.
1642: We will first discuss how deferred updates are created
1643: and processed, and second we will discuss
1644: the use of purge and restore.
1645: .sp 1
1646: DEFERRED UPDATE (Batch update)
1647: .in +4
1648: .sp 1
1649: .ti -5
1650: An append, replace or delete command is run in four steps:
1651: .sp 1
1652: .ti -5
1653: 1. An empty batch file is created.
1654: .ti -5
1655: 2. The command is run to completion
1656: and each change to the result relation is written into
1657: the batch file.
1658: .ti -5
1659: 3. The batch file is read and the
1660: relation and its secondary indices (if any)
1661: are actually updated.
1662: .ti -5
1663: 4. The batch file is destroyed and INGRES
1664: returns back to the user.
1665: .sp 1
1666: .in -4
1667: Deferred update defers all actual
1668: updating until the very end of
1669: the query.
1670: There are three advantages to doing this.
1671: .sp 1
1672: l. Provides recovery from system failures
1673: .sp 1
1674: If the system "crashes" during an update,
1675: the INGRES recovery program will decide to either
1676: run the update to completion or else
1677: "back out" the update, leaving the
1678: relation as it looked before the update
1679: was started.
1680: .sp 1
1681: 2. Prevents infinite queries
1682: .sp 1
1683: If "donation" were a heap and the query
1684: .sp 1
1685: .ti +4
1686: range of d is donation
1687: .ti +4
1688: append to donation(d.all)
1689: .sp 1
1690: were run without deferred update,
1691: it would terminate only when it ran
1692: out of space on disk!
1693: This is because INGRES would start reading the
1694: relation from the beginning and
1695: appending each tuple at the end.
1696: It would soon start reading the tuples it
1697: had just previously appended and
1698: continue indefinitely to
1699: "chase its tail".
1700: .sp 1
1701: While this query is certainly not
1702: typical, it illustrates the point.
1703: There are certain classes of queries
1704: where problems occur if WHEN
1705: an update actually occurs
1706: is not precisely defined.
1707: With deferred update we can
1708: guarantee consistent and logical
1709: results.
1710: .sp 1
1711: 3. Speeds up processing of secondary indices
1712: .sp 1
1713: Secondary indices can be updated
1714: faster if they are done one at a time
1715: instead of all at once.
1716: It also insures protection against
1717: the secondary index becoming inconsistent
1718: with its primary relation.
1719: .sp 1
1720: TURNING DEFERRED UPDATE OFF
1721: .sp 1
1722: If you are not persuaded by any of
1723: these arguments, INGRES
1724: allows you to turn deferred update off!
1725: Indeed there are certain cases when
1726: it is appropriate (although
1727: certainly not essential) to perform
1728: updates directly, that is, the relation is updated
1729: while the query is being processed.
1730: .sp 1
1731: To use direct update, you must be given
1732: permission by the INGRES
1733: super user.
1734: Then when invoking INGRES
1735: specify the "-b" flag which turns
1736: off batch update.
1737: .sp 1
1738: .ti +4
1739: % ingres mydate -b
1740: .sp 1
1741: INGRES will use direct update on any relation without
1742: secondary indices.
1743: It will still silently use
1744: deferred update if a relation
1745: has any secondary indices.
1746: By using the "-b" flag you are
1747: sacrificing points 1 and 2 above.
1748: In most cases you SHOULD NOT
1749: use the -b flag.
1750: .sp 1
1751: If you are using INGRES
1752: to interactively enter
1753: or change one tuple at
1754: a time, it is slightly
1755: more efficient to have deferred
1756: update turned off.
1757: If the system crashes during an
1758: update the person entering the data
1759: will be aware of the situation
1760: and can check whether the tuple
1761: was updated or not.
1762: .sp 1
1763: RESTORE
1764: .sp 1
1765: INGRES is designed to recover
1766: from the common types of system
1767: crashes which leave the Unix file
1768: system intact.
1769: It can recover from updates, creates,
1770: destroys, modifies and index commands.
1771: .sp 1
1772: INGRES is designed to "fail safe".
1773: If any inconsistancies are
1774: discovered or any failures
1775: are returned from Unix,
1776: INGRES will generate a system error
1777: message (SYSERR) and exit.
1778: .sp 1
1779: Whenever Unix crashes while INGRES
1780: is running or whenever an INGRES
1781: syserr occurs, it is
1782: generally a good idea to have the date
1783: base administrator run the command
1784: .sp 1
1785: .ti +5
1786: % restore data_base_name
1787: .sp 1
1788: The restore program performs the
1789: following functions:
1790: .in +4
1791: .sp 1
1792: .ti -5
1793: 1. Looks for batch update files.
1794: If any are found, it examines each
1795: one to see if it is complete.
1796: If the system crash occured while
1797: the batch file was being read
1798: and the data base being updated,
1799: then restore will complete
1800: the update.
1801: Otherwise the batch file was not
1802: completed and it is simply destroyed;
1803: the effect is as though the query had never been run.
1804: .sp 1
1805: .ti -5
1806: 2. Checks for uncompleted modify commands.
1807: This step is crucial.
1808: It guarantees that you will either have the
1809: relation as it existed before
1810: the modify, or restore will complete
1811: the modify command.
1812: Modify works by creating a new copy
1813: of the relation in the new structure.
1814: Then when it is ready to replace the old
1815: relation, it stores the new information in a
1816: "modify batch file".
1817: This enables restore to determine the state of
1818: uncompleted modifies.
1819: .sp 1
1820: .ti -5
1821: 3. Checks consistency of system
1822: relations.
1823: This check is used to complete "destory"
1824: commands, back out "create" commands,
1825: and back out or complete "index"
1826: commands that were interrupted by a
1827: system crash.
1828: .sp 1
1829: .ti -5
1830: 4. Purges temporary relations and files.
1831: Restore executes the "purge" program to
1832: remove temporary relations and temporary
1833: files created by the system.
1834: Purge will be discussed in more detail a bit later.
1835: .in -4
1836: .sp 1
1837: Restore cannot tell the user which queries have run and
1838: which have not.
1839: It can only identify those queries which were in the
1840: process of being run when the crash occured.
1841: When batching queries together,
1842: it is a good idea to save the output in a file.
1843: By having the monitor print out each query or set of
1844: queries,
1845: the user can later identify which queries were run.
1846: .sp 1
1847: Restore has several options to increase its
1848: usability.
1849: They are specified by "flags".
1850: The options include:
1851: .sp 1
1852: .in +4
1853: .nf
1854: -a ask before doing anything
1855: -f passed to purge. used to remove temporary files.
1856: -p passed to purge. used to destory expired rela-
1857: tions.
1858: no database restores all data bases for which you are the
1859: dba.
1860: .fi
1861: .in -4
1862: .sp 1
1863: Of these options the "-a" is the most
1864: important.
1865: It can happen that a Unix crash can cause a page of
1866: the system catalogues to be
1867: incorrect.
1868: This might cause restore to destory
1869: a relation.
1870: In fact, you might want
1871: to "patch" the system relations to correct
1872: the problem.
1873: No restore program can account
1874: for all possibilities.
1875: It is therefore no replacement
1876: (fortunately) for a human.
1877: .sp 1
1878: If "-a" is specified, restore
1879: will state what it wants to do and then ask
1880: for permission.
1881: It reads standard input and
1882: accepts "y" to mean go ahead and anything
1883: else to mean no.
1884: For example, to have restore ask you before
1885: doing anything
1886: .sp 1
1887: .ti +5
1888: restore -a mydatabase
1889: .sp 1
1890: To have it take "no" for all its questions
1891: .sp 1
1892: .ti +5
1893: restore -a mydatabase </dev/null
1894: .sp 1
1895: Using the -a flag,
1896: restore might ask for permission
1897: to perform some cleanup;
1898: for example,
1899: if it finds an attribute for which there
1900: is no corresponding relation,
1901: or if it finds a secondary index for which
1902: there is no primary relation,
1903: etc.
1904: .sp 1
1905: To date, we have never had a system
1906: crash which INGRES
1907: could not recover from.
1908: This does not mean that it will never happen, but
1909: rather that it shouldn't
1910: be too great
1911: a concern for you.
1912: It should be mentioned that restore is not
1913: a substitution for doing periodic
1914: backing up, nor does it
1915: ever perform such a function.
1916: .sp 1
1917: PURGE
1918: .sp 1
1919: Purge can be used to report expired relations,
1920: destroy temporary system relations,
1921: remove extraneous files,
1922: and destory expired relations.
1923: To use purge you must be the DBA
1924: for the data base.
1925: .sp 1
1926: .ti +5
1927: % purge mydatabase
1928: .sp 1
1929: Purge has several options which are
1930: specified by flags which are
1931: worth noting:
1932: .nr in 4n
1933: .sp 1
1934: .nf
1935: -f (default is off) remove all extraneous files.
1936: Each file is reported and then removed. If "-f"
1937: is not specified then the file is only reported.
1938: .sp 1
1939: -p (default is off) destroy all expired relations.
1940: Each expired relation is reported and if "-p"
1941: was specified the relation is destroyed.
1942: .fi
1943: .nr in 0
1944: .sp 1
1945: Purge always destroys relations and files
1946: which are known to be INGRES
1947: system temporaries.
1948: When processing multi-variable
1949: queries and queries with aggregate functions,
1950: INGRES will usually create temporary relations
1951: with intermediate results.
1952: These relations always begin with the
1953: characters "_SYS".
1954: Other INGRES commands create temporary files which also
1955: begin with "_SYS".
1956: Under normal processing they are
1957: always destroyed.
1958: If a system crash occurs, they might be left.
1959: Purge will always clean up the temporary
1960: system files.
1961: It cleans up the user relations only
1962: when specifically asked to.
This archive runs on limited infrastructure. Preserving old code on modern bandwidth. Automated agents are requested to crawl responsibly.