JP4084740B2 - Database search apparatus, database search method and program - Google Patents

Description

  The present invention relates to a technique for performing data search for a database having a plurality of schemas.

  In a database search system for a database composed of a plurality of data tables used as a data warehouse, a database is constructed by a schema defined by a database administrator or the like. When the user performs a search on the database, the database free search tool generates a query according to the database schema from the search and output conditions input by the user, performs a search to the database, and outputs the data. Is.

  In a conventional database search system, a uniform schema that can satisfy all the conditions related to the input search is defined, and a simple fact table is prepared for that purpose. Alone could not optimize the query execution time.

  Therefore, for example, in Patent Document 1, in order to solve the problem that the schema does not match the user usage method, SQL (Structured Query Language) is stored as the user usage status information, and the schema usage pattern of the user is derived and set. An optimal schema pattern is generated and used according to a tuning target (throughput, storage capacity, etc.).

JP 2000-259723 A

  However, in the database system described in Patent Document 1, it is possible to optimize and use the schema pattern. However, in order to have the optimization mechanism as the RDBMS function of the database, it is necessary to introduce a very expensive database. Necessary. In these expensive databases, it is necessary to construct a database called by a name such as a cube. In this case, it is necessary to create a plurality of identical data tables, and useless storage capacity is required.

  Furthermore, although it is possible to optimize the schema in Patent Document 1, since query generation is not optimized, uniform query generation is performed even when a simple search is performed using the optimized schema. Therefore, there is a disadvantage that useless processing is performed in query generation. Further, conventionally, the schema defined in the schema definition table cannot be found immediately, and this processing may take a long time.

An object of the present invention is to provide a search process for searching for a suitable schema from a plurality of schemas and a mechanism for shortening the processing time of the entire search process of the search process using the searched schema.

In the database search apparatus of the present invention, a pair of associations between a fact table having an aggregation target item and a dimension table having an aggregation axis item associated with the fact table is associated as one record, Included in the storage means for storing a schema definition table that defines a plurality of schemas corresponding to the record, which is the logical structure of the database, input means for inputting data search conditions, and search conditions input by the input means Determination means for determining a fact table or dimension table having a search item, and determination means for determining whether or not a record having all the fact tables or dimension tables determined by the determination means is in the record of the schema definition table And the determination means If it is determined that the record having all the fact table or dimension table determined in step 1 is not in the record of the schema definition table, the search including the items including all the search target items is set as the search target, and the search is input by the input unit. The first query generation means for generating a query in accordance with the conditions and the determination means determine that the record including all the fact tables or dimension tables determined by the determination means is in the record of the schema definition table In this case, a second query generation unit that generates a query according to the search condition input by the input unit using the schema according to the record as a search target is provided.
The database search method of the present invention is a database search method in a database search device, wherein the storage means of the database search device has a fact table having items to be aggregated, and items of aggregate axes associated with the fact table. A storage step of storing a schema definition table in which a plurality of schemas corresponding to the record, which is a logical structure of the database, is linked with a pair of associations with a dimension table having a database, and the database search An input unit of the apparatus inputs an input process of data search conditions, and a determination unit of the database search apparatus determines a fact table or a dimension table having search items included in the search conditions input in the input process. Determination process and the database A determination unit for determining whether a record having all the fact table or dimension table determined in the determination step is in the record of the schema definition table; and a first of the database search device In the determination step, the query generation means includes all the search target items when it is determined that the record including all of the fact table or dimension table determined in the determination step is not in the record of the schema definition table. A first query generation step for generating a query in accordance with a search condition input by the input unit using a schema as a search target, and a second query generation unit of the database search device include the determination in the determination step. Fact table or dimension table determined in the process A second query that generates a query according to the search condition input in the input step, with the schema according to the record as a search target, when it is determined that all the records are included in the record of the schema definition table And a generation step.
A program of the present invention is a program for causing a computer to execute a database search method in a database search device, wherein the storage means of the database search device associates a fact table having items to be aggregated with the fact table. A memory for storing a schema definition table in which a pair of associations with a dimension table having an item of the aggregated axis specified is linked as one record and which is a logical structure of the database and which defines a plurality of schemas corresponding to the record A fact table in which the input unit of the database search device inputs a data search condition; and the determination unit of the database search device has a search item included in the search condition input in the input step Or dimension A determination step of determining a table, and a determination unit of the database search device determining whether or not a record including all the fact table or dimension table determined in the determination step is in the record of the schema definition table And the first query generation means of the database search device determines in the determination step that a record including all the fact table or dimension table determined in the determination step is not included in the record of the schema definition table. A first query generation step of generating a query according to a search condition input by the input means, using a schema including all search target items as a search target, and a second query generation of the database search device Means for determining When it is determined that the record having all the fact table or dimension table determined in the determination step is in the record of the schema definition table, the schema according to the record is input as the search target in the input step. A computer is caused to execute a second query generation step of generating a query according to a search condition.

  According to the present invention, it is possible to shorten the processing time of the entire search process of the search process for searching for a suitable schema from a plurality of schemas and the search process using the searched schema.

  DESCRIPTION OF EXEMPLARY EMBODIMENTS Hereinafter, preferred embodiments to which the invention is applied will be described in detail with reference to the accompanying drawings.

