JPH0772898B2 - Index creation method - Google Patents

Description

The present invention relates to a method of creating an index that can be executed without recreating the index even when the data item length is changed by creating a key of the index by a predetermined encoding method. Is. According to the present invention, even if the data structure of the logical structure of the database is changed, it is possible to continue the operation without changing the index.

In a database processing device that shares data from a plurality of users at the same time, there is a need for a technology that realizes a database operation mode that is highly expandable and flexible in the trend of sophisticated database management systems.

For example, in the database design performed at the time of introducing the database, it is necessary to clarify the logical relationship of complete data by analyzing, organizing, and integrating existing data, which is positioned as an upstream design. However, in actual data analysis, data must be handled in an enormous and constantly changing environment, and it is usual to redesign the data due to some influence at the time when the analysis ends. Such problems require reworking of the database even during database introduction period and operation. Therefore, a flexible and scalable database management system is required. In particular, with the commercialization of relational databases, independence between application programs handled by users and databases is strongly required, and the independence of databases (logical databases) from the user's standpoint and databases on the database management system side. Sex is becoming an important issue.

In such a demand, in addition to a storage structure for data on a logical structure which is a physical structure of a database, a flexible index structure is required for an index structure as a means for searching data at high speed.

Databases that share data at the same time can be roughly classified into logical structures and physical structures. This is described in Reference 1-3,
It is clear from Chapter 4.

Reference 1 "Computer Data-Base Organization" 2ed, JAMES MART
IN, PRENTICE-HALL, INC, 1977 The database management system realizes the conversion process of the data access of the logical file or table (this is called the logical database) and the physical database that can be seen from the application program of the user. is doing. here,
A description of logical databases not relevant to the present invention will not be given. In the physical database, the well-known indexed sequential structure (Indexed Sequential Organ) as described in Chapter 20 of Reference 1 is used.
ization), Hashing, Pointers and Chains AND Ring Structures as described in Chapters 22 and 23. These data structures are mainly used for mapping the data of the logical database. Here, mapping means that records (rows) in a logical database are divided and arranged in multiple structures in the logical database, or records in multiple logical databases are arranged in one physical database structure.
Or say a combination thereof.

Access to each physical database storage structure includes well-known configurations such as insert / delete / search / update. For example, the forward processing and the irregular processing in Chapter 20 of Document 1 show the configuration of the search system and the configuration of insertion / deletion. The configuration of update can be done by inserting / deleting, but in general, the configuration of update is often independent.

In a logical database, a record is rarely searched by one key, and another item is usually searched as a key. In the physical database structure, it is usually realized by a secondary index structure. The secondary index structure is also well-known as described in Chapters 26 and 29 of Document 1, and thus the detailed mechanism is omitted.

Usually, the index is accessed before accessing the storage structure of the logical database or the physical database to be mapped (referred to as base data structure).
Indexes provide a fast way to search for data in a target base data structure. On the other hand, in a database in which data is shared at the same time, it is common to update the data of the base data structure, so the index key must be upgraded (updated). At present, as shown in Chapter 30 of Document 1 and Document 2 below, the index of balanced tree structure is becoming mainstream.

Reference 2 “The Ubiquitous B-Tree”, D.Comer, ACM Computing S
urveys, VoI11, No2, PP 121-138, ACM, 1979 It is well known that the access of the index structure consists of search by key, forward search, key insertion, and key deletion. In the present invention, the structure of the index section is described by taking the key search method and the key insertion method as examples.

Further, Document 3 below describes a configuration for symbolizing a plurality of keys for a character item. by this,
It is possible to avoid the reorganization of the index when the length of the character item is changed by utilizing the configuration that enables compression of the blank part of the character item. Document 3 does not describe the configuration related to numerical items. The composition of the symbolization / expansion technique for character items is the multi-key
This is described later in this specification as a field concatenation technique.

