WO1992015954A1 - Compaction of a querying in relational databases - Google Patents

Abstract

Suffix trees are used for database compaction and rapid query processing. Suffix trees with Backward Item Pointers (BIP's) (108) encode all text fields and certain numeric fields within the database. BIPs in suffix trees allow any number of records to be represented by a single terminal node. Suffix trees provide compaction of large bitmap data structures comprising resident arrays (110). Suffix tree compaction allows residency arrays (110) to be compactly stored and to serve as direct sevential indexes into their underlying disk files, and allows certain SELECT operations to be performed more efficiently. Suffix tree encoding is used to identify and substitute pointers for redundant substrings within databases containing text for text compaction. A method is described for rapidly identifying a series of optimum substrings whose elimination progressively shortens the database by the largest possible increments.

Description

COMPACTION OF A QUERYING IN RELATIONAL DATABASES

TECHNICAL FIELD

This invention relates to the retrieval of data from computer database systems. In particular, the invention relates to the compaction of both database content and database lookup structures to facilitate the rapid querying of relational databases. BACKGROUND ART

Databases, by definition, are used for the storage and retrieval of data. In recent years, great emphasis has been placed on the requirement for retrieving data from databases in the most flexible ways possible. In particular, it is often important to be able to ask for any combination of data elements, and to be able to use any combination of lookup constraints as part of a database query. This requirement for "equal access" has led to the development of relational databases. Relational databases attempt to offer exactly the above promise of "equal access" to all data. Within the last ten years most relational database vendors have adopted Structured Query Language (SQL) as the access language for their databases, hereafter also database systems. SQL is well-known in the prior art. One description of SQL is given in Trimble, J. Harvey, "A Visual Introduction to SQL", John Wiley & Sons, 1989, hereafter 'Trimble".

In reality, although relational databases do a good job of permitting "equal access" to the underlying data, they do not provide "equal cost". In the context of the present invention, equal cost refers to processing time or system resources, taken alone or in any combination. For instance, almost all relational database tables are organized around a primary index. This primary index usually determines the sort order of data within the table, and usually provides a very efficient way of finding a single record if presented with the primary key value of that record. In a hypothetical PRODUCT table containing descriptions of products, t e table typically has a primary index, on a field which, for purposes of this discussion, is called PRODKEY. Every PRODKEY must be different in the table. Under these circumstances, every relational database will do a fine job of servicing the following SQL query, because the primary index field is constrained to a single value (i.e. 378). Thus,

select PRODUCTNAME from PRODUCT where PRODKEY = 378.

The typical response time for the above query on nearly any machine will be less than 1 second, even if PRODUCT contains many thousands of records.

If the constraint is on a field that has no index associated with it, the database system must process every record in the table. This is called a relation scan. The longer the table, the more expensive the process becomes. For example, if FLAVOR is not indexed, the following SQL query will cause a relation scan:

select PRODUCTNAME from PRODUCT where FLAVOR = 'Lemon-Lime'

For a large table, a typical response time is now many seconds or a number of minutes, depending on the system configuration. Note that the original promise of "equal access" has been met, since data can be retrieved either on the basis of PRODKEY or FLAVOR, but "equal cost" has not been achieved. The disparity of costs becomes very severe when multiple tables are used in a query. Relational databases promote the use of "joins" among tables. For instance, suppose that in addition to PRODUCT, another table, called SALES, has sales information for every product over a period of time.

To get the sales data on Lemon-Lime products in January, the following SQL query is entered:

select PRODUCTNAME, UNITS SOLD from PRODUCT, SALES where PRODUCT.PRODKEY = SALES.PRODKEY and PRODUCT.FLAVOR = 'Lemon-Lime' and SALES.MONTH = 'January'. Immediately after the word "where" is the phrase RODUCT.PRODKEY

= SALES.PRODKEY". This phrase causes the PRODUCT table and the SALES table to be "joined", whenever the PRODKEY values are equal. Thus, in the PRODUCT table, 'Lemon-Lime' has been requested, and in the SALES table, 'January' has been requested. In most existing relational databases, an "optimizer" tries to decide what to do with the above query. Usually, the optimizer will notice that FLAVOR is not indexed. If PRODUCT is a large table, then the optimizer may decide to evaluate the constraint on SALES (especially if MONTH is indexed), and then take the resulting records and compare them one by one with all of the records in PRODUCT to see if the PRODKEYs match. For realistically sized commercial applications, however, the SALES table may contain millions of records. Many relational databases will process the above query on a set of large tables in hours, at disastrous access cost.

Even if the optimizer decides that the better strategy is looking up Lemon-Lime first, and then joining this result to the SALES table, it is possible that much wasted computation can take place. For instance, if there are many

Lemon-Lime products, the relational database will match each of these products to January in the SALES table, and attempt to retrieve each of these combinations from the disk. However, only a fraction of the retrievals will actually get back data, since not all of the Lemon-Lime products were sold in

January. In practice, this becomes a significant factor. A typical commercial database system describing product shipments, for instance, is only 5 to 10% populated across all possible combinations of product, market, and time. This means that even with "perfect optimization", up to 90% to 95% of the attempts to retrieve data are wasted.

As described, a simple business query can run for hours on a large computer. Complex business queries exacerbate all of the above problems to the point where the relational database becomes useless for processing business data, no matter how large the underlying machine is, i.e. even if it has unlimited system resources.

Truly complex business queries almost always involve one or many simultaneous retrievals of related records from different parts of the database, hereafter termed join-sum queries. Join-sum queries are considered as embedded subqueries within the context of the overall query. Such embedded subqueries are uniformly treated as "left outer joins with implicit summation".

For instance, a comparison of product shipments with "year ago" requires a join-sum query retrieving today's shipment records as well as the corresponding shipment records for the same time period "a year ago". Thus, a single SELECT statement, used as a benchmark on a multi-million record database, requesting twelve join-sum comparisons of sales changes over time compared to various products, various markets, and various different time periods has never been observed to run to completion on any SQL relational database, even after days of run time on large computers. Tweaking and tuning the database does not help. Therefore, it appears that the basic conventional architecture of an optimizer, join processor, index evaluator, relation scanner, and temporary table builder is simply the wrong architecture for processing such queries.

To resolve the above problems in commercial database environments, a new approach is needed which: 1) provides much more uniform access cost for different kinds of queries on a single table; 2) processes joins of multiple "dimensional" tables, such as PRODUCT, into larger tables, such as SALES, much more efficiently; 3) processes complex business queries involving join- sums efficiently; and 4) only attempts to retrieve data stored on the disk that is "actually there". DISCLOSURE OF INVENTION A database system constructed according to the present invention employs three new variants of a data structure known as a "suffix tree". To date, suffix trees have been used in deep space telemetry communications to reduce the redundancy of transmissions, as well as in applications such as the pre-processing of data prior to encryption. See Fiala and Greene, "Data Compression with Finite Windows", Communications of the ACM, April 1989, pp 490-505 hereafter 'Tiala and Greene", and McCreight, E.M., "A space- economical suffix tree construction algorithm", Journal of the ACM 23, 2 (1976), pp 262-272, hereafter "McCreight", which are incorporated by reference as if fully set forth herein.

