KR101747270B1 - Method, server and computer program stored in computer readable medium for synchronizing query result - Google Patents

Abstract

According to an embodiment of the present invention, a method for synchronizing query results between databases performed in a first database server is disclosed. The method comprising: determining to perform synchronization for a materialized view residing on a second database server located remotely on a first database server, the materialized view comprising a master table And the first database and the second database are heterogeneous; Generating a synchronization command to be transmitted to the second database server in response to the synchronization performing determination; And transmitting the generated synchronization command to the second database server to cause the second database server to perform synchronization between the materialized view existing in the second database server and the master table existing in the first database server .

Description

[0001] METHOD, SERVER AND COMPUTER PROGRAM STORED IN COMPUTER READABLE MEDIUM FOR SYNCHRONIZING QUERY RESULT [0002] BACKGROUND OF THE INVENTION [0003]

The present invention relates to a database management system (DBMS), and more particularly to synchronizing query results between heterogeneous databases using a remote materialized view.

Businesses are rapidly expanding with explosive data growth and the emergence of diverse environments and platforms. With the advent of new business environments, more efficient and flexible data services, information processing and data management capabilities are required. In response to these changes, research is continuing on databases to solve the problems of high performance, high availability, and scalability that are the foundation of corporate business implementation.

In a database management system (DBMS), data can be stored in a data store. In a relational database management system (RDBMS), this data store can be referred to as a table. Such a table may include one or more rows and each of the one or more rows may include one or more columns.

If the database contains a large amount of data, it may take a relatively long time to perform a query to retrieve the data that the user is interested in. If the database takes a long time to respond to a query, it can adversely affect the performance of the database.

In such situations it is desirable to limit direct access to columns or tables that are accessed frequently and by a large number of users. This problem can be solved when performing indirect access to the corresponding column or table through a "view " rather than directly accessing the corresponding column or table. This "view" may refer to a virtual or logical table that does not physically exist in the same form as a master table derived from one or more master tables. That is, when you execute a view, the result set of the defined query is created in memory, and the result set disappears when the session ends.

A materialized view can refer to a table that can physically store the results of a view query. This materialized view can fetch the results directly from a table in which the query results are directly stored, without any extra processing of the query, to speed up the execution of the query. That is, an instantiation view can facilitate continuous updating at low cost, because the result set of the query is stored as a table in the tablespace. Such an instantiation view can also be used for caching complex queries or for replicating a particular table to another database.

When the data in the master table referenced by the materialized view changes, the data that has changed in the master table must be reflected in this materialized view (that is, the materialized view table or the container table) in order to maintain consistency in the query result values .

Therefore, various studies related to synchronization (i. E., Refresh) for such materialized views are continuing.

The present invention is for implementing query result synchronization between heterogeneous databases in an efficient manner.

The present invention is intended to implement a fast refresh for an instantiation view between heterogeneous databases that are incompatible with each other.

According to an embodiment of the present invention, a method for synchronizing query results between databases performed in a first database server is disclosed. The method comprising: determining to perform synchronization for a materialized view residing on a second database server located remotely on a first database server, the materialized view comprising a master table Wherein the first database server and the second database server are heterogeneous; Generating a synchronization command to be transmitted to the second database server in response to the synchronization performing determination; And transmitting the generated synchronization command to the second database server to cause the second database server to perform synchronization between the materialized view existing in the second database server and the master table existing in the first database server .

According to an embodiment of the present invention, a method for synchronizing query results between databases performed in a second database server is disclosed. The method comprising the steps of: receiving a synchronization command from a first database server located remotely of the second database server, wherein the master database exists in the first database server and the master database is referred to the second database server Wherein the first database server and the second database server are heterogeneous; And performing synchronization between the materialized view residing in the second database server and the master table existing in the first database server in response to the received synchronization command.

According to an embodiment of the present invention, a first database server for synchronizing query results between databases is disclosed. Wherein the server is configured to: determine to perform synchronization for an instantiation view residing on a second database server located remotely on the first database server, the instantiation view module comprising: Wherein the first database server and the second database server are heterogeneous; A synchronization command generation module configured to generate a synchronization command to be transmitted to the second database server in response to the synchronization performing determination; And transmitting the generated synchronization command to the second database server to cause the second database server to perform synchronization between the materialized view existing in the second database server and the master table existing in the first database server And a communication module configured to allow the communication module to communicate with the communication module.

According to one embodiment of the present invention, a second database server for synchronizing query results between databases is disclosed. The server comprising: a communication module configured to receive a synchronization command from a first database server located remotely of the second database server, the communication module having a master table in the first database server, Wherein the first database server and the second database server are heterogeneous; And a synchronization performing module configured to perform synchronization between the materialized view existing in the second database server and the master table existing in the first database server in response to the received synchronization command.

According to one embodiment of the invention, a computer program stored on a computer readable medium comprising encoded instructions is provided. The computer program, when executed by one or more processors of a computer system, causes the one or more processors to perform the following operations: the first database server Wherein the realization view is an instantiation view that references a master table present in the first database server, and wherein the first database server and the second database server are different from each other -; Generating a synchronization command to be transmitted to the second database server in response to the synchronization performing determination; And transmitting the generated synchronization command to the second database server to cause the second database server to perform synchronization between the materialized view existing in the second database server and the master table existing in the first database server And the like.