FIG. 1 is a diagram schematically showing a configuration of a database search system according to an embodiment of the present invention.
Reference numeral 100 denotes a client PC operated by the user, which displays a Web page transmitted from a database search server 200 (described later) on the screen using a Web browser. A database search server 200 includes a free search application 21 and a free search DB (free search database) 22 in which various data are recorded. The free search application 21 searches the free search DB 22 for data according to a request from the client PC 100. Then, the result is transmitted to each client PC 100 as a Web page. A system DB server 300 transmits data to be stored in the free search DB 22 to the database search server 200 sequentially or by batch processing.

  Each client 100 connected to the database search server 200 inputs an output condition for designating a desired output data item and the search condition on the screen, and transmits the input contents to the database search server 200. Receiving the input content, the database search server 200 acquires from the free search DB 22 data that matches the search condition among the data listed in the output condition. The output condition is also a condition for searching for data, and is included in the concept of the output condition. Therefore, in the following description, the term “search condition” is used to include the output condition.

  Next, the database search server 200 returns the acquired data to the client PC 100 as, for example, CSV (Comma Separated Value) data, and the client PC 100 can check the search result on the screen.

FIG. 2 is a diagram illustrating a hardware configuration of the database search server 200.
The CPU 201 comprehensively controls each device and controller connected to the system bus 204. Further, the ROM 202 or the external memory 211 is necessary for realizing a function executed by the database search server 200, such as a BIOS (Basic Input / Output System), an operating system program (hereinafter referred to as OS), which is a control program of the CPU 201. Various programs to be described later are stored. The RAM 203 functions as a main memory, work area, and the like for the CPU 201. The CPU 201 implements various operations by loading a program necessary for execution of processing into the RAM 203 and executing the program.

  An input controller (input C) 205 controls input from a pointing device such as a keyboard 209 or a mouse (not shown). A video controller (VC) 206 controls display on a display device such as a CRT display (CRT) 210. The display device may be a liquid crystal display as well as a CRT. These are used by the administrator as necessary, and are not directly related to the present invention. The memory controller (MC) 207 has an adapter in a hard disk (HD), flexible disk (FD), or PCMCIA card slot that stores a boot program, browser software, various applications, font data, user files, editing files, various data, and the like. Controls access to an external memory 211 such as a compact flash (R) memory connected via the network.

  A communication I / F controller (communication I / FC) 208 is connected to and communicates with an external device via a network, and executes communication control processing in the network. For example, Internet communication using TCP / IP is possible. Note that the CPU 201 enables display on the CRT 210 by executing outline font rasterization processing on a display information area in the RAM 203, for example. In addition, the CPU 201 enables a user instruction with a mouse cursor (not shown) on the CRT 210.

  The hardware configuration of the client PC 100 is the same as that of the database search server 200, and the programs stored in the external memory 211 are different.

Next, a data table used in the present embodiment will be described.
In this embodiment, as shown in FIG. 3, two master tables and three transaction tables are used as one configuration of a database to be searched. The master table includes a customer master table and a product master table, and the transaction table includes an actual transaction table, a customer total transaction table, and a product total transaction table. The customer aggregation transaction and the product aggregation transaction table are data tables created based on the actual transaction table in the DB server 300 by night batch processing or the like in order to improve the data search speed for each customer or each product. Then, the date record in the transaction table is aggregated in the first half and second half records for each customer and product.

  The customer master table includes a customer CD item in which a customer code is recorded and a customer name item in which a customer name is recorded. The product master table includes a product CD item in which a product code is recorded and a product name item in which a product name is recorded.

  The actual transaction table includes the year / month item in which the date of sale is recorded, the customer CD item in which the customer code is recorded, the product CD item in which the product code is recorded, the number of sales item in which the number of sales is recorded, and the sales amount. Consists of recorded sales amount items.

  Customer tabulation transaction table includes customer CD item recording customer code, first half sales number recording first half sales, first half sales amount recording first half sales, second half sales number recording second half sales And the second half sales amount item that records the second half sales amount.

  Product summary transaction table includes product CD item with product code, first half sales number with first half sales, first half sales amount with first half sales, second half sales with second half sales and second half sales Consists of sales amount items in the second half that record the amount.

