SYSTEM, METHOD, AND PROGAM PRODUCT FOR DATABASE RESTRUCTURING SUPPORT

• US 20100217759A1
• Filed: 04/30/2008
• Published: 08/26/2010
• Est. Priority Date: 05/11/2007
• Status: Active Grant

• Current Assignee: NEC Corporation
• Original Assignee: NEC Corporation
• Inventors: Ma, Qiang, Ishiguro, Yoshihide
• Granted Patent: US 8,280,876 B2
• Time in Patent Office: Days
• US Class Current: 707/719
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof

Abstract

A database restructuring support system that enables a user to flexibly design the structure of a database after a restructure.

A storage cost estimate unit 112 calculates storage cost before and after restructure by using catalog information included in a database 122. In the case of calculating the storage cost of the restructure, tuples of a new schema is calculated and the storage cost is calculated by using the tuples. The access cost estimate unit 113 uses the catalog information and the log DB 121 included in the database 122 to calculate each access cost before and after restructure. In the case of calculating the access cost after restructure, the number of types of values of an attribute in the new schema is calculated to calculate the access cost by using the number of types. The integrated cost estimate unit 111 calculates the integrated cost by integrating the access cost and the storage cost.

Claims

1. A database restructuring system that calculates an evaluation value in the case of changing a structure of a database by using a storage cost and an access cost, the storage cost indicating a capacity of a storage device necessary to store data and the access cost indicating a capacity of the storage device accessed by a relation operation, the database restructuring support system comprising:

a database storage unit that stores a database, the database being a set of schemata which are tables or views;

a log storage unit that stores an access log of the database;

a catalog information storage unit that stores catalog information including a number of tuples of a table, a header size of the tuple, an attribute set of the table, a size of each attribute belonging to the attribute set of the table, a number of types of a value of each attribute belonging to the attribute set of the table, a block size of a management area of the database, and a size of a block header;

an input unit that inputs information to specify an existing schema inside the database stored to the database storage unit, a definition of a new schema generated from the existing schema, and information to specify an existing schema remaining after a restructure;

an existing schema storage cost calculation unit that sets a storage cost of a view to 0, and calculates a storage cost of an existing table by calculating a number of tuples of a table that can be stored to one block by using the catalog information and dividing a number of tuples of the existing table by the number of tuples that can be stored to one block;

an existing database storage cost calculation unit that calculates a storage cost of the database before a restructure by obtaining a sum of the storage cost of each view and each table, the storage cost of each view and each table being calculated by the existing schema storage cost calculation unit;

a new schema storage cost calculation unit that sets a storage cost of a new schema to 0 if the new schema is a view and calculates a storage cost of a new schema if the new schema is a table by calculating a number of tuples of the new schema, calculating a number of tuples that can be stored to one block by using the catalog information, and dividing the number of tuples of the new schema by the number of tuples that can be stored to one block;

a new database storage cost calculation unit that calculates the storage cost of the database after the restructure by obtaining a sum of the storage cost of the new schema and the storage cost of the existing schema remaining after the restructure;

an existing schema access cost calculation unit that calculates an access cost of the existing schema by calculating a number of tuples accessed by a relation operation for each SQL sentence representing a relation operation in an access log of a schema to calculate the access cost therefor, converting the number of tuples for each relation operation into a number of blocks by using the catalog information, calculating an access cost for each SQL sentence by obtaining a sum of each block, and obtaining a sum of the access cost of the each SQL sentence;

an existing database access cost calculation unit that calculates an access cost of the database before the restructure by obtaining the sum of the access cost calculated for the each SQL sentence in the access log calculated by the existing schema access cost calculation unit;

a new schema access cost calculation unit that calculates an access cost of the new schema by creating a virtual access log considered as an access log of the new schema from an access log of an existing schema in which the new schema is generated from, calculating a number of types of a value of each attribute belonging to an attribute set of the new schema, calculating a number of tuples accessed by a relation operation for each SQL sentence representing the relation operation in the virtual access log, converting the number of tuples for the each relation operation into a number of blocks by using the calculated number of types of the value of the attribute, the number of tuples of the new schema, and the catalog information, and calculates a sum of the access cost for the each SQL sentence;

a new database access cost calculation unit that calculates the access cost of the database after the restructure by calculating a sum of an access cost for each SQL sentence in an access log of an existing schema not used to create the virtual access log and the access cost calculated by the new schema access cost calculation unit for each SQL sentence in the virtual access log; and

an integrated cost estimate unit that calculates an integrated cost of the database before the restructure by integrating the access cost and the storage cost of the database before the restructure, calculates an integrated cost of the schema before the restructure by integrating the access cost and the storage cost of the schema before the restructure, calculates an integrated cost of the database after the restructure by integrating the access cost and the storage cost of the database after the restructure, and calculates an integrated cost of the schema after the restructure by integrating the access cost and the storage cost of the schema after the restructure.

