CA2427071C - Method and system for space management for multidimensionally clustered tables - Google Patents

Method and system for space management for multidimensionally clustered tables Download PDF

Info

Publication number
CA2427071C
CA2427071C CA2427071A CA2427071A CA2427071C CA 2427071 C CA2427071 C CA 2427071C CA 2427071 A CA2427071 A CA 2427071A CA 2427071 A CA2427071 A CA 2427071A CA 2427071 C CA2427071 C CA 2427071C
Authority
CA
Canada
Prior art keywords
block
storage space
checking
cell
page
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Expired - Lifetime
Application number
CA2427071A
Other languages
French (fr)
Other versions
CA2427071A1 (en
Inventor
Leslie A. Cranston
Bishwaranjan Bhattacharjee
Matthew A. Huras
Bruce G. Lindsay
Timothy R. Malkemus
Catherine S. Mcarthur
Sriram K. Padmanabhan
Michael J. Winer
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
IBM Canada Ltd
Original Assignee
IBM Canada Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by IBM Canada Ltd filed Critical IBM Canada Ltd
Priority to CA2427071A priority Critical patent/CA2427071C/en
Publication of CA2427071A1 publication Critical patent/CA2427071A1/en
Application granted granted Critical
Publication of CA2427071C publication Critical patent/CA2427071C/en
Anticipated expiration legal-status Critical
Expired - Lifetime legal-status Critical Current

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/28—Databases characterised by their database models, e.g. relational or object models
    • G06F16/284—Relational databases
    • G06F16/285—Clustering or classification

Landscapes

  • Engineering & Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • Theoretical Computer Science (AREA)
  • Data Mining & Analysis (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

An information retrieval system and method of operation therefor, wherein a plurality of dimensions for a table are identified, and at least one block in a plurality of blocks in the table is associated with a dimension value for each dimension in the plurality of dimensions, where each block comprises a plurality of contiguous storage pages. Additionally, the at least one block can be further associated with an associated cell, wherein the associated cell has a unique combination of dimension values comprising an associated dimension value for each dimension in the plurality of dimensions. A unique associated bit list for each dimension value for each dimension in the plurality of dimensions has a unique corresponding list entry for each block associated with that dimension value, and a unique associated bit list for each cell has a unique corresponding list entry for each block associated with that cell. These unique associated bit lists facilitate searching, insertion and deletion of records. Each list entry in the unique associated bit lists may also have an associated storage space indicia bit for its associated block for indicating whether the block is likely to be full for further facilitating insertion of records and management of data storage space. Each block may also have a unique associated empty page bitmap for indicating when pages in the block are empty and for identifying when the entire block is empty so that the block may be disassociated from its associated dimension values and associated cell.

Description

METHOD AND SYSTEM FUR SPACE MANAGEMENT FOR
MULTIDIMENSIONALLY CLUSTERED TABLES
Field of the Invention [0001] The present invention relates generally to database systems and, more particularly, to techniques for providing multidimensional disk clustering in relational databases and for efficient access and maintenance of information stored in relational databases using multidimensional disk clustering.
Background of the Invention
[0002] Almost all businesses are interested in deploying data warehouses to obtain business intelligence in order to improve profitability. It is widely recognized in the technical world that most data warehouses are organized in multidimensional fashion. The text by Ralph Kimball et al., The Data Warehouse T'oolkit: Practical Techniques for Building Dimensional Data Warehouses, John Wiley & Sons, ISBN: 0471 153370, 1996, describes the use of multidimensional schema to model data warehouses.
[0003] A multidimensional array layout has been used by many online analytical processing (OLAP) systems for organizing relatively small data warehouses.
However, this multidimensional array structure does not scale well for large daGr warehouses such as those that require more than 100 gigabytes of storage. Such large data warehouses are still implemented using the relational database model. While conventional relational databases provide some clustering and data partitioning, these techniques are not adequate for supporting multidimensional data.
[0004] OLAP systems tend to organize data using many or all dimensions. For efficiency reasons, the conceptual multidimensional array is actually implemented by a multilevel structure. The dimensions are separated into dense and sparse sets based or the expected number of entries for each dimension value. The dense dimensions are implemented as a multidimensional array and the sparse dimensions are used to point to each sub-array.
U.S. Patent No. 5,359,724 by Earle describes such a technique. This arrangement is still inefficient because the dense dimensions are only partially utilized. For instance, in real-world data, it has been reported that dense arrays are usually only about 20%
occupied.
[0005] Spatial databases and geographic information systems use a two- or three-dimensional data model. Many data structures and methods have been proposed for organizing and indexing spatial data, e.g., R-Trees, QuadTrees, and Grid Files. Some of these indexing structures have been implemented as extensions of a relational database management system (RDBMS) but have not considered the full requirement for maintenance and query processing required in data warehouses or other such implementations.
Additionally, the techniques for efficiently clustering the two- or three-dimensional data have not been considered in these systems.
j0006] A multidimensionally clustered table is one whose data is simultaneously clustered along one or more independent dimensions, or clustering keys, and physically organized into blocks of pages on disk. Once such a table is created, one can specify one or more keys as dimensions along which to cluster the table's data. Each of these dimensions can consist of one or more columns as index keys do.
[0007] Every unique combination of dimension values forms a logical "cell", which includes blocks of pages, where a block is a set of consecutive pages on disk.
The set of blocks that contain pages with data having a certain key value of one of the dimensions is called a "slice". Every page of the table is part of exactly one block, and all blocks of the table consist of the same number of pages: the blocking factor.
[0008] When inserCing new records into the table, it is highly desirable that the number of blocks searched in a cell for space to record the new record in the table be minimized. Further, if none of the blocks in the table have space available to store the new record, and an additional block must be associated with a cell, it is highly desirable that it be quickly determinable what block can be assigned. Accordingly, new techniques for managing blocks in a table are highly desirable.
Summary of the Invention [0009] In one aspect, the invention provides, for an information retrieval system, a method for maintaining clustered data, the method including identifying a plurality of dimensions of a table using a plurality of table definition parameters, associating at least one block in a plurality of blocks in the. table. with an associated cell, the associated cell having a unique combination of dimension values including an associated dimension value for each dimension in the plurality of dimensions, clustering data according to dimension value, for each dimension in the plurality of dimensions, by storing the data in the at least one associated block, and, storing storage state information regarding each block in the plurality of blocks.
[0010] In another aspect, the invention provides an information retrieval system for maintaining clustered data, including means for identifying a plurality of dimensions of a table using a plurality of table definition parameters, means for associating at least one block in a plurality of blocks in the table with an associated cell, the. associated cell having a unique combination of dimension values including an associated dimension value for each dimension in the plurality of dimensions, means for clustering data according to dimension value, for each dimension in the plurality of dimensions, by storing the data in the at least one associated block, and, means for storing storage state information regarding each block in the plurality of blocks.
[0011] In yet another aspect, the invention provides a computer program product having a computer readable medium tangibly embodying computer executable code for directing an information retrieval system to mainritin clustered data, the computer program product including: code for identifying a plurality of dimensions of a table using a plurality of table definition parameters, code for associating at least one block in a plurality of blocks in the table with an associated cell, the associated cell having a unique combination of dimension values including an associated dimension value for each dimension in the plurality of dimensions, code for clustering data according to dimension value, for each dimension in the plurality of dimensions, by storing the data in the at least one associated block, and, code for storing storage state information regarding each block in the plurality of blocks.
[0012] In a still further aspect, the invention provides a computer data signal embodied in a carrier wave and having means tangibly embodied within the computer data signal for directing an information retrieval system to maintain clustered data, the computer data signal including: means for identifying a plurality of dimensions of a table using a plurality of table definition parameters, means for associating at least one block in a plurality of blocks in the table with an associated cell, the associated cull h<riving a unique combination of dimension values including an associated dimension value for each dimension in the plurality of dimensions, means for clustering data according to dimension value, for each dimension in the plurality of dimensions, by storing the data in the at least one associated block, and, means for storing storage state information regarding each block in the plurality of blocks.

Brief Description of the Drawings [0013] A better understanding of these and other embodiments of the present invention can be obtained with reference to the following drawings and detailed description of the preferred embodiments, in which:
[0014] Figure 1 is a block diagram of an exemplary environment for use with the methodologies of the invention;
(0015] Figure 2 is a graphical illustration of the exemplary relational database management system of Figure l in accordance with an embodiment of the present invention;
[0016] Figure 3 is a graphical illustration of an exemplary multidimensional table clustered along two dimensions in accordance with an embodiment of the present invention;
[0017] Figure 4 is a graphical illustration of the exemplary multidimensional table of Figure 3 with dimensional block indexes on each dimension in accordance with an embodiment of the present invention;
[0018] Figure 5 is a graphical illustration of an exemplary composite dimension index in accordance with an embodiment of the present invention;
(0019] Figure 6 is a graphical illustration of the exemplary multidimensional table of Figure 3 extended to a third dimension in accordance with an embodiment of the present invention;
[0020] Figures 7(a)-(c) are graphical illustrations of an index ANDing technidue in accordance with an embodiment of the present invention;
[0021] Figure 8 is a graphical illustration of an exemplary block map in accordance with an embodiment of the present invention;
[0022] Figure 9 is a graphical illustration of various exemplary maintenance functions in accordance with an embodiment of the present invention;
[0023] Figures 10A, l0B is a flowchart of a method of searching blocks in a cell for space in which to insert a record in accordance with a further aspect of the present invention;
(0024] Figure 1 l, in a llowchant, illustrates a method of searching a block for storage space in accordance with an aspect of the invention;

(0025] Figure 12 is a flowchart illustrating a method for determining if there is a free block in the table available for re-use; and, [0026] Figure 13 is a flowchart of a method of deleting a record in accordance with a preferred or variant of the invention.
[0027] Similar references are used in different figures to denote similar components.
Detailed Description of the Embodiments (0028] The following detailed descr7ption of the embodiments of the present invention does not limit the implementation of the invention to any particular computer programming language- The presem invention may be implemented in any computer programming language provided that the OS (Operating System) provides the facilities that may support the requirements of the present invention. A preferred embodiment is implemented in the C or C++ computer programming language (or other computer programming languages in conjunction with C/C++). Any limitations presented would be a result of a particular type of operating system, computer programming language, or data processing system and would not be a limitation of the present invention.
[0029] First, an environment for multidimensional disk clustering using a relational database management system (RDBMS) in accordance with an aspect of the invention is described in the context of FIG. 1. The system 100 includes a processor 102 coupled to a memory 101, one or more input/output (I/O) devices 104, and an RDBMS 103. It is t.o be appreciated that the processor 102 can implement the aspects or methods of the present invention. Alternatively, the RDBMS 103 may have its own processor, memory, and I/O
device configuration (not shown) to implement the aspects or methods of the present invention. In this arrangement, the RDBMS 103 can be hosted on a server platform, for example, and the processor 102, the memory 101, and the I/O devices 104 can be associated with the processor 102 in a client system. Of course, one skilled in the art would readily appreciate that there are many other configurations that may be adapted to process queries using the RDBMS 103 without departing from the spirit and scope of the present invention.
[0030] The memory 101 may be used by the processor 102 in performing, for example, storage of information used by the processor 102. The I/O devices 104 may include a keyboard, a mouse, and/or any other data input device, which permits a user to enter queries and/or other data to the system 100. The I/O devices 104 may also include a displa.~y, a printer, and/or any other data output device, which permits a user to observe results associated with queries and/or other processor operations. The RDBMS 103 may contain system software (such as depicted in Fig. 2) to process structured query language (SQL) commands (or other types of queries). including optimizing the SQL commands (or other types of queries) and executing them to obtain information passed back to the processor 102.
It is to be understood that the structured data associated with the RDBMS l03 is organized in individual tables, where each table may have a multitude of rows and columns.
In a common configuration, an end user using an I/O device 104, such as a standard computer keyboard and/or pointing device, may enter a particular SQL command (or cause the command t.o be generated). The processor 102 may then receive this command from the I/O
device 104, and send it to the RDBMS 103. The RDBMS 103 may then parse the SQL command, optimize the parsed result, and execute it against information associated with the RDBMS. The RDBMS 103 may send a result table back to the processor 102, and the processor 102 may then cause the result table to be displayed to the end user associated with the I/O device 104.
It should be appreciated, however, that there are many other ways of interacting with the RDBMS 103. For instance, a computer program running in batch mode may interact with the RDBMS I03 without direct user interaction.
[0031 j It is to be appreciated that the term "processor" as used herein is intended to include any processing device, such as, for example, one that includes a CPU
(central processing unit). The term "memory" as used herein is intended to include memory associated with a processor or CPU, such as, for example, RAM, ROM, a fixed memory device (e.g., hard drive), a removable memory device (e.g., diskette), etc. In addition, the term "input/output devices" or "I/O devices" as used herein is intended to include, for example, one or more input devices, e.g., a keyboard, for making queries and/or inputting data to the processing unit, and/or one or more output devices, e.g., CRT
display and/or printer, for presenting query results and/or other results associated with the processing unit.
It is also to be understood that various elements associated with a processor may be share~.d by other processors. Accordingly, software components including instructions or code for performing the methodologies of the aspects of the invention, as described herein, may be stored in one or more of the associated memory devices (e.g., ROM, fixed or removable memory) and, when ready to be utilized, loaded in part or in whole (e.g., into RAM) and executed by a CPU.

[0032] Those skilled in the art will recognize that the exemplary environment illustrated in Figure 1 is not intended to limit the present invention. Those skilled in the art will appreciate that other alternative environments may be used without departing from the spirit and scope of the present invention.
(0033] Figure 2 illustrates the exemplary RDBMS 103. The RDBMS 103 has a query compiler 201, a code generator 202, an execution engine 203, an access methods engine 204, a buffer manager 205, a utility engine 206, a data manager 207, and other components 208.
[0034] In preferred embodiments of the present invention, the RDBMS 103 includes the DB2 product offered by International Business Machines Corporation for UNIX, WINDOWS NT, and other systems. It should be appreciated, however, that the aspects of the present invention has application to any relational database software, whether associated with the DB2 product or otherwise.
[0035] In operation, the RDBMS 103 executes on a computer system and may communicate with one or more clients using a network interface, for example.
It can also operate in a standalone server mode receiving instructions from a user via commands.
Typically, the client/user issues SQL commands that are processed by the RDBMS
103 and results are returned. During operation, the query compiler 201 parses the input SQL
commands and uses the code generator 202 to generate an execution plan. The parsed SQL
commands are typically transformed into an internal representation and are then optimized.
Optimization involves looking at several alternative strategies for obtaining the correct result, and choosing the most efficient strategy. The execution engine 203 interprets and executes the plan and produces the desired results. The execution engine 203 submits requests to the data manager 207 to obtain information from tables. This is done in the manner that was determined by the query compiler 201 (or separate optimizer), using available indexes, scanning tables, etc. The execution engine 203 uses the access methods engine 204 to efficiently access the. underlying database tables that are stored in the access methods engine 204 (or externally thereto). The relevant data items are then retrieved and stored in the buffer manager 205 for reusability of the data. Typically, relational database management systems provide sequential table scan access as well as index-based access to tables.
The B-Tree index is the most preferred index technique in RDBMS systems. Optionally, some RDBMS
systems allow that the underlying data be clustered and/or partitioned using one or more columns (or index).

[0036] In accordance with the aspects of present invention, the multidimensional clustering technique described herein impacts the following major components of the RDBMS 103:
[0037] 1 ) Data Manager 207 and Access Methods Engine 204: Several new data layout and record management structures, along with modifications to the B-Tree index technique for accessing the data are provided. Also, new techniques for managing concurrent access and recovery of the data structures are supported.
[0038] 2) Execution Engine 203: New operators for query processing and database maintenance operations to take advantage of the changes to the Data Manager 207 and the Access Methods Engine 204 are provided.
[0039] 3) Query Compiler 201 and Code Generator 202: New cost-based techniques for choosing between the new operators and existing operators are provided.
[0040] 4) Utility Engine 206: New schemes to perform utility operations such as bulk loading and data reorganization are provided.
[0041] In general, the RDBMS 103 software, and the instructions derived therefrom, are all tangibly embodied in a computer-readable medium, e.g., a medium that may be read by a computer. The RDBMS software and the instructions derived therefrom, are all comprised of instructions which, when read and executed by a computer system, causes the computer system to perform the necessary steps to implement and/or use the aspects of the present invention. Under control of an operating system, the RDBMS 103 software and the instructions derived therefrom, may be loaded from an appropriate data storage device and into memory of a computer system for use during actual operations.
[0042] Figure 3 illustrates an exemplary multidimensional table clustered along two dimensions. The multidimensional table shown in Figure 3 is clustered along dimensions 'YearAndMonth' 300 and 'Province' 310. Records in the table are stored in blocks, which may contain an extent's worth of consecutive pages stored on disk or some other suitable medium. In the diagram, a block is represented by an oval, and is numbered according to the logical order of allocated extents in the table. For example, the oval 301 points to the first block in the table, which is block 1. The grid in the diagram represents the logical partitioning of these blocks, and each square, such as square 302, represents a logical cell. A
column or row in the grid represents a slice for a particular dimension. For example, all records containing the. value 'ON' (for 'Ontario') in the Province 310 column are found in the blocks contained in a slice defined by the '(7N' column in the grid. In fact, each block in this slice only contains records having 'ON' in the province field. Thus, a block is contained in this slice or column of the grid if and only if' it contains records having 'ON' in the province field. In the exemplary multidimensional table depicted in Figure 3, the slice for the 'ON' province column includes blocks 9, 16, t8, l9, 22, 24, 25, 30. 36, 3y, 41, and 42.
[0043] While blocks are numbered sequentially starting from block 1 in the exemplary table shown herein, it should be appreciated that the blocks could be identified in numerous other ways. For instance, the first block in a table could alternatively be labelled i 0 as block 0. One skilled in the art would realize that various other ways to identify portions of information related to a table could be devised and different terminology employed without departing from the spirit and scope of the present invention.
[0044] Figure 4 illustrates the exemplary multidimensional table of Figure 3 with dimensional block indexes on each dimension. To facilitate the determination of which blocks lie in a slice, or which blocks contain all records having a particular dimension key value, a dimension block index may he automatically created for each dimension when a table is created. Thus, a dimension block index 400 may be cretated on the YearAndMonth dimension 300, and a dimension block index 410 may be created on the Province dimension 310. Each dimension block index can be structured in the same manner as a traditional record-based index, except that at the leaf level the keys point to a block identifier (BID) instead of a record identifier (RID). Advantageously, since each block contains potentially many pages of records, these block indexes are much smaller than RID indexes and need only be updated as new blocks are required and so added to a cell, or existing blocks are emptied so as to be removed from a cell.
[0045] A slice, or the set of blocks containing pages with all records having a particular key value in a dimension, will be represented in the associated dimension block index by a BID list for that key value.
[0046] In the exemplary multidimensional table depicted in FIG. 4, to find the slice containing all records with 'ON' for the Province dimension, we would look up this key value in the Province dimension block index, and find a key such as the following:
<ON: 9, 16, 18, 19, 22, 24, 25, 30, 36, 39, 41, 42>

