JP2008299797A - Database system, database retrieval method in database system, and computer program used for database system - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To improve a retrieval processing speed in a client server type database system. <P>SOLUTION: A database system is provided with; a client device 1 having a display part 4 for generating retrieval sentences including a plurality of sequenced item names; and a server device 2 having a database 8 which stores a table constituted of a plurality of records each comprising a plurality of items. The server device 2 is provided with; an item priority order storage part 9 which stores the priority order for the plurality of items in each record of the table; a retrieval item rearrangement part 10 which rearranges, on the basis of the priority order, the order of the plurality of item names included in the retrieval sentences transmitted from the client device 1; and a retrieval part 11 which retrieves, from the database 8, the records to be transmitted to the client device 1 on the basis of the retrieval sentences having the plurality of rearranged item names. <P>COPYRIGHT: (C)2009,JPO&INPIT

Description

  The present invention relates to a database system and a database suitable for use in a technique for improving a search processing speed when a user searches a database of a server device by setting a search condition in the client device in a server client type database system, for example. The present invention relates to a database search method in a system and a computer program used in the database system.

  The server device is equipped with a database and a database management system that is software for inquiring the database. When the client device application issues a search statement to the database management system, the database management system checks the search statement, and if the check is OK, executes the search statement to search the database and perform a search. The result is returned to the client device application. In the database, a plurality of records including fields as a plurality of items are arranged and stored.

  Conventionally, using a general-purpose screen on which search conditions can be set, a user arbitrarily assembles search conditions for any table and performs a search. On this screen, the user inputs items for the search condition by the following methods (1) to (4), creates a search sentence by connecting the conditions in the order of the items entered by the user, and searches the database. Running.

  (1) The user selects a search condition item. For example, one or a plurality of items are selected by describing a SQL statement such as SELECT (item name 1, item name 2...) FROM (table name);

  (2) The user sets conditions such as a magnitude relationship for the item. A condition such as WHERE (item name> = 3) is added to the SQL statement.

  (3) The user selects whether to add another condition. When designating the display order of search results on the screen, a condition such as ORDER BY (item name) is added to the SQL statement.

  (4) The user repeats (1) to (3).

  In a relational database system, a record is stored in a storage device, and information indicating a parent-child relationship (or reference relationship) between a plurality of tables is stored. The parent-child relationship between the two tables is set by storing a key for identifying the reference destination table in the reference source table. The user knows the table configuration by viewing a list of a plurality of tables displayed in the dialog window on the screen. As the client device specifies the table name, the database management system performs the search process and does not take measures to finish the search process faster. The same applies when searching a plurality of tables.

  The database management system can perform a search using an index for efficiently searching for records in response to a search request. That is, the database management system does not know how to combine tables at the time of retrieval, but knows the retrieval mechanism such as the use of an index. The database management system also knows table characteristics such as the number of rows in each table.

  By the way, the speed of the search process varies by rearranging the constituent elements of the search sentence. Depending on how the item names are assembled, a comfortable search performance may not be obtained during the search process. In order to code a search sentence for obtaining a comfortable search performance, the user is required to have high skill. A user performs a search without considering the characteristics of a search mechanism of a database management system such as Oracle (registered trademark) or the characteristics of a database table created by the database management system. Therefore, the search process may be inefficient, such as wasting time for the search.