2. The database restructuring support system according to claim 1, wherein a first parameter and a second parameter are input to the input unit, the first parameter being for weighting the storage cost and the second parameter being for weighting the access cost, and

the integrated cost estimate unit calculates the integrated cost as a sum of a value obtained by multiplying the storage cost by the first parameter and a value obtained by multiplying the access cost by the second parameter.

3. The database restructuring support system according to claim 1, further comprising a visualizing unit that creates an image of a graph illustrating the access cost, the storage cost, and the integrated cost.

4. The database restructuring support system according to claim 3, wherein the existing schema storage cost calculation unit, the existing database storage cost calculation unit, the new schema storage cost calculation unit, the new database storage cost calculation unit, the existing schema access cost calculation unit, the existing database access cost calculation unit, the new schema access cost calculation unit, the new database access cost calculation unit, the integrated cost estimate unit, and the visualizing unit are realized by a same device.

5. The database restructuring support system according to claim 3, wherein the existing schema storage cost calculation unit, the existing database storage cost calculation unit, the new schema storage cost calculation unit, the new database storage cost calculation unit, the existing schema access cost calculation unit, the existing database access cost calculation unit, the new schema access cost calculation unit, the new database access cost calculation unit, and the integrated cost estimate unit are realized by a same device, and the visualizing unit is separately provided from the device.

6. The database restructuring support system according to claim 4, wherein the visualizing unit

creates an image of a graph illustrating the access costs, the storage costs, and the integrated costs of a plurality of types of the schemata, and

creates a document to display a screen including the image and schema structure information that indicates an attribute of the schema.

7. The database restructuring support system according to claim 6, wherein the visualizing unit

creates an image of a graph illustrating the access cost, the storage costs, and the integrated costs of a plurality of types of databases, and

creates a document to display a screen including the image, database structure information indicating a schema included in each database, and schema structure information indicating an attribute of the schema.

8. The database restructuring support system according to claim 7, wherein the visualizing unit

creates a document to display an image of a graph illustrating the access costs, the storage costs, and the integrated costs of the plurality of types of the schemata,

creates a document to display an image of a graph illustrating the access costs, the storage costs, and the integrated costs of the plurality of types of the databases, and

sets a hyperlink to another document in each document.

9. A method of supporting a restructure of a database applied to a database restructuring support system that calculates an evaluation value in the case of changing a structure of a database by using a storage cost and an access cost, the storage cost indicating a capacity of a storage device necessary to store data and the access cost indicating a capacity of the storage device accessed by a relation operation, the database restructuring support system comprises:

a database storage unit that stores a database, the database being a set of schemata which are tables or views;

a log storage unit that stores an access log of the database;

a catalog information storage unit that stores catalog information including a number of tuples of a table, a header size of the tuple, an attribute set of the table, a size of each attribute belonging to the attribute set of the table, a number of types of a value of each attribute belonging to the attribute set of the table, a block size of a management area of the database, and a size of a block header; and

an input unit that inputs information to specify an existing schema inside the database stored to the database storage unit, a definition of a new schema generated from the existing schema, and information to specify an existing schema remaining after a restructure, the method comprising;

setting a storage cost of a view to 0, and calculating a storage cost of an existing table by calculating a number of tuples of a table that can be stored to one block by using the catalog information and dividing a number of tuples of the existing table by the number of tuples that can be stored to one block by an existing schema storage cost calculation unit;

calculating a storage cost of the database before a restructure by obtaining a sum of the storage cost of each view and each table, the storage cost of each view and each table being calculated by the existing schema storage cost calculation unit by an existing database storage cost calculation unit;

setting a storage cost of a new schema to 0 if the new schema is a view and calculating a storage cost of a new schema if the new schema is a table by calculating a number of tuples of the new schema, calculating a number of tuples that can be stored to one block by using the catalog information, and dividing the number of tuples of the new schema by the number of tuples that can be stored to one block by a new schema storage cost calculation unit;

calculating the storage cost of the database after the restructure by obtaining a sum of the storage cost of the new schema and the storage cost of the existing schema remaining after the restructure by a new database storage cost calculation unit;

calculating an access cost of the existing schema by calculating a number of tuples accessed by a relation operation for each SQL sentence representing a relation operation in an access log of a schema to calculate the access cost therefor, converting the number of tuples for each relation operation into a number of blocks by using the catalog information, calculating an access cost for each SQL sentence by obtaining a sum of each block, and obtaining a sum of the access cost of the each SQL sentence by an existing schema access cost calculation unit;

calculating an access cost of the database before the restructure by obtaining the sum of the access cost calculated for the each SQL sentence in the access log calculated by the existing schema access cost calculation unit by an existing database access cost calculation unit;