According to one embodiment of the invention, a computer program stored on a computer readable medium comprising encoded instructions is provided. The computer program causing the one or more processors to perform the following operations when executed by one or more processors of a computer system, the operations comprising: receiving from a first database server remote from the second database server Wherein the first database server has a master table, the second database server has an instantiation view that refers to the master table, and the first database server and the second database server Are heterogeneous; And performing synchronization between the materialized view residing in the second database server and the master table existing in the first database server in response to the received synchronization command.

According to one embodiment of the present invention, query result synchronization between heterogeneous databases can be implemented in an efficient manner.

According to one embodiment of the present invention, a fast refresh for an instantiation view between heterogeneous databases incompatible with each other can be implemented.

Various aspects are now described with reference to the drawings, wherein like reference numerals are used to refer to like elements throughout. In the following examples, for purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of one or more aspects. However, it will be apparent that such aspect (s) may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form in order to facilitate describing one or more aspects.
Figure 1 shows a schematic diagram for a database system according to an embodiment of the invention.
2 shows a schematic diagram for a first database server according to an embodiment of the present invention.
3 shows a schematic diagram for a second database server according to an embodiment of the present invention.
4 illustrates a flowchart for synchronizing query results between databases performed in a first database server according to an embodiment of the present invention.
5 illustrates a flowchart for synchronizing query results between databases performed in a second database server according to an embodiment of the present invention.
6 illustrates a schematic diagram of a first database server and a second database server for synchronizing query results in accordance with one embodiment of the present invention.
Figure 7 illustrates a simplified schematic diagram of an exemplary computing environment in which embodiments of the present invention may be implemented.

Various embodiments and / or aspects are now described with reference to the drawings. In the following description, for purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of one or more aspects. However, it will also be appreciated by those of ordinary skill in the art that such aspect (s) may be practiced without these specific details. The following description and the annexed drawings set forth in detail certain illustrative aspects of one or more aspects. It is to be understood, however, that such aspects are illustrative and that some of the various ways of practicing various aspects of the principles of various aspects may be utilized, and that the description set forth is intended to include all such aspects and their equivalents.

In addition, various aspects and features will be presented by a system that may include multiple devices, components and / or modules, and so forth. It should be understood that the various systems may include additional devices, components and / or modules, etc., and / or may not include all of the devices, components, modules, etc. discussed in connection with the drawings Must be understood and understood.

As used herein, the terms "an embodiment," "an embodiment," " an embodiment, "" an embodiment ", etc. are intended to indicate that any aspect or design described is better or worse than other aspects or designs. . As used herein, the terms 'component,' 'module,' 'system,' 'interface,' and the like generally refer to a computer-related entity and include, for example, hardware, It can mean software.

In addition, the term "or" is intended to mean " exclusive or " That is, it is intended to mean one of the natural inclusive substitutions "X uses A or B ", unless otherwise specified or unclear in context. That is, X uses A; X uses B; Or when X uses both A and B, "X uses A or B" can be applied to either of these cases. It should also be understood that the term "and / or" as used herein refers to and includes all possible combinations of one or more of the listed related items.

It is also to be understood that the term " comprises "and / or" comprising " means that the feature and / or component is present, but does not exclude the presence or addition of one or more other features, components and / It should be understood that it does not. Also, unless the context clearly dictates otherwise or to the contrary, the singular forms in this specification and claims should generally be construed to mean "one or more. &Quot;

The computer readable media herein may include any type of storage medium in which programs and data are stored so that they can be read by a computer system. According to one aspect of the present invention, such medium may be a ROM (Read Only Memory), a RAM (Random Access Memory), a CD (Compact Disk) -ROM, a DVD Storage devices, and the like. Additionally, such media may be distributed over networked systems and may store computer readable codes and / or instructions in a distributed manner.

Before describing the embodiments of the present invention in detail, it is to be understood that the present invention is not limited to the above-described embodiments, but may be modified and changed without departing from the scope and spirit of the invention. It is also to be understood that the terminology or words used in the present specification and claims should be interpreted with reference to the meaning of the inventive concept of the present invention based on the principle that the inventor can define the concept of appropriate terms to describe his invention in the best way It should be interpreted as a concept.

1 illustrates a schematic diagram of a database system 100 in accordance with one embodiment of the present invention.

As shown in FIG. 1, the database system 100 may include a client 110, a first database server 120, and a second database server 130.

As shown in FIG. 1, client 110 may refer to node (s) in a database system having a mechanism for communicating over a network. For example, the client 110 may include a PC, a laptop computer, a workstation, a terminal, and / or any electronic device having network connectivity. In addition, the client 110 may include any server implemented by at least one of an agent, an application programming interface (API), and a plug-in. For example, the client 110 in FIG. 1 may be associated with a user using the second database server 130. In this example, the client 110 can confirm the materialized view referring to the table in the first database server 120 through the second database server 130.

Database servers 120 and 130 may include any type of computer system or computer device, such as, for example, a microprocessor, a mainframe computer, a digital single processor, a portable device and a device controller. Each of these database servers 120 and 130 may include a Database Management System (DBMS) 120a and 130a and persistent storage 120b and 130b. The first database server 120 and the second database server 130 may denote heterogeneous database servers located remotely from each other. In addition, although FIG. 1 shows two database servers, it will be apparent to those skilled in the art that many more database servers may be included within the scope of the present invention.

Although not shown in FIG. 1, the database servers 120 and 130 may include one or more memories containing a buffer cache. Also, although not shown in FIG. 1, database servers 120 and 130 may include one or more processors. Thus, DBMSs 120a and 120b may be operated by the processor on the memory.