In the present invention, three forms of suffix trees are used to encode both the content of database files and the lookup indexes of database files to permit database compaction and rapid query processing of many-to-1 joins. Suffix trees, whose terminal nodes are marked with backward item pointers (BEPs), are used to encode certain text fields and certain numeric fields within the database. The resulting suffix tree is a fast index into the database, where every possible sequence, hereafter also "substring", of the encoded data is equally accessible. The first use of BIPs in the suffix tree allows an unlimited number of discontinuous records within the database to be economically represented by a single terminal node in the suffix tree, hence allowing a practical implementation of this approach in an actual database.

The second application of suffix trees provides the compaction of a large bitmap data structure known as the residency array. The residency array encodes the true disk residency of all key combinations within certain tables in the database. The suffix tree compaction allows the residency array to be stored in a practical amount of computer storage, allows the residency array to serve as a direct sequential index into the underlying disk file and allows certain kinds of embedded SELECT operations to be performed more efficiently.

In the third application, suffix tree encoding is also used to identify redundant substrings within databases 'containing text as well as redundant substrings within indexes, and to substitute pointers for these redundant substrings so as to compact the database and to compact the indexes. The present invention provides a method for rapidly identifying a series of optimum substrings whose elimination progressively shortens the database by the largest possible increments. The shorter database is more practical to store on magnetic or optical media.

The present invention includes a new provision for join-sum processing. Within the inner loop of the primary record retrieval algorithm according to the present invention, subqueries required by each requested join-sum are launched if and only if the current record retrieval for the overall query is actually successful. If multiple records are returned by the subquery, the values retrieved are aggregated by summation before they are joined to the current record from the overall query (hence the term join-sum). If multiple join-sums are requested in a single query which lead to redundant subqueries, the redundancy is discovered and the subqueries are only performed once.

BRIEF DESCRIPTION OF DRAWINGS For fuller understanding of the present invention, reference is made to the accompanying drawing in the following detailed Description of the Preferred Embodiment of the invention. In the drawing:

Figure 1 is a block diagram of a computer system configured for loading and query processing of compacted databases in relational database systems constructed according to the principles of the present invention.

Figure 2 is a block diagram of a tree structured multi-star database schema constructed according to the principles of the present invention.

Figure 3 is a block diagram of a permitted subset of the schema of Figure 2.

Figure 4 illustrates a conventional suffix tree according to the prior art Figure 5 is a block diagram of a system for loading compacted databases in relational database systems constructed according to the principles of the present invention. Figure 6 is a block diagram of a system for query processing of the compacted databases of Figure 5.

Figures 7 is a block diagram of the query processing sequence for joined dimension tables in the system of Figure 6. Figure 8 is a perspective drawing of a residency array constructed according to the principles of the present invention.

Figure 9 is a block diagram of an example of the query processing sequence for the joined dimension tables of Figure 7B.

Figure 10 illustrates a terminal node crisis in a conventional suffix tree according to the prior art.

Figure 11 is a block diagram of a suffix tree constructed according to the principles of the present invention.

Figure 12 is a diagram of a bit vector constructed according to the principles of the present invention. Figure 13 illustrates a common substring table constructed according to the principles of the present invention.

Figure 14 is a diagram showing bit vector processing according to the principles of the present invention.

Figure 15 is a flow diagram of a common string elimination algorithm constructed according to the principles of the present invention.

Figure 16 is a diagram of an augmented suffix tree constructed according to the principles of the present invention.

Reference numbers refer to the same or equivalent parts of the present invention throughout the several figures of the drawing.

BEST MODE OF CARRYING OUT THE INVENTION

The preferred embodiment of the present invention incorporates computer system 10, configured as shown in Figure 1. The preferred embodiment is dedicated to processing queries on schemas that can be described as "tree structured multi-star joins" or as subsets of such schemas, and where all joins are foreign-key to primary-key joins. Referring again to Figure 1, computer system 10 comprises central processing unit (CPU) 11, operator console 13, disk drives 14 and 9-track magnetic tape drive 15. CPU 11 may be a VAX 4000, model 300, having 32 megabytes of random access memory (RAM) and VMS operating system 12 installed thereon. Disk drives 14 may comprise six model RA71 400 megabyte disk drives for a total of 2.4 gigabytes of mass storage. Magnetic tape drive 15 may comprise a Model M2444, 6250 bpi, 9-track drive manufactured by Fujistu America, Inc. The components of system 10 are currently manufactured by or are available from Digital Equipment Corporation. Computer system 10 may be coupled to local area network (LAN) 5 through which it may communicate with 20 or more remote work stations and other network servers. LAN 5 may be Ethernet or any other suitable network designed for use with the DEC system components listed herein or other equivalent computer system. A tree structured multi-star join schema according to the present invention is illustrated in Figure 2. Within such a schema, tables with multi¬ part keys are designated as "fact tables" and tables with single part keys are designated as "dimension tables". In this example, lines 20, i.e. between tables, represent joins. Fact table 22 has a three-part concatenated key. Market table 24 has a foreign key, hereafter also "fkey", (region), which is joined to the primary key (region) in region table 25. A fully general multi-star join could have additional fact tables joined on some or all of the dimension tables, and could have an arbitrary number of additional dimension tables, such as time table 21 and product table 23, joined in a tree structure, as long as no cyclic joins are represented. A permitted subset of the schema of Figure 2 is shown in Figure 3 where fkey 1 of dimension table 31 is joined to primary key 2 of dimension table 32 and fkey 2 of the last-mentioned table is joined to primary key 3 of dimension table 34. Similarly, fkey 2 of dimension table 31 is joined to primary key 4 of dimension table 33. Dimension table 33, in turn, has fkey 4 and fkey 5 joined to primary keys 5 and 6 of dimension tables 35 and 56, respectively. In the preferred embodiment of the present invention, fact tables are always joined to the primary keys of dimension tables, and the separate primary keys of the dimension tables are concatenated together to form the single primary key of the fact table. There are three advantages to this class of schemas: first, virtually all large databases used for querying are already in exactly this format, or can easily be transformed into this format; second, using the techniques described in this specification, the choice of which constraints to evaluate first can be made immediately; and third, this schema is easily understood by end users. The database management system of the present invention utilizes a suffix tree data structure. The suffix tree techniques described herein are applicable to any database schema organization.