FIG. 4 is a diagram showing a database of three schema structures to be searched in the present embodiment.
As shown in FIG. 4, the configuration of the database whose schema ID is “0” (hereinafter abbreviated as schema 0. The same applies to schema IDs “1” and “2”) is a star schema structure. Schema 0 uses the actual transaction table as a fact table. The actual transaction table is associated with the customer master table and the customer aggregation transaction table using the customer CD item as a key. The actual transaction is performed using the product CD item as a key. The table is associated with the product master table and the product aggregation transaction table. Since the database of schema 0 has a configuration including all data tables, all items can be searched.

  The database of schema 1 is composed of a customer master table and a customer aggregation transaction table. When this database is a search target, only the items included in the customer master table and the customer aggregation transaction table are searched. Can do. In this database structure, the customer aggregation transaction table is a fact table, and the customer master table and the customer aggregation transaction table are associated with each other using the customer CD item as a key.

  The database of the schema 2 includes a product master table and a product aggregation transaction table. When this database is a search target, only items included in the product master table and the product aggregation transaction table can be searched. In this database structure, the product aggregation transaction table is a fact table, and the product master table and the product aggregation transaction table are associated with each other using the product CD item as a key.

FIG. 5A shows a data table defining a schema structure (hereinafter referred to as a schema definition table).
The database configuration of schemas 0 to 2 is defined by this schema definition table. That is, in this schema definition table, with respect to the schema 0, four dimension tables, a customer master table, a product master table, a customer aggregation transaction table, and a product aggregation transaction table correspond to the actual transaction table that is a fact table. For schema 1, one dimension table and customer master table are associated with the customer aggregation transaction table that is the fact table, and for schema 2, the product aggregation transaction that is the fact table. One dimension table and a product master table are associated with the table.

FIG. 5B shows a data table defining a fact table (hereinafter referred to as a fact table definition table).
A fact table in the database structure of schemas 0 to 2 is defined by the fact table definition table. That is, in the fact table definition table, the actual transaction table in the case of schema 0, the customer total transaction table in the case of schema 1, and the product total transaction table in the case of schema 2 indicate the fact table. . The customer master table and the product master table are associated with the schema ID “null” because the master table cannot be a fact table. The schema definition table and the fact table definition table are recorded in the RAM 203, for example.

  In FIG. 5B, only three database configurations of schema 0 to schema 2 are illustrated. When the number of data tables is increased in addition to the above five, more various database configurations by combining the data tables can be searched. For example, the fact tables of schemas 3 to 9 (schema ID = 1) It is needless to say that it is possible to increase variations by defining (˜9: fact-limited schema fact table) and simultaneously registering it in the schema definition table of FIG.

Next, the data search operation by the database search server 200 will be described with reference to FIGS. 6-1 and 6-2. 6A and 6B are flowcharts illustrating the operation of the database search server 200 according to the present embodiment.
In this database search operation, the user's search condition designation is, for example, 1 in one or more data tables for a database composed of a plurality of data tables as described in Japanese Patent Application No. 2002-382470 by the applicant of the present application. It is possible to apply a mechanism for inputting search specification conditions by specifying the above item names, and similarly performing search as display specification conditions (search output conditions) by specifying one or more item names in one or more data tables. Is possible.