where the key is in the form of a <key value: BID(s)> pair.
[0047] The key comprises a key value, namely 'ON', and a list of BIDs. Each BID
contains a block location. We se.e that, in this example, the block numbers listed are the same as those found in the 'ON' column, or slice, found in the grid for the multidimensional table.
Similarly, to find the list of blocks containing all records having '9902' for the YearAndMonth dimension, we would look up this value in the YearAndMonth dimension block index, and find a key such as the following:
<9902: 2, 5, 7, $, 14, 15, 17, 18, 31, 32, 33, 4a>.
[0048] The clustering of the table may be specified in the SQL language using an appropriate clause added to the Create Table or Alter Table statements by which the clustering attributes can be specified. For example, the following Create Table statement may be used to create the table in this example.
CREATE TABLE TABLE_1 (Date DATE, Province CHAR(2), YearAndMonth INTEGER
ORGANIZE BY DIMENSIONS (YearAndMonth, Province);
[0049] In this case, the dimensions YearAndMonth and Province were defined for table TABLE_1 using the DIMENSIONS clause. The clustering of the table must be enforced on all the data in the table. In particular, if the clustering is specified using the Alter Table command on an existing table, this will require a reorganization of the data to be performed as well. Note that the block indexes can be created for the clustering attributes automatically.
[0050] Figure 5 illustrates a data structure for an exemplary composite dimension index. When a record is inserted into a table, we wish to determine if a cell already exists for its dimension values. If one does, we will insert the record into an existing block of that: cell if possible, or add another block to that cell if the current blocks are full.
If the cell doesn't yet exist, we will want to create a new cell and add a block to it. This automatic maintenance may be implemented with an additional block index, called a composite dimension index, and can be created when the multidimensional table is created. This composite dimension index will be on all the dimension columns of the table, so that each key value corresponds to a particular cell in the table and its BID list of blocks comprising that cell.
This is shown in Figure 5. This composite block index assists in quickly and efficiently finding those blocks having a particular set of values for their dimensions. It may also be used to dynamically and efficiently manage and maintain the physical clustering of data along the dimensions of the table during an insert operation. It should be. appreciated that multidimensional index structures such as R-trees could also be used to implement a composite dimension index if they were to point to blocks rather than records or objects.
[0051] Figure 6 illustrates an extension of the table of Figure 4 that includes an additional dimension. If we have a multidimensional table dimensioned on the YearAndMonth dimension 300, the Province dimension 310, and a Color dimension 600, as shown in Figure 6, for example, this can be thought of as a logical cube. In this example, four block indexes would be created: one for each of the individual dimensions, i.e., the YearAndMonth dimension 300, the Province dimension 310, and the Color dimension 400;
and a composite dimension index (not shown) with all of these dimension columns as a key.
[0052] One of the goals of the above-described multidimensional table structure is to facilitate efficient query processing. We now discuss the query processing methods that are facilitated by the aspects of present invention. Consider the three-dimensional cube shown in Figure 6, which is dimensioned along the YearAndMonth dimension 300, the Province dimension 310, and the Color dimension 600. A query such as the following:
"What is the aggregate sales of Color = 'Red' over all dates and regions'?"
could be processed in several different ways. The choices for processing this query include:
1 ) Table Scan: Scan the entire table and only select the rows with Color =
'Red'.
2) Block Scan: Use the block index on Color to narrow down the search to a specific set of blocks.
3) Record Scan: Use a record-based index on Color (if it exists) to narrow down the search to a specific set of records.
(0053] The query optimizer can use a cost model to find the best of these choices.
The block scan method is a new operation that is introduced in this aspect of the invention.
This block scan operation proceeds in two steps: (i) scan the block index to find block identifiers that satisfy the query predicate, and (ii) process all the records in the block. This might involve additional predicates. The block scan operation is most effective when most of a block or sets of blocks or records need to be processed for a given query.
Such requirements are fairly typical in data warehouses. For example, the above query is very likely to involve access to a whole set of blocks. Thus, the block scan operation is likely to be the most efficient method of processing this query.
[0054] It should be noted that RID indexes may also be supported for multidimensional tables, and RID and block indexes can be combined by index ANDing and index ORing techniques. Multidimensional tables are otherwise treated like any existing table. For instance, triggers, referential integrity, views, and automatic summary tables can be defined upon them.
[0055] Figure 7(a)-(c) illustrates how index ANDing may be accomplished using block indexes. Consider a query against the three-dimensional cube shown in Figure b for Color = 'Blue' and Province = "QB'. We would first determine the slice containing all blocks with Color = 'Blue', by looking up the 'Blue' key value in the Color dimension block index associated with the table. We would find a key such as <Blue: 1, 2, 3, 4, 5, 6, 7. 8. 9, 10, 11, 12, 13, 14>
corresponding to slice 715 shown in the cube diagram of FIG. 7(a) (highlighted in grey).
[0056] We would then determine the blocks containing all records having Province =
'QB', by looking up the 'QB' key in the Province dimension block index, finding a key such as <QB: 11, 12, 13, 14, 27, 28, 35, 37, 40, 51>
corresponding to slice 725 shown in the cube diagram of Figure 7(b). To find the set of blocks containing all records having both values, we would have to find the intersection of these two slices. This is done by index ANDing the two BID lists. In this example, the common BID values are 1 l, 12, 13, and 14, and this corresponds to section 735 of the cube diagram shown in Figure 7(c).
[0057] Once we have a list of blocks to scan, we can simply do a mini-relational scan on each block. This would involve just one I/O as a block is stored as an extent on disk and can be read into the bufferpool as a unit. If the query predicates need to be reapplied and some of the predicates are only on dimension values, we need only to reapply these predicates on one record in the block since all records in the block are guaranteed to have the same dimension key values. If other predicates are present, we need only check these on the remaining records in the block.

