JPH023870A - Method for retrieving record of relational data base - Google Patents

Abstract

PURPOSE:To improve join processing performance by extracting the contents of a join field from a first table, and setting up a retrieving condition in a second table, and retrieving a record to agree with this condition, and generating a third table, and join-processing the first and the third tables. CONSTITUTION:The contents of the join field is extracted from a first table T1' retrieved before, and the extracted contents of the join field is added to the retrieving condition of a second table T2, and a new retrieving condition is set up, and only the record to agree with the retrieving condition is extracted from the second table of a relational data base on this retrieving condition, and a third table T2' is generated. Join processing is performed by considering the records of the first and the third tables to be objects. At this time, since all the records in the third table consist of the object records of join, unnecessary processing for the record with no possibility of the join can be excluded. Thus, the performance of the join processing can be improved.

Description

DETAILED DESCRIPTION OF THE INVENTION [Field of Industrial Application] The present invention relates to relational databases, and particularly to a record search method suitable for join processing.

[Conventional technology]

One of the search processes in relational databases is join processing. This is based on fields that have common attribute information for records in multiple tables.
This is a process that combines corresponding records according to join conditions and creates a new record.

An overview of the join process will be explained using the model in FIG. 2 as an example.

In FIG. 2, it is assumed that two tables Tl and T2 are stored on the auxiliary storage. Regarding this, let us assume that there is a search request using the following search command (SQL statement).

5ELECT C12, CC2 3FROTl, T2 WHERE C11=C21ANDC1l≧20 The meaning of this command is "Two tables Tl.

From T2, the condition (C11=C2L AND 011≧
Select the record that satisfies 20, field C12,
"Output C23."

The primary processes (1) to (3) are performed for this search request.

(1) Read tables Tl and T2 on auxiliary memory into main memory, at this time, selection condition C1l≧20 for T1
Read only records that meet the criteria.

Since there is no selection condition for T2, all records are read.

(2) Perform join processing on tables Tl' and T2' read into the main memory according to join conditions (sort processing, record comparison).

(3) Create table T3 based on the result of the join process and output only fields C12 and C23.

In the second step (2), table T
The number of records for l' and T2j is generally large.

This is a bottleneck in the efficiency of join processing.

Conventionally, as a method to improve the efficiency of this join process, as described in Japanese Patent Laid-Open No. 57-105035, the maximum and minimum values of the join field are found, and the records are narrowed down to the range of these two values and joined. processing method (hereinafter referred to as the first conventional method), or JP-A-59-6
As described in Publication No. 0659, when the fields specified in the join condition and the search condition are the same, a new search condition is created from this, and the records are narrowed down using this condition and the join process is performed (hereinafter, the second conventional method). method)
etc. are known. Applying these to the case of FIG. 2 results in the following. In the first conventional method, the table Tl
Check the minimum and maximum values of the join fields of ', T2' and limit the join range. As a result, the join range becomes 20 or more and 30 or less, and the number of records to be joined in table T2' decreases from 50 to 11. Furthermore, in the second conventional method, the stain-in condition C11=C
21 and the selection condition C1l≧20, the condition C21≧20 is created and is set as the selection condition of table T2.

As a result, the number of records in table T2' is reduced from 50 to 31.

[Problem to be solved by the invention]

The above-mentioned conventional technology does not take into consideration the fact that there are records that have no possibility of simi-in even when the range of join processing is limited, and the sorting process includes records that have no possibility of joining.

Since comparison processing was also performed with records that had no possibility of joining, there was a limit to the performance improvement of join processing.

Furthermore, in the second conventional method, there is a problem in that records cannot be narrowed down unless a search condition using the same field as the field specified in the join condition is specified in the search command.

An object of the present invention is to further improve the performance of join processing by removing records that have no possibility of joining when searching the database for records of tables to be joined in relational database record searches. be.

[Means to solve the problem]

In the relational database record search method of the present invention, when joining a plurality of tables based on a symin field and extracting records that meet the join conditions as a set, a first table that has already been searched based on the search conditions is searched. Extract the content of the join field from , add the extracted content to the search conditions in the second table to create a new search condition, search the second table for records that match the search conditions, and search the second table for records that match the search conditions. This method is characterized in that it generates three tables and performs join processing on the first and third tables.

[For production]