-Processing Example 1-
First, processing example 1 by the database search server 200 will be described. Here, a case will be described in which “search for a customer name by specifying a customer code (customer CD =“ 1001 ”)” is received from the client PC 100 as a search condition.

  In this processing example, since it is assumed that the search condition specifying the customer code and the customer name is received from the client PC 100, as shown in FIG. , Referred to as a usage item) is a customer code item and a customer name item, and a table used for searching (hereinafter referred to as a usage table) is a customer master table. The user selects a desired item from items displayed on the screen of the client PC 100. The selected item information is transmitted as a search condition from the client PC 100 to the database search server 200, and the database search server 200 simultaneously determines the use table as well as the use item by receiving the search condition including the item information.

  In response to the search condition from the client PC 100, the CPU 201 determines a usage table from user specified items (customer code, customer name) (step S601), and records the customer master table in the RAM 203 as usage table information. To do. The usage table can be determined by knowing which table item information is specified. In this case, the case where all conditions are specified while viewing the items in the customer master table is applicable. Even when the same item is specified, if the customer CD of the customer totaling transaction table is specified, for example, the customer totaling transaction table is added to the usage table. When the same table is specified a plurality of times, the same table is overwritten when the usage table is written in order to keep the same table one so as not to overlap. Here, the description will be continued assuming that only the customer master table is designated.

  Subsequently, the CPU 201 determines whether or not reading of all usage tables included in the usage table information from the RAM 203 has been completed (step S602). At this point, since the usage table has not yet been read, the process proceeds to step S603.

  Subsequently, the CPU 201 reads one usage table from the RAM 203, refers to the table ID of the usage table from the usage table ID table shown in FIG. 8, and sets it in the table variable [A] (step S603). Since the usage table read here is the customer master table, the table variable [A] is set to “0” in the RAM 203. The usage table ID table is recorded in the RAM 203, for example.

  Subsequently, the CPU 201 refers to the table with the table ID “0” set in the table variable [A], that is, the schema ID of the customer master table from the fact table definition table, and stores the schema ID in the RAM 203 as the schema ID. Variable [B] is set (step S604). Here, in the fact table definition table, the schema ID when the customer master table is the fact table is defined as “null”, so the schema ID variable [B] is set to “null”. The fact table definition table is recorded in the RAM 203, for example.

  Subsequently, the CPU 201 determines whether or not the set schema ID variable [B] is “0” (step S605). Here, since the schema ID variable [B] is set to “null”, the process proceeds to step S606.

  Subsequently, the CPU 201 determines whether or not the set schema ID variable [B] is “null” (step S606). Since the schema ID variable [B] is “null”, the process proceeds to step S607.

  Subsequently, the CPU 201 additionally sets a table variable [A] to the usage table definition variable [C]. Now, since nothing is set in the usage table definition variable [C], the usage table definition variable [C] is set to “0” in the RAM 203. Further, the CPU 201 sets a use table definition variable [C], and sets a schema determination definition variable [D] and a schema determination definition variable [E]. That is, in the RAM 203, the table variable [A] “0” is set to the schema determination definition variable [D], and “null” is set to the schema determination definition variable [E] (step S607).

  The process of the CPU 201 returns to step S602, and it is determined again whether or not reading of all the usage tables has been completed. Now, since all the usage tables have been read from the RAM 203 by the CPU 201, the processing moves to step S611 in FIG.

  The CPU 201 reads the schema judgment definition variable [D] and the schema judgment definition variable [E] from the RAM 203 (step S611), and the contents set in the schema judgment definition variable [E] are “null”, “0”, and “ It is determined whether it is 1 or more "(step S612). Now, since “null” is set in the schema determination definition variable [E], the process proceeds to step S613.

  Subsequently, the CPU 201 reads usage table information from the RAM 203, sets the number of usage tables included in the usage table information in the count variable [F] in the RAM 203 (step S613), and sets the count variable [F]. It is determined whether or not the value is “1” (step S614). Now, since the value of the count variable [F] is set to “1”, the search process for only the master table specified by the search condition, that is, the customer master table is determined (step S615). .

  On the other hand, when the value of the count variable [F] is not “1”, the database structure to be searched is determined as schema 0. This is because, when the value of the count variable [F] is not “1”, there is a possibility that a plurality of master tables are set in the usage table, and a database of a schema with a transaction table having a large amount of data as a fact table. This is because it is more efficient to determine schema 0 and move to immediate search processing rather than spending time in determining the schema during search processing.

Subsequently, the CPU 201 generates a search query. Here, since only the customer master table is determined as the search target in step S615, the following search query is generated. The SELECT statement specifies the name of the item to be searched and the table name to which the item belongs, the FROM statement specifies the name of the table to be searched, and the WHERE statement specifies the search condition.
<Search query>
SELECT customer M. customer name
FROM Customer M
WHERE customer M. customer CD = '1001'