Here, the memory is a main storage device directly accessed by the processor, such as a random access memory (RAM) such as a dynamic random access memory (DRAM), a static random access memory (SRAM) Volatile storage devices that are erased instantaneously, but are not limited to these. Such memory may be operated by the processor. The memory may temporarily store a data table containing data values. The data table may include a data value, and in an embodiment of the present invention, the data value of the data table may be written to the persistent storage medium from memory. In a further aspect, the memory includes a buffer cache, wherein data may be stored in a data block of the buffer cache. The data may be written to a persistent storage medium by a background process.

The permanent storage media 120b and 130b may be, for example, magnetic disks, optical disks and magneto-optical storage devices as well as storage devices based on flash memory and / or battery- Quot; refers to a non-volatile storage medium that can continuously store any data. These persistent storage media 120b and 130b may communicate with the processors and memory of the database servers 120 and 130 via various communication means. In a further embodiment, these persistent storage media 120b and 130b may be external to the database servers 120 and 130 and may be capable of communicating with the database servers 120 and 130. [

The DBMSs 120a and 130a are programs for allowing the database servers 120 and 130 to perform operations such as retrieving, inserting, modifying, and / or deleting necessary data, 130 in a memory.

The client 110 and the database servers 120 and 130 or the database servers 120 and 130 may communicate with each other via a network (not shown). The network according to an exemplary embodiment of the present invention may be a Public Switched Telephone Network (PSTN), a Digital Subscriber Line (xDSL), a Rate Adaptive DSL (RADSL), a Multi Rate DSL (MDSL) ), Universal Asymmetric DSL (UADSL), High Bit Rate DSL (HDSL), and Local Area Network (LAN).

In addition, the network presented in this specification can be applied to various wireless communication systems such as Code Division Multi Access (CDMA), Time Division Multi Access (TDMA), Frequency Division Multiple Access (FDMA), Orthogonal Frequency Division Multiple Access (OFDMA), Single Carrier- ), And other systems. In addition, the network herein may include a database link (dblink) such that the first database server 120 and the second database server 130 may communicate with each other via this database link to receive data from another database server You can import the data. In one example, the database link may include a database link from the first database server 120 to the second database server 130. The techniques described herein may be used in other networks as well as in the networks mentioned above.

As shown in FIG. 1, the first database server 120 may be located remotely from the second database server 130. Also, the first database server 120 and the second database server may refer to a heterogeneous database server that is incompatible with each other. The database server (e.g., the first database server 120) of one of these database servers includes a master table (i.e., a table included in the FROM clause used in the query) The database server 130) includes an instantiation view (a table in which the query results are stored) for the master table. The materialized view here can be used interchangeably with the materialized view table, Mview, or container table.

The query result synchronization (refresh) in this specification may mean reflecting the result of the materialized view query in the materialized view table. Such a reflection method includes a complete refresh method, which is a method of clearing the materialized view table and inserting the entire result of the query, and a fast refresh method, which is a method of changing only the change (diff) of the master table . In the case of the full refresh method, a high-speed refresh method is generally preferred because a large amount of processing time is required, for example, when a large amount of data is processed as compared with the high-speed refresh method.

As shown in FIG. 1, since the first and second database servers are different from each other and located remotely, there is a difficulty in implementing synchronization (i.e., refresh) of query results. That is, in the high-speed refresh method, the change in the table included in the FROM clause used in the query (that is, the master table) is reflected in the table (i.e., the materialized view) The meta data contained in the stored log tables must be able to be interpreted by heterogeneous servers located remotely. However, it is practically impossible to share coding rules, authorization rules, communication rules, encryption rules, etc. between database servers independently manufactured by different manufacturers. Therefore, instead of high-speed refreshing of the realization view among the above-described different types of database servers, the entire data of the realization view table is deleted and the query related to the realization view is performed again to insert the entire result data of the query into the realization view table Method (i.e., a complete refresh method) may be possible. However, this method is not only time-consuming to process large amounts of data, but also adversely affects the performance of the database server.

2 illustrates a schematic diagram of a first database server 120 in accordance with one embodiment of the present invention.

2, the first database server 120 may include a synchronization determination module 201, a synchronization command generation module 203, a communication module 205, and a storage module 207. Each module in the first database server 120 is capable of communicating with each other. Also, for example, the components of the above-described first database server 120 may be included in the DBMS 120a. In addition, the components of the first database server 120 described above are exemplary, and additional components other than the above-described components may also be included in the first database server 120. [

The synchronization determination module 201 may determine to perform synchronization for the materialized view residing on a second database server located remotely from the first database server. For example, the synchronization determination module 201 may determine whether a synchronization request from the client 110, a synchronization request from the second database server 130, a predetermined synchronization period, Commit occurrence) based on at least one of the following: For example, the synchronization determination module 201 may check a set of log tables of the first database server 120 to determine whether there has been a data change in the master table. The synchronization determination module 201 may decide to perform synchronization if there is a data change. In addition, the synchronization determination module 201 may determine not to perform synchronization if it is determined that there is no data change in the master table.

The synchronization command generation module 203 may generate a synchronization command to be transmitted to the second database server 139 in response to the synchronization execution decision. For example, the synchronization command generation module 203 generates connection information for accessing the materialized view existing in the second database server 130 from the mview object existing in the first database server 120 It is possible to acquire the metadata including the metadata. This materialized view object may reference the materialized view of the second database server 130. The synchronization command generation module 203 may generate a synchronization command to be transmitted to the second database server 130 based on the obtained metadata. The metadata herein may include, for example, query information for an instantiation view, database link information for communication between the first and second databases, and / or name information of the instantiation view table. In one embodiment of the present invention, the synchronization command generated by the synchronization command generation module 203 may include log information in the first database table and log information in the second log table set associated with the master table, To insert into the second materialized view log table. Additionally, the synchronization command may include information for a procedure call to the second database server 130.

