JP2006163968A - Data retrieval system, method and program - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To efficiently conduct a data retrieval by using a predetermined access path preferentially in the case of a specific retrieval condition, for each user or every user in common. <P>SOLUTION: A system for conducting a data retrieval in a relational database management system is provided with an input means for accepting input for a retrieval condition from a user, an SQL generation means for generating a query language regarding the relational database management system, and a setting file in which a specific retrieval condition and an access path to data corresponding thereto are previously registered. The SQL generation means adds a corresponding access path when the retrieval condition accepted with the input means has been registered in the setting file, and generates a query language regarding the relational database management system. <P>COPYRIGHT: (C)2006,JPO&NCIPI

Description

  The present invention relates to a data search system, method, and program, and more particularly to a technique for achieving high-speed processing using an optimal access path during data search in a relational database management system.

  In data management in an electronic computer system, a relational database management system (hereinafter referred to as “RDBMS” where appropriate) is expressed as a set of a plurality of items (fields) and a set of data is represented by a table called a table. Is widely used. The RDBMS is operated through a standardized query language called SQL (Structured Query Language), and is configured to easily combine and extract data using key information such as ID numbers and names.

  On the other hand, slip data such as sales slips and journal slips managed by the core business system, for example, holds a number of items and is composed of a plurality of tables. When searching for a specific slip from these, the search is generally performed by specifying a combination of items held in the data as a search condition. A conventional example of such a relational database management system is shown in FIG. As shown in this figure, the RDBMS 100 has a data file 101 composed of a plurality of tables and server process means 102 for managing the data file 101, and a slip search program 111 is installed in the client terminal 110. When the user inputs a search condition to the client terminal 110, the slip search program 111 generates an SQL including the search condition and sends it to the RDBMS 100. The server process means 102 has an optimization function for determining an optimal access path for the search request SQL, creates an access path determined to be optimal, searches for target data, and sends it to the client terminal 110.

  By the way, in such a relational database management system, proposals have been made to speed up data retrieval by classifying and managing the entire object into a hierarchical tree structure (for example, Patent Document 1 and Patent Document 2).