-Processing example 2-
Next, processing example 2 by the database search server 200 will be described. Here, a case will be described in which “searching for a customer name, a product name, and a sales number by specifying the actual date, customer code, and product code” is received from the client PC 100 as a search condition.

  In this processing example, since it is assumed that the search condition specifying the actual date, customer code, product code, customer name, product name, and number of sales is received from the client PC 100, as shown in FIG. The usage items are a year / month item, a customer CD item, a product CD item, a customer name, a product name, and a sales number, and the usage tables are an actual transaction table, a customer master table, and a product master table.

  In response to the search condition from the client PC, the CPU 201 determines a usage table from user-specified items (actual date, customer code, product code, customer name, product name, and number of sales) (step S601). The actual transaction table, customer master table, and product master table are recorded in the RAM 203 as usage table information.

  Subsequently, the CPU 201 determines whether or not reading of all the usage tables included in the usage table information from the RAM 203 has been completed (step S602). At this point, since the usage table has not yet been read, the process proceeds to step S603.

  Subsequently, the CPU 201 reads one usage table from the RAM 203, refers to the table ID of the usage table from the usage table ID table shown in FIG. 8, and sets it in the table variable [A] (step S603). Assuming that the usage table read here is an actual transaction table, the table variable [A] is set to “2” in the RAM 203.

  Subsequently, the CPU 201 refers to the table of the table ID “2” set in the variable [A], that is, the schema ID of the actual transaction table from the fact table definition table, and stores the schema ID in the RAM 203 in the schema ID variable [ B] is set (step S604). Here, in the fact table definition table, the schema ID when the actual transaction is the fact table is defined as “0”, so the schema ID variable [B] is set to “0”.

  Subsequently, the CPU 201 determines whether or not the set schema ID variable [B] is “0” (step S605). Here, since the schema ID variable [B] is set to “0”, the process proceeds to step S610.

  Subsequently, the CPU 201 sets a schema judgment definition variable [D] and a schema judgment definition variable [E] (step S610). A table variable [A], that is, a table ID “2” of the actual transaction table is set in the schema judgment definition variable [D], and “0” is set in the schema judgment definition variable [E].

  The CPU 201 reads the schema judgment definition variable [D] and the schema judgment definition variable [E] from the RAM 203 (step S611), and the contents set in the schema judgment definition variable [E] are “null”, “0”, and “ It is determined whether it is one or more "(step S612). Now, since “0” is set in the schema judgment definition variable [E], the process proceeds to step S616.

Subsequently, the CPU 201 determines the configuration of the database to be searched as schema 0 (step S616). Next, the CPU 201 generates a search query. Here, since schema 0 is determined as a search target in step S616, the following search query is generated.
<Search query>
SELECT Customer M. Customer Name, Product M. Product Name, Actual TRN. Number of Sales
FROM Customer M, Product M, Actual TRN
WHERE Customer M. Customer CD = '1001' AND Product M. Product CD = 'A001'
AND (customer M. customer CD = actual TRN. Customer CD)
AND (Product M. Product CD = Actual TRN. Product CD)