As a technique for simplifying database search, a database search processing method (see Patent Document 1) that creates a secondary index in which data in one data item is classified and grouped into meaningful units is known. A relational database management device (see Patent Document 2) that manages the link relations of tables in a lump so that necessary information can be easily obtained from the RDB even if the table structure of the relational database is not grasped. There has also been proposed a database search system (see Patent Document 3) that enables a search without a user creating a program for setting conditions for matching a plurality of tables in a database.
JP-A-4-273378 Japanese Patent Laid-Open No. 11-3257 JP 2000-35967 A

  However, the user must set the search conditions while recognizing the table structure of the database and the characteristics of the table, and considering how the search sentence can be assembled to efficiently perform the search. If the item names are combined on the screen without considering the characteristics of the table, the search process does not end in the shortest time. For example, when the client device sends the item name selected from the multiple pull-down menus on the screen to the server device, the database management system will display the search performance sufficiently according to the parent-child relationship between the tables and the number of rows in each table. The search is not always performed in the state in which the search is performed.

  Therefore, in order to perform a search at a comfortable search speed, the user must always be aware of the database table configuration and table characteristics. Therefore, in a large-scale database system, the search can be completed in the shortest time. Is impossible at all.

  The present invention has been made in view of the above problems, and an object of the present invention is to improve the speed of search processing in a client-server database system.

  According to one aspect of the present invention, there is provided a client device having a display unit for generating a search sentence including a plurality of ordered item names, and a table configured by arranging a plurality of records including a plurality of items. And a server device that searches the stored database for a record that matches the condition of the search statement, and transmits the searched record to the client device. The server device stores each record in the table stored in the database. An item priority storage unit for storing priorities among a plurality of items possessed by and a search item for rearranging the order of a plurality of item names included in a search sentence transmitted from the client device based on the priority The client based on a recombination processing unit and a search statement having a plurality of item names rearranged in the search item recombination processing unit A search unit for searching a record to be sent to the location from the database, the database system characterized by comprising a are provided.

  According to another aspect of the present invention, a client device having a display unit for generating a search sentence including a plurality of ordered item names includes a step of generating a search sentence and a plurality of items. A server apparatus having a database for storing a table formed by arranging a plurality of records, records the order of a plurality of item names included in a search sentence transmitted from the client apparatus in this database stored in the storage means. Recombination based on the priority order among a plurality of items possessed by each record of the stored table, and the server device records records that match the conditions of a search statement having a plurality of item names that have been recombined. And the server device includes a step of transmitting the searched record to the client device. Database search and wherein is provided.

  Further, according to another aspect of the present invention, a procedure for enabling access to a database storing a table configured by arranging a plurality of records composed of a plurality of items, and a plurality of records possessed by each record of the table The procedure for storing the priority order between items in the storage means, the procedure for receiving a search sentence including a plurality of ordered item names, and the order of the plurality of item names included in the search sentence are set to this priority order. There is provided a computer program for causing a computer to execute a recombination procedure based on the above and a procedure for searching the database for a record that matches the condition of a search statement having a plurality of recombined item names.

  According to the present invention, it is possible to create an optimal search sentence and perform a search in an optimal search time without making the user aware of the table configuration and the table characteristics by the same screen operation as before. Further, it is possible to eliminate a waste of search time that occurs when the order of items included in a search sentence is inefficient for search.

  Hereinafter, a database system, a database search method, and a computer program according to embodiments of the present invention will be described with reference to the drawings. In the drawings, the same portions are denoted by the same reference numerals, and redundant description is omitted.

  FIG. 1 is a configuration diagram of a database system according to an embodiment of the present invention. This database system is a client server type database search system, and includes a client device 1 that issues a data search command and a server device 2 that manages a database. Are connected by the network 3.

  The client device 1 is a personal computer, and includes a display unit 4 for generating a search sentence including a plurality of ordered items, a display processing unit 5 for generating data displayed on the display unit 4, and input by a user The input processing unit 6 that generates a search request corresponding to the search sentence that has been sent, and the transmission / reception unit 7 that transmits the search request to the server device 2 and receives the search result from the server device 2. By the user's input operation, a plurality of item names for referring to a plurality of fields included in a record stored in the server device 2 are included in the search sentence. The input processing unit 6 orders these item names. For the display unit 4, for example, a display or an interactive window displayed on the display is used. The functions of the display processing unit 5 and the transmission / reception unit 7 are realized by a CPU (Central Processing Unit), LSI, ROM, and RAM. The functions of the input processing unit 6 are realized by input means such as a keyboard and a mouse, and a program for processing data input by the input means.

  The server device 2 is a server computer and has a database 8 that is a relational database. The server device 2 searches the database 8 for a record that matches the search statement condition transmitted from the client device 1, and transmits the retrieved record to the client device 1. This server device 2 is transmitted from the client device 1 and a table information storage unit 9 as an item priority storage unit for storing priorities among a plurality of items of each record of the table stored in the database 8. Retrieval recombination processing unit 10 as a search item recombination processing unit that reorders the order of a plurality of item names included in a search sentence based on the priority order, and a plurality of items rearranged in this search sentence recombination processing unit 10 A search processing unit 11 serving as a search unit that searches the database 8 for data to be transmitted to the client device 1 based on a search sentence having a name, and receives a search request from the client device 1 and sends a search result to the client device 1. A transmission / reception unit 12 for transmission. The table information storage unit 9 is also a storage unit. A large-capacity storage device is used for the database 8. The function of the search processing unit 11 is realized by a database management system.

  FIG. 2 is a diagram illustrating an example of a table stored in the database 8. The database 8 stores a table A in which six records 1 to 6 are arranged, and these records 1 to 6 have at least items 1 to 4, respectively. In other words, the database 8 stores a table A configured by arranging a plurality of, for example, six records including a plurality of items. Item 3 is designated as a unique key. Further, the database 8 stores tables B and C different from the table A as shown in FIGS. 3A and 3B, for example. In the server device 2 according to the present embodiment, the table information storage unit 9 stores priorities among the items of the tables A to C.

  FIG. 4 is a diagram illustrating an example of table information stored in the table information storage unit 9. The table information storage unit 9 stores priorities among items of the tables A to C as table information. For example, for the table A, the priority order is written in the order of item 3, item 2, item 4, and item 1. It can be said that this table information indicates whether or not the search can be performed in the shortest when any one of the items 1 to 4 is placed at the top when the search processing unit 11 makes an inquiry to the database 8. Similarly, priority is set in the order of item 2 and item 1 for table B, and priority is given in the order of item 1, item 3, item 5, item 4, item 2, and item 6 for table C. The ranking is set.

  In the server device 2 according to the present embodiment, the following (1) and (2) are used.

