JP2001101233A - Data base processor - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a data base processor capable of retrieving a data base and modifying the data base by using a view. SOLUTION: For the retrieval of a view to be defined by coupling of plural tables 11a, 11b, a pointer from the lines of one list to the ones of the other list to be coupled with an index to columns of the list on the view is included as a key 13a. When the retrieval by specifying a condition of a value for the columns of the view is performed, a set of pointers of lines is obtained by referring to the index and the lines of the view are selected by integrating the lines to be pointed by the pointers as one line. When lines are inserted into the tables 11a, 11b to constitute the view, the lines of the other table to satisfy a coupling condition with the lines are searched and inserting operation is performed to the index. In addition, when the lines are deleted, the lines of the other table to satisfy the coupling condition with the lines are searched and deletion to the index is performed by values of the columns and the pointers of the lines in which the obtained index is defined.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database processing apparatus, and more particularly, to a database processing apparatus capable of searching a database using a view and changing the database.

[0002]

2. Description of the Related Art In a relational database, a table represents the real world. FIG. 5 is a diagram showing a table composed of 5 columns and 3 rows as an example of the table. Here, the table is a set of rows created as columns of values. In a database system using a relational database, an operation called table normalization is performed to maintain data consistency. As a result, one table in the real world is converted into multiple tables in the database system. A phenomenon that is divided and expressed occurs. For this reason, at the time of inquiry, it is necessary to remember how multiple tables should be combined by a join operation in order to restore the real world table from the divided table, but the user does not remember In order to eliminate the complexity of not being able to do so, it is common practice to define a method of joining a plurality of tables by a view in a database system. Such a definition of how tables are joined is called a "view definition".

Since a view is used to restore a single table in the real world from a plurality of tables stored in a database system, the definition of a view is defined as a join operation, that is, a table that takes a direct product. Consists of what is and the conditions for selecting rows from the set of rows obtained by the direct product.

FIG. 3 is a diagram showing a method of constructing a table represented by a view and an example of dividing a row of the view into rows of each table constituting the view. In the following, the tables to be subjected to the direct product (tables L (11a) and R (11b) in FIG. 3) are referred to as tables constituting views, and are used to select rows from a set 21 of rows of the direct product. The condition is called a join condition. Also, each row 22 selected from the direct product is a table 11a,
11b are connected. Therefore, each row 22 selected from the direct product is a table 1
It is possible to divide into rows 1a and 11b, and each of the divided rows will be referred to as a row associated with a join condition.

[0005] For a view, it is sufficient to store its definition in the database system, and the rows of the view are not permanently stored in the database system. In other words, the view is a table in which only the definition is registered in the database system, and is a virtual one. Therefore,
A view may be called a "virtual table", and a table whose contents are stored permanently in a database system may be called an "entity table".

[0006] By the way, most of the search for the database is to extract a row of the table satisfying the condition from the condition of the value of the column of the table. In order to perform such a search for the entity table at high speed, a means for quickly obtaining a pointer of a row having the value as a column from the value of the column of the table, that is, an index is used. It should be noted here that a row must be permanent in order to obtain a pointer to the row, so a table that can be indexed is an entity table. It is.

FIG. 2 is a diagram for explaining an index for a column of the table, and is a diagram showing an index for the second column of the table L (11a). The index is usually realized by a data structure called a B-tree, and a key value is converted to a corresponding value (hereinafter, the corresponding value is referred to as “value”).
Can be obtained at high speed. When a B-tree is used as an index, the key is the value of a particular column and the value is a pointer to a row. As a feature of the B-tree, a key can be a set of a plurality of values, and a value can be quickly obtained from the plurality of values. It is possible to obtain the set including the first part and the value at high speed. For example, when the key is a set of (a1, a2, a3, a4), by specifying the values of a1 and a2, the keys having the specified values as a1 and a2 (a1, a2)
a2, a3, a4) and value.

[0008]