-Processing example 3-
Next, processing example 3 by the database search server 200 will be described. Here, a description will be given of a case where the client PC 100 receives “customer code (customer CD =“ 1001 ”) and search for customer name and customer first-half sales number” as a search condition.

  In this processing example, since it is assumed that a search condition specifying a customer code, a customer name, and the number of first-half sales is received from the client PC 100, as shown in FIG. It becomes a CD item, a customer name item, and a first half sales number item, and a usage table becomes a customer master table and a customer aggregation transaction table.

  In response to the search condition from the client PC, the CPU 201 determines a usage table from user-specified items (customer CD, customer name, first half sales number) (step S601). The fact that it is a customer aggregation transaction table is recorded in the RAM 203 as usage table information.

  Subsequently, the CPU 201 determines whether or not reading of all the usage tables included in the usage table information from the RAM 203 has been completed (step S602). At this time, since the usage table information has not yet been read, the process proceeds to step S603.

  Subsequently, the CPU 201 reads one usage table from the RAM 203, refers to the table ID of the usage table from the usage table ID table shown in FIG. 8, and sets it in the table variable [A] (step S603). Assuming that the usage table read here is the customer master table, the table variable [A] is set to “0” in the RAM 203.

  Subsequently, the CPU 201 refers to the table of the table ID “0” set in the table variable [A], that is, the schema ID of the customer master table from the fact definition table, and stores the schema ID in the RAM 203 in the schema ID variable. [B] is set (step S604). Here, in the fact table definition table, the schema ID when the customer master table is the fact table is defined as “null”, so the schema ID variable [B] is set to “null”.

  Subsequently, the CPU 201 determines whether or not the set schema ID variable [B] is “0” (step S605). Here, since the schema ID variable [B] is set to “null”, the process proceeds to step S606.

  Subsequently, the CPU 201 determines whether or not the set schema ID variable [B] is “null” (step S606). Since the schema ID variable [B] is “null”, the process proceeds to step S607.

  Subsequently, the CPU 201 additionally sets a table variable [A] to the usage table definition variable [C]. Now, since nothing is set in the usage table definition variable [C], the usage table definition variable [C] is set to “0” in the RAM 203. Further, the CPU 201 sets a use table definition variable [C], and sets a schema determination definition variable [D] and a schema determination definition variable [E]. That is, in the RAM 203, the table variable [A] “0” is set to the schema determination definition variable [D], and “null” is set to the schema determination definition variable [E] (step S607).

  The process of the CPU 201 returns to step S602, and it is determined again whether or not reading of all the usage tables has been completed. Since the customer totaling transaction table is still left in the RAM 203 as a usage table, the process proceeds to step S603.

  Subsequently, the CPU 201 reads one usage table (customer aggregation transaction table) from the RAM 203, refers to the table ID of the usage table from the usage table ID table shown in FIG. 8, and updates the table variable [A]. (Step S603). Since the usage table read here is the customer aggregation transaction table, the table variable [A] is updated to “3” in the RAM 203.

  Subsequently, the CPU 201 refers to the table of the table ID “3” set in the variable [A], that is, the schema ID of the customer aggregation transaction table from the fact table definition table, and the schema ID variable [B] in the RAM 203. Is updated to the schema ID (step S604). Since the schema ID when the customer aggregation transaction table is the fact table is defined as “1” in the fact table definition table, the schema ID variable [B] is updated to “1”.

  Subsequently, the CPU 201 determines whether or not the set schema ID variable [B] is “0” (step S605). Here, since the schema ID variable [B] is set to “1”, the process proceeds to step S606.

  Subsequently, the CPU 201 determines whether or not the set schema ID variable [B] is “null” (step S606). Since the schema ID variable [B] is set to “1”, the process proceeds to step S608.

  Subsequently, the CPU 201 determines whether the variable [E] has a value greater than the schema ID variable [B] or whether the variable [E] is set to “null” (step S608). Now, since “null” is set in the variable [E], the process proceeds to step S609. Here, in this embodiment, the schema ID value is defined so as to be an easier schema structure from 0 to 9. In response, if the schema ID variable [B] is smaller than the value of the variable [E] that has already been set, the process proceeds to step S609, and the variable [E] is set with the value of the schema ID variable [B]. The schema ID of a small value, that is, a complicated schema designation is left in the variable [E].

  Subsequently, the CPU 201 sets a use table definition variable [C], a schema determination definition variable [D], and a schema determination definition variable [E] (step S609). The current table variable [A] is additionally set in the use table definition variable [C], and is updated to “0, 3”. The schema determination definition variable [D] is updated to the current table variable [A] “3”, and the schema determination definition variable [E] is updated to the schema ID variable [B] “1”.

  Subsequently, the processing of the CPU 201 returns to step S602, and it is determined again whether or not reading of all the usage tables included in the usage table information from the RAM 203 has been completed. Now, since all the usage tables have been read from the RAM 203 by the CPU 201, the processing moves to step S611 in FIG.

  The CPU 201 reads the schema judgment definition variable [D] and the schema judgment definition variable [E] from the RAM 203 (step S611), and the contents set in the schema judgment definition variable [E] are “null”, “0”, and “ It is determined whether it is “1 or more” (step S612). Now, since “1” is set in the schema judgment definition variable [E], the process proceeds to step S617.

  Subsequently, the CPU 201 reads one of the table IDs set in the usage table definition variable [C] from the RAM 203, and sets it in the table variable [G] in the RAM 203 (step S617). Here, it is assumed that the table ID “0” of the customer master table is set in the table variable [G].

  Subsequently, the CPU 201 determines whether or not all table IDs set in the use table definition variable [C] have been read from the RAM 203 (step S618). At this time, since the table ID “3” of the customer aggregation transaction table has not been read yet, the process proceeds to step S619.

  Subsequently, the CPU 201 sets the table ID “3” of the customer aggregation transaction table set in the schema determination definition variable [D] and the table ID “0” of the customer master table set in the table variable [G]. As a key, the schema definition table in FIG. 5A is searched for a record in which “customer aggregation transaction table” is registered in the fact table item and “customer master table” is registered in the dimension table item.

  Subsequently, the CPU 201 determines whether or not the corresponding record has been searched from the schema definition table as a result of the search processing in step S619 (step S620). Since the corresponding record exists in the schema definition table of FIG. 5A, the process proceeds to step S617.

  The CPU 201 again reads one of the table IDs set in the usage table variable [C] from the RAM 203 and sets it in the table variable [G] in the RAM 203 (step S617). Here, the table variable [G] is updated to the table ID “3” of the customer aggregation transaction table.

  Subsequently, the CPU 201 determines whether or not all table IDs set in the use table definition variable [C] have been read from the RAM 203 (step S618). Now, since the reading of all the table IDs has been completed because the table ID of the customer totaling transaction table has been read, the process proceeds to step S621.

  Subsequently, the CPU 201 determines a database schema to be searched based on the contents set in the schema determination definition variable [D] (step S621). Since the table ID “3” of the customer aggregation transaction table is set in the schema determination definition variable [D], the CPU 201 sets the schema ID “1” when the customer aggregation transaction table is a fact table. Refer to the fact table definition table and determine the schema of the database to be searched.

  If it is determined in step S620 that the corresponding record does not exist in the schema definition table, the database structure to be searched is determined as schema 0. In this embodiment, when the usage table is determined from the search condition, the most easily searchable schema that connects the usage tables is examined. However, the schema defined in the schema definition table is not immediately found, and this processing is not performed. If there is a possibility that a long time is required, the database structure of the schema 0 is determined as a search target, and the process immediately moves to the search process to optimize the entire search process.