The communication module 205 may provide a communication function with the second database server 130 or the client 110. For example, the communication module 205 may send the generated synchronization command to the second database server 130. The communication module 205 may also communicate with the second database server 130 or the client 110 using any of the networks and / or database links described above. In addition, the communication module 205 can receive data from a client using the first database server 120, inquire, index build, inquiry request, and the like. In addition, the communication module 205 may transmit the result information on the data storage, the inquiry, the index build, and the inquiry request. In addition, the communication module 205 may forward the synchronization command to the second database server 130 by calling the procedure to the second database server 130.

The storage module 207 may include an instantiation view object, a master table, and a set of log tables. The storage module 207 may store all data stored in association with the task execution of the first database server 120. The storage module 207 may be included in the DBMS 120a and / or the persistent storage medium 120b. In addition, the storage module 207 may generate a master table, materialized view objects, a first materialized view log table, a DD_SLOG table, a DD_MLOG table, and the like on the first database server 120. In another example, the generation of such tables may be performed by a separate component, such as a control module (not shown). In addition, the storage module 207 may process and manage requests associated with storage (including updates) of data. Such a storage module 207 may decide to store data, index tables, and the like. The storage module 207 may also determine storage locations for data and / or index tables. For example, the storage module 207 may determine the storage location on the data table for the data. In another example, the storage module 207 may determine the storage location on the persistent storage medium 130a for the data.

3 illustrates a schematic diagram of a second database server 130 in accordance with one embodiment of the present invention.

As shown in FIG. 3, the second database server 130 may include a synchronization performing module 301, a communication module 303, and a storage module 305. Each of the modules in the second database server 130 is capable of communicating with each other. Also, for example, the components of the second database server 130 described above may be included in the DBMS 130a. In addition, the components of the second database server 130 described above are exemplary, and additional components other than the above-described components may also be included in the second database server 130.