Reference 3 "An Encoding Method For Multifield Sorting and In
dexing '', Michal W Blasgen, Richard G. Gasey, and Kapa
li P Eswaran, Communications of the ACM, Vo120, No11,
ACM, 1977 The problem with the prior art is that the physical database must be reorganized whenever the data of a numerical item is changed during or after the introduction of the database.

However, changing the attribute or the data length of the database associated with the index is often a prohibited item in the related art. In this case, it was necessary to delete the index once and recreate it. System data needs to be temporarily stopped because all data in the base data structure must be retrieved for re-creation.

Therefore, after the introduction of the database, it was necessary to stop the service of the database when changing the data.

According to the present invention, the service is interrupted due to the change of the schema and the physical structure definition in a short time such as the transaction processing of the application program or the break of the job, but the database service is continued without affecting the index structure. It enables you to do it.

These are necessary services especially for large-scale transaction processing databases, department databases, and personal databases for individuals. For a large-scale transaction processing database, the database is designed in advance, an introduction test is conducted, and the service is entered. In the upstream process of database design, information analysis of existing data will be performed completely before the introduction and service will be entered, but since the external environment is constantly changing, in reality the changes in the database installation and service period may occur. Occur. Department databases and personal databases require less time to design databases than large-scale systems. Database changes frequently occur in such databases.

The present invention is based on the above consideration, and provides a method of creating an index in a relational database system that does not require re-creation of the index even when the length and attributes of data items are changed. It is intended to be provided. Therefore, according to the index creating method of the present invention, the index part (1) composed of a plurality of indexes having the compression key, the expansion of the compression key extracted from the index part (1), and the writing to the index part (1) should be performed. A method of creating an index in a relational database system comprising a compression / decompression means (2) for compressing a key and a symbolization means (3), wherein the symbolization means (3) If there is numeric item data in it, the numeric item data is converted to binary data of a specified byte length, and the binary data obtained as a result of the conversion is positive when it is positive and has been deleted. Converted to symbolized binary data consisting of a control byte indicating the number of valid bytes excluding "(hexadecimal) and a data part excluding" 00 "(hexadecimal), and a negative Sometimes it is negative and it is converted to symbolic binary data consisting of a control byte that indicates the number of reduced bytes of'FF '(hexadecimal) and a data part in which'FF' (hexadecimal) is deleted. If there are other data items inside,
The symbolic binary data obtained as a result of the conversion is concatenated with another data item, and the concatenated data is output as a key.

It is characterized by that.

In summary, the present invention defines two internal decimal items and two external decimal items.
Converting a binary item into a symbolic binary item having the same characteristics as a character item, and converting a plurality of data items including the symbolic binary item into a conventionally known multi-key item.
They are connected using the field connection technique.

It is now assumed that Key = {A, B} A char (10) B char (10). The above formula shows that the key consists of data items A and B, A is 10 bytes in character format,
Also, B and B indicate that the character format is 10 bytes.
It is assumed that the arbitrary item data a ₁ and b ₁ include characters as shown in FIG. Item data is divided by a value n. Character items have the property that their size is determined by the number of comparison bytes from the beginning. Therefore, the blank can be omitted by assigning the weight of the control byte to the divided portion. For example, the continuous connection part is'FF'X, and the others are valid characters. Using the above technique, when coupled with the item data a ₁ and b ₁ in FIG. 1 (b), so that the first view (b). Further, when connecting the item data a ₂ and b ₂ of FIG. 2 (b), the multi-key is as FIG. 2 (b). In addition, ‖ represents a bond. If the key in FIG. 1 (b) is k ₁ and the key in FIG. 2 (b) is k ₂ , then k ₁ <k ₂ .
By fixing the value of n to, for example, 4 bytes, it is possible to cope with the change in the length of the character item. In addition, '03'X in FIG. 2B indicates that the number of effective characters in the immediately preceding 4-byte character area is three.