Next, the CPU 201 generates a search query. Here, since schema 1 is determined as a search target in step S621, the following search query is generated. Then, the CPU 201 performs a data search based on the search query.
<Search query>
SELECT Customer M. Customer Name, Customer Aggregation TRN. Number of Sales in 1H
FROM customer M, customer total TRN
WHERE customer M. customer CD = '1001'
AND (customer M. customer CD = customer aggregation TRN. Customer CD)

In the present embodiment, as described above, the schema of the database to be searched is determined according to the search condition, thereby reducing the time required for generating and executing a search query. If the schema is not determined as shown in the above, the database with a uniform schema will be the target of the search, so the generated search query will be complicated as follows and the search query processing can be optimized. Can not.
<Search query>
SELECT Customer M. Customer name, Customer total TRN. Number of first half sales
FROM Customer M, Customer Aggregation TRN, Actual TRN
WHERE customer M. customer CD = '1001'
AND (customer.customer CD = actual TRN.customer CD)
AND (actual TRN.customer CD = customer summary TRN.customer CD)

  As described above, according to this embodiment, data is obtained from a plurality of different data tables (customer master table, product master table, actual transaction table, customer aggregation transaction table, and product aggregation transaction table) according to the search conditions. A table is selected, and a schema pattern including the selected data table is determined. Therefore, it is not necessary to create a plurality of the same data tables in order to optimize the schema pattern as in the prior art, and it is possible to reduce the amount of storage capacity used.

  In addition, according to the present invention, a search query corresponding to an optimized schema pattern is generated and executed, so that the schema pattern is optimized and, as a result, a schema pattern database including a small number of data tables. Accordingly, the search query is not subject to unnecessary information, and the search query generation process and execution process can be optimized accordingly.

  Another object of the present invention is to supply a storage medium storing software program codes for realizing the functions of the above-described embodiments to a system or apparatus, and the computer (or CPU or MPU) of the system or apparatus stores the storage medium. Needless to say, this can also be achieved by reading and executing the program code stored in.

  In this case, the program code itself read from the storage medium realizes the functions of the above-described embodiments, and the program code itself and the storage medium storing the program code constitute the present invention.

  As a storage medium for supplying the program code, for example, a flexible disk, a hard disk, an optical disk, a magneto-optical disk, a CD-ROM, a CD-R, a magnetic tape, a nonvolatile memory card, a ROM, or the like can be used.

  Further, by executing the program code read by the computer, not only the functions of the above-described embodiments are realized, but also an OS (basic system or operating system) running on the computer based on the instruction of the program code. Needless to say, a case where the functions of the above-described embodiment are realized by performing part or all of the actual processing and the processing is included.

  Further, after the program code read from the storage medium is written in a memory provided in a function expansion board inserted into the computer or a function expansion unit connected to the computer, the function is determined based on the instruction of the program code. It goes without saying that the CPU or the like provided in the expansion board or function expansion unit performs part or all of the actual processing and the functions of the above-described embodiments are realized by the processing.

