JPH11306183A - Data base retrieval system - Google Patents

Abstract

PROBLEM TO BE SOLVED: To improve retrieval processing to obtain the set operation result by retrieving the number of items storing the same data by means of an index which stores data in order of their value, e.g. in ascending order and then returning the retrieved set operation result. SOLUTION: A database retrieval system includes a set operation processing means 1 which returns the set operation result based on the data value that is read out of an index 2 and the number of items of the duplicated value. The index 2 holds the data value which are stored in ascending order and the duplicated item numbers as the information. When a set operation performs a designated retrieval operation, the index 2 is defined with the value where the columns to be grouped and the set operation are aligned in order of columns to be designated used as the data value. If the same columns are designated for both columns to be grouped and set operation, the index 2 is defined with the value of a single column used as the data value.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database search system and, more particularly, to a search method for performing a set operation designation at the time of a data search request.

[0002]

2. Description of the Related Art A conventional database search system of this kind includes a database search means, a sorting means, and a set operation processing means. This database search system operates as follows.
That is, the table data to be searched is read from the database by the database search means, the data group read by the sorting means is rearranged in ascending order, and the set operation processing means performs a set operation from the data arranged in ascending order as a search result. return.

[0003]

However, the above-mentioned conventional database retrieval system has a problem that the retrieval time for an inquiry for which a set operation is specified is slow.

[0004] The reason is that in order to obtain information on the number of data values that are duplicated with the data values used in the set operation processing, the table data is sorted, and the data groups that are sorted in ascending order are duplicated. This is because all values are read out in order and the number of duplicate values is counted, so that the processing time of the set operation becomes slow.

Japanese Patent Laid-Open Publication No. 4-33767 proposes a database search system that shortens the search process by reducing the search range by eliminating the sort process required when performing a set operation using a group index. Have been.

Accordingly, the present invention has been made in view of the above problems, and an object of the present invention is to provide a database search system that improves the processing performance of a search for obtaining a result of a set operation.

[0007]

According to the present invention, a record has a plurality of data attributes, and data of each data attribute is stored in an area called a data string to form a table. A database search system for outputting a record satisfying a search condition in response to a search request from a user for a database having a data structure, wherein: a column grouped among the data columns; By the grouping, set operations (number, total,
Average, maximum value, minimum value, etc.) are stored in the order of data value size, for example, in ascending order, and the number of cases storing the same data value are stored. It is provided with means for searching using the index and returning the result of the set operation.

[0008]

Embodiments of the present invention will be described below. In a preferred embodiment, the database search system of the present invention includes a set operation processing means (1 in FIG. 1).
The result of the set operation is obtained from the read data value and the number of duplicate data values. Embodiments of the present invention will be described below in detail with reference to the drawings.

FIG. 1 is a block diagram for explaining a first embodiment of the present invention. Referring to FIG. 1, in the first embodiment, the database search system according to the present invention is a set operation processing means for returning a set operation result from the number of data values read from index 2 and the number of duplicate values. 1 is provided.

[0010] The index 2 holds, as information, the number of cases that overlap with the data values stored in ascending order.

When performing a search in which a set operation is specified, an index 2 is defined as a data value using values arranged in the order of a column to be grouped and a column to which the set operation is specified.

If the column to be grouped and the column for which the set operation is specified are the same column, an index 2 is defined using the value of one column as a data value.

The overall processing flow of the first embodiment of the present invention will be described below with reference to FIG. 2, FIG. 3, and FIG. Each processing step described below with reference to FIGS. 2 to 4 and the processing of the set operation processing unit 1 are as follows.
The function can be realized by a program executed on a computer constituting the database search system.

FIG. 2 shows a first embodiment of the present invention in which an index is searched, the number of cases where data values are duplicated is read, and a set operation (number of cases, total, average, minimum value,
9 is a flowchart showing the flow of processing from performing a process of (maximum value) to returning a result.

Referring to FIG. 2, first, an index is searched to read out a data value and the number of records (step 201).

Then, it is determined in step 202 whether or not the next record of the last index record could not be retrieved in order to read out the data. It branches to step 203.

In step 203, it is determined whether or not the index reading is the first reading. In the case of the first search, the process proceeds to step 204.
Branch to step 206.

In step 204, the type of set operation is checked. If the set operation is the minimum value, the flow branches to step 209. If the set operation is not the minimum value, the flow branches to step 205.

In step 209, the retrieved data value is returned as the operation result, and the process returns to step 201.

On the other hand, in step 205, the number of cases, the total, and the cumulative number required for the processing of the set operation and the cumulative total required are initialized (cleared to 0), and the process proceeds to the set operation processing 1 in step 208.

FIG. 3 is a flowchart showing the flow of the set operation processing 1 in step 208 of FIG.

Referring to FIG. 3, in set operation processing 1,
First, it is checked whether or not the type of the set operation is “number of cases” (step 301).
Otherwise, branch to step 302.

In step 305, the value of the number of cases read from the index is added to the number of cumulative cases, and the set operation processing 1 is completed.

In step 302, it is checked whether the type of the set operation is "total". If "total", the flow branches to step 306;

In step 306, the product of the number of data values and the number of data values read from the index is obtained, added to the cumulative total, and the set operation processing 1 is completed.

In step 303, it is checked whether the type of the set operation is “average”. If “average”, the flow branches to step 307; otherwise, the flow branches to step 304.

In step 307, the value of the number read from the index is added to the cumulative number. In the next step 308, the product of the number of data values read from the index and the number is obtained and added to the cumulative total. Then, the processing of the set operation processing 1 is completed.

In step 304, it is checked whether the type of the set operation is "maximum value". If the type is "maximum value", the flow branches to step 309;
Finish the process.

In step 309, the data value read from the index is saved, and the set operation processing 1 is completed.

Referring again to FIG. 2, set operation processing 1 is performed in step 208, and when the processing is completed,
Return to 01.

If it is determined in step 203 that the search is not the first search (in the second or later search), it is determined whether or not the values of the columns to be grouped have changed (step 20).
6) If the value has not changed, the flow branches to step 208, and if the value has changed, the flow branches to set operation processing 2 (step 207), and the result of the set operation is returned.