Next, the coding of binary items will be described. First, the input binary item is expanded to a length of 8 bytes. As shown in FIG. 3, a control byte is added, and the first bit is empty and the second bit is a sign bit, which is "1" when positive and "0" when negative. Then, depending on the 5th to 8th bits of the control byte, when the number is negative, the number of deleted bytes of "FF" X, and when the number is positive, it is set to "0".
Indicates the number of valid bytes excluding 0'X. Omitting the number of bytes of'FF 'and' 00 'indicates that the smaller the absolute number, the easier the omission.

The comparison of the size of the symbolized keys made in this way is equivalent to that of characters. That is, the sign bit of the control byte enables positive / negative character comparison of data. Since 5 to 8 bits in the control byte are set to the default value of'FF'X for negative numbers and the number of valid bytes excluding '00'X for positive numbers, character comparison based on the number of digits of data Is possible. For the same digit, the negative number is the complement expression and the positive number itself is set, so that character comparison is possible.

FIG. 4 shows a concrete example of symbolization of binary items. Fourth
FIG. 4A shows input data, FIG. 4B shows representation data inside the computer, and FIG. 4C shows symbolized binary data after suppression.

For example, when the input data is -8888 (hexadecimal), the expression data inside the computer is FFFFFFFFFFFF7778 (hexadecimal). As described above, the first bit of the control byte is 0 (indicating an empty space), and when the number is negative, the second bit of the control byte is 0.
And the FF (16
Since the number of deleted bytes is 0, the control byte for the expression data in the computer = FFFFFFFFFFFF7778 (hexadecimal) is 00.
It becomes 000110 (binary). This is 06 when expressed in hexadecimal.

For example, when the input data is +8888 (hexadecimal), the expression data inside the computer is 0000000000008888 (hexadecimal). As described above, the first bit of the control byte is 0 (indicating an empty space), and when the number is positive, the second bit of the control byte is 1.
And is 00 (16) depending on the 5th to 8th bits of the control byte.
Since the number of valid bytes excluding the hexadecimal) is represented, the control byte for the expression data in the computer = 0000000000008888 (hexadecimal) is 01000010 (binary). This is 42 when expressed in hexadecimal.

Represent a binary number in doubleword length and encode it again. According to this, since the binary number before symbolization does not depend on the data length of the data definition of 1 to 8 bytes, even if the data attribute is changed, the symbol size can ensure the magnitude relation of the keys. In addition, by symbolizing (compressing) again,
It is possible to suppress the double word length data area to an increase of only control bytes. The attribute change can also be made independent of the attribute by converting the internal decimal or the external decimal into a binary double word length and using a binary symbolization means.

FIG. 5 illustrates a database search sequence. The index of the index part 1 encodes numerical data, connects the resulting encoded binary data and other data by a multi-key field concatenation technique, and forward-compresses the resulting key. It contains a compression key that is backward compressed. The front / rear compression / expansion routine 2 has a function of compressing the key in the front / rear direction and a function of expanding the compressed key in the original form. The symbolization routine is to symbolize the input data items i ₁ , i ₂ , i ₃ and concatenate them with the multi-key field concatenation technique. Next, the search sequence will be described.

B. Input data items i ₁ , i ₂ , i ₃ are symbolized respectively.

(B) Output the concatenation key k (however, the maximum value is used for comparison)

C Expands the index key in index section 1.

The key of Niha is compared with the input link key k, and Ha and Ni are repeated.

If the key matches, it outputs the data item pointed to by the matching index.

The insertion of the index of the index part 1 is performed in the following sequence.

(B) Symbolize the input data items i ₁ , i ₂ , i ₃ .

(B) Output the concatenation key.

(C) Expand the index key of the index section 1.

(D) The key of (c) is compared with the input link key k.

Repeat (c) and (d).

(E) When the insertion position of the index is determined, the input concatenated key is compressed by the forward / backward compression routine, and the index formed by the compression key and the pointer is inserted at that position.