However, views cannot be indexed like entity tables because only the definitions are stored in the database system and the view rows are not stored permanently. Therefore, when performing a search by specifying a value condition for a column of a view, the direct product of the tables that make up the view is calculated, and the rows that satisfy the join condition are
Search for a column that satisfies the specified column condition, or, if an index is defined on the entity table column corresponding to the view column, obtain a row that satisfies the condition in the entity table at that index. ,
It is necessary to find a row in another table that satisfies the join condition with the row, and any of these methods is a very time-consuming operation. Also, an index for a view devised to solve this problem must be changed in accordance with a change operation of a table constituting the view.

SUMMARY OF THE INVENTION The present invention has been made in view of the above circumstances, and has as its object to provide a database processing apparatus for performing a database inquiry process using a view defined by joining a plurality of entity tables. And It is still another object of the present invention to provide a database processing apparatus capable of performing a high-speed operation of changing a table constituting a view for changing an index for the view.

[0010]

SUMMARY OF THE INVENTION In order to retrieve a view defined by a join of a plurality of tables, an index to a column of a table includes, as a key, a pointer to a row of another table to be joined on the view. An index on an entity table uses a column value as a key and a value as a pointer to a column, but this is changed to eliminate the value, and as a key, a column value and a row with that value are used as a key. Is a pair of the row indicated by the pointer to the row of the key part and the pointer of the row satisfying the joining condition.

When performing a search in which a value condition is specified for a column of a view, a set of pointers of a row is obtained by referring to the index, and a row indicated by these pointers is converted into one row. The rows of the view are selected.

When a row is inserted into a table constituting a view, a row of another table which satisfies the join condition with the row is searched for, and an insertion operation is performed on an index. Also, when a row is deleted from a table that composes a view, a search is made for a row in another table that satisfies the join condition with the deleted row, and as a result, the value of the column defined by the index and the pointer of the row are changed. Then, the index is referred to and the index is deleted.

The invention of each claim is constituted by the following technical means. According to the first aspect of the present invention, a pointer to a row of another table to be joined on the view is added to data of an index of the view for a search on a column of a view defined by joining of a plurality of tables. An index for a view, which is created as a result, is provided.

According to a second aspect of the present invention, in the first aspect of the present invention, the index is changed when a change operation is performed on a table constituting a view by using only the key portion as the index. It is a characteristic.

[0015]

FIG. 1 is a diagram showing an outline of a database processing apparatus according to an embodiment of the present invention. The database processing apparatus according to this embodiment is a terminal including a CPU (central processing unit), a display, and a keyboard. The magnetic disk 1 includes an apparatus 5 and a magnetic disk 1, on which permanent table data 11, index data 12, and data dictionary information 13 are permanently recorded. Entity table data 11, index data 12
Are the table L (11a) and the index data 12 in FIG.
The data has a structure as shown in FIG. As the data dictionary information 13, the number of columns and the number of rows of the entity table, the data type of each column, the index of which column of which entity table stores a row pointer, and View definitions are permanently recorded. These magnetic disks 1
In order to read and write data recorded in the database, a secondary storage access control unit 2, a data dictionary processing unit 3 for referencing and operating a data dictionary, and an inquiry to a database and an operation of data while referring to the data dictionary. The processing unit 4 for performing the processing is implemented as a program.

In the present embodiment, in order to retrieve a view defined by joining a plurality of tables, a pointer to a row of another table to be joined on the view is included as a key in an index for a column of the table. The index for the entity table uses the values of the columns as keys and the values as pointers to the columns, but by changing this, eliminating the values,
A set of a column value, a pointer to a row having the value, a row indicated by a pointer to a key part row, and a pointer to a row satisfying a join condition.

FIG. 4 is a diagram showing an example of an index for the view shown in FIG. 3, which is an example of the view 22 shown in FIG.
Table L (11a) and Table R (11
13B is an example of an index using a pointer to the row of b) as a key.
The definition information of such an index, that is, the information about which index is an index for which view, which key is which column of which table, and which table is a pointer to a row of the table are recorded on the magnetic disk 1 as data dictionary information 13a. Is done. The index data illustrated in FIG. 2 is also recorded on the magnetic disk 1 as index data 12.

