MODIFY(QUEL) 2/23/79 MODIFY(QUEL) NAME modify - convert the storage structure of a relation SYNOPSIS modify relname to storage-structure [ on key1 [ : sortorder ] [ { , key2 [ : sortorder ] } ] ] [ where [ fillfactor = n ] [ , minpages = n ] [ , maxpages = n ] [ , lidn = lidname ] [ DESCRIPTION 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: isam - indexed sequential storage structure cisam - compressed isam hash - random hah storage structure chash - compressed hash heap - unkeyed and unstructured cheap - compressed heap heapsort - heap with tuples sorted and duplicates removed cheapsort - compressed heapsort truncated - heap with all tuples deleted orderedn - ordered relation where n is the ordering dimension The paper ``Creating and Maintaining a Database in INGRES'' (ERL Memo M77-71) discusses how to select storage structures based on how the relation is used. 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. If the 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 on phrase must be omitted. When modifying to heapsort or cheapsort the on phrase is optional. When a relation is being sorted (isam, cisam, heapsort and cheapsort), the primary sort keys will be those specified in the on phrase (if any). The first key after the 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 on phrase will be used as least significant sort keys in domain number sequence. - 1 - MODIFY(QUEL) 2/23/79 MODIFY(QUEL) When a relation is modified to heapsort or cheapsort, the sortorder can be specified to be ascending or descending. The default is always ascending. Each key given in the on phrase can be optionally modified to be: key:descending which will cause that key to be sorted in descending order. For completeness, ascending can be specified after the colon (`:'), although this is unnecessary since it is the default. Descending can be abbreviated by a single `d' and, correspondingly, ascending can be abreviated by a single `a'. When modifying to orderedn, up to n ordering keys can be specified using the 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). Lidn can only be specified if modifying to orderedn. Default values are lid1 , lid2 , and lid3. Fillfactor specifies the percentage (from 1 to 100) of each primary data page that should be filled with tuples, under ideal conditions. 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. Minpages specifies the minimum number of primary pages a hash or chash relation must have. Maxpages specifies the maximum number of primary pages a hash or chash relation may have. Minpages and maxpages must be at least one. If both minpages and maxpages are specified in a modify, minpages cannot exceed maxpages. Default values for fillfactor, minpages, and maxpages are as follows: FILLFACTOR MINPAGES MAXPAGES hash 50 10 no limit chash 75 1 no limit isam 80 NA NA - 2 - MODIFY(QUEL) 2/23/79 MODIFY(QUEL) cisam 100 NA NA EXAMPLES /* modify the emp relation to an indexed sequential storage structure with "name" as the keyed domain */ modify emp to isam on name /* if "name" is the first domain of the emp relation, the same result can be achieved by */ modify emp to isam /* do the same modify but request a 60% occupancy on all primary pages */ 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 */ modify supply to chash on num, quan /* 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 */ modify supply to chash on num, quan where fillfactor = 75, minpages = 7, maxpages = 43 /* again the same modify but only request a minimum of 16 primary pages */ modify supply to chash on num, quan where minpages = 16 /* modify parts to a heap storage structure */ modify parts to heap /* modify parts to a heap again, but have tuples sorted on "pnum" domain and have any duplicate tuples removed */ modify parts to heapsort on pnum - 3 - MODIFY(QUEL) 2/23/79 MODIFY(QUEL) /* modify employee in ascending order by manager, descending order by salary and have any duplicate tuples removed */ modify employee to heapsort on manager, salary:descending /* ordered relation */ modify text to ordered1 on lid where lid1 = lidfield SEE ALSO sysmod(unix) ordered(quel) - 4 -