The technique of a related art to optimize a database is disclosed in non-patent document 1 (Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom, “Database Systems: the Complete Book”, the U.S., Prentice Hall, 2002, pp. 787-835.) and non-patent document 2 (C. J. Date, Japanese translation supervised by Y. Fujiwara, “Database System Gairon” (An Introduction to Database Systems), 6th edn., Maruzen Co., Ltd., 1997, pp. 547-571.) in detail. There are various methods for database optimization technique, such as database restructure, query optimization, index optimization, and optimization of data arrangement.
Restructuring a database is to change the organization of data stored in the database in order to improve the efficiency of data accesses and data storage. The organization of data stored in the database is a data schema, schema type, and the number of schemata. Therefore, the database structure (organization of data) can be expressed by a schema set. In this document, a database indicates a relational database. Further, the schema is defined as an attribute set or a pair of an attribute set and a condition set. The definition of the schema can be represented by the following formula 1, where the schema, the attribute set, and the condition set are respectively referred to as S, A, and C, and a pair of the attribute set A and the condition set C is referred to as (A, C). Note that, “|” indicates “or”.S:=A|(A,C)  (Formula 1)
Moreover, the attribute set A and the condition set C are represented by the following formulas 2 and 3 respectively.A:=attribute+  (Formula 2)C:=(LOP+(attributeCOP(attribute|value)))+  (Formula 3)
Note that “attribute” indicates an attribute and “value” indicates an attribute value. The sign “+” indicates that the contents on the left side of the sign appear 0 or more times. Further, COP indicates a comparative operator and LOP indicates a logical operator. As kinds of the comparative operators COP, there are “>”, “<”, “=”, “<=”, “>=”, and “≠”. Moreover, as the logical operators, there are “and (AND)”, “or (OR)”, and “not (NOT)”.
The formula 2 indicates that the attribute set A is defined as a set composed of 0 or more attributes. Further, the formula 3 indicates that the condition set C is defined as a set of 0 or more conditional expressions including LOP (logical operator), an attribute (attribute), COP (comparative operator), and a scalar value (value).
The attribute set A is a set of attributes, in other words, the attribute set A can represent a table of a relational database. For example, a set of attributes of “name” and “age” etc., which is {name, age}, can represent a “table” composed of “name” and “age”.
A pair (A, C) of the attribute set A and the condition set C represents a “view”. A “view” is a virtual table which is made by retrieving or joining a part of a table. For example, if a conditional expression indicates “40 or more years old”, (A, C) indicates a view that has retrieved only the part satisfying the condition of “40 or more years old” from the table, which is the attribute set A. Note that “name”, “age”, etc. indicated here are examples of the attributes and the attributes are not limited to “name” and “age”.
Note that the types of the comparative operator COP or the logical operator are as abovementioned, thus the types of those operator is expressed by the following formula.COP:=“<”|“>”|“=”|“<=”|“>=”|“≠”LOP:=“and”|“or”|“not”
Restructuring a relational database is to update schema sets of the database through schema operations. In this document, the schema operations for restructuring relational database are categorized and defined to 4 types, which are; join, divide, create/delete, and convert type.
The “join” is an operation to merge two or more schemata into one schema and is equivalent to a full outer join of a SQL (Structured Query Language) sentence.
The “divide” is an operation to divide one schema into two or more schemata.
The “create” is an operation to define and generate a new schema. The “delete” is a deleting operation of an existing schema.
The “convert type” is an operation to convert a view into a table.
FIG. 1 is an explanatory diagram illustrating an example of database restructure. In FIG. 1, a case is explained as an example in which a table 1 and a table 2 are merged into one table 3 according to relevance and usage frequency of data. In the example illustrated in FIG. 1, two tables with a common attribute X are restructured to be one table. In the restructure, the schema structure can be modified to optimize the database as described above.
There are many database restructuring systems suggested heretofore (see for example patent document 1 (U.S. Pat. No. 7,007,006), patent document 2 (U.S. Pat. No. 6,546,381), patent document 3 (United States Patent Publication No. 2005/0198019A1) and patent document 4 (United States Patent Publication No. 2005/0198013A1)). In these systems of a related art, an access log in the database is analyzed to automatically generate and recommend a view (materialized view, indexed view) according to an access cost, so as to improve the access efficiency to data. That is, a new schema (view) is created to restructure the database.
Further, patent document 5 (Japanese Unexamined Patent Publication No. 2006-127418) describes a computer system which determines a plan for a database management system to access a database based on the cost.