Suffix trees are a generalization of Patricia trees, described in "The Art of Computer Programming", Volume 3, Addison-Wesley, 1973, which is incorporated by reference as if fully set forth herein. Referring to Figure 4, a conventional suffix tree includes terminal nodes 40 pointing to the position in the text passage uniquely prefixed by the set of arc labels to that terminal node (e.g., ADAM). A suffix tree encodes information found in a text string so that every possible substring of the text string may be looked up directly in the suffix tree by beginning at the root node and following arcs labeled by the characters of the desired substring. The suffix tree contains explicit arcs for the characters of the substring up to the point where the substring is unique within the overall text string. At the point where uniqueness occurs, the suffix tree possesses a terminal node pointing to the unique location of the next character in the substring defined by the arcs leading to that terminal node. A suffix tree differs from a Patricia tree in several ways, especially by containing a special set of navigational pointers within the tree structure that allows a search algorithm to jump from one node in the tree directly to the point in the tree defined by the suffix of that node, i.e., the substring consisting of all characters of the original substring except the first character. The ability to perform this jump without having to scan the tree from the root node is central to the efficient construction of suffix trees. The preferred embodiment of the present invention is used in two modes: Loading and Querying. During Loading, end users are not given access to the data. A database administrator identifies data to the system intended to be either dimension tables or fact tables. The database system builds the database by reading the data and preparing various data structures, including those based on suffix trees. At the conclusion of Loading, the system is ready for Querying.

Loading

The system diagram for the Loading mode of the preferred embodiment of the present invention is shown in Figure 5. Thus loader system 100 includes loading specification 101, specification parser 102, loading specification error messages 103, loader processor 104, original dimension file input 105, original fact file input 106, rejected record list 107, BIP suffix trees 108, unique number tables 109, residency array 110, and fact table storage 111. To load data, the system administrator prepares a loading specification 101 as a file. The loading specification 101 describes the format and content of dimension table input 105 and fact table input 106. Errors in the loading specification are sent to the operator as loading specification error messages 103. If no loading specification errors exist, control is passed to the loader processor 104. The loader processor 104 reads all of the dimension file input 105 first Various data structures are prepared from the dimension file input, including the BIP suffix trees 108, and the unique number tables 109. After all the dimension tables have been processed, the loader processor 104 reads all fact files 106. Residency array 110 is prepared for each fact file, and the records of the fact file are transferred to disk storage 111 to await actual retrieval during Query mode.

The meaning and interpretation of the data structures in BIP suffix trees 108, unique number tables 109, residency array 110, and fact table storage 111 are described in detail in the discussion of the Query mode. Querying

The system diagram for the Query mode of the preferred embodiment of the present invention is shown in Figure 6. Thus, relational database system 200 includes applications programming interface (API) 201, parser 202, BIP suffix trees 108, BIP lookup routines 204, unique number table lookup routines 205, unique number tables 109, fetcher 207, residency array 110, fact table storage 111, disk storage cache 210, staging array 211, and calculator 212. To process a query, a remote application engages API 201 and establishes a session. The SQL describing this query is passed through API 201 to parser 202. Parser 202 examines the query, and for each dimension table, passes control to the BIP lookup routines 204 to evaluate the respective text constraints against that table using the BIP Suffix Trees, or to the unique number table lookup routines 205 to evaluate the numeric constraints against that table using the unique number tables 109. Dimension tables are fully inverted: every possible substring of every text element is encoded in the BIP suffix trees 108, and every number in the dimension table is stored in the unique number tables 109. In this way, the system is essentially insensitive to nature of the constraint No field in a dimension table is "unindexed" as is often the case with conventional relational database systems, and no kind of constraint is discriminated against as being "expensive".

Referring to Figure 7, if dimension tables 70 and 72 are joined to each other via join 20, the system starts with the table for which the join is specified against the table's primary key. Thus, any constraints on table 72 are evaluated first A join is processed by passing values of primary key 75 in table 72 to foreign key field 73 of table 70 as a set of constraints specified by constrained field 77. The remaining constraints on table 70 specified by constrained field 79 are processed last.

With continuing reference to Figure 7, since join 20 is a foreign key-to- primary key join, the join is specified against the foreign key for table 70. Using the BIP suffix tree routines 204 or the unique number table lookup routines 205, the primary keys of table' 70 are identified from the applicable constraints. The values of the primary key are then used as constraints on table 72 against its foreign key. This process is then repeated if additional dimension tables are joined. The order of processing is always in the above described direction, as shown in Figure 3. For dimension tables joined to fact tables, the primary keys of all the dimension tables are first evaluated from the applicable constraints. Then the system concatenates these primary keys together to form the concatenated key of the fact table. The system then looks up the value of the concatenated key in residency array 110 to determine if the desired record exists. If it exists, and it is not already present in the disk storage cache 210, a disk retrieval of the record is performed.

Referring now to Figure 8, suppose that the fact table has three concatenated keys, representing Product, Market, and Time. In the loading process, the records of each dimension table have been assigned consecutive numerical ID's starting in each case with 1. The product ID is referred to as PID, the market ID as MID, and the time ID as TID. Using List-of-IDs 80, which satisfy the dimension table constraints, the system begins with the first combination of keys in the overall sort order, for example, (PID, MID, TID) = (2, 15, 8). With continuing reference to Figures 6 and 8, the separate dimension keys (PID, MID, and TID) are combined to form all possible fact lookup requests. Each lookup request is passed to residency array 110. Four records (marked by *) are resident. The block ID's for those records are computed from residency array 110.

If the star join is a multi-star join, then additional fact tables 90 and 92 exist that are joined to one or more of dimension tables 21, 23 and 24, as shown in Figure 9. In this case, each time the constraints against dimension tables are processed, where the dimension tables are joined to more than one fact table, the joins to all other fact tables are also evaluated, and the proper relational subset of the resulting cartesian product is created. After the record is fetched from a fact table, it is added to staging array

211. If no aggregations (such as SUM, MIN, MAX, COUNT OR AVERAGE) are being performed, the newly fetched record is added to the end of staging array 211. If aggregations are taking place, the newly fetched record is aggregated into the appropriate row of staging array 211 as shown in Table I.

TABLE I Finally, after all fetches have taken place, calculator 212 performs certain sequential operations on staging array 207, including sorting, ranking, cumulations, tertiles, running sums, running averages and break point aggregations as shown in Table II. As the last step, the contents of staging array 211 are passed back to the remote application via API 201.

Table II