The synchronization performing module 301 may perform synchronization (fast refresh) between the materialization view and the master table in response to the synchronization command received from the first database server 120. [ For example, the synchronization performing module 301 may store the log information in the log table set that is present in the first database server 120 and associated with the master table in the second materialized view log table existing in the second database server 130 Can be inserted. In addition, the synchronization executing module 301 may perform synchronization by performing a join operation between the second materialized view log table including the inserted log information and the materialized view table. In addition, the synchronization performing module 301 may generate a second materialized view log table and an materialized view table on the second database server 130. In another example, generation of these tables may be performed by a separate component, such as storage module 305 and / or control module (not shown).

The communication module 303 can provide a communication function with the first database server 120 or the client 110. [ For example, the communication module 303 may receive a synchronization command from the first database server 120. The communication module 303 may also communicate with the first database server 120 or the client 110 using any of the networks and / or database links described above. In addition, the communication module 303 can receive data from a client using the second database server 130, inquiry, index build, and inquiry request. The communication module 303 may also communicate the result information for data storage, lookup and index build, query request, and synchronization performance. In addition, the communication module 303 may receive a procedure call from the first database server 120.

The storage module 305 may include an materialized view table and a second materialized view log table. The storage module 305 may store all data stored in association with the task execution of the second database server 130. [ The storage module 305 may be included in the DBMS 130a and / or the persistent storage medium 130b. In addition, the storage module 305 may process and manage requests associated with storage (including updates) of data. Such a storage module 305 may decide to store data, index tables, and the like. The storage module 305 may also determine storage locations for data and / or index tables. For example, the storage module 305 may determine a storage location on the data table for the data. As another example, the storage module 305 may determine the storage location on the persistent storage medium 130b for the data.

FIG. 4 illustrates a flowchart for synchronizing query results between databases performed in the first database server 120 according to an embodiment of the present invention.

The order of the flowchart shown in FIG. 4 may be variable depending on the implementation, and some of the sequences may be added or some sequences may be omitted.

As shown in FIG. 4, the first database server 120 may determine to perform synchronization for the materialized views residing in the second database server 130 located remotely from the first database server 120 401). In one embodiment of the present invention, the first database server 120 may receive a synchronization request from the client 110, a synchronization request from the second database server 130, a preset synchronization cycle, Based on at least one of the following conditions: < RTI ID = 0.0 > < / RTI > Also, the first database server 120 may check the log table set of the first database server 120 to determine whether there has been data change in the master table. For example, the first database server 120 may determine whether there has been a data change in the master table since the latest synchronization point, taking into account the time information for the previous (latest) synchronization point in time. The first database server 120 may decide to perform synchronization if there is a data change. In addition, the first database server 120 may determine not to perform synchronization if it is determined that no data change in the master table is present. If it is determined to perform the synchronization, the first database server 120 stores the latest time (i.e., sysdata) of the youngest last refresh time of each materialized view included in the DD_MLOG table in the log table set, . In a further embodiment, the aforementioned determination of whether or not to perform synchronization at the first database server 120 may be performed after calling the procedure to the second database server 130.

In one embodiment of the present invention, in an embodiment of the present invention, the first database server 120 refers to the materialized view object residing in the first database server 120 and includes connection information for access to the materialized view (403), and the first database server 120 may generate a synchronization command to be transmitted to the second database server 130 (405), in response to the determination to perform synchronization. The first database server 120 may generate the materialized view object indicating the materialized view existing in the second database server 130. An instantiated view object in the present invention may, for example, comprise a separate table that stores metadata associated with the instantiated view. Such an instantiation view object may include metadata such as query information for the instantiation view, database link information for communication between the first and second database servers, and / or table name information of the instantiation view. Because the materialized view object, which is typically located at the location where the materialized view is located (e.g., the second database server 130), is located on the first database server 120, the first database server 120, having the master table, Can control the second database server 130 having the materialized view, so that high-speed refresh can be implemented among different kinds of database servers. That is, the second database server 130 used by the client 110 who wants to view the materialized view does not generate the synchronization command but the first database server 120 having the master table actually referred to by the materialized view, Can create a synchronization command using the materialized view object that it has. In other words, by placing the materialized view object on the first database server 120, by referring to the set of log tables (e.g., the DD_SLOG table and / or the DD_MLOG table) located in the first database server 120, Determination of whether or not to perform synchronization and / or creation of a synchronization performing instruction may be performed on the first database server 120. The logic generation subject of the high-speed refresh may be the first database server 120 and the actual storage of the materialization view may be the second database server 130. [ Accordingly, the second database server 130 can not resolve the log table set of the first database server 120, which is different from the first database server 120, so that high-speed refresh among different kinds of databases can not be performed.

 For example, this synchronization command may include a procedure call to the second database server 130. The procedure herein can be stored in the second database server 130 and can interpret and perform synchronization commands (e.g., SQL statements) issued from the first database server 120. The synchronization command may also include connection information for access to the materialized view. The synchronization command may also include a database manipulation language (DML) for controlling the second database server 130 (e.g., INSERT INTO CONT_TBL @ DBLINK). That is, the synchronization command may be used to synchronize the log information in the set of log tables existing in the first database server 120 and associated with the master table to a second materialized view log table or materialized view table , A container table).

Then, the first database server 120 may transmit the generated synchronization command to the second database server 130, thereby allowing the second database server 130 to perform the high-speed refresh (407).

In addition, when synchronization is completed, the first database server 120 changes the last synchronization point in the DD_SLOG table to the current point of time, and updates the youngest last refresh time and the oldest last value in the DD_MLOG table refresh time information, and remove unnecessary change data from the first materialized view log table. For example, if a completion message from the second database server 130 is received or if the first database server 120 sends a synchronization command to the second database server 130, it may be determined that the synchronization has been completed .

5 illustrates a flowchart for synchronizing query results between databases performed in the second database server 130 according to an embodiment of the present invention.

The order of the flowcharts shown in FIG. 5 may vary depending on the implementation, and some of the sequences may be added or some of the sequences may be omitted.

5, the second database server 130 may receive a synchronization command from the first database server 120 located remotely from the second database server 130 (501).

The second database server 130 may generate a temporary table having the same or similar schema as the first materialized view log table of the first database server 120 (i.e., the second materialized view log table).

Then, the second database server 130 stores the log information in the log table set existing in the first database server 120 and associated with the master table in the second materialized view log table existing in the second database server 130 (503). Prior to this insertion step, the second database server 130 may create a second materialized view log table on the second database server 130. For example, this second materialized view log table can mean a temporary table to be joined with the materialized view table.

Then, the second database server 130 may perform a synchronization operation by performing a join operation between the second materialized view log table and the materialized view table including the inserted log information (operation 505).

In one embodiment of the present invention, the second database server 130 may perform the fast repress using the DML received from the first database server 120. That is, a case may be considered in which the materialized view table existing in the second database server 130 is moved to the first database server 120 and then the set of the log table and the materialized view table in the first database server 120 are joined have. However, since the capacity of the materialized view table is very large as compared with the log related table, the log information in the log table set (for example, the DD_MLOG table and / or the DD_SLOG table) existing in the first database server 120, It is preferable to perform the joining operation with the materialized view table in the second database server 130. [

That is, the general processes for realizing the high-speed refresh include: (1) an actualized view log setup process (mview log setup process) for determining whether refresh of the materialized view table is necessary because there is data to be changed from the master table; (Eg, DELETE, INSERT, or UPDATE) and an instantiation view query type (eg, one table, join, and aggregation) (3) update the DD_SLOG refresh point to the current point (sysdate), update the DD_MLOG minimum value (oldest value), and store the changed contents of the master table The materialized view that removes data that is no longer needed in the first materialized view log table. The mview log wrapup process It may contain.

Therefore, according to the above-described embodiments of the present invention, the second materialized view log table and the materialized view table including the log information for synchronization of the materialized views exist in the same location (for example, in the second database server 130) The materialized view log setup process and the materialized view log wrapping process can be implemented in the same manner as the general local high-speed refresh described above. In addition, the materialized view fast refresh execution process issues a DML command to the second database server 130 having the materialized view table in the first database server 120 having the master table, and the DML command is issued to the second database server 130 Since the refresh operation is performed, the synchronization of the realization view can be implemented while solving the compatibility problem between the heterogeneous database servers remote from each other.

Figure 6 shows a schematic diagram of a first database server 120 and a second database server 130 for synchronizing query results in accordance with an embodiment of the present invention.

6, the first database server 120 includes a master table 601, which is an original table referred to by the materialized view table, an materialized view object 602, which points to the materialized view table, A DD_SLOG table 605 for storing information for identifying the materialized view referring to the master table, and information about the log of the materialized view referring to the master table And a DD_MLOG table 606 for storing the DD_MLOG table. The components of this first database server 120 may be located in the memory and / or persistent storage medium of the first database server 120. Here, the master table 601 may be referred to by a plurality of materialized view tables.

The first materialized view log table 604, the DD_SLOG table 605, and the DD_MLOG table 606 may be referred to herein as the log table set 603. The DD (data dictionary) _SLOG table may store at least one of the information about the materialized view referring to the master table and the information about the last synchronization point with respect to the materialized view. In addition, the DD_MLOG table may store at least one of a youngest last refresh time and an oldest last refresh time information for a last synchronization point in an instantiation view. Since the materialized view object 602 exists in the first database server 120, the first database server 120 in which the master table exists can be the subject of management and control of the high-speed refresh, and the first database server 120 To the second database server 130 may be facilitated.

6, the second database server 130 is associated with the materialized view table 609 and the materialized view table 609 referencing the master table to update (i.e., refresh) the materialized view table 609, And a second materialized view log table 608 that is used to create a second materialized view. The components of this second database server 120 may be located in the memory and / or persistent storage medium of the second database server 130. In addition, log information from the DD_SLOG table 605, the DD_MLOG table 606, and / or the first materialized view log table 604 (for example, Information and time stamp information may be inserted into the second materialized view log table 608. Accordingly, by the join operation between the second materialized view log table 608 and the materialized view table 609, A procedure capable of executing an SQL statement issued by the first database server 120 may be stored in the second database server 130. Accordingly, the realization view The execution subject of the high-speed refresh of the table may be the second database server 130. [

Figure 7 illustrates a simplified schematic diagram of an exemplary computing environment in which embodiments of the present invention may be implemented.

Although the present invention has been described above generally in terms of computer-executable instructions that can be executed on one or more computers, those skilled in the art will appreciate that the present invention may be implemented in combination with other program modules and / will be.

Generally, program modules include routines, programs, components, data structures, etc. that perform particular tasks or implement particular abstract data types. It will also be appreciated by those skilled in the art that the methods of the present invention may be practiced with other computer systems, such as single-processor or multiprocessor computer systems, minicomputers, mainframe computers, as well as personal computers, handheld computing devices, microprocessor-based or programmable consumer electronics, And may operate in conjunction with one or more associated devices).

The described embodiments of the invention may also be practiced in distributed computing environments where certain tasks are performed by remote processing devices connected through a communications network. In a distributed computing environment, program modules may be located in both local and remote memory storage devices.

Computers typically include a variety of computer readable media. Any medium accessible by a computer may be a computer-readable medium, which may include volatile and non-volatile media, transitory and non-transitory media, removable and non-removable media, Removable media. By way of example, and not limitation, computer readable media may comprise computer readable storage media and computer readable communications media. Computer-readable storage media includes both volatile and nonvolatile media, both temporary and non-volatile media, both removable and non-removable, implemented in any method or technology for storage of information such as computer readable instructions, data structures, program modules or other data Media. The computer-readable storage medium may be any form of storage device such as RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital video disk (DVD) or other optical disk storage, magnetic cassette, magnetic tape, Apparatus, or any other medium that can be used to store the desired information that can be accessed by a computer.

Computer readable communication media typically embodies computer readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism, It includes all information delivery media. The term modulated data signal refers to a signal that has one or more of its characteristics set or changed to encode information in the signal. By way of example, and not limitation, communication media includes wired media such as a wired network or direct-wired connection, and wireless media such as acoustic, RF, infrared, or other wireless media. Combinations of any of the above-mentioned media are also intended to be included within the scope of computer-readable communication media.

There is shown an exemplary environment 1100 that implements various aspects of the present invention including a computer 1102 and includes a processing unit 1104, a system memory 1106 and a system bus 1108 do. The system bus 1108 couples system components, including but not limited to, system memory 1106 to the processing unit 1104. The processing unit 1104 may be any of a variety of commercially available processors. Dual processors and other multiprocessor architectures may also be used as the processing unit 1104.

The system bus 1108 may be any of several types of bus structures that may additionally be interconnected to a local bus using any of the memory bus, peripheral bus, and various commercial bus architectures. The system memory 1106 includes read only memory (ROM) 1110 and random access memory (RAM) The basic input / output system (BIOS) is stored in a non-volatile memory 1110, such as a ROM, EPROM, EEPROM or the like, which is a basic (non-volatile) memory device that aids in transferring information between components within the computer 1102 Routine. The RAM 1112 may also include a high speed RAM such as static RAM for caching data.

The computer 1102 may also be an internal hard disk drive (HDD) 1114 (e.g., EIDE, SATA) - this internal hard disk drive 1114 may also be configured for external use within a suitable chassis , A magnetic floppy disk drive (FDD) 1116 (e.g., for reading from or writing to a removable diskette 1118), and an optical disk drive 1120 (e.g., a CD-ROM For reading disc 1122 or reading from or writing to other high capacity optical media such as DVD). The hard disk drive 1114, magnetic disk drive 1116 and optical disk drive 1120 are connected to the system bus 1108 by a hard disk drive interface 1124, a magnetic disk drive interface 1126 and an optical drive interface 1128, respectively. . The interface 1124 for external drive implementation includes at least one or both of USB (Universal Serial Bus) and IEEE 1394 interface technologies.

These drives and their associated computer-readable media provide non-volatile storage of data, data structures, computer-executable instructions, and the like. In the case of computer 1102, the drives and media correspond to storing any data in a suitable digital format. While the above description of computer readable media refers to HDDs, removable magnetic disks, and removable optical media such as CDs or DVDs, those skilled in the art will appreciate that other types of storage devices, such as a zip drive, magnetic cassette, flash memory card, Or the like may also be used in the exemplary operating environment and any such medium may include computer-executable instructions for carrying out the methods of the present invention.

A number of program modules may be stored in the drive and RAM 1112, including an operating system 1130, one or more application programs 1132, other program modules 1134, and program data 1136. All or a portion of the operating system, applications, modules, and / or data may also be cached in the RAM 1112. It will be appreciated that the present invention may be implemented in a variety of commercially available operating systems or combinations of operating systems.

A user may enter commands and information into the computer 1102 via one or more wired / wireless input devices, such as a keyboard 1138 and a pointing device such as a mouse 1140. [ Other input devices (not shown) may include a microphone, IR remote control, joystick, game pad, stylus pen, touch screen, etc. These and other input devices are often connected to the processing unit 1104 via an input device interface 1142 that is coupled to the system bus 1108, but may be a parallel port, an IEEE 1394 serial port, a game port, a USB port, an IR interface, ≪ / RTI > and so forth.

A monitor 1144 or other type of display device is also connected to the system bus 1108 via an interface, such as a video adapter 1146, In addition to the monitor 1144, the computer typically includes other peripheral output devices (not shown) such as speakers, printers,

Computer 1102 may operate in a networked environment using logical connections to one or more remote computers, such as remote computer (s) 1148, via wired and / or wireless communication. The remote computer (s) 1148 can be a workstation, a server computer, a router, a personal computer, a portable computer, a microprocessor-based entertainment device, a peer device or other conventional network node, Includes a number of or all of the described elements, but for simplicity, only memory storage device 1150 is shown. The logical connections depicted include a wired / wireless connection to a local area network (LAN) 1152 and / or a larger network, e.g., a wide area network (WAN) These LAN and WAN networking environments are commonplace in offices and corporations and facilitate enterprise-wide computer networks such as intranets, all of which can be connected to computer networks worldwide, for example the Internet.

When used in a LAN networking environment, the computer 1102 is connected to the local network 1152 via a wired and / or wireless communication network interface or adapter 1156. [ The adapter 1156 may facilitate wired or wireless communication to the LAN 1152 and the LAN 1152 also includes a wireless access point installed therein to communicate with the wireless adapter 1156. [ When used in a WAN networking environment, the computer 1102 may include a modem 1158, or may be coupled to a communications server on the WAN 1154, or to other devices that establish communications over the WAN 1154, such as via the Internet. . A modem 1158, which may be an internal or external and a wired or wireless device, is coupled to the system bus 1108 via a serial port interface 1142. In a networked environment, program modules described for the computer 1102, or portions thereof, may be stored in the remote memory / storage device 1150. It will be appreciated that the network connections shown are exemplary and other means of establishing a communication link between the computers may be used.

The computer 1102 may be any wireless device or entity that is deployed and operable in wireless communication, such as a printer, a scanner, a desktop and / or portable computer, a portable data assistant (PDA) Any equipment or place, and communication with the telephone. This includes at least Wi-Fi and Bluetooth wireless technology. Thus, the communication may be a predefined structure, such as in a conventional network, or simply an ad hoc communication between at least two devices.

Wi-Fi (Wireless Fidelity) allows you to connect to the Internet without wires. Wi-Fi is a wireless technology such as a cell phone that allows such devices, e.g., computers, to transmit and receive data indoors and outdoors, i. E. Anywhere within the coverage area of a base station. Wi-Fi networks use a wireless technology called IEEE 802.11 (a, b, g, etc.) to provide a secure, reliable, and high-speed wireless connection. Wi-Fi can be used to connect computers to each other, the Internet, and a wired network (using IEEE 802.3 or Ethernet). The Wi-Fi network may operate in unlicensed 2.4 and 5 GHz wireless bands, for example, at 11 Mbps (802.11a) or 54 Mbps (802.11b) data rates, or in products containing both bands have.

Those skilled in the art will appreciate that the various illustrative logical blocks, modules, processors, means, circuits, and algorithm steps described in connection with the embodiments disclosed herein may be embodied directly in electronic hardware, (Which may be referred to herein as "software") or a combination of both. To clearly illustrate this interchangeability of hardware and software, various illustrative components, blocks, modules, circuits, and steps have been described above generally in terms of their functionality. Whether such functionality is implemented as hardware or software depends on the design constraints imposed on the particular application and the overall system. Those skilled in the art may implement the described functionality in various ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present invention.

The various embodiments presented herein may be implemented as a method, apparatus, or article of manufacture using standard programming and / or engineering techniques. The term "article of manufacture" includes a computer program, carrier, or media accessible from any computer-readable device. For example, the computer-readable medium can be a magnetic storage device (e.g., a hard disk, a floppy disk, a magnetic strip, etc.), an optical disk (e.g., CD, DVD, etc.), a smart card, But are not limited to, devices (e. G., EEPROM, cards, sticks, key drives, etc.). The various storage media presented herein also include one or more devices and / or other machine-readable media for storing information. The term "machine-readable medium" includes, but is not limited to, a wireless channel and various other media capable of storing, holding, and / or transferring instruction (s) and / or data.

It will be appreciated that the particular order or hierarchy of steps in the presented processes is an example of exemplary approaches. It will be appreciated that, based on design priorities, certain orders or hierarchies of steps in processes may be rearranged within the scope of the present invention. The appended method claims provide elements of the various steps in a sample order, but are not meant to be limited to the specific order or hierarchy presented.

The description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the scope of the invention. Thus, the present invention is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features presented herein.

Claims (12)

A method for synchronizing query results between databases performed on a first database server,
Determining to perform synchronization on a materialized view residing on a second database server located remotely on the first database server, the materialized view comprising a master table The first database server and the second database server being heterogeneous;
Generating a synchronization command to be transmitted to the second database server in response to the synchronization performing determination; And
And transmits the generated synchronization command to the second database server so that the second database server performs synchronization between the materialized view existing in the second database server and the master table existing in the first database server ;
/ RTI >
The synchronization command comprises:
And inserting log information in a set of log tables existing in the first database server and associated with the master table into a second materialized view log table present in the second database server.
A method for synchronizing query results between databases performed on a first database server. The method according to claim 1,
The log table set includes:
A first materialized view log table containing data changes of the master table;
A DD (Data Dictionary) _SLOG table storing at least one of information on an instantiation view referencing the master table and information on a last synchronization point with respect to the instantiation view; And
A DD_MLOG table storing information on a youngest last refresh time and an oldest last refresh time with respect to the last synchronization point with respect to the materialized view;
≪ / RTI >
A method for synchronizing query results between databases performed on a first database server. 3. The method of claim 2,
Wherein the step of determining to perform the synchronization comprises:
Checking the log table set to determine whether there has been data change in the master table; And
Determining that the synchronization is not to be performed if it is determined that the data change did not occur, and determining to perform the synchronization if the data change is determined to be performed;
/ RTI >
A method for synchronizing query results between databases performed on a first database server. 3. The method of claim 2,
When the synchronization is completed,
Changing a last synchronization point in the DD_SLOG table to a current point in time;
Changing a youngest last refresh time and an oldest last refresh time in the DD_MLOG table; And
Removing unnecessary change data from the first materialized view log table;
/ RTI >
A method for synchronizing query results between databases performed on a first database server. The method according to claim 1,
Wherein the step of determining to perform the synchronization comprises:
A synchronization request from a user of the second database server, a preset synchronization period, and a situation where a data change of the master table occurs,
A method for synchronizing query results between databases performed on a first database server. The method according to claim 1,
Wherein the synchronization is a fast refresh between a master table of the first database server and an instantiation of the second database server,
A method for synchronizing query results between databases performed on a first database server. A method for synchronizing query results between databases performed in a second database server,
Receiving a synchronization command from a first database server located remotely of the second database server, wherein the first database server has a master table and the second database server has an instantiation view that refers to the master table And the first database server and the second database server are different from each other; And
In response to the received synchronization command, inserting log information in a log table set existing in the first database server and associated with the master table into a second materialized view log table existing in the second database server, Performing synchronization between the materialized view existing in the second database server and the master table existing in the first database server;
/ RTI >
A method for synchronizing query results between databases performed on a second database server. 8. The method of claim 7,
Wherein performing the synchronization comprises:
Performing a join operation between the second materialized view log table including the inserted log information and the materialized view;
≪ / RTI >
A method for synchronizing query results between databases performed on a second database server. 1. A first database server for synchronizing query results between databases,
A synchronization decision module adapted to determine to perform synchronization on an instantiation view present in a second database server located remotely of the first database server, the instantiation view comprising a master table The first database server and the second database server being heterogeneous;
A synchronization command generation module configured to generate a synchronization command to be transmitted to the second database server in response to the synchronization performing determination; And
And transmits the generated synchronization command to the second database server so that the second database server performs synchronization between the materialized view existing in the second database server and the master table existing in the first database server A communication module configured to permit the communication module;
/ RTI >
The synchronization command comprises:
And inserting log information in a set of log tables existing in the first database server and associated with the master table into a second materialized view log table present in the second database server.
A first database server for synchronizing query results between databases. A second database server for synchronizing query results between databases,
A communication module configured to receive a synchronization command from a first database server located remotely of the second database server, the communication module having a master table in the first database server, The first database server and the second database server are different from each other; And
In response to the received synchronization command, inserting log information in a log table set existing in the first database server and associated with the master table into a second materialized view log table existing in the second database server, A synchronization executing module configured to perform synchronization between the materialized view existing in the second database server and the master table existing in the first database server;
/ RTI >
A second database server for synchronizing query results between the databases. 21. A computer program stored in a computer-readable storage medium comprising encoded instructions, the computer program causing the one or more processors, when executed by one or more processors of a computer system, to perform the following operations: Actions include:
Determining to perform synchronization on an instantiation view residing on a second database server located remotely on a first database server, the instantiation view being an instantiation view referencing a master table present on the first database server, and The first database server and the second database server are different from each other;
Generating a synchronization command to be transmitted to the second database server in response to the synchronization performing determination; And
And transmitting the generated synchronization command to the second database server to cause the second database server to perform synchronization between the materialized view existing in the second database server and the master table existing in the first database action;
/ RTI >
The synchronization command comprises:
And inserting log information in a set of log tables existing in the first database server and associated with the master table into a second materialized view log table present in the second database server.
A computer program stored on a computer readable storage medium. 21. A computer program stored in a computer-readable storage medium comprising encoded instructions, the computer program causing the one or more processors, when executed by one or more processors of a computer system, to perform the following operations: Actions include:
Receiving a synchronization command from a first database server located remotely on a second database server, the first database server having a master table, the second database server having an instantiation view referring to the master table, And the first database server and the second database server are different from each other; And
In response to the received synchronization command, inserting log information in a log table set existing in the first database server and associated with the master table into a second materialized view log table existing in the second database server, Performing synchronization between the materialized view present in the second database server and the master table existing in the first database server;
/ RTI >
A computer program stored on a computer readable storage medium.

Images