(1) Have the table structure and table characteristics of the database 8.

  The administrator creates table information in consideration of the table configuration of the database 8 and the table characteristics, and is set in advance as a search condition for searching the corresponding table in order to create a search sentence that performs the search in the shortest time. The order of items is written in the storage device.

(2) The search sentence is rearranged when the search process is executed.

  Before executing the search, the server device 2 refers to the table information, rearranges the order of the search condition items input by the user, and automatically generates an optimal search sentence.

  As a result, the user can always execute a search within a search time without waste without being aware of the table configuration and table characteristics of the database 8 by a screen operation similar to the conventional one.

  FIG. 5 is a flowchart for explaining a database search method according to an embodiment of the present invention. The client device 1 generates a search sentence (step A1). The server apparatus 2 rearranges the order of the plurality of item names included in the search sentence transmitted from the client apparatus 1 based on the priority order among the plurality of items stored in the table information storage unit 9 (step A2 ) The server device 2 searches the database 8 for a record that matches the search statement condition having a plurality of item names that have been rearranged (step A3), and transmits the searched record to the client device 1 (step A4). . In order to code an SQL sentence that allows the system to exhibit a comfortable search performance, the user is required to have a high skill. However, according to the database search method according to this embodiment of the present invention, the server device 2 displays a search sentence. Since it is automatically rearranged, the speed of the search process can be improved regardless of the skill of the user and whether the user knows or does not know the mechanism of the table.

  An example of a processing flow in the database system according to the present embodiment having such a configuration will be described.

[1] Data Input Operation In the client device 1, the input processing unit 6 performs an input operation for search conditions. Specifically, the display unit 4 displays a plurality of types of pull-down menus. The display unit 4 displays a pull-down menu for selecting a table name, a pull-down menu for selecting an item name, and a pull-down menu for adding a WHERE clause, an ORDER BY clause, and the like. When the user selects and confirms a desired table name, item name, and additional condition from these pull-down menus, the input processing unit 6 assembles an SQL sentence that is a search sentence. The assembled search condition data is transmitted from the transmission / reception unit 7 to the transmission / reception unit 12 of the server device 2. Here, it is assumed that the input processing unit 6 performs the following setting as a search condition.

Search target table: Table A
Search condition: Item 1 = "ABC"& Item 3 = "d"
[2] Recombination of data search conditions In the server device 2, when the search processing unit 11 receives a search request, the search processing unit 11 inputs data of the search conditions to the search statement recombination processing unit 10.

  FIG. 6 is a flowchart for explaining the processing contents of the search statement reordering processing unit 10. The search sentence reordering processing unit 10 refers to the table information in the table information storage unit 9 and reads the priority order among the search condition items 1 to 4 of the table A (step B1). The search statement reordering processing unit 10 compares the items 1 and 3 included in the search statement from the client device 1 with the table information (step B2), and there is a difference between these items 1 and 3 and the table information. It is determined whether or not there is (step B3). In step B3, if the search text reordering processing unit 10 determines that there is a difference, the YES route is passed. In step B4, the search text reordering processing unit 10 stores the search condition from the search processing unit 11 and the table information storage. Based on the priority order read by the unit 9, the search conditions are rearranged, and the rearranged result data is input to the search processing unit 11. A rearrangement example based on the example of FIG. 2 is shown below. The result of rearrangement in the order of item 3 and item 1 is shown.

Reading item priority order: priority order is item 3, item 2, item 4, item 1 Recombination result of search condition: item 3 = "d"& item 1 = "ABC"
[3] Display of Data Search Result The search processing unit 11 of the server apparatus 2 searches the database 8 by using the rearranged search sentence, and the transmission / reception unit 12 transmits the search result to the client apparatus 1. The transmission / reception unit 7 of the client device 1 passes the search result from the server device 2 to the display processing unit 5. The display processing unit 5 displays the received search result. In step B3, if the search statement reordering processing unit 10 determines that there is no difference, the search processing unit 11 notifies the search processing unit 11 that the reorganization of the item is performed through the NO route, and the search processing unit 11 receives the client device. The database 8 is searched based on the search sentence from 1.

  As described above, the database system and the database search method according to this embodiment have the following advantages (1) to (3) because the search conditions can be automatically rearranged.