JP 2004-46650 A JP-A-6-203078

  For example, in daily operations of sales management using an electronic computer system, it is often necessary to call past transaction slips for inquiries from business partners, periodic slip input / output, confirmation of past transactions, etc. appear. In general, the voucher management database stores a large number of vouchers and ledger data for several years in order to perform past performance management and analysis, and it is not easy to search for a specific voucher from a large amount of data. It may take time. Further, the server process means 102 determines the access path by the optimization function when searching for slip data, but the optimal access path may not always be determined depending on the database index creation method, data variations, and combinations of search keys. . If an application program tries to compensate for a situation in which an access path changes depending on a search condition, it is necessary to develop a program for each of a myriad of search conditions, which is not very realistic.

  The present invention is configured to preferentially use a preset access path in the case of a specific search condition for each user or in common to all users so that data can be efficiently searched. It is an object of the present invention to provide a data search system and method or program in a relational database management system.

  The present invention is a system for performing data search in a relational database management system, comprising a setting file in which a specific search condition and an access path to data corresponding thereto are registered in advance, and the specified search condition is the setting The main feature is that data search is performed using an access path corresponding to the case where the file is registered in the file.

  An embodiment of the present invention includes an input means for receiving an input of a search condition from a user, an SQL generation means for generating a query language for the relational database management system, an access path to a specific search condition and data corresponding thereto. A setting file registered in advance, and the SQL generation unit adds an access path corresponding to a case where the search condition received by the input unit is registered in the setting file, and makes an inquiry to the relational database management system. Generate a language.

  In another embodiment, each of the client terminals includes a plurality of client terminals connected to each other by a network including the input unit and the SQL generation unit, and the setting file is set to be accessible from each terminal. The SQL generation means refers to the setting file when receiving the search condition from the input means, adds an access path corresponding to the search condition registered in the setting file, and makes an inquiry to the relational database management system. Generate a language.

  In the present invention, it is effective to be able to additionally register a specific search condition and an access path corresponding to the specific search condition in the setting file as necessary.

  In the present invention, a specific search condition and an access path corresponding to the specific search condition are registered in the setting file in advance, and an access corresponding to the case where the specified search condition is registered with reference to the setting file at the time of the search. A path is extracted, and data search is executed using this access path. Thereby, the time for the server process means to determine the access path based on the search condition is omitted, and the data search can be performed quickly. In addition, the server process means may not always be optimal when the access path is created. In the present invention, however, the user is preliminarily registered with the optimum access path in the setting file. A quick search is performed with the optimum access path, and no unnecessary delay occurs.

  In the embodiment of the present invention, a setting file specific to each user may be created in each user's terminal, or a plurality of search terminals may share one setting file in a LAN environment. . If a setting file specialized for each user is provided, if the method for specifying search conditions differs for each user, it is possible to perform a search specialized for that user's method without interfering with the specification method of other users. It becomes. On the other hand, if the configuration file is shared, each user can save the trouble of creating the configuration file from 1 and save storage resources in each terminal.

  Further, a more comfortable search environment can be constructed by additionally registering search conditions and access paths in the setting file as necessary.

  The best mode for carrying out the present invention will be described in detail below with reference to the drawings.

  FIG. 1 is a diagram showing an embodiment of a data search system according to the present invention. As shown in FIG. 1, in the first embodiment, a client terminal 10 and a relational database management system (RDBMS) server 20 are connected so as to be capable of bidirectional communication. The client terminal 10 is a personal computer provided with a display screen, input means such as a keyboard and a mouse, a central processing unit, a main storage device, an auxiliary storage device or the like (all not shown) or an information processing device such as a PDA or a mobile phone. The search program 11 and the external setting file 12 are installed in the storage area. The search program 11 receives a search condition input by the user, creates a SQL (Structured Query Language) for data search, and sends it to the RDBMS 20. The external setting file 12 is a file in which search conditions that can be searched by the search program 11 and access paths corresponding to the search conditions are registered in advance, and the contents thereof can be appropriately registered and changed by the user of the client terminal 10. In the present specification and claims, the term “search condition” is a concept including a search keyword and a combination of search keywords.

  The RDBMS server 20 is, for example, a workstation server having a large-capacity storage area, and includes a data file 21 composed of a plurality of tables and server process means 22 for managing the data file 21. In this embodiment, the RDBMS server 20 manages, for example, the slip data of the core business system. As shown in FIG. 1, the data file 21 includes order slip data, order slip data, entry / exit slip data, sales slip data, Purchase slip data is provided. The server process means 22 is a program that runs on the RDBMS server 20, accepts a connection to the database from the client terminal 10, and deals with the database on behalf of the client. The server process means 22 receives a search request SQL from the client search program 11 and performs a search according to an access path request if an access path is specified in the SQL, and is included in the SQL if no access path is specified. The access path is determined based on the search condition and the search is performed.

  FIG. 2 is a diagram showing an image of the external setting file 12. As shown in FIG. 2, first, a section 13 for judging the type of slip is provided so that access path information such as an index can be set for each slip type. A search condition 14 that is sometimes used and an access path information 15 such as an index that is used when searching for slip data are set correspondingly. FIG. 3 shows an example of a user interface of the external setting file 12 displayed on the screen of the client terminal 10. In the list as shown in FIG. 3, the user is configured to be able to register or change a search condition and an access path corresponding to the search condition. For example, when the search condition is “customer + product” with “order receipt slip”, “INDEX (TDA1BA TDA1BA_IDX1” is designated as the access path, and this is set in the external setting file 12 as shown in FIG.

  The operation of the data search system of this embodiment will be described with reference to the flowchart of FIG. When performing the slip search operation, the user activates the search program 11 of the client terminal 10 and inputs search conditions to search for slip data (step S1). Receiving this, the search program 11 first refers to the external setting file 12 (step S2), and confirms whether a file that matches the specified search condition is set (step S3). If a search condition that matches the specified search condition is registered, an access path corresponding to the condition is extracted (step S4), a search request SQL is created by adding this access path, and the server process of the RDBMS server 20 is performed. The means 22 is requested to search for slip data (step S5). The server process means 22 searches the data file 21 for slip data based on the SQL from the search program 11, and if the access path is specified in the SQL at this time, the slip data is searched according to the request for the access path ( Step S7).

  On the other hand, if there is no search program 11 that matches the search conditions specified in the external setting file 12 in step S3 (step S3: No), the search program 11 creates SQL for searching slip data without adding an access path. Then, the created SQL is passed to the server process means 22 of the RDBMS server 20 to make a search request for slip data (step S5). If the access path is not included in the SQL, the server process means 22 performs a search by determining the access path from the given search conditions (steps S6 to S7). The slip data search result searched by the RDBMS server 20 in this way is returned from the server process means 22 to the client-side search program 11 (step S8). The search program 11 displays the search result received from the server process means 22 on the screen.

  If the search process under the search conditions not set in the external setting file 12 takes time, or if the search performance is not satisfactory even if the search processing is already set, the user can use the external to set or change the access path. The setting information in the setting file 12 is updated. In this operation, the list shown in FIG. 3 is called on the screen of the client terminal 10 as described above, and a new record is created or an existing record is updated by direct input. After the information of the external setting file 12 is set in this way, the search performance check operation is performed as shown in FIG.

  As shown in FIG. 5, the performance verification of the set slip search is performed by actually executing a search by specifying a newly set search condition (step S11). If it takes a long time to search and this causes a problem in system operation (step S12), the external setting file 12 is reset again to change the access path at the time of the slip search (step S13), and the same conditions are again set. A search is performed to check whether the performance is improved (step S14). If the performance is not improved even if the access path at the time of the slip search is changed, it should be interpreted as a problem of the processing performance of the RDBMS server 20, and the subsequent search depends on the execution plan determined by the server process means 22. To do.

  In this embodiment, a system including one client terminal 10 and one RDBMS server 20 is disclosed. However, a plurality of client terminals 10 may be connected to the RDBMS server 20 via a network such as an in-house LAN. In this case, each client terminal 10 includes a search program 11 and an external setting file 12, and the search program 11 of each client confirms whether the search condition is registered by referring to the external setting file 12 of the client at the time of the search. To do.

  In the embodiment of FIG. 1, the storage location of the external setting file 12 is not limited to the client terminal 10. For example, the external setting file 12 may be stored on the RDBMS server 20. In this case, when receiving a search condition input from the user, the search program 11 of the client terminal 10 accesses the RDBMS server 20 to refer to the external setting file 12 and creates SQL based on this to search the server process means 22. Make a request. Further, the client area (that is, the search program 11 and the external setting file 12) and the RDBMS area (the data file 21 and the server process means 22) may be configured in the same information processing terminal.

  In this way, the user can set an access path for a specific search condition. For example, the conditions such as the table stored and the amount of data differ depending on the type of data. In the case where the data is not derived, it is possible to perform a search using an appropriate path in consideration of the structure of the database and the data amount of each table. Further, since the configuration is such that the access path is set or changed by newly registering or changing the information of the external setting file, it is possible to search with the optimum access path without the trouble of changing the internal configuration of the search program. In the above embodiment, since an external setting file is provided for each user, even if search conditions (combination of keywords) are different for each user to search for the same data, it is affected by the manner of others. The optimal access path can be derived without any problems.

  FIG. 6 is a diagram showing the configuration of the second embodiment of the present invention. In this figure, the same components as those in FIG. 1 are denoted by the same reference numerals, and redundant description is omitted. As shown in FIG. 6, in this embodiment, a plurality of client terminals 10a, 10b, 10c... Are connected to the RDBMS server 20 via a network. Each client terminal has search programs 11a, 11b,..., But the external setting file 12 is stored as a shared file on one client terminal 10a. The file 12 can be accessed. That is, setting information can be written from each client terminal 10 to the external setting file 12, and an access path can be extracted by referring to the setting information from each client terminal 10.

  FIG. 7 is a flowchart for explaining the operation of the second embodiment. In this figure, steps other than step S22 and step S24 are the same as the steps shown in FIG. 4, so only steps S22 and S24 will be described. When the search program 11a, 11b, 11c,... Of each terminal receives specification of search conditions from the input means of its own terminal, it accesses the client terminal 10a and refers to the external setting file 12, and the specified search conditions are It is confirmed whether it is set (step S22). If the designated search condition is set, the corresponding access path is read from the external setting file 12, SQL to which this is added is created, and a search request is sent to the RDBMS server 20. The server process means 22 of the RDBMS server 20 can handle simultaneous connections from a plurality of client terminals. If an access path is specified in the SQL, a search is performed according to this, and if not specified, an access path is determined from the search condition. The search is performed after determination, and the search result is returned to the requesting client terminal (S7 to S8).

  In this way, by maintaining the external setting information that defines the access path for each search condition pattern specified in the search program in an area that can be shared by multiple client terminals, all users are similarly optimized Search work using the access path becomes possible. In addition, since the external setting file is constructed in such a way that a plurality of users bring the setting information, it is possible to construct setting information with reduced duplication and at the same time. The storage location of the external setting file 12 is not limited to the above example, and may be on other client terminals 10b, 10c,... Or on the RDBMS server 20.

  FIG. 8 is a diagram showing the configuration of the third embodiment of the present invention. In the present embodiment, when the server process means receives the SQL, the external setting file is referred to, and if an access path that meets the search condition is registered, it is used to execute the search. As shown in FIG. 8, a plurality of client terminals 100a, 100b, 100c... Are connected to the RDBMS server 30. Each client terminal 100 is equipped with a search program 110. The search program 110 receives an input of a search condition from a user, creates an SQL including the search condition, and sends it to the RDBMS server 30. A conventional search program 110 can be applied.

  The RDBMS server 30 includes a data file 21, server process means 32, and an external setting file 12. The external setting file 12 is the same as that shown in FIGS. 2 and 3, and setting information can be added and updated from each client terminal 100. Upon receiving the SQL from the search program 110 of the client terminal 100, the server process means 32 refers to the external setting file 12 based on the search conditions included therein, and an access path corresponding to the case where the search conditions are registered And the data file 21 is searched using this access path. If the given search condition is not registered in the external setting file 12, the access path is determined based on the search condition and data search is performed.

  The operation of this embodiment is shown in FIG. The left side shows the processing of the search program 110 of the client terminal 100, and the right side shows the processing of the server process means 32 of the RDBMS server 30. When the user specifies a search condition at the client terminal 100 (step S21), the search program 110 creates an SQL and transmits a search request to the server process means 32 (step S22). The server process means 32 refers to the external setting file 12 based on the search conditions included in the SQL (step S23), and adopts the corresponding access path if the search conditions are set (steps S24 to S25). If is not set, an access path determined to be optimal is created (step S26), and a search for the data file 11 is executed (step S27). When this search process ends, the search result is transmitted to the client terminal 100 (step S28), and the search program 110 of the client terminal 100 displays the search result on the terminal screen (step S29).

  In this way, the client search program 110 transmits the search conditions to the server process means 22 as they are, and the server process means 32 refers to the external setting file 12 and adopts the corresponding access path if the search conditions are set. For example, the search may be performed by determining the access path.

  Although the embodiment of the present invention has been described above, the present invention is not limited to the above-described embodiment but can be realized as various other examples. The search program that runs on the client terminal can be realized in various forms such as a text-oriented tool, a graphical application, and a browser that accesses a database and displays a web page.

  The data search system and method or program according to the present invention provides a data search by preferentially using an access path set by a user for a specific search condition for which an optimum access path cannot always be determined by a server process means of a relational database management system. Can be used suitably in the information processing industry.

It is a figure which shows the structure of the data search system which concerns on embodiment of this invention. It is a figure which shows the image of an external setting file. It is a figure which shows the registration content of an external setting file in a list format. It is a flowchart explaining operation | movement of the data search system which concerns on this invention. It is a flowchart explaining the verification method of the registration content to an external setting file. It is a figure which shows the structure of 2nd Example of the data search system which concerns on this invention. It is a flowchart explaining operation | movement of 2nd Example. It is a figure which shows the structure of 3rd Example of the data search system which concerns on this invention. It is a flowchart explaining operation | movement of 3rd Example. It is a figure which shows the structure of the conventional data search system.

Explanation of symbols

10 Client terminal 11 Search program 12 External setting file 20 RDBMS server 21 Data file 22 Server process means

Claims (7)

  A system for performing data search in a relational database management system, comprising a setting file in which specific search conditions and access paths to data corresponding thereto are registered in advance, and specified search conditions are registered in the setting file. A data search system, wherein data search is performed using an access path corresponding to the case.   A data search system for a relational database management system, which includes input means for receiving input of search conditions from a user, SQL generation means for generating a query language for the relational database management system, and specific search conditions and corresponding to them A setting file in which an access path to the data to be registered is registered in advance, and the SQL generation unit adds an access path corresponding to a case where the search condition received by the input unit is registered in the setting file. A data retrieval system for generating a query language for the relational database management system.   3. The data search system according to claim 2, wherein each of the plurality of client terminals includes a plurality of client terminals connected to each other through the network, and the setting file is set to be accessible from each terminal. The SQL generation unit of each client terminal receives the search condition from the input unit, refers to the setting file, adds an access path corresponding to the case where the search condition is registered in the setting file, and adds the access path. A data retrieval system for generating a query language for a database management system.   One or more client terminals perform a data search for a relational database management system, and each client terminal receives an input of a search condition from a user, and an SQL generation means for generating a query language for the relational database management system The system includes a specific search condition and a setting file in which an access path to data corresponding to the specific search condition is registered in advance, and the SQL generation unit of the client terminal receives the search condition from the input unit And a reference language for the relational database management system by adding an access path corresponding to the search condition registered in the setting file. Data retrieval system system, characterized in that performing data retrieval using the appended access path.   A data search method in a relational database management system, wherein a step of receiving a search condition input by a user, a specific search condition and a setting file in which an access path to data corresponding thereto is registered in advance and the reception is performed A data search method comprising the steps of: extracting an access path corresponding to the search condition registered in the setting file; and executing a data search using the extracted access path.   6. The data search method according to claim 5, further comprising a step of accepting additional registration of a specific search condition and an access path corresponding to the specific search condition in the setting file as necessary.   A search program for performing a data search for a relational database management system at a client terminal, in which a step for receiving an input of a search condition from a user, a setting file in which a specific search condition and an access path to data corresponding thereto are registered in advance And generating a query language for the relational database management system by adding an access path corresponding to the case where the accepted search condition is registered in the setting file, and causing the client terminal to execute A search program characterized by

Images