[0058] The block-based index ANDing scheme is very efficient since a bit map scheme can be used. Also, since the block-level indexes are smaller than the RID indexes, the processing time for index ANDing is significantly less. Finally, the intersecting list of blocks is accessed efficiently using block-based I/O operations. Overall, the operation is extremely efficient and should be significantly faster than existing alternatives prior to this technique.
[0059] Conventional RID-based indexes are also supported for multidimensional tables, and RID and block indexes can he combined by index ANDing and ORing.
[0060] As mentioned, a block-based index ORing operation may also be performed using block indexes. For example, if the query includes the condition Province = 'ON' or Province = 'BC', then the province block index can be scanned for each category and an aggregated list of blocks can be obtained by an ORing operation. The ORing operation can eliminate duplicate BIDS which are possible for conditions such as Province =
'AB' or ('.olor _ 'Red'.
[0061 ] A secondary block index scan can also be supported. Given a secondary block index, a single BID can appear under numerous keys. Note that this is never possible in a RID index. When a secondary block index is used to access a fact table, it is critical that a qualifying block be scanned just once. All records from a qualifying block should be accessed on that scan and the block should not be fetched again. This requires that the qualifying list of blocks be maintained so that duplicates can be eliminated.
[0062] Figure 8 illustrates an exemplary block map associated with a multidimensional table. The block map records the current state of each block belonging to a particular table. For example, element 801 in this block map represents block 0 in a multidimensional table. Its availability status is 'X' indicating that it is reserved; it will never be used to store data rows of the table. Element 802 in the block map represents block 8. Its availability status is 'U' indicating that it is in use. Similarly, blocks 1-7, 10-1S, 17, 20, 25, and 26 are also in use. This means that the blocks are currently assigned to cells and records may be found in them. Element 803 in the block map represents block l9. Its availability status is 'F' indicating that the block is free. Similarly, blocks 21, 23, 24, and 27-29 are also free. This means that these blocks are not currently being used by the table and are available.
Element 804 in the block map represents block 22, and its availability status is 'L' indicating that the block has recently been loaded with information. Similarly, blocks 9, 16, and 30-33 are also recently loaded blocks. Element 806 in the block map represents block 18. Its availability status is 'C'. Block 18 was previously loaded and still needs constraint checking done.
[0063] It should be appreciated that the block map shown in Figure 8 contains various status flags having particular values but that other values could also be used to reflect different or additional block statures. Furthermore, it should be appreciated that the bit map data structure may be constructed in such a way that additional elements representing additional blocks or other information may be dynamically allocated.
[0064] Each block may have a header, located in a first slot of the block's first page which stores a structure containing, amp>ng other possible things, a copy of the block status so that the block map can be re-created if necessary in case of deletion or corruption of the map, and a bit map covering the pages of the block, indicating which pages are empty (e.g.,, 0 =
empty, 1 = nonempty, even if it contains only overflow or pointer records).
Each block may also have a free space control record (FSC.R) associated with it that could contain page offsets and approximations of free space per page. These FSCR's may be located on the first page of a block, and stored as the second record on this page, for example.
[0065] The above-mentioned organization of a table is very space efficient. 1t is important to choose the multidimensional keys and a corresponding block size so that each cell will have one or more blocks of data. Only the last block is likely to be partially full.
This highly efficient state can be maintained even in the presence of frequent insert and delete operations or background reorganization. In contrast, OLAP
organizations will lead to quite a bit of unused space as has been discussed previously.
[0066] Figure 9 illustrates various maintenance functions that can be performed in accordance with the techniques of the aspects of the present invention. Figure 9 includes a maintenance function 900 which includes a load function 901, a reorganization ("reorg") function 902, an insert function 903, a delete function 904, a purge function 905, and an update function 906. These will now be described in detail.
Load Function 901 [0067] A load is typically used to load relatively large amounts of data into a Cable rather than issuing numerous insert commands. The load utility can access a data set formatted in a particular manner, and use the information in the data set to create rows in a particular table.
[0068] It is important that the load utility employ an efficient scheme to insert data into a table. Loading data into <~ multidimensional table may be advantages>usly accomplished by organizing the input along dimension values. (This may be established as the default for multidimensional tables ). This is necessary in order to ensure that records are clustered appropriately along dimension values and block boundaries. For example, a bin can be created for the logical cell corresponding to <YearAndMonth = 9903, Province = 'ON', Color = 'Red'>. All records with similar values of the dimension attributes can be assigned to this bin. Physically, each bin can be represented by a block of data pages.
Recently processed bins can be maintained in memory and written to disk when they become full or if there is a need to bring other bins into memory.
(0069] One method to reduce processing is to allow users to specify a clause in their LOAD command, such as, for example, a MODIFY BY ASSERTORDER clause. This optional clause (or one similar to it having the same effect) can be used to inform the load utility that the input data set is already in sorted order and hence the processing can be done more efficiently. This is useful in several instances, including, for example when the data is already sorted on dimension and key value, allowing the load utility to merely verify the order. As another example, it is useful when loading records for a particular cell and thus all dimension values are the same for the records added. This may be the case, for instance, when a table has a single dimension and the user is rolling in records having a particular value for that dimension (e.g., all records from February, 2001 ). If the MODIFY BY
ASSERTORDER clause (or one similar to it having the same effect) is specified, the load utility can be configured to verify that the data is properly ordered. In the event that the load utility encounters a record out of sequence, the load can cease processing, leaving the table in a load pending state, for example.
Reorganization Function 902 [0070] Reorganization utilities are used to rearrange the physical layout of the data in a database. Reorganization (or "reorg'") of a database may be needed to release fragmented data space or to rearrange (cluster) records having a cluster index.