It is the figure which showed typically the structure of the database search system which concerns on one Embodiment of this invention. It is the figure which showed the hardware constitutions of the database search server. It is the figure which showed the data table which is one structure of the database used as search object. It is the figure which showed the database of three schema structure bodies used as search object. It is the figure which showed the structure of a schema definition table and a fact definition table. It is a flowchart which shows the data search operation | movement by a database search server. It is a flowchart which shows the data search operation | movement by a database search server. It is a figure for demonstrating the usage item and usage table in a search process. It is the figure which showed the structure of the utilization table ID table.

Explanation of symbols

100 client PC
200 Database Search Server 300 DB Server 21 Free Search Application 22 Free Search DB
201 CPU
202 ROM
203 RAM
204 System Bus 205 Input Controller 206 Video Controller 207 Memory Controller 208 Communication I / F Controller 209 Keyboard 210 CRT Display 211 External Memory

Claims (4)

A pair of associations between a fact table having an aggregation target item and a dimension table having an aggregation axis item associated with the fact table is linked as one record, and is a logical structure of the database. Storage means for storing a schema definition table that defines a plurality of schemas corresponding to records;
An input means for inputting data search conditions;
A determination unit for determining a fact table or a dimension table having a search item included in the search condition input by the input unit;
A determination means for determining whether or not a record including all the fact table or dimension table determined by the determination means is in the record of the schema definition table;
When the determination unit determines that a record including all the fact table or dimension table determined by the determination unit is not included in the record of the schema definition table, a schema including all search target items is set as a search target. First query generation means for generating a query according to the search condition input by the input means;
When the determination unit determines that the record including all of the fact table or dimension table determined by the determination unit is in the record of the schema definition table, the input unit uses the schema according to the record as a search target. And a second query generation means for generating a query corresponding to the search condition input in step (b). Table determining means for determining whether or not there is one dimension table determined by the determining means;
If the table determining means determines that there is only one dimension table, a query according to the search condition input by the input means is generated with the data table of the item specified in the dimension table as a search target And a third query generation means for
2. The query according to claim 1, wherein the dimension table is a master table, and the first query generation unit generates a query when the table determination unit determines that there are a plurality of the dimension tables. Database search device. A database search method in a database search device,
The storage means of the database search device associates a pair of associations between a fact table having an aggregation target item and a dimension table having an aggregation axis item associated with the fact table as one record. Storing a schema definition table that defines a plurality of schemas corresponding to the record, which is a logical structure of the database;
An input step in which the input means of the database search device inputs data search conditions;
The determination unit of the database search device determines a fact table or a dimension table having a search item included in the search condition input in the input step;
The determination unit of the database search device determines whether a record including all the fact table or dimension table determined in the determination step is in the record of the schema definition table,
When the first query generation means of the database search device determines that the records including all the fact tables or dimension tables determined in the determination step are not included in the records of the schema definition table in the determination step, all A first query generation step of generating a query according to the search condition input by the input means, using a schema including the search target item as a search target;
When the second query generation unit of the database search device determines that the record including all the fact tables or dimension tables determined in the determination step is in the record of the schema definition table in the determination step, A database search method comprising: a second query generation step of generating a query according to a search condition input in the input step, using a schema according to a record as a search target. A program for causing a computer to execute a database search method in a database search device,
The storage means of the database search device associates a pair of associations between a fact table having an aggregation target item and a dimension table having an aggregation axis item associated with the fact table as one record. Storing a schema definition table that defines a plurality of schemas corresponding to the record, which is a logical structure of the database;
An input step in which the input means of the database search device inputs data search conditions;
A determination step of determining a fact table or a dimension table having a search item included in the search condition input in the input step;
The determination unit of the database search apparatus determines whether or not the record including all the fact table or dimension table determined in the determination step is in the record of the schema definition table,
If the first query generation means of the database search device determines that the records including all the fact tables or dimension tables determined in the determination step are not included in the records of the schema definition table in the determination step, all A first query generation step of generating a query according to the search condition input by the input means, using a schema including the search target item as a search target;
When the second query generation unit of the database search device determines that the record including all the fact tables or dimension tables determined in the determination step is in the record of the schema definition table in the determination step, A program for causing a computer to execute a second query generation step of generating a query according to a search condition input in the input step, using a schema according to a record as a search target.

Images