Backward Item Pointer (BIP, Suffix Trees

Each field of a database table can be considered to be of type text or type number, and in both cases, limits are placed on the maximum length or size of the data within an individual field. In practice, text fields are rather short, with many fields having a maximum width of 20 to 40 characters. Typically, very few commercial business data databases use text fields wider than 256 characters. Similarly, most numeric fields are integer fields with maximum widths of 1, 2, or 4 bytes. The remaining numeric fields usually are floating point fields occupying 4 or 8 bytes. As described elsewhere in this specification, suffix trees have been used successfully to provide rapid access into very long text passages. In such cases, each terminal node of the suffix tree points to a position in the text passage whose unique prefix is described by the set of arcs leading from the root node of the suffix tree to the terminal node in question.

By using a suffix tree in a database application, every possible substring of a text field, and every unique occurrence of a number can be found by traversing the suffix tree in a very efficient manner starting at the root node. The problem with previous suffix tree encodings is that they are inapplicable to the case of thousands or millions of discrete little text fragments that typify the contents of databases. Previous suffix tree encodings would require each terminal node to enumerate all the discontinuous record locations that contained the substring described by the set of arcs leading to that terminal node. Thus, there could be potentially millions of pointers to the terminal node for ADAM of Figure 4 as depicted in Figure 10. The data structures required to enumerate all such discontinuous record locations would overwhelm the data storage required to represent the suffix tree, and the advantages of using the suffix tree technique would be lost

The complexity of terminal node descriptions in previous suffix tree encodings is overcome by building a BIP Table for each field according to the present invention. Referring to Table IE, a BIP Table contains of all the unique occurrences of a given text field, sorted backward. In other words, all the duplicate field values are removed, then the remaining list is sorted by last character, then by second-to-last character, and so on. The resulting list is numbered from 1 to N. These numbers are the BIP values in Table IE. There are exactly as many BIPs as there are unique occurrences of values in the field. BIP Unique Substring BIP-to-ID List

1 xyz 1, 5, 29

2 abz 2, 35

3 kly 56, 292 4 abm 82, 99, 100

5 zzl 543

Table m The suffix tree is then built over the unique substrings in the BIP table in accordance with McCreight. A heretofore unrecognized property of suffix tree encoding is that, in the context of the present invention, every terminal node in the suffix tree must point to a consecutive span of BIPs. This allows every terminal node to be represented by exactly two integers as shown in Figure 11, rather than by thousands or millions of pointers to the original fields of the database as depicted in Figure 10. Thus, the terminal node for ADAM according to the present invention is defined by BIP span 237:254.

In order to actually use the BIPs to retrieve data, the original record IDs in the database must be recoverable from a given BIP or span of BIPs. This is made possible by building a list of original database record IDs with each BIP at the time the original table is reduced to its set of unique occurrences. This list of record IDs with each BIP is referred to as the BIP-to-ID lists. With continuing reference to Table IE, by representing the original record IDs as integers from 1 to K, the BIP-to-ID lists turn out to be simple lists of monotonically increasing integers. When processing a span of BIPs, or multiple spans of BIPs, these lists of integers are combines with a very efficient single pass merge. An alternate form of the BIP-to-ID list is a bit vector whose length is the number of records in the table. In this case, a 1 indicates that the record identifies by the bit position contains exactly the backward item substring. To recapitulate, all text constraints and selected numeric constraints are evaluated by looking up the substring (or value) in BIP Suffix Tree 108. This always yields a span of BIPs or multiple spans of BIPs. Each BIP has an associated BIP-to-ID list. These lists are then merged in one pass to produce a set of monotonically increasing original record IDs. This set of record IDs is very useful in this form because, in effect, these IDs are the ordered values of the particular index for one of the dimensions in the final "array lookup" that defines the query.

Many advantages are gained by sequentially ordering IDs because efficient disk caching and other high performance techniques can be employed when the data in the fact table is looked up sequentially. Processing of the list of original record IDs is described elsewhere in this specification with respect to the description of residency array 110. Where two dimensions tables are joined to each other, the list of original record IDs are the primary keys in the foreign key-to-primary key join. This type of join is processed as described previously.

A second set of pointers is needed to handle a "reverse lookup". A reverse lookup occurs when the system knows which original record IDs are required but needs the value of some field for each of those IDs. This second set of pointers is called the ID-to-BIP Table. Referring to Table IV, the ID-to- BIP Table is simply a list of BIPs, one for each original record. To evaluate a reverse lookup, the original ID is used as the index into this list, and the BIP is produced. Then the BIP Table is accessed for the actual value of the field.

ID BIP

1 752

2 2

3 67 4 13

5 95

6 133 Table IV

Note that the original field values may be discarded, since all the information about the original text column is found in the combination of information from the ID-to-BIP Table and the BIP Table. Unique Number Tables

For numeric fields, a similar technique to that used for text fields is used. The following data structures are needed:

1) Unique Number Pointer (UNP) Table (See Table V); 2) UNP-to-ID lists (See Table V); and

3) ID-to-UNP Table (See Table VI).

. . .

Table V In a given numeric column, all the values are sorted and the duplicates removed. The remaining entries are the values of the Unique Number Table. The entries in the Unique Number Table are numbered from 1 to N, in sorted order. These numbers are the UNP's. A list of original record IDs, in numerical order, is stored with each UNP and original numerical value which, in turn, comprises Unique Number Table V.

Table VI During the sorting and duplicate suppression step, a list of exactly N UNPs is built so that the UNP may be found in a single lookup, given the original record ID, as shown in Table VI. This is the mechanism needed to handle reverse lookups. In summary, a numeric constraint is evaluated by looking up the number in the Unique Number Table, using binary search or hash table access. This always yields a list, or multiple lists, of UNPs. Each UNP has an associated UNP-to-ID list These lists are then rapidly merged to produce a set of monotonically increasing original record IDs. Such a list is used in exactly the same way that the list of record IDs from BIP Suffix Tree 103 is used for text fields. Again, the description of residency array 110 includes a discussion of how this list of original record IDs is processed.

Residency Array

Residency array 110 is used with fact table storage 111 1) to determine if a record actually exists; and 2) to determine exactly where in the table an existing record resides.

All fact tables have a concatenated key. Each component of the key is "managed" by a particular dimension table. When a query is processed, the constraints on each dimension table are separately evaluated, yielding separate lists of IDs, one per dimension as described elsewhere in this specification with respect to Figure 8. Note that for dimension table-to-fact table joins, the IDs are, in all cases, consecutive integers beginning with 1 for each dimension. The IDs were assigned by the system during the data loading process, and are not visible to the user. Original "native" key fields are kept, but are not used by the system to actually compute dimension table-to-fact table joins.