(0071] Reorganization of a multidimensional table is much simpler and is required less often than for a table with a clustering index. Since cluster7ng can be automatically and continuously maintained in multidimensional tables, reorganization would no longer be required to recluster data. The reorganization utility can still be used, however, to reclaim space in a table (specifically, to reclaim space within cells) and to clean up overflow records.
The reorganization utility for a multidimensional table is block-oriented. The composite dimension block index can be used to access records of particular blocks. The records can be rearranged into a new block using reorganization parameters such as, for example, an amount of free space needed. It is possible that the initial logical cell might contain many blocks while the rearranged cell might contain fewer blocks. For example, initially, a cell might contain blocks l, 10, 30, and 45. After reorganization, the cell might contain only new blocks I and 2. The rest of the space would have been released for use by other cells or completely freed up from this table. A new block map may also be reconstructed at the end of the reorganization.
Insert Function 903 (0072] Inserting involves creating new records in a table. Clustering must be maintained during an insert operation. Suppose we wish to insert a record with dimension values <9903, 'AB'> into a multidimensional table (such as the one depicted in Fig. 4). We would first need to identify the appropriate block for this new record by using a composite block index. We would look up the key value 9903, AB in the composite block index (such as is depicted in Fig. 5), and find a key such as:
<9903, AB: 3, 10>
(0073] In this case, we find that there are two blocks 3 and 10 with this key value.
These blocks contain all the records - and only those -- that have the dimensions specified.
We, therefore, insert the new record in one of these blocks if thexe is space on any of their pages. If there is no space on any pages in these blocks, we either allocate a new block to the table or use a previously emptied block in the table. A block map (such as is depicted in Figure 8) can be searched to find a free block. Let's say that after consulting the block map for the table, it was determined that block 48 is currently not in use by the table (e.g., its status is 'F' indicating it is free). In this case, we would set the block's status to in use in the block map, insert the record on a page of block 48, and assign this block to the cell by adding its BID to the composite block index and to each of the dimension block indexes.