calculating an access cost of the new schema by creating a virtual access log considered as an access log of the new schema from an access log of an existing schema in which the new schema is generated from, calculating a number of types of a value of each attribute belonging to an attribute set of the new schema, calculating a number of tuples accessed by a relation operation for each SQL sentence representing the relation operation in the virtual access log, converting the number of tuples for the each relation operation into a number of blocks by using the calculated number of types of the value of the attribute, the number of tuples of the new schema, and the catalog information, and calculating a sum of the access cost for the each SQL sentence by a new schema access cost calculation unit;

calculating the access cost of the database after the restructure by calculating a sum of an access cost for each SQL sentence in an access log of an existing schema not used to create the virtual access log and the access cost calculated by the new schema access cost calculation unit for each SQL sentence in the virtual access log by a new database access cost calculation unit; and

calculating an integrated cost of the database before the restructure by integrating the access cost and the storage cost of the database before the restructure, calculating an integrated cost of the schema before the restructure by integrating the access cost and the storage cost of the schema before the restructure, calculating an integrated cost of the database after the restructure by integrating the access cost and the storage cost of the database after the restructure, and calculating an integrated cost of the schema after the restructure by integrating the access cost and the storage cost of the schema after the restructure by an integrated cost estimate unit.

10. A database restructuring support program product mounted to a computer that calculates an evaluation value in the case of changing a structure of a database by using a storage cost and an access cost, the storage cost indicating a capacity of a storage device necessary to store data and the access cost indicating a capacity of the storage device accessed by a relation operation, the computer comprises

a database storage unit that stores a database, the database being a set of schemata which are tables or views;

a log storage unit that stores an access log of the database;

a catalog information storage unit that stores catalog information including a number of tuples of a table, a header size of the tuple, an attribute set of the table, a size of each attribute belonging to the attribute set of the table, a number of types of a value of each attribute belonging to the attribute set of the table, a block size of a management area of the database, and a size of a block header; and

an input unit that inputs information to specify an existing schema inside the database stored to the database storage unit, a definition of a new schema generated from the existing schema, and information to specify an existing schema remaining after a restructure, the database restructuring support program product causing the computer to execute;

an existing schema storage cost calculation process that sets a storage cost of a view to 0, and calculates a storage cost of an existing table by calculating a number of tuples of a table that can be stored to one block by using the catalog information and dividing a number of tuples of the existing table by the number of tuples that can be stored to one block;

an existing database storage cost calculation process that calculates a storage cost of the database before a restructure by obtaining a sum of the storage cost of each view and each table, the storage cost of each view and each table being calculated by the existing schema storage cost calculation process;

a new schema storage cost calculation process that sets a storage cost of a new schema to 0 if the new schema is a view and calculates a storage cost of a new schema if the new schema is a table by calculating a number of tuples of the new schema, calculating a number of tuples that can be stored to one block by using the catalog information, and dividing the number of tuples of the new schema by the number of tuples that can be stored to one block;

a new database storage cost calculation process that calculates the storage cost of the database after the restructure by obtaining a sum of the storage cost of the new schema and the storage cost of the existing schema remaining after the restructure;

an existing schema access cost calculation process that calculates an access cost of the existing schema by calculating a number of tuples accessed by a relation operation for each SQL sentence representing a relation operation in an access log of a schema to calculate the access cost therefor, converting the number of tuples for each relation operation into a number of blocks by using the catalog information, calculating an access cost for each SQL sentence by obtaining a sum of each block, and obtaining a sum of the access cost of the each SQL sentence;

an existing database access cost calculation process that calculates an access cost of the database before the restructure by obtaining the sum of the access cost calculated for the each SQL sentence in the access log calculated by the existing schema access cost calculation process;

a new schema access cost calculation process that calculates an access cost of the new schema by creating a virtual access log considered as an access log of the new schema from an access log of an existing schema in which the new schema is generated from, calculating a number of types of a value of each attribute belonging to an attribute set of the new schema, calculating a number of tuples accessed by a relation operation for each SQL sentence representing the relation operation in the virtual access log, converting the number of tuples for the each relation operation into a number of blocks by using the calculated number of types of the value of the attribute, the number of tuples of the new schema, and the catalog information, and calculates a sum of the access cost for the each SQL sentence;

a new database access cost calculation process that calculates the access cost of the database after the restructure by calculating a sum of an access cost for each SQL sentence in an access log of an existing schema not used to create the virtual access log and the access cost calculated by the new schema access cost calculation process for each SQL sentence in the virtual access log; and

an integrated cost estimate process that calculates an integrated cost of the database before the restructure by integrating the access cost and the storage cost of the database before the restructure, calculates an integrated cost of the schema before the restructure by integrating the access cost and the storage cost of the schema before the restructure, calculates an integrated cost of the database after the restructure by integrating the access cost and the storage cost of the database after the restructure, and calculates an integrated cost of the schema after the restructure by integrating the access cost and the storage cost of the schema after the restructure.