FIG. 4 is a flowchart showing the flow of the set operation processing 2 in step 207 of FIG.

Referring to FIG. 4, first, it is checked whether or not the type of the set operation is "number of cases" (step 401).
If it is “number”, the process branches to step 405;

In step 405, the value of the number of cases is returned as the result of the set operation, and the flow advances to step 409.

In step 402, it is checked whether or not the type of the set operation is "total". If "total", the flow branches to step 406;

In step 406, the value of the cumulative total is returned as the result of the set operation, and the flow advances to step 409.

In step 403, it is checked whether or not the type of the set operation is "average". If "average", the flow branches to step 407;

At step 407, the cumulative total is divided by the cumulative number, the average value is returned as the result of the set operation, and the routine proceeds to step 409.

In step 409, the values of the cumulative number and the cumulative total are cleared to 0, and the set operation processing 2 is completed.

In step 404, it is checked whether or not the type of the set operation is "minimum value". If it is "minimum value", the flow branches to step 408;

In step 408, the most recently searched data value is returned as the result of the set operation, and the processing of the set operation processing 2 is completed.

In step 410, the data value saved in step 309 of set operation processing 1 is returned as an operation result, and the processing of set operation processing 2 is completed.

Referring again to FIG. 2, the processing of the set operation processing 2 is performed in step 207, and after the processing is completed, the process proceeds to the set operation processing 1 in step 208.

In the search processing in step 201, if the next index record is to be read and the search cannot be performed, the process jumps from step 202 to step 210 to determine whether or not the set operation has the minimum value. If the value is a value, the process ends; otherwise, the process branches to the set operation process 2 in step 211.

In step 211, the set operation processing 2 is performed, and the processing is terminated. Next, effects of the first embodiment will be described.

In the first embodiment of the present invention, since the set operation is performed using the data values obtained in ascending order by the index search and the number of duplicates, the sort operation is not performed in the search for specifying the set operation. Further, since the result of the set operation can be obtained without searching for duplicate values, the search processing cost can be reduced and the search processing performance can be improved.

Next, the operation of the first embodiment of the present invention will be described with reference to a specific example (embodiment). FIG. 5 is a diagram for specifically explaining the first embodiment of the present invention.

Referring to FIG. 5, it is assumed that a table data group 101 of place names and the number of sheets is stored in a database, and an index 2 is generated.

Here, it is assumed that a search for obtaining the total number of data having the same place name is requested from the database search system. In FIG. 5, the column “place name” is a column to be grouped, and the column “number” is a column for calculating the number of set operations.

The index record is read, the data value and the number of cases are taken out (step 201 in FIG. 2), the value of the number of cases is accumulated (step 305 in FIG. 3), and the next index record is read. This process is repeated while the value of 'place name' is the same 'kyoto', and the fourth case reads the index record of 'osaka' where 'place name' is different from 'kyoto' which was read up to then. At this point, the value of the number of cases accumulated up to that time, 5 (1 case + 2 cases + 2 cases), is returned as the result of the number of cases of the set operation (step 405 in FIG. 4).

Similarly, the total number of cases of the group whose “place name” is “osaka” is 4 and the total number of cases of the group whose “place name” is “tokyo” is 3
Return the result as the result of the set operation.

Next, a second embodiment of the present invention will be described in detail. FIG. 6 is a flowchart illustrating a processing flow according to the second embodiment of this invention. Referring to FIG. 6, according to a second embodiment of the present invention, the set operation processing means 1 includes an operation processing determination process (step 212) in the configuration of the first embodiment shown in FIG. An arithmetic processing determination process (step 213) is added.

In each of the arithmetic processing determination processes in steps 212 and 213, the presence or absence of an unprocessed set operation is determined.

Next, the operation of the second embodiment will be described in detail with reference to FIG. When a plurality of set operations are specified, each time an index record is read, after the set operation process is performed by the set operation process 1 or the set operation process 2, the process is not performed in the operation process determination process of step 212. If there is a set operation for processing, step 2
Returning to 03, the unprocessed set operation is performed.

If the index record could not be read in step 202, after the last grouped set operation was performed, if there is any unprocessed set operation in the operation , Step 2
Returning to step 10, the unprocessed set operation is performed.

Next, the operation and effect of the second embodiment of the present invention will be described. In the second embodiment of the present invention,
When there is an unprocessed set operation, the processing of the set operation is repeated. Therefore, even when a plurality of set operations are designated, the set operation processing result can be returned.

[0057]

As described above, according to the present invention,
This has the effect of greatly saving and reducing the entire database search processing time.

The reason is as follows. In a business using a database, a column for eliminating duplication, a column for performing grouping, and a column for specifying a set operation are generally determined in advance. In the present invention, a data value obtained by an index search and the number of duplicates are used. This is because, in the processing of the set operation, the processing of sorting all data groups is not required.

[Brief description of the drawings]

FIG. 1 is a block diagram for explaining a first embodiment of the present invention.

FIG. 2 is a flowchart for explaining a processing flow according to the first embodiment of this invention, and is a flowchart showing a search process for returning a result of a set operation.

FIG. 3 is a flowchart for explaining a processing flow according to the first embodiment of the present invention, and is a diagram showing a processing flow of set operation processing 1 in FIG. 2;

FIG. 4 is a flowchart for explaining a processing flow of the first embodiment of the present invention, and is a diagram showing a processing flow of the set operation processing 2 in FIG. 2;

FIG. 5 is a diagram for specifically explaining the first embodiment of the present invention, and is a diagram illustrating an example of a table data group and an index.

FIG. 6 is a flowchart showing a processing flow according to a second embodiment of the present invention.

[Explanation of symbols]

 1 set operation processing means 2 index 3 search result output

Claims (5)

[Claims] 1. A database in which a record has a plurality of data attributes and data of each data attribute is stored in an area called a data string and has a data structure in a tabular form as a whole. What is claimed is: 1. A database search system for outputting a record satisfying a search condition in response to a search request, comprising: a column grouped in the data columns; and a group operation (number, total, average, maximum) by the grouping. Value, minimum value, etc.) are searched using an index stored in the order of the data value, and a data value composed of a set of values of a column on which the same data value is stored,
A database search system comprising means for returning a result of a set operation. 2. In a database search system in which a data item column and a value column storing a value associated with the data item are stored in a table format in a record having a plurality of data attributes, the data to be grouped For the item and the value column for which a set operation is to be performed on the data item, the number information of the item whose value is duplicated is associated with the data value consisting of the data item and its value, and Means for calculating the result of the set operation specified by the search request from the data value information and the number of duplicate data values read in the search of the index file in response to the data search request A database search system comprising: 3. A database in which a record has a plurality of data attributes and data of each data attribute is stored in an area called a data string and has a data structure in a tabular form as a whole. What is claimed is: 1. A database search system for outputting a record satisfying a search condition in response to a search request, comprising: a column that is grouped among the data columns; and a number, a total, an average, a maximum, and a minimum by the grouping. A data value composed of a set of numerical values of a column on which a set operation such as a value is performed and the number of duplicates of the data value are stored as one entry in the order of the data value. If the columns for which the set operation is specified are the same column, an index file that stores the value of one column as a data value is provided. Set operation that returns the result of the set operation specified from the cumulative number, total, average, maximum value, minimum value, etc., based on the number of data values that overlap the read data value and the value corresponding to the number And a processing means. 4. A means for controlling, when a plurality of set operations are specified in the search request, to sequentially activate the set operation processing means for each set operation and returning a plurality of set operation results. The database retrieval system according to claim 3, further comprising: 5. A database in which a record has a plurality of data attributes and data of each data attribute is stored in an area called a data string and has a data structure in a tabular form as a whole. What is claimed is: 1. A database search system for outputting a record satisfying a search condition in response to a search request, comprising: a column that is grouped among the data columns; and a number, a total, an average, a maximum, and a minimum by the grouping. A data value composed of a set of numerical values of a column on which a set operation such as a value is performed and the number of duplicates of the data value are stored as an entry in the order of the data value, and the column and the group to be grouped are stored. If the columns for which the operation is specified are the same column, an index file storing the values of one column as data values is prepared, and (a) the index file is stored in response to a search request. (B) decoding the specification of the search request and, in accordance with the specification, determining the number of data values of the grouped columns and the number of the data items according to the specification. (A) a process of performing a set operation such as a cumulative number, a total, an average, a maximum value, and a minimum value from the corresponding value; (c) a process of performing the above (a) and (b) until the end of the index record; A) a recording medium on which a program for causing the computer to execute the processes of (c) to (c) is recorded.