[0074] When a record is inserted into a new block in the table, that block is added to the appropriate cell by inserting its BID into each of the above-mentioned block indices and into the composite block index. This efi~ectively associates this block with a particular cell, or set of dimension key values, in the table.
[0075] Keys in the dimension block indices have after addition of block 48:
<9903: 3, 4, 10, l6, 20, 22, 26, 30, 36, 48>
<AB: 1, 3, 5, 6, 7, 8, 10, 12, 14, 32, 48>
and the resulting keys in the composite block index would be <9903, AB: 3, 10, 48>.
[0076] Suppose there were no more free blocks in the table. Then, a new block would be allocated for the table, and used to insert the record. The indexes would be updated as shown above in that case as we-Il.
[0077] If a record with new dimension values is inserted, then a new block or free block must be allocated. The block would be set to in use in the block map, and its new key values would be added to the dimension and composite block indexes.
[0078] When performing the insert function, we have to pay special attention to the insert of the first record in a block as well as the first record to a new page in a block. We can use a page bit map for each block to maintain the state of the pages in the block. A bit in the page bit map can be se.t when the first record is inserted into the page.
This bit map enables us to track the occupancy of pages in a block and helps to maintain the state of the block in the presence of inserts and delete operations.
Delete Function 904 [0079] Blocks should be managed in such a way that a block can belong to a particular cell when it contains data having values of that cell, but can also be disassociated from any particular cell when the block is empty and contains no records. In this way, blocks can be reused for different cell values after they have been emptied of data.
[0080] Technically, a delete operation deletes one or more records in a table and frees up the space occupied by these records. Deletion of a multidimensional table also does the same thing. However, special attention is given to the state of pages in a block and the entire block as well. If we delete the last record of a page, then the page bit map is updated to clear the bit associated with the particular page. When all pages in a block are empty, this page bit map is fully cleared and this will indicate that the block can be marked free in the block map.
This free block can be reused by future insert and load operations, for example. When a block is freed, we must also update all the dimension indexes and remove the BID associated with the freed block from particular keys) corresponding to the dimension attributes for the block. Specifically, when the last record is deleted from a block, this block can be disassociated from the cell to which it formerly belonged. Such a block can be reused for any cell that needs it, regardless of the cell's dimension values. An empty block is disassociated from a cell by removing the BID from each of the block indices and setting the block map bit for this block to FREE.
Pure Function 905 [0081] Purge is a special form of the delete operation when a large set of related records are deleted. Consider the following SQL statement:
Delete from Table_1 where color = 'Red';
[0082] Assume that the color attribute is a dimension of this table (such as is the case for the table depicted in Figure 6). Hence, the constraint color 'Red' indicates that all blocks associated with the 'Red' value are to be deleted. We could accomplish this by looking up the dimension block index of color for 'Red' and finding a list of associated BIDs. Suppose this list was 12, 17, 21, and 30. If there were no other indexes cor related data structures on this table, we could mark these blocks as tree in the block map for the table and delete the BIDS from the block indexes. This would be an extremely fast operation and would reduce the logging and index maintenance costs as well.
[0083] It is possible to detect that a purge style of delete is applicable by examining the delete statement and verifying that the constraint is based on one or more dimension clauses. In particular, if one or more block indexes are used to identify the set of BIDS that need to be processed, we can consider using the purge style of delete. The optimizer can detect this and generate a suitable query plan accordingly. Note that the optimizer will also have knowledge of the additional issues that may enable or disable a fast purge. These issues include presence of additional indexes and constraints on the table.
Update Function 906 [0084] The update operation involves modifying information in a table. In a multidimensional table, the updates are of two kinds:
1 ) Simple update: In this case, the update applies only to attributes or fields of the table that are not dimensions. Elence, the. record continues to belong to the same logical cell. It may reside in the same location if the changes do not require additional space.
Otherwise, it could move to a new location in the same block or to another block. When moving this record, we maintain the original location's RID and point to the new location using the overflow pointer technique. If the new location is a new block, we will need to insert the new block in the dimension block indexes.
2) Update of the dimension column(s): If the update is to one or more dimension columns, we will need to treat this as a delete and an insert, internally. This will ensure that the record is now clustered in the new cell since it cannot be located in a block belonging to the old cell. The techniques described above for delete and insert are combined in order to perform this operation.
3) Slice update: If the update is to one or more dimension columns, and all the rows of one or more qualifying slices are to be updated to the same new values of the dimension columns, then the update can be done in the standard way, as in the simple update case. There is no need for the rows to be deleted and inserted.
Rendering the Insert Function 903 More Efficient [0085] It is highly desirable that the insert function 903 minimize the number of blocks searched in a cell before finding space to insert new records in a table.
[0086] In order to minimize the number of blocks searched for insert, a bit is associated with each BID in the composite dimension block index. If a BID has been previously searched and found not to have space for the record being inserted at that time, then the bit for this BID is set to indicate this. This bit then provides a hint to subsequent inserters as to whether or not a block is likely to have space in it. When scanning the BIDs for a particular cell, up to two passes of the BID list may be performed. On the first pass, BIDs having the full bit set are skipped and only those with it unset are actually searched. If no space for the record is found in a searched block, then the bit for that BID is set on return to the index scan, and the scan is resumed from where it left off. If the entire BID list is scanned and no space has been found, the BID list is scanned a second time.
This is done since the bit is used only as a hint, and all relevant existing blocks should be searched before physically allocating a new block to the table. On the second pass, any blocks that were' not searched in the first pass would be visited. For these BIDS, the full bit is first unset and the block searched. Then, if there is insufficient space in the block for the record, the bit is set again. This two-pass technique ensures that the entire cell is searched for space before adding a new block to the cell. At the same time, however, those blocks that have been determined to have had insufficient space in the past are not searched until last.
[0087] Referring to Figure 10, there is illustrated in a flowchart 1000 a method of searching blocks in a cell for space in which to insert a record. In step 1002, the composite block index is first searched for the key having the dimension values of the record to be inserted. If the key is not found in the dimension block index, then query 1004 returns the answer NO and the method pros=Beds to step 1036, in which the search is ended, a new cell for these dimension values is assigned, and the record is inserted into a block assigned to this new cell. If, the key searched for in step 1002 is found, then query 1004 returns the answer YES and the method proceeds to step 1006.
[0088] In step 1006, the method checks the first BID in the list to determine if its full bit is set or not. If its full bit is set. then the method proceeds to step 1018 in which the next BID in the list is considered. If, on the other hand, query 1008 returns the answer NO, in that the full bit is not set for this BID, then the method proceeds to step 1010 and the Mock designated by the BID is searched for space to store the record. If there is sufficient space on the block to store the record, then query 1012 returns the answer YES and the method proceeds to step 1014 in which the record is inserted on the block and then terminates. If query 1012 returns the answer NO, in that there is insufficient space on the block to store the record, then the method proceeds to step I O16, in which the full bit is set for the BID for this block and this BID is added to a list in memory of those BIDS that have been searched on this first pass and found to be full. The method then proceeds to step 1 Ol 8 in which the next BID
is considered.
[0089] The method then proceeds to query 1020, which queries whether the end of the BID list has been reached. If query 1020 returns the answer NO, then the method returns to query 1008. lf, on the other hand, query 1020 returns the answer YES, then the method proceeds to step 1022 and returns to the start of the BID list again for a second pass, in which blocks that were skipped in the first pass are checked to see if any space has been freed up since the full bit had been set. Query 1024 queries whether the block has been searched already. If query 1024 returns the answ~.:r YES - that is, if this block was searched on the first pass - the.n the method proceeds to step 1032 and moves to the next BID on the list. If query 1024 returns the answer NO, then the method proceeds to step 1026. In step 1026, the full bit for the BID is upset, and the block is searched for space on which to insert the record. If space is found-in the block, then query 1028 returns the answer YES, and the method proceeds to step 1014, in which the record is inserted in the block. If there is insufficient or no space in the block, then query 1028 returns the answer NO, and in step 1030 the full bit for this block is set once again before the method moves on to step 1032 in which the next BID on the list is searched. The method next proceeds to query 1034, which queries whether the end of the BID list has been reached. If query 1034 returns the answer NO, then the method returns to the query 1024. If, on the other hand, the end of the BID
list has been reached, then query 1034 returns the answer YES, and in step 1036 a new block provided in which the record is inserted.
Minimize Number of Pag~Accesses When Searching a Block For Space [0090] When searching a block for space to insert a particular record, it is desirable to access as few pages as possible. A free space control record (FSCR) is used for this purpose.
The first page of each block contains au FSCR, which stores the approximate available space for each page in that block. By fixing the first page of the block, space information for the pages of the block can be scanned to find one a page having space for the record. Only those entries for pages that could have space for the record are then accessed.
[0091 ] FSCRs are used in relational database management systems, and have very efficient free space search algorithms. For non-MUC tables, these records are interspersed through the table and can be found on every Nth page of the table, such that each one maps an array element to each of the N pages including and following the page that it is on. In MDC tables, an FSCR is stored in every block, and so on every M pages, where M
is the block size. Since the blocks that are to be searched are determined using the composite block index described above, only those FSCRs relevant to the insert are searched.
[0092] Referring to Figure 11, step 1010 of Figure 10 in which a block is searched for storage space is broken down into a series of steps. When a BID is found in the composite block index, the next step is to determine whether the block associated with that BID contains space for the record to be inserted. To determine if this is the case, in step 1 102, the method 1 100 proceeds to the first page of the block and examines the first entry of the FSCR record on that page. If the FSCR indicates that there is likely to be enough space on that page, then query 1 104 returns the answer YES and the method goes to the page corresponding to the FSCR entry in step 1 106. If there is in tact enough space on this page, then step 1 108 returns the answer YES and the method proceeds to step 11 14. If there is not in fact enough space on this page, then query 1108 returns the answer NO and the method goes to step 1110.
Similarly, if query 1 104 had returned the answer NO, in that the FSCR entry did not indicate that there was enough space on the page, then the method would have proceeded directly to step 11 10. In step 11 10, the next FSCR entry is examined. Following step 1 I
10, query I I 12 returns the answer NO if this entry is not at the end of the FSCR, and the method 1100 then proceeds back to query 1104. If, on the other hand, the entry examined is at the end of the FSCR, then the method terminates.
[0093] Lf, on the other hand, query 1108 had returned the answer YES, in that there was enough space on the page for the record, then the method proceeds to step 1114 in which the record is inserted on the page. Then, query 1 1 16 queries whether this is the only record on the page. If query 1116 returns the answer NO, then the method terminates. If, on the other hand, query 1116 returns the answer YES, then the method proceeds to step 1118 in which the empty page bit is set for the page (the page is no longer empty) before the method terminates.
Searching For a Free Block in the Table [0094] In step 1036 of Figure 10, a free block in the table must be found to store data.
Referring to Figure 12, there is illustrated in a flowchart a method implemented in step 1036 of Figure 10 for determining if there is a free block in the table available for reuse. In step 1202, the method begins at a random block map entry of the block map of Figure 8. This is done so that the method does not always search from the same spot and end up scanning past what is already being reused. Then, the method checks whether the in use bit is set. If the in use bit is set, then query 1204 returns the answer YES and the method proceeds to step 1206.
if the in use bit is not set, then query 1204 returns the answer NO and the method proceeds to step 1214. In step 1214, the entry is set to in use, the BID is added to the block indices., and the record is added to the block.
[0095] In step 1206, the method moves to the next entry in the block map.
Query 1208 then queries whether every entry in the map has been checked. If query 1208 returns the answer NO, then the method proceeds to step 1210, and the method moves to the next entry or wraps back to the beginning of the block map before proceeding to query 1204. If query 1208 returns the answer YES, then the method proceeds to step 1212. Step 1212 is reached if there are no blocks that are not in use in the block map. In step 1212, the table is extended by a block of pages, and the block map is extended by one entry to designate this block of pages.
The method then proceeds to step 1214 in which this new -entry is set to in use in the block map, the BID for this block is recorded in the block indices, and the record is added to the block. The method then terminates.
Quicklx Determine Whether the Delete of a Record Emptied the Block [0096] An efficient method is required for determining when the last record in a block is deleted, so that the block can be freed and ma-de available for different dimension values.
Although it is not difficult or expensive to determine whether the record being deleted is last on the page, it will be expensive to then check every page in the block to determine if they are all now empty. A bitmap is used to keep track of the empty state of each page in the block. When the block is allocated, its bitmap is initialized to zero, indicating that each page in the block is empty. When a record is inserted, and is the first record inserted on a page, the bit for that page is set in the empty page map. When the last record is deleted from a page, its corresponding bit is unset and it is determined whether any bits remain set in the bitmap. This check is also optimized for efficiency. Only when the block's entire bitmap is zero can we free the block and disassociate it from its current cell.
[0097] Recall that in step 1 1 14 of Figure 1 l, that when a record is inserted on a page in the block, it is first determined whether the page is empty. if the page.
is empty, then the empty page bit is set for this page (step 1 1 18), thereby indicating that the page is no longer empty.
[0098] Referring to Figure 13, there is illustrated in a flowchart an analogous method implemented when a record is deleted. In step 1302, the record is deleted, and the RID for the record is removed from any RID indices. Then, the method checks whether the page is now empty. If the page is not empty, then query 1304 returns the answer NO and the method terminates. If the page is empty, then query 1304 returns the answer YES and the method proceeds to step 1306 in which the empty page bit associated with this page is unset. Then, the method checks whether the entire empty page map is all zeros. If the entire empty page bitmap is not all zeros, then query 1308 returns the answer NO and the method terminates. If, on the other hand, the entire empty page bitmap are all zeros, then the method proceeds to step 1310, in which the block is freed from the cell by unsetting its in use bit and removing its BID from the block indices. The method then terminates.
[0099] Although illustrative embodiments of the present invention have been described herein with reference to the accompanying drawings, it is to be understood that the aspects of the invention are not limited to those precise embodiments, and that various other changes and modifications may be affected therein by one skilled in the art without departing from the scope or spirit of the invention.