When performing a search in which a value condition is specified for a column of a view, a set of pointers to rows is obtained by referring to this index, and the row indicated by these pointers is converted into one row. The rows of the view are selected.

An inquiry for a view, the table L
When the user inputs a query in which the value condition for the second column is specified, the query processing unit 4
Recognizes that there is an index for the view through the data dictionary processing unit 3, refers to the index for the view through the secondary storage access control unit 2, and obtains necessary information. For example, when obtaining the column of the view 22 in which the value of the second column of the table L (11a) is 1, the index of the view is referred to, and the data whose leftmost key value is 1 is referred to. Then, the second row of the table L (11a) and the table R (11b)
Get a pointer to the second row of Then, the data of the row of the table L (11a) and the row of the table R (11b) are fetched from the secondary storage from the respective pointers, and the target row is formed, which is set as the search result for the inquiry.

When a row is inserted into a table constituting a view, a row of another table that satisfies the row and the joining condition is searched, and an insert operation is performed on an index. In the case where a row is added to a table constituting a view, for example, a table L (11
When a row is added to a), a row associated with the row to be added to the table L (11a) and a view join condition is searched from the rows of the table R (11b) by ordinary query processing, and Is added to the index for the column value of the table L (11a), a pointer to the row to be added, and a pointer to the row associated with the row to be added by the join condition. The same applies when a row is added to the table R (11b).

When a row is deleted from a table constituting a view, a row of another table that satisfies the deleted row and a join condition is searched, and as a result, a value of a column defined by an index,
Since the pointer of the row is obtained, the index is referred to and the index is deleted. When a row is deleted from a table forming a view, for example, when a row is deleted from the table L (11a) in FIG.
From the row b), a row associated with the row to be deleted from the table L (11a) and the view join condition is searched by a normal query process, and the values constituting the index key are obtained by the above operation. Therefore, by referring to the index, data to be deleted from the index can be obtained at high speed, and the data can be deleted.

[0022]

According to the database processing apparatus of the present invention, a pointer to a row of another table joined on a view to a row obtained by a condition for a column of a table is selected only by referring to an index, , And an index change operation accompanying deletion of an entity table can be performed efficiently.

That is, in a query in which a condition for a value of a column of a view is specified, a pointer to a row of an entity table constituting a row of the view that satisfies the condition can be obtained only by referring to the index. Can be processed. Also, when a row is deleted from a table constituting a view, a change operation on the index corresponding to the row can be performed at high speed.

[Brief description of the drawings]

FIG. 1 is a diagram illustrating an outline of a database processing apparatus according to an embodiment of the present invention.

FIG. 2 is a diagram for explaining an index for a column of a table.

FIG. 3 is a diagram illustrating an example of a method of configuring a table represented by a view and an example of dividing a row of the view into rows of each table configuring the view.

FIG. 4 is a diagram illustrating an example of an index for the view illustrated in FIG. 3;

FIG. 5 is a diagram showing a table including five columns and three rows as an example of the table.

[Explanation of symbols]

DESCRIPTION OF SYMBOLS 1 ... Magnetic disk, 2 ... Secondary storage access control part, 3 ...
Data dictionary processing unit, 4 ... inquiry and data operation processing unit, 5 ... terminal device, 11 ... entity table data, 11a, 11
b: table constituting the view, 12, 12a: index data,
13: Data dictionary information, 13a: Index data for search in view, 21: Set of rows of direct product, 22: View (row selected from direct product).

Claims (2)

[Claims] 1. For a search on a column of a view defined by a join of a plurality of tables, by appending a pointer to the data of the index of the view to a row of another table to be joined on the view. A database processing device comprising an index for a view to be created. 2. The database processing apparatus according to claim 1, wherein the index is changed only when a change operation is performed on a table constituting a view by using only the key portion as the index. And a database processing device.