In its simplest form, residency array 110 is a bitmap with the same overall dimensionality as its associated fact table. A "1" at a particular combination of PID, MID, and TID ( in the current example) indicates that the record exists. Furthermore, by keeping track of how many I's have been traversed from the "upper left corner" of residency array 110, the system knows what record number in the fact table is being accessed. By storing the fact records in fixed length format, the system can immediately compute which data block must be fetched to retrieve the record, either by computing the disk address directly, or by computing the location of a pointer to the desired disk address. Furthermore, by looking up the records in the order of the underlying storage, consecutive fetches can be more easily monitored to determine whether a record is already in the disk storage cache 210 because one of its neighbors has been previously fetched in the same block of data. It should be noted that variable length storage schemes may be handled by separating the records into fixed and variable components. The fixed component then contains a pointer to the variable component, which exits elsewhere in a storage "heap", i.e. an area of computer memory used as a scratch pad for managing data storage.

The model of residency array 110 as a full bitmap is unworkable for large databases because the residency array must be the full^* size of the abstract space defined by the dimensions. For a fact table that is 5% occupied, such a residency array is many times bigger than it needs to be. Therefore, the key to a workable residency array is compression.

Consider residency array 110 organized as shown in Figure 8. The actual records in its associated fact table are stored in sorted order, by Product, then by Market, and finally by Time. The actual order chosen is immaterial. The uncompressed Residency Array thus appears as a matrix of bit vectors, indexed by Product and Market. The bit vectors are stored in their own storage heap. Each bit vector represents the residency of the set of Times, for a particular Product and Market. A specific PID and MID combination provides the index to the bit vector, and the TID denotes which bit is to be examined. Stored with each bit vector is the master count of I's from the "upper left corner" of the fact table. This master count is picked up as the bit vector is processed, and the number of 1-bits is determined by scanning the bit vector and adding the number to the master count.

Referring now to Figure 12, the vectors 120 are organized into groups of 7 bits. Each group is then stored in an 8-bit byte. The 8th bit is used as a flag to determine if the byte is to be interpreted as part of the bit vector or is to be interpreted as a pointer.

As a mnemonic aid, the 7-bit groups may be thought of as characters.

Thus, the bit vectors are character sequences. Compression of the character sequences is accomplished by finding and removing common substrings. This technique, which is also used for data compression of the dimension and fact tables, is described elsewhere in this specification, and is referred to as

Common Substring Elimination.

The common substrings that appear redundantly in a given set of bit vectors 120 are removed and placed in a separate substring table as illustrated in Figure 13. With each common substring is stored its length and the number of 1-bits it contains. In the bit vector, a common substring is pointed to by using the 8th bit in the storage byte as a flag to indicate that the remaining 7 bits are a pointer into the common substring area rather than bit vector data. In the preferred embodiment of the present invention, if the 8th bit is a zero, the byte is bit data; if the 8th bit is a one, the byte is a pointer.

The system can be configured so that a maximum of 128 common substrings may be referenced by interpreting the 7 bits as a pointer.

Alternatively, this may be extended by always interpreting the 7 bits plus the next 8 bits as a pointer. This allows 2^K common substrings. The decision as to how many common substrings to allow is an administrative decision made at database load time.

As bit vectors 120 are being scanned, the system counts the number of bits from the beginning of each bit vector. The number of bits in a normal bit vector data byte is determined in one step by using the contents of the byte to look up the number of bits in a 128-way dispatch table, as shown in Table VIL If the desired target record is contained in the byte itself, more detailed processing is required. If a pointer byte is encountered, then the system continues scanning the string by accessing the common substring table to determine if the entire substring can be skipped. If so, then the substring is not examined further, but the count of I's is added to the current accumulation. Byte Contents # of I's

1 1

2 1

3 2 4 1

5 2

6 2

7 3

8 1 9 2 etc. etc.

Table VH This skipping of common substrings is a key step. Not only are the common substrings the key to data compression of residency array 110, they are also the key to sub-linear processing time in the lookup of a given record. If the entire substring cannot be skipped, then the desired record is found somewhere in the substring. The substring itself is retrieved, and the processing continues as described above.

Note that, in general, the common substrings may be recursively defined. A common substring is encoded both as regular bit vector data, as well as embedded substrings, using the above described encoding scheme.

To recapitulate, a query is evaluated by first evaluating the constraints on the dimensions. For each dimension, an ordered list of dimension IDs is produced. When all such lists are ready, the candidate concatenated IDs are generated by taking one ID from each dimension list. The concatenated IDs are generated in order of storage within the fact table. The concatenated IDs form a set of array indexes for accessing residency array 110. The particular bit vector defined by the first n-1 IDs is scanned from its beginning to the desired bit identified by the nth ID. As the individual bytes of the bit vector are being scanned, they are interpreted either as bits or as pointers to common substrings. Bytes interpreted as bits are simply evaluated to count the number of embedded bits. Bytes interpreted as pointers are followed to the Common Substring Table where string scanning is resumed and the length of the common substring is examined. If the target bit is not in the common substring, then the common substring is entirely skipped and its bit count is added to the running total. If the target bit is found to be a zero, the search is halted, no disk retrieval takes place, and the next candidate concatenated

ID is searched for. Note that this is what happens most of the time. If the target bit is a 1, the current bit count is added to the master bit count stored with this bit vector, and the location of the fact record is known at that point

In certain types of queries, in applications, the next concatenated ID may be in the same bit vector as the last one. As explained elsewhere in this specification, the list of concatenated IDs is always presented in sequential order to the fetcher. In this case, the search for the target bit resumes at the point the last search (successful or unsuccessful) left off.

Finally, after the location of the desired record is known, the current memory cache of disk blocks fetched from the fact file is examined to see if the record is already in memory. If it is, no I/O is necessary. In small to medium sized tables, it is sufficient to compress 'only the bit vectors themselves. In large sparse tables, it is also necessary to compress the indexes to the bit vectors, since most of the bit vectors will be empty. In the example using PIDs, MIDs, and TIDs, it was assumed that the bit vectors described the residency of TIDs for all the combinations of PID and MID. If this table is large and sparse, then for each PID, a bit vector, hereafter also PID-vector, is defined that describes which MIDs possess any bit vectors.

With reference now to Figure 14, to lookup a concatenated ID, the PID- vector is scanned, counting the I's from its origin. If a 1 is found at the location of the desired MID, then a bit vector of TIDs exists. The location of the bit vector is found by the count of I's in the PID-vector. This count provides an offset into an array of addresses for bit vectors.

If there are a very large number of PID-vectors, then they can also be compressed using the common substring technique described elsewhere herein. The PID level itself may also be encoded, especially if the dimension table for PIDs contains many PIDs that are known not to occur anywhere in the database. However, usually the top level ID will have 100% residency in the underlying fact table. In this case, the bit vector lookup scheme is not used.

Embedded loin-Sum Processing