Claims (56)

Claims:

The embodiments of the invention in which an exclusive property or privilege is claimed are defined as follows:
1. For an information retrieval system, a method for maintaining clustered data, the method comprising:
identifying a plurality of dimensions of a table using a plurality of table definition parameters;
associating at least one block in a plurality of blocks in the table with an associated cell, the associated cell having a unique combination of dimension values including an associated dimension value for each dimension in the plurality of dimensions;
clustering data according to dimension value, for each dimension in the plurality of dimensions, by storing the data in the at least one associated block; and, storing storage state information regarding each block in the plurality of blocks.
2. The method of claim 1 wherein each block includes a plurality of contiguous storage pages.
3. The method of claim 1 wherein said storing includes providing, for each block associated with a cell, an associated storage space indicia for indicating whether storage space is likely to be available on the block.
4. The method of claim 1 wherein said storing includes providing a unique associated list for each cell.
5. The method of claim 4 wherein:
the unique associated list has a unique corresponding list entry for each block in the at least one block associated with that cell;
the unique corresponding list entry has an associated a block identifier (BID) for designating the block and has an associated storage space indicia bit for indicating whether storage space is likely to be available on the at least one associated block;
and, the associated storage space indicia bit is configurable to a FULL setting for indicating that the at least one associated block is likely to be full, and is configurable out of the FULL setting for indicating that storage space is likely to be available on the at least one associated block.
6. The method of claim 4 further comprising:
determining an associated cell for a new record to be inserted;
a first checking step for checking a block in the associated at least one block for the associated cell for storage space for the new record;
inserting, if the block checked has storage space for the new record, the new record in the block; and, configuring, if the block checked lacks storage space for the new record, the associated storage indicia bit in the associated list entry to the FULL
setting.
7. The method of claim 6 further comprising:
a second checking step for checking, before. checking the block in the associated at least one block for the associated cell for storage space for the new record, the associated storage space indicia bit in the associated list entry in the list for the associated cell;