(1) The search time is not wasted by being used as a search condition from a high priority item when executing a search.

(2) Since the user does not need to be aware of the table configuration and table characteristics, the operability is improved.

(3) Since the search time is not wasted, the load on the CPU is reduced.

  As described above, according to the database system and the database search method according to this embodiment of the present invention, useless search time can be reduced, operability is improved, and user-friendly search processing can be performed.

  Thus, the search can be executed in the shortest search time by rearranging the order of the search conditions so that the search conditions set by the user and the conditions of the agreement are satisfied.

  Although the server client type has been described in the above embodiment, the present invention can also be realized by a single computer. In this case, the functions of the database 8, the table information storage unit 9, the search sentence rearrangement processing unit 10, and the search processing unit 11 are realized by a program. The server can also be recorded by recording this program on a recording medium such as a CD-ROM, DVD-ROM, flash memory or floppy (registered trademark) disk, and installing the program recorded on the recording medium in a general-purpose computer. It is possible to obtain the same effect as that obtained by the client type database system.

  The computer program according to an embodiment of the present invention includes a procedure for making the database 8 accessible, a procedure for storing priorities among a plurality of items of each record of the tables A to C stored in the database 8, and , A procedure for receiving a search sentence including a plurality of ordered item names, a procedure for rearranging the order of a plurality of item names included in the search sentence based on the priority order, and a plurality of rearranged item names. And a procedure for searching the database 8 for a record that matches the condition of the search statement that the user has. By causing the computer to execute this program, the same effects as those obtained in the above embodiment can be obtained.

  Note that the present invention is not limited to the above-described embodiment as it is, and can be embodied by modifying the constituent elements without departing from the scope of the invention in the implementation stage. In the above embodiment, there is one client device 1, but the database system of the present invention may be configured by connecting a plurality of client devices 1 via the network 3. The contents of the records, the number of records, and the number of items in the tables A to C of the above embodiment can be variously changed.

  In addition, various inventions can be formed by appropriately combining a plurality of constituent elements disclosed in the embodiment. For example, some components may be deleted from all the components shown in the embodiment. Furthermore, constituent elements over different embodiments may be appropriately combined.

It is a block diagram of the database system which concerns on one Embodiment of this invention. It is a figure which shows an example of the table memorize | stored in the database. (A) And (b) is a figure which shows the example of the other table memorize | stored in the database, respectively. It is a figure which shows an example of the table information memorize | stored in the table information storage part. It is a flowchart for demonstrating the database search method which concerns on one Embodiment of this invention. It is a flowchart for demonstrating the processing content of the search sentence rearrangement process part of a server apparatus.

Explanation of symbols

  DESCRIPTION OF SYMBOLS 1 ... Client apparatus, 2 ... Server apparatus, 3 ... Network, 4 ... Display part, 5 ... Display processing part, 6 ... Input processing part, 7, 12 ... Transmission / reception part, 8 ... Database, 9 ... Table information storage part (item) (Priority storage unit, storage means), 10... Search sentence rearrangement processing unit (search item rearrangement processing unit), 11... Search processing unit (search unit).

Claims (3)

A client device having a display unit for generating a search sentence including a plurality of ordered item names;
A server that searches a record that matches a condition of the search statement from a database that stores a table configured by arranging a plurality of records including a plurality of items, and transmits the searched record to the client device; Prepared,
This server device
An item priority storage unit for storing priorities among a plurality of items of each record of the table stored in the database;
A search item reordering processing unit for reordering the order of a plurality of item names included in the search sentence transmitted from the client device based on the priority;
A search unit that searches the database for a record to be transmitted to the client device based on a search statement having a plurality of item names recombined in the search item recombination processing unit;
A database system characterized by comprising: A client device having a display unit for generating a search sentence including a plurality of ordered item names, and generating a search sentence;
A server device having a database for storing a table configured by arranging a plurality of records composed of a plurality of items stores the order of a plurality of item names included in a search sentence transmitted from the client device in a storage unit. Reordering based on the priority among the items of each record of the table stored in this database,
The server device searches the database for a record that matches a search statement condition having a plurality of rearranged item names;
The server device transmitting the retrieved record to the client device;
A database search method characterized by comprising: Providing access to a database storing a table composed of a plurality of records composed of a plurality of items,
A procedure for storing the priority order among a plurality of items of each record of this table in the storage means;
A procedure for receiving a search statement including a plurality of ordered item names;
Reordering the order of multiple item names in this search sentence based on this priority;
A procedure for searching the database for a record that matches the condition of a search statement having a plurality of recombined item names;
A computer program for causing a computer to execute.

Images