A common query situation in business data processing involves the comparison of multiple records in different parts of the fact table. For instance, a share of market calculation involves the ratio of a given product's performance to that of a base product or perhaps to an entire set of base products. Similarly, an index of production calculation involves the ratio of shipments made at one time to the shipments made during a base period in the past The calculation of these quantities is awkward in SQL because SQL forces the alternate records to be referenced as if they existed in separate tables, as shown elsewhere in this section of the specification. Frequently, these references are processed very inefficiently by the database management system. As noted elsewhere in this specification, SQL is more fully described in Trimble, which is incorporated by reference as if fully set forth herein.

Embedded join-sum processing, according to the present invention, processes the alternate query fetches as close to the inner loop of the main fetch as possible. In join-sum processing according to the present invention, constraints are inherited from surrounding queries which allows the system to recognize duplicate queries. When the alternate records are physically close to the main records, they will be fetched at the same time. Finally, the join-sum query is interpreted as an outer join to the main query. The technique described here automatically produces the correct outer join, as shown in the example given below. Consider the following pseudo-SQL: select dollars,

Join-Sum(dollars, where market = Total U.S.') from fact f, product p, market m, time t where f.prodkey = p.prodkey and f.marketkey = m.marketkey and f .timekey = ttimekey ' and p.brand = 'Crest Toothpaste' and (m.market = 'Los Angeles' or m.market = 'San Francisco⁷) and tmonth = 'July, lJW The purpose of this pseudo-SQL is to display the dollar shipments of Crest Toothpaste in Los Angeles and San Francisco in July, 1989 together with the dollar shipments of the same product in the Total U.S. in the same time period. All of the SQL is normal except for the "join-sum" function call. The meaning of the join-sum function is to fetch a field from the fact table, subject to all of the constraints inherited from the main query, except where modified on a dimension-by-dimension basis by the embedded constraint of the join- sum function (in this case, market = Total U.S.'). It is intended that the embedded constraint replace all of the constraints of the main query for the dimensions implied by the embedded constraint (in this case, market). For those dimensions not specified in the join-sum query, those dimensions are inherited from the surrounding query (i.e. brand = 'Crest Toothpaste', and month = 'July, 1989⁷).

Note that the conventional SQL for the above query (not using the pseudo SQL extensions described in the present invention) requires multiple references to the fact and market tables to express the same result as the join- sum syntax as given below. select fl.dollars, f2.dollars from fact fl, fact f2 product p, time t market ml, market m2 where fl.prodkey=p.prodkey and fl.market=m2marketkey and fl.timekey-=ttimekey and f2.prodkey=ρ.prodkey and f2.marketkey=m2.marketkey and f2.timekey=ttimekey and p brand = 'Crest Toothpaste' and (ml.market = 'Los Angeles' ^• or ml.market = 'San Francisco') and m2.market = Total U.S.' and tmouth = 'July, 1989'

The join-sum function syntax presented above is not merely a convenient way to express a subquery. It is the specification for a scheme to efficiently process queries that are related to the main query.

When the above query is processed, the "global" constraints on the product, market, and time dimensions are evaluated, and the primary records are fetched from the fact table. These records are loaded into staging array 211 as shown in Figure 6. Staging array 211 contains columns for each of the items called for in the select-list and is indexed by the PID, MID, and TID of each record that is retrieved.

As each record from the main query is fetched into the staging array 211, the join-sum query is evaluated. In the above case, when a particular PID, MID, TID combination is retrieved, the join-sum query replaces the MID with the MID (or MIDs) implied by the alternate constraint, and the alternate records are fetched in a separate step. Where multiple records are fetched in such a step of the join-sum query, the values returned are always summed into the particular row of staging array 211. For this reason, the join-sum query is used with "additive" fields in the fact table.

As the join-sum query is being evaluated, the system keeps track of which PID-TID combinations it has already retrieved. Thus, PID-TID combinations for Los Angeles in the above example are not retrieved again for

San Francisco if they exist in both circumstances. Therefore, constraints inherited from the surrounding query eliminate duplicate processing.

During the processing of the join-sum query, a number of efficiencies are possible. In the present example, if the bit vectors in the residency array denote consecutive MIDs, then the records searched for in the join-sum query will be very close to the records found in the main query. Any processing required to locate the bit vector is saved because the same bit vector is used for both the main query and the join-sum query. Since the values being retrieved into the staging array 211 from the join-sum query must be additive, the two retrievals may be done in parallel, in order to maximize the overlap of the use of the disk memory cache.

Even if the join-sum query is not evaluated in the same bit vector as the main query, the two retrievals will still both be done in the most efficient PID, MID, TID order. Multiple join-sum queries are processed at the same time.

Common Substring Elimination

Common substring elimination is used in several of the key data structures in the preferred embodiment of the present invention. Text fields in dimension tables and fact tables are compressed by eliminating the common substrings. Residency arrays for fact tables are also compressed by considering their bit vectors as substrings of characters. The following discussion will consider all of these cases simultaneously. The uncompressed data structure and its various partially compressed versions will be referred to as the "source string", where it is understood that this source string is alternatively either a text field in a dimension table, a text field in a fact table, or a string of characters in a bit vector in a residency array or even in a BIP- to-ID list expressed as a bit vector. Common substrings of strings may also comprise substrings in substrings of characters.

The Common substring elimination algorithm is depicted in Figure 15. In step 531, the suffix tree is scanned to find the optimal substring to be replaced, where the optimal substring produces the greatest overall reduction in total size of the source string plus the common substring storage. For example, if the pointer substring is two bytes long, and if the candidate substring to be replaced is 10 bytes long, then if there are 1000 instances of the candidate substring, there will be a net savings of 1000*(10-2) - 10 = 7990 bytes. The pointer substring is a substring that points into an array containing the original text of the substrings that have been replaced. In step 532, the program exits if the optimal savings found are less than or equal to zero, or if the pointer space is exhausted, or if a pre-defined recursion level has been exceeded. The pointer space is defined by the length of the pointers inserted into the source string. A one-byte pointer yields 128 substrings, a two-byte pointer yields 32,768 substrings, and so on. The recursion level is defined by how many pointer within pointer levels are tolerated. In step 533, the substitution defined by the optimal substring is stored on a list for later batch update of the original source string. In step 534, the suffix tree is updated to account for all instances of the substitution (even though the original source string is not updated until later). Steps 531, 532, 533, and 534 are repeated until the system exits from step 532. In step 535, all of the substitutions are performed on the original source string in a single pass, and the process terminates.

This process is then repeated to find a second substring to be replaced, and so on, until the pointer substring array is filled up, or until no more savings can be effected by replacing substrings. Note that this procedure is optimal in the sense that, at each step, the substring is found that gives the largest possible net saving in total storage.

It is possible for a candidate substring itself to contain a pointer substring. This is allowed, although in such a case the pointer substring only counts for two characters in the candidate substring.

While the above scheme is straightforward to describe, it may be difficult to find the successive candidate substrings rapidly without repeatedly scanning the uncompressed substring. Note that the source string is often radically changed by the removal of a common substring. It is not correct to make up a list of replacements in a single pass and then perform them all. True optimal compression requires a recalculation of the best candidate substring after each substitution. Optimal compression is achieved by building a specially augmented suffix tree for the source string and then by successively scanning and modifying this suffix tree as the substitutions are made.

Referring now to Figure 16, suffix tree 160 is augmented by an auxiliary data structure that, for each node of the tree, includes 1) the name of the node, i.e., the complete substring defined by all the arcs from the root node of the suffix tree to the current node; 2) the character count C from the root of the tree, i.e., C=2; 3) the count of the number of terminal nodes F below the current node i.e., F=628; 4) suffix link 161, as described in McCreight, which is a navigational pointer to the unique node in the tree where the substring consisting of all but the first character in the substring defining the current node terminates; 5) threading pointer 163 that provides a circular alphabetic sort of all the nodes that possess the same suffix link; and 6) reverse suffix link 165 that points to the first node in a threading sequence such that its suffix link points to the current node. Only the first and second items are altered by the substring processing. The remaining four items are computed exactly once when the suffix tree is created.