a third checking step for checking, if the storage space indicia bit is not in the FULL
setting, the block in the associated at least one block for the associated cell for storage space for the new record; and, a fourth checking step for checking, if the storage space indicia bit is in the FULL
setting, the associated storage space indicia bit in a next list entry in the list for the associated cell.
8. The method of claim 7 further comprising:
a fifth checking step for checking all of the associated storage space indicia bits in the unique associated list for the associated cell for the new record to be inserted, before checking a block having an associated storage space indicia bit in the full setting for storage space for the new record.
9. The method of claim 3 wherein said storing includes providing, for each block in the table, an associated empty block indicia for indicating whether the block is empty.
10. The method of claim 9 further comprising a first disassociating step for disassociating the block from the cell when the associated empty block indicia indicates the block is empty.
11. The method of claim 2 wherein said storing includes providing a unique associated empty page bitmap for each block in the table.
12. The method of claim 11 wherein:
the unique associated empty page bitmap has a unique corresponding map entry for each page in the block; and, the unique corresponding map entry has an associated page identifier for designating the page and an associated indicia bit for indicating whether the page is empty.
13. The method of claim 10 further comprising:
a sixth checking step for checking, when a record is deleted from a page in a block in a dimension of the table, if the page is empty.
14. The method of claim 13 further comprising, if the page is empty:
changing the associated indicia bit in the associated unique corresponding map entry for the page to indicate that the page is empty;
a seventh checking step for checking, for each of the other pages in the block, the associated indicia bit for the page to determine if the page is empty; and, a second disassociating step for disassociating, if all pages in the block are empty, the block from the cell.
15. An information retrieval system for maintaining clustered data, comprising:
means for identifying a plurality of dimensions of a table using a plurality of table definition parameters;
means for associating at least one block in a plurality of blocks in the table with an associated cell, the associated cell having a unique combination of dimension values including an associated dimension value for each dimension in the plurality of dimensions;
means for clustering data according to dimension value, for each dimension in the plurality of dimensions, by storing the data in the at least one associated block; and, means for storing storage state information regarding each block in the plurality of blocks.
16. The information retrieval system of claim 15 wherein each block includes a plurality of contiguous storage pages.
17. The information retrieval system of claim 15 wherein said means for storing includes providing, for each block associated with a cell, an associated storage space indicia for indicating whether storage space is likely to be available on the block.
18. The information retrieval system of claim 15 wherein said means for storing includes providing a unique associated list for each cell.
19. The information retrieval system of claim 18 wherein:
the unique associated list has a unique corresponding list entry for each block in the at least one block associated with that cell;
the unique corresponding list entry has an associated a block identifier (BID) for designating the block and has an associated storage space indicia bit for indicating whether storage space is likely to be available on the at least one associated block;
and, the associated storage space indicia bit is configurable to a FULL setting for indicating that the at least one associated block is likely to be full, and is configurable out of the FULL setting for indicating that storage space is likely to be available on the at least one associated block.
20. The information retrieval system of claim 18 further comprising:
means for determining an associated cell for a new record to be inserted;

first checking means for checking a block in the associated at least one block for the associated cell for storage space for the new record;
means for inserting, if the block checked has storage space for the new record, the new record in the block; and, means for configuring, if the block checked lacks storage space for the new record, the associated storage indicia bit in the associated list entry to the FULL
setting.
21. The information retrieval system of claim 20 further comprising:
second checking means for checking, before checking the block in the associated at least one block for the associated cell for storage space for the new record, the associated storage space indicia bit in the associated list entry in the list for the associated cell;
third checking means for checking, if the storage space indicia bit is not in the FULL
setting, the block in the associated at least one block for the associated cell for storage space for the new record; and, fourth checking means for checking, if the storage space indicia bit is in the FULL
setting, the associated storage space indicia bit in a next list entry in the list for the associated cell.
22. The information retrieval system of claim 21 further comprising:
fifth checking means for checking all of the associated storage space indicia bits in the unique associated list for the associated cell for the new record to be inserted, before checking a block having an associated storage space indicia bit in the full setting for storage space for the new record.
23. The information retrieval system of claim 17 wherein said means for storing includes providing, for each block in the table, an associated empty block indicia for indicating whether the block is empty.
24. The information retrieval system of claim 23 further comprising first disassociating means for disassociating the block from the cell when the associated empty block indicia indicates the block is empty.
25. The information retrieval system of claim 16 wherein said means for storing includes providing a unique associated empty page bitmap for each block in the table.
26. The information retrieval system of claim 25 wherein:
the unique associated empty page bitmap has a unique corresponding map entry for each page in the block; and, the unique corresponding map entry has an associated page identifier for designating the page and an associated indicia bit for indicating whether the page is empty.
27. The information retrieval system of claim 24 further comprising:
sixth checking means for checking, when a record is deleted from a page in a block in a dimension of the table, if the page is empty.
28. The information retrieval system of claim 27 further comprising, if the page is empty:
means for changing the associated indicia bit in the associated unique corresponding map entry for the page to indicate that the page is empty;

seventh checking means for checking, for each of the other pages in the block, the associated indicia bit for the page to determine if the page is empty; and, second disassociating means for disassociating, if all pages in the block are empty, the block from the cell.
29. A computer program product having a computer readable medium tangibly embodying computer executable code for directing an information retrieval system to maintain clustered data, the computer program product comprising:
code for identifying a plurality of dimensions of a table using a plurality of table definition parameters;
code for associating at least one block in a plurality of blocks in the table with an associated cell, the associated cell having a unique combination of dimension values including an associated dimension value for each dimension in the plurality of dimensions;
code for clustering data according to dimension value, for each dimension in the plurality of dimensions, by storing the data in the at least one associated block; and, code for storing storage state information regarding each block in the plurality of blocks.
30. The computer program product of claim 29 wherein each block includes a plurality of contiguous storage pages.
31. The computer program product of claim 29 wherein said code for storing includes providing, for each block associated with a cell, an associated storage space indicia for indicating whether storage space is likely to be available on the block.
32. The computer program product of claim 29 wherein said code for storing includes providing a unique associated list for each cell.
33. The computer program product of claim 32 wherein:
the unique associated list has a unique corresponding list entry for each block in the at least one block associated with that cell;
the unique corresponding list entry has an associated a block identifier (BID) for designating the block and has an associated storage space indicia bit for indicating whether storage space is likely to be available on the at least one associated block;
and, the associated storage space indicia bit is configurable to a FULL setting for indicating that the at least one associated block is likely to be full, and is configurable out of the FULL setting for indicating that storage space is likely to be available on the at least one associated block.
34. The computer program product of claim 32 further comprising:
code for determining an associated cell for a new record to be inserted;
first checking code for checking a block in the associated at least one block for the associated cell for storage space for the new record;
code for inserting, if the block checked has storage space for the new record, the new record in the block; and, code for configuring, if the block checked lacks storage space for the new record, the associated storage indicia bit in the associated list entry to the FULL
setting.
35. The computer program product of claim 34 further comprising:

second checking code for checking, before checking the block in the associated at least one block for the associated cell for storage space for the new record, the associated storage space indicia bit in the associated list entry in the list for the associated cell;
third checking code for checking, if the storage space indicia bit is not in the FULL
setting, the block in the associated at least one block for the associated cell for storage space for the new record; and, fourth checking code for checking, if the storage space indicia bit is in the FULL
setting, the associated storage space indicia bit in a next list entry in the list for the associated cell.
36. The computer program product of claim 35 further comprising:
fifth checking code for checking all of the associated storage space indicia bits in the unique associated list for the associated cell for the new record to be inserted, before checking a block having an associated storage space indicia bit in the full setting for storage space for the new record.
37. The computer program product of claim 31 wherein said code for storing includes providing, for each block in the table, an associated empty block indicia for indicating whether the block is empty.
38. The computer program product of claim 37 further comprising first disassociating code for disassociating the block from the cell when the associated empty block indicia indicates the block is empty.
39. The computer program product of claim 30 wherein said code for storing includes providing a unique associated empty page bitmap for each block in the table.
40. The computer program product of claim 39 wherein:
the unique associated empty page bitmap has a unique corresponding map entry for each page in the block; and, the unique corresponding map entry has an associated page identifier for designating the page and an associated indicia bit for indicating whether the page is empty.
41. The computer program product of claim 38 further comprising:
sixth checking code for checking, when a record is deleted from a page in a block in a dimension of the table, if the page is empty.
42. The computer program product of claim 41 further comprising, if the page is empty:
code for changing the associated indicia bit in the associated unique corresponding map entry for the page to indicate that the page is empty;
seventh checking code for checking, for each of the other pages in the block, the associated indicia bit for the page to determine if the page is empty; and, second disassociating code for disassociating, if all pages in the block are empty, the block from the cell.
43. A computer data signal embodied in a carrier wave and having means tangibly embodied within the computer data signal for directing an information retrieval system to maintain clustered data, the computer data signal comprising:

means for identifying a plurality of dimensions of a table using a plurality of table definition parameters;
means for associating at least one block in a plurality of blocks in the table with an associated cell, the associated cell having a unique combination of dimension values including an associated dimension value for each dimension in the plurality of dimensions;
means for clustering data according to dimension value, for each dimension in the plurality of dimensions, by storing the data in the at least one associated block; and, means for storing storage state information regarding each block in the plurality of blocks.
44. The computer data signal of claim 43 wherein each block includes a plurality of contiguous storage pages.
45. The computer data signal of claim 43 wherein said means for storing includes providing, for each block associated with a cell, an associated storage space indicia for indicating whether storage space is likely to be available on the block.
46. The computer data signal of claim 43 wherein said means for storing includes providing a unique associated list for each cell.
47. The computer data signal of claim 46 wherein:
the unique associated list has a unique corresponding list entry for each block in the at least one block associated with that cell;

the unique corresponding list entry has an associated a block identifier (BID) for designating the block and has an associated storage. space indicia bit for indicating whether storage space is likely to be available on the at least one associated block;
and, the associated storage space indicia bit is configurable to a FULL setting for indicating that the at least one associated block is likely to be full, and is configurable out of the FULL setting for indicating that storage space is likely to be available on the at least one associated block.
48. The computer data signal of claim 46 further comprising:
means for determining an associated cell for a new record to be inserted;
first checking means for checking a block in the associated at least one block for the associated cell for storage space for the new record;
means for inserting, if the block checked has storage space for the new record, the new record in the block; and, means for configuring, if the block checked lacks storage space for the new record, the associated storage indicia bit in the associated list entry to the FULL
setting.
49. The computer data signal of claim 48 further comprising:
second checking means for checking, before checking the block in the associated at least one block for the associated cell for storage space for the new record, the associated storage space indicia bit in the associated list entry in the list for the associated cell;
third checking means for checking, if the storage space indicia bit is not in the FULL
setting, the block in the associated apt least one block for the associated cell for storage space for the new record; and, fourth checking means for checking, if the storage space indicia bit is in the FULL
setting, the associated storage space indicia bit in a next list entry in the list for the associated cell.
50. The computer data signal of claim 49 further comprising:
fifth checking means for checking all of the associated storage space indicia bits in the unique associated list for the associated cell for the new record to be inserted, before checking a block having an associated storage space indicia bit in the full setting for storage space for the new record.
51. The computer data signal of claim 45 wherein said means for storing includes providing, for each block in the table, an associated empty block indicia for indicating whether the block is empty.
52. The computer data signal of claim 51 further comprising first disassociating means for disassociating the block from the cell when the associated empty block indicia indicates the block is empty.
53. The computer data signal of claim 44 wherein said means for storing includes providing a unique associated empty page bitmap for each block in the table.
54. The computer data signal of claim 53 wherein:
the unique associated empty page bitmap has a unique corresponding map entry for each page in the block; and, the unique corresponding map entry has an associated page identifier for designating the page and an associated indicia bit for indicating whether the page is empty.
55. The computer data signal of claim 52 further comprising:
sixth checking means for checking, when a record is deleted from a page in a block in a dimension of the table, if the page is empty.
56. The computer data signal of claim 55 further comprising, if the page is empty:
means for changing the associated indicia bit in the associated unique corresponding map entry for the page to indicate that the page is empty;
seventh checking means for checking, for each of the other pages in the block, the associated indicia bit for the. page to determine if the page is empty; and, second disassociating means for disassociating, if all pages in the block are empty, the block from the cell.
CA2427071A 2003-04-28 2003-04-28 Method and system for space management for multidimensionally clustered tables Expired - Lifetime CA2427071C (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CA2427071A CA2427071C (en) 2003-04-28 2003-04-28 Method and system for space management for multidimensionally clustered tables

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CA2427071A CA2427071C (en) 2003-04-28 2003-04-28 Method and system for space management for multidimensionally clustered tables

Publications (2)

Publication Number Publication Date
CA2427071A1 CA2427071A1 (en) 2004-10-28
CA2427071C true CA2427071C (en) 2010-07-06

Family

ID=33315194

Family Applications (1)

Application Number Title Priority Date Filing Date
CA2427071A Expired - Lifetime CA2427071C (en) 2003-04-28 2003-04-28 Method and system for space management for multidimensionally clustered tables

Country Status (1)

Country Link
CA (1) CA2427071C (en)

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN103838766A (en) * 2012-11-26 2014-06-04 深圳市腾讯计算机系统有限公司 Empty cache prevention method and device

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN103838766A (en) * 2012-11-26 2014-06-04 深圳市腾讯计算机系统有限公司 Empty cache prevention method and device
CN103838766B (en) * 2012-11-26 2018-04-06 深圳市腾讯计算机系统有限公司 Antiaircraft caching method and device

Also Published As

Publication number Publication date
CA2427071A1 (en) 2004-10-28

Similar Documents

Publication Publication Date Title
US7765211B2 (en) System and method for space management of multidimensionally clustered tables
US7080081B2 (en) Multidimensional data clustering scheme for query processing and maintenance in relational databases
US7158996B2 (en) Method, system, and program for managing database operations with respect to a database table
US6629102B1 (en) Efficiently updating a key table during outline restructure of a multi-dimensional database
US6374232B1 (en) Method and mechanism for retrieving values from a database
US7171427B2 (en) Methods of navigating a cube that is implemented as a relational object
US6898588B2 (en) Method and apparatus for viewing the effect of changes to an index for a database table on an optimization plan for a database query
US7469241B2 (en) Efficient data aggregation operations using hash tables
US8868544B2 (en) Using relational structures to create and support a cube within a relational database system
JP2583010B2 (en) Method of maintaining consistency between local index table and global index table in multi-tier index structure
US6470344B1 (en) Buffering a hierarchical index of multi-dimensional data
US6209000B1 (en) Tracking storage for data items
US5995973A (en) Storing relationship tables identifying object relationships
US6505205B1 (en) Relational database system for storing nodes of a hierarchical index of multi-dimensional data in a first module and metadata regarding the index in a second module
US6665682B1 (en) Performance of table insertion by using multiple tables or multiple threads
Padmanabhan et al. Multi-dimensional clustering: A new data layout scheme in db2
US7213025B2 (en) Partitioned database system
US6480848B1 (en) Extension of data definition language (DDL) capabilities for relational databases for applications issuing DML and DDL statements
EP2020637B1 (en) Method and system for fast deletion of database information
Turner et al. A DBMS for large statistical databases
US6366902B1 (en) Using an epoch number to optimize access with rowid columns and direct row access
US7136861B1 (en) Method and system for multiple function database indexing
US6535895B2 (en) Technique to avoid processing well clustered LOB&#39;s during reorganization of a LOB table space
US6925463B2 (en) Method and system for query processing by combining indexes of multilevel granularity or composition
Baumann et al. Bitwise dimensional co-clustering for analytical workloads

Legal Events

Date Code Title Description
EEER Examination request
MKEX Expiry

Effective date: 20230428

MKEX Expiry

Effective date: 20230428