With respect to the index, when the index key length is changed, the operation of the symbolization routine will be specifically described in FIG. You want to change the index definition from a small integer (defined as a 2-byte binary item) to a long integer (defined as an 8-byte binary item) when the index key is a single binary item. The fourth
A description will be given using the figure as an example. + 8888, -for small integer
If you enter 8888 (hexadecimal), the symbolization routine 3
Created keys called 428888 and 067778, indexed
Register in index 1 as a key. index·
Keys can be compared as they are, and key expansion is also small inte
It can be converted to ger. Then change to long integer.
If you enter +888888888888, -888888888888 (hexadecimal), 46888888888888 and 027777 are generated by symbolization routine 3.
A key called 77777778 is created and registered in index 1. At this time, the order is as shown in Fig. 4 (c), and sm
Does not affect the key created when all is integer. Furthermore, even if +0 is input, it becomes a key called 40, and it is inserted into the index between 067778 and 428888. Each of these keys can be expanded according to the long integer definition.

FIG. 6 is a diagram showing an example of an inventory table. This table has four items. PRODUCT is the product name, PNO is the product number, QOH is the quantity, and WHNO is the warehouse number. For example, if you want to know the names of products whose inventory quantity is 50 or less from the inventory table, select PRODUCT FROM STOCK WHERE
Enter the statement QOH ≤ 50 into a relational database.

FIG. 7 is a diagram showing an example of the storage structure. In the example shown in the figure, the dense (secondary) index is created with the PRODUCT and PNO multi-keys.

FIG. 8 is a diagram showing an example of symbolic routine conversion and forward / backward compression. For example, the number 3000 is represented as'42'X'0BB8'X when expressed in symbolic binary data. When this symbolic binary data and character data called stereo are concatenated by the multi-key field concatenation technique, 'stereo'
It becomes ‖'04'X ‖'42'X ‖'0BB8'X. On the actual index, as shown in FIG. 9, the compression keys output from the forward / backward compression / expansion routines are combined and arranged, and stored in the index block.

As is clear from the above description, according to the present invention, the numerical data to be a key is converted into binary data having a predetermined byte length, the binary data having a predetermined byte length is symbolized, and the symbolized binary data and other data are obtained. Are concatenated and the concatenated ones are compressed by the compression technique, and the compression key created in this way is used as the index key, so even if you change the length or attributes of numeric data items, the index part The effect is that no reorganization is required.

[Brief description of drawings]

1 and 2 are diagrams for explaining a conventional multi-key field concatenation technique, and FIG. 3 is a symbolization 2 according to the present invention.
FIG. 4 is a diagram showing the structure of a binary data item, FIG. 4 is a diagram showing a specific example of a symbolic binary data item, FIG. 5 is a diagram explaining a database search sequence, FIG. 6 is a diagram showing an example of an inventory table, FIG. 7 is a diagram showing an example of a storage structure, FIG. 8 is a diagram showing an example of symbolic routine conversion and forward / backward compression, and FIG.
The figure is a diagram showing the arrangement of compressed keys on an actual index. 2 ... Index part, 2 ... Forward / backward compression / expansion routine, 3 ... Symbolization routine.

Claims (1)

[Claims] 1. An index part (1) consisting of a plurality of indexes having a compressed key, a compression key for expanding a compressed key extracted from the index part (1) and compressing a key to be written in the index part (1). A method of creating an index in a relational database system, comprising: an expanding means (2); a symbolizing means (3), wherein the symbolizing means (3) includes numerical item data in input item data. If it exists, the numeric item data is converted to binary data of a specified byte length, and the binary data obtained as a result of conversion is positive when it is positive and the deleted '00' (hexadecimal). Converted to symbolic binary data consisting of the data part from which the control byte sequence "00" (hexadecimal) that indicates the number of valid bytes removed is removed, and when it is negative, it is negative and "FF" (hexadecimal) Shaving Control byte and indicates the number of bytes 'FF' (16 decimal) is converted into a symbol of binary data consisting of deleted data unit, if there is other data items in the input item data is
A method of creating an index, characterized in that the symbolic binary data obtained as a result of conversion is concatenated with other data items and the concatenated data is output as a key.