Note that, in Figure 16, the reverse suffix link for XY is the node ID for AXY. The suffix link for node AXY is the node ID for XY. The threading pointer for node AXY is the node ID for BXY.

The best possible substring to be replaced is found by scanning all the nodes of the tree and computing the value F*(C-P)-C, where F is^*the number of terminal nodes, C is the character count, and P is the length of the pointer substring, usually 1 or 2. This value is the net number of bytes saved in the overall source string and substring array by making the substitution of the substring defined by the node with a pointer substring. The maximum value of this metric found anywhere in the suffix tree defines the best possible substring to replace.

Once the best possible substring is found, all the terminal nodes beneath the candidate nodes are enumerated, since they point to all the places in the source string where the substitution is to take place. The substitutions are either performed at that time, or are stored in a list for batch processing against the original data.

Now the character count of the chosen node and all of its descendants must be reduced by the net number of characters saved in each substitution.

The chosen node is marked with the name of the pointer substring. Thus, if the same pointer is traversed again at some point in the future, the substring for which it is substituted is correctly identified. With continuing reference to Figure 16, additionally, all of the other instances of the substring in the tree must be found and treated similarly. The other instances are all suffixes of other longer substrings. To find the other instances of the substring, the reverse suffix link from the chosen node is followed to the first "cousin" node. For a cousin node, the character count of the cousin node and all of its descendants is reduced, and the cousin node is renamed in the same manner as described for chosen nodes.

The threading pointer from the cousin node is followed to all of its sisters who are also cousins of the original chosen node (i.e. whose suffix links point to the chosen node). It should be noted that the reverse suffix link points to the first such cousin node. Thus, the threading pointer will identify all the sisters in succession.

For each cousin and sister node found in the above procedure, their reverse suffix links are followed to find the "second cousin" nodes and the entire process is repeated. This process is recursively repeated until no more nodes are found. The tree derived according to Figure 11 has now been modified to reflect the substitution and is now ready for scanning for the next substring. In practice, this procedure converges rapidly.

The entire substitution process is repeated until 1) the pointer substrings are all used up, or 2) no more savings can be realized by making further substitutions, or 3) a maximum limit to the number of recursively embedded substitutions is reached. The last limitation will be determined empirically for each machine and memory configuration, since it provides a direct way to trade off CPU processing vs. I/O. It should be noted that common substring elimination procedures are used only at data loading time to build the primary static data structures used by the preferred embodiment. This procedure is not used during queries.

Note that, in general, it is not required to build the entire suffix tree for the original string. If the string consists of millions of addresses, for instance, the suffix tree derived from the first ten thousand addresses may be a good "predictor" of a set of efficient substitutions to reduce the size of the original string. Care must be taken, in this case, that the portion of the original substring used to build the predictor suffix tree is statistically representative of the entire string.

The present invention has been particularly shown and described with respect to certain preferred embodiments of features thereof. However, it should be readily apparent to those of ordinary skill in the art that various changes and modifications in form and details may be made without departing from the spirit and scope of the invention as set forth in the appended claims.

Claims

CLAIMS We claim:

1. In storage media having a plurality of locations capable of storing digital information, a data structure for use in query processing for relational database systems comprising: a first set of storage locations containing data representing retrievable information, said data having unique substrings; a second set of storage locations containing data representing lookup indexes, said data having unique substrings; and in each of said first and second sets of storage locations, the data representing retrievable data and lookup indexes including suffix tree encoding of said unique substrings.

2. A data structure as in claim 1 wherein the data representing retrievable information and lookup indexes includes text and numeric data fields.

3. A data structure as in claim 1 wherein accessibility of said substrings is substantially equal among all substrings.

4. A data structure as in claim 1 further including a third set of storage locations containing data representing the true residency of all unique substrings in the storage media.

5. A data structure as in claim 4 wherein the data representing the true residency of all unique substrings includes suffix tree encoding of said unique substrings.

6. A data structure as in claim 4 wherein said third set of storage locations includes pointers for accessing said data representing the true residency of all unique substrings.

7. A data structure as in claim 1 further including a fourth set of storage locations containing data representing redundant substrings in said first and second sets of storage locations.

8. A data structure as in claim 7 wherein said fourth set of storage locations includes pointers for accessing said redundant substrings in said fourth sets of storage locations.

9. A data structure as in claim 7 wherein said data representing redundant substrings includes suffix tree encoding of said redundant substrings.

10. A data structure as in claim 1 wherein: said query processing includes processing subqueries; and said data structure includes a fifth set of storage locations containing data representing aggregations of values retrieved by subqueries.

11. A data structure as in claim 10 wherein said fifth set of storage locations includes pointers for accessing data representing aggregations of values in said fifth set of storage locations.

12. A data structure as in claim 10 wherein said data representing aggregations of values retrieved by subqueries includes suffix tree encoding of said data representing aggregations of values.

13. A data structure as in claims 7 or 8 wherein said data representing redundant substrings includes data representing the length of said redundant substrings and includes data representing the number of a preselected type of data incorporated into said substrings.

14. Apparatus for retrieving information from relational database systems in response to user queries, said apparatus comprising: data storage means having a plurality of locations for storing information and having a first set of storage locations containing data representing retrievable information, said data having unique substrings, a second set of storage locations containing data representing lookup indexes, said data having unique substrings, in each of said first and second sets of storage locations, the data representing retrievable data and lookup indexes including suffix tree encoding of said unique substrings, a third set of storage locations containing data representing the true residency of all unique substrings in the storage media and a fourth set of storage locations containing data representing redundant substrings in said first'and second sets of storage locations; lookup means, coupled to said second and fourth sets of storage locations, for evaluating constraints on said user queries; and fetching means, coupled to said lookup means and said first and third storage locations, for retrieving data representing queried information from said first set of storage locations in response to evaluated constraints from said lookup means and from said third set of storage locations.

15. Apparatus as in claim 14 further including staging means coupled to said fetching means for receiving and processing said data representing queried information and for transmitting said queried information to said user.

16. Apparatus as in claim 15 wherein: said user queries include subqueries; and said staging means includes a fifth set of storage locations containing data representing aggregations of values retrieved by a subquery.

17. Apparatus for loading information into relational database systems in response to user queries, said information including original fact data and original dimension data, said apparatus comprising: data storage means having a plurality of locations for storing information and having a first set of storage locations containing data representing retrievable information, said data having unique substrings, a second set of storage locations containing data representing lookup indexes, said data having unique substrings, in each of said first and second sets of storage locations, the data representing retrievable data and lookup indexes including suffix tree encoding of said unique substrings, a third set of storage locations containing data representing the true residency of all unique substrings in the storage media and a fourth set of storage locations containing data representing redundant substrings in said first and second sets of storage locations; loading means for producing instructions for controlling the format and content of data contained in said first and second set of storage locations; and processor means, coupled to said loading means and to said data storage means, for producing data contained in said third and fourth sets of storage locations in response to instructions received from said loading means.

18. In storage media having a plurality of locations capable of storing digital information, a method for structuring data for use in query processing in relational database systems, said method comprising the steps of: storing data representing retrievable information in a first set of storage locations, said data having unique substrings; storing data representing lookup indexes in a second set of storage locations, said data having unique substrings; and in each of said first and second sets of storage locations, including suffix tree encoding of said unique substrings in the data representing retrievable data and lookup indexes.

19. A method of structuring data as in claim 18 wherein the data representing retrievable information and lookup indexes includes text and numeric data fields.

20. A method of structuring data as in claim 18 wherein the steps of storing data in said first and second sets of storage locations provide substantially equal access to said substrings.

21. A method of structuring data as in claim 18 further including the step of storing data representing the true residency of all unique substrings in the storage media in a third set of storage locations.

22. A method of structuring data as in claim 18 further including the step of storing data representing redundant substrings in said first and second sets of storage locations in a fourth set of storage locations.

23. A method of structuring data as in claim 22 wherein said step of storing data representing redundant substrings in said fourth set of storage locations includes the step of providing pointers for accessing said redundant substrings in said first and second sets of storage locations.

24. A method of structuring data as in claim 18 further including the step of storing data representing aggregations of values retrieved by a subquery in a fifth set of storage locations.

25. A method of structuring data as in claim 24 wherein said step of storing data representing aggregations of values retrieved by a subquery includes the step of providing pointers for accessing data representing aggregations of values in said fifth set of storage locations.

26. A method of structuring data as in claim 24 wherein said step of storing data representing aggregations of values retrieved by subqueries includes the step of suffix tree encoding said data representing aggregations of values.

27. A method of structuring data as in claims 22 or 23 wherein said step of storing data representing redundant substrings includes the steps of storing data representing the length of said redundant substrings and storing data representing the number of a preselected type of data incorporated into said substrings.

28. A method for retrieving information from relational database systems in response to user queries, said method comprising the steps of: storing information in data storage means having a plurality of locations and having a first set of storage locations containing data representing retrievable information, said data having unique substrings, a second set of storage locations containing data representing lookup indexes, said data having unique substrings, in each of said first and second sets of storage locations, the data representing retrievable data and lookup indexes including suffix tree encoding of said unique substrings, a third set of storage locations containing data representing the true residency of all unique substrings in the storage media and a fourth set of storage locations containing data representing redundant substrings in said first and second sets of storage locations; evaluating constraints on said user queries; and retrieving data representing queried information from said first set of storage locations in response to evaluated constraints from said lookup means and from said third set of storage locations.

29. A method as in claim 28 further including the step of receiving and processing said data representing queried information and for transmitting said queried information to said user.

30. A method as in claim 29 wherein: said user queries include subqueries; and further including the step of storing data representing aggregations of values retrieved by a subquery in a fifth set of storage locations.

31. A method for loading information into relational database systems in response to user queries said information including original dimension data and original fact data, said method comprising the steps of: storing information in data storage means having a plurality of locations and having a first set of storage locations containing data having unique substrings representing retrievable information; a second set of storage locations containing data having unique substrings representing lookup indexes; in each of said first and second sets of storage locations, the data representing retrievable data and lookup indexes including suffix tree encoding of said unique substrings; a third set of storage locations containing data representing the true residency of all unique substrings in the storage media; and a fourth set of storage locations containing data representing redundant substrings in said first and second sets of storage locations; producing instructions for controlling the format and content of data contained in said first and second set of storage locations; and producing data contained in said third and fourth sets of storage locations in response to instructions for controlling the format and content of data in said first and second set of storage locations.

32. A data structure as in claims 1 or 4 or 9 or 12 wherein said suffix trees include backward item pointers for identifying terminal nodes of said suffix trees.

33. A data structure as in claim 32 wherein a plurality discontinuous data representing retrievable information is represented by single terminal node.

34. A method as in claim 21 wherein the step of storing dat representing the true residency of all unique substrings includes the step suffix tree encoding of said unique substrings.

35. A method as in claim 22 wherein the step of storing dat representing redundant substrings includes the step of suffix tree encoding said redundant substrings.

36. A method for structuring data as in claim 18 or 26 or 34 or 3 wherein the step of including suffix tree encoding includes the step including backward item pointers for identifying terminal nodes of said suffi trees.

37. A method for structuring data as in claim 36 wherein the step storing data representing retrievable information includes the step including the step of representing discontinuous data by a single termin node.

38. A method as in claims 22 or 23 wherein the step of storing dat representing redundant substrings includes the steps of storing dat representing the length of said redundant substrings and storing dat representing the number of a preselected type of data incorporated into sai substrings.

39. Apparatus as in claim 15 wherein said fetching means includ means for determining whether the queried information exists in said first s of storage locations and, if so existing, determining where said querie information resides in said first set of storage locations.

40. Apparatus as in claims 15 or 39 wherein said fetching mea includes subquery fetching means for retrieving data representing subquerie information in close proximity to data representing said queried informatio

41. A method as in claims 28 or 31 further including the steps determining whether the queried information exists in said first set of storag locations and, if so existing, determining where said queried information resides in said first set of storage locations.

42. A method as in claims 28 or 31 wherein the step of retrieving data representing queried information further includes the steps of retrieving data representing subqueried information in close proximity to data representing queried information.