First, the content of the join field is extracted from the already searched first table (Tl'). Next, the content of the extracted join field is extracted.

In addition to the search conditions of the second table (T2).

Create a new search condition, use this search condition to retrieve only the records that match the search condition from the second table of the relational database, and retrieve the records that match the search condition from the third table (T2).
'). The join processing is performed on the records of the first table and the third table.
Since the records in the table are all records to be joined, unnecessary processing for records that have no possibility of being joined can be eliminated, and the performance of join processing can be improved.

In case 1 of the example shown in FIG. 2, in the present invention, join condition C11=
Look at C12 and select only records that satisfy this condition from table T2. In this case, C of table Tl'
For 1 liter, the values 20, 25.30 are selective liquids, so
(C21=20 0RC21=25 0RC21=30
) is created and used as the selection condition (search condition) for table T2. As a result, the searched table T2
The number of records ' has been reduced from 50 to 3, indicating that the efficiency of the 3-in processing is much improved compared to the conventional method.

〔Example〕

An embodiment of the present invention will be described below with reference to the drawings.

FIG. 1 shows a block diagram of one embodiment of the invention.

A join field extraction unit 101 analyzes the join conditions and extracts the contents of the join fields from the join target table 1 (Tl') that has already been searched from the relational database 105. A search condition assembling unit 102 assembles a search condition from the contents of the join field extracted by the join field extraction unit 101, and adds it to the search condition of another join target table 2 (T2). Reference numeral 103 denotes a record search unit that searches for records in the join target table 2 in the relational database 105 using the search conditions created by the search condition assembling unit 102, and searches the join target table 3 (T2') for only records that match the search conditions. ) is generated. The join processing unit 104 targets the records of tables 1 and 3,
Performs join processing to extract records with matching join fields as a set.

゛Specific processing examples of this embodiment are listed below in Fig. 3.

FIG. 3 shows search conditions and join conditions written in a database operation language. 301 is a database operation language; 302, 303, and 304 are search conditions for records in join target table 1; and 305 are search conditions for records in join target table 2. 306 indicates join conditions for records in tables 1 and 3.

FIGS. 4 and 5 show specific examples of tables used in join processing, where 401 is a product table and 501 is a manufacturer table.

From the product clothing 401 in Figure 4, 302.30 in Figure 3
A table 402 is obtained by extracting only the records matching the search conditions No. 3 and 304, and this corresponds to the searched join target table 1 in FIG. This table 40
2, the join field extraction unit 101 performs a join.
The field 403 (manufacturer name) is extracted, and the search condition assembling unit 102 assembles a search condition from the extracted contents, resulting in the search condition 602 in FIG.

A search condition 601 in FIG. 6 is a search condition for the manufacturer table 501 indicated by 305 in FIG. In Fig. 6, 603 is a field identifier, 604 is a comparison operator, 605 is the number of comparison values, and 606 and 607 are comparison values.
07 is the content of the join field extracted by the join field extractor lot.

The record search unit 103 searches for a record that satisfies both search conditions 601 and 602 from the manufacturer table 501 in FIG. 5 (corresponding to join target table 2 in FIG. corresponding). The join processing unit 104.

A join process is performed for records in tables 402 and 502 to extract records whose respective join fields 403 and 503 match the join condition 306 in FIG. 3 as a set.

The join process itself is the same as before.

According to this embodiment, in the process of searching for records in the manufacturer table 501 and generating the search result manufacturer table 502, it is possible to exclude records with no possibility of simi-in. Sorting processing and join field comparison processing are eliminated, improving the processing performance of join processing.

〔Effect of the invention〕

As explained above, according to the present invention, when performing a join process, it is possible to eliminate the sorting process that includes unnecessary records that have no possibility of being joined, and the process of comparing join fields with unnecessary records. This has the effect of significantly improving the processing performance of one-in processing.

[Brief explanation of the drawing]

FIG. 1 is a block diagram of an embodiment of the present invention, FIG. 2 is a diagram explaining an overview of join processing, and FIG. 3 is a diagram showing a specific example of join search conditions written in a database operation language.
FIG. 4 is a diagram showing a specific example of one join target table, FIG. 5 is a diagram showing a specific example of the other join target table, and FIG. 6 is a diagram showing a specific example of search conditions for the other join target table. It is. 101...Join field extraction section, 102...
- Search condition assembling unit, 103...Record search unit, 104...Join processing unit, 105...Relational database. Figure 2

Claims (1)

[Claims] (1) In a relational database that has multiple tables in which records of the same type consisting of attribute information corresponding to multiple fields are collectively described, join the multiple tables mentioned above based on the join field. When extracting records that match the conditions as a set, the contents of the join field are extracted from the first table that has already been searched according to the search conditions, and the extracted contents are added to the search conditions of the second table to create a new table. The method is characterized by assembling a search condition, searching a second table for a record matching the search condition to generate a third table, and performing join processing on the first and third tables. How to search for records in relational databases.