Improved flip-flop execution of flip-flop with multiple insertion operations

Abstract

Techniques and solutions are provided for executing database triggers. In particular, the disclosed techniques allow for the creation of a database trigger having a plurality of insertion statements. For a trigger comprising first and second insertion statements, first and second tables are created for the first and second insertion statements, respectively, each table comprising a sequence column. At least the first insertion statement references a sequence that increments during trigger execution. A sequential column of the first table and the second table has a plurality of elements corresponding to the number of times a given insertion operation is to be performed as a result of a database operation that satisfies a trigger condition. The first and second insertion operations are performed using the respective first and second tables.

Description

Technical Field

[0001] The present disclosure generally relates to processing of database triggers. Certain embodiments relate to techniques for improved processing of triggers with multiple insert operations. Background Art

[0002] Modern database systems are often tasked with processing large amounts of data. For example, enterprise-level database systems often have tables with hundreds of individual fields and thousands of rows or more. A large number of changes can be received for processing in a short period of time, including in situations involving real-time data replication. For example, changes to one database system can be detected and applied to another database system. In the case of columnar format (also known as column store) tables, modifications, deletions, or insertions may need to be processed on multiple partitions. For example, consider that a new row has been added. In a column store database, this can correspond to an insert operation for each column in the row, where each column corresponds to a partition.

[0003] Improving the performance of database operations is an ongoing goal. Therefore, for example, it is often beneficial to implement optimizations when possible. However, certain types of actions such as insert, update, or delete operations may include logic that causes the operation to be unsuitable for existing optimization techniques. Therefore, there is room for improvement. Summary of the invention

[0004] This summary is provided to introduce some concepts in a simplified form, which are further described in the detailed description below. This summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.

[0005] Techniques and solutions for executing database triggers are provided. In particular, the disclosed techniques allow for the creation of a database trigger having multiple insert statements. For a trigger including first and second insert statements, first and second tables are created for the first and second insert statements, respectively, each table including a sequence column. At least the first insert statement references a sequence that is incremented during the execution of the trigger. The sequence columns of the first and second tables have a plurality of elements corresponding to the number of times a given insert operation will be executed as a result of a database operation that satisfies a trigger condition. The first and second insert operations are executed using the corresponding first and second tables.

[0006] In one aspect, the present disclosure provides a process for executing a trigger, the trigger including multiple insert operations. A database trigger definition for a database trigger for a first table is received. The database trigger includes a condition, a first insert operation to be executed when the condition is met, and a second insert operation to be executed when the condition is met. At least the first insert operation references a sequence, the sequence being incremented during the execution of the trigger.

[0007] A database operation that satisfies a condition is received. In response to receiving the database operation that satisfies the condition, a second table is generated. The second table includes a first column that includes a first set of sequence values. The first set of sequence values ​​has a number of elements corresponding to the number of times the first insert operation and the second insert operation will be performed as a result of the database operation.

[0008] In response to receiving a database operation that satisfies the condition, a third table is generated. The third table includes a second column having a second set of sequence values. The second set of sequence values ​​has a plurality of elements corresponding to the number of times the first insert operation and the second insert operation will be performed as a result of the database operation. The first insert operation is performed in response to the database operation using the first table. The second insert operation is performed in response to the database operation using the second table.

[0009] The present disclosure also includes computing systems and tangible, non-transitory computer-readable storage media configured to perform the above-described methods or including instructions for performing the above-described methods. As described herein, various other features and advantages may be incorporated into the technology as desired. BRIEF DESCRIPTION OF THE DRAWINGS

[0010] Figure 1 is a diagram depicting an example database system that can be used to implement aspects of the disclosed technology.

[0011] Figure 2 is a diagram depicting a computing environment in which the disclosed technology may be implemented, including showing how to use triggers to apply changes to data at a source system to data at another system.

[0012] Figure 3 is a diagram illustrating a prior art process for executing a trigger with a single insert operation.

[0013] Figure 4 is a diagram illustrating techniques of the present disclosure for executing a trigger with multiple insert operations.

[0014] Figure 5 Yes Figure 4 Figure 3 is a diagram of a specific example of the technology shown in FIG.

[0015] Fig. 6A Shows that you can use Figure 3The example trigger with a single insert operation is handled by the Figure 6B Shows that you can use Figure 4 The same triggers are handled by the same technique.

[0016] Figure 7 is a flow chart of a process for executing a database trigger that includes multiple insert operations, at least one of which references a sequence that is incremented during trigger execution.

[0017] Figure 8 is a diagram of an example computing system in which some described embodiments may be implemented.

[0018] Fig. 9 is an example cloud computing environment that can be used in conjunction with the techniques described herein. DETAILED DESCRIPTION

[0019] Example 1 - Overview

[0020] Modern database systems are often tasked with processing large amounts of data. For example, enterprise-level database systems often have tables with hundreds of individual fields and thousands of rows or more. A large number of changes can be received for processing in a short period of time, including in situations involving real-time data replication. For example, changes to one database system can be detected and applied to another database system. In the case of columnar format (also known as column store) tables, modifications, deletions, or insertions may need to be processed on multiple partitions. For example, consider that a new row has been added. In a column store database, this can correspond to an insert operation for each column in the row, where each column corresponds to a partition.

[0021] Improving the performance of database operations is an ongoing goal. Therefore, for example, it is often beneficial to implement optimizations when possible. However, certain types of actions such as insert, update, or delete operations may include logic that causes the operation to be unsuitable for existing optimization techniques. Therefore, there is room for improvement.

[0022] Database triggers are an example of a specific database feature that has been the subject of optimization work. Processing the elements of a database trigger in a serial manner can be very time consuming, involving repeated execution of various steps. However, certain types of operations in database triggers can prevent the use of optimization techniques.

[0023] One optimization technique involves storing insert or update operations in a temporary table that is instantiated (and de-instantiated) during the execution of a trigger. Often, it is desirable to include additional information as well as specific values ​​specified for an insert or update. For example, consider when new data is inserted into a local table based on changes to data at a remote system. In addition to storing the new value, it may be desirable to store an indicator of when the data was added. Similarly, for updated data, it may be useful to record when the update was made, or to store information about the changes made by the update, such as the value before the update, or to store both the new value and the old value of the update. As another example, if data is added to a table, it may be desirable to add a technical keyword that can be used to distinguish between table records.

[0024] Specific trigger actions have been developed that create internal tables for data changes. For example, the data change may be an insert or update operation of a column store table. In the case where sequence information is added to the inserted or updated data, the internal table can be "pushed down" to the database software layer that performs the update. In particular, a pointer to the internal table can be pushed down. Pushing the internal table down by using a pointer can be helpful for a variety of reasons, including avoiding data copy operations and allowing a single change operation to be sent to the database software layer, rather than performing the changes serially. For example, an insert operation can be sent for all columns associated with a single record insert, a pointer to the internal table can be used to access the inserted data, and insert operations for different columns can be parallelized because the columns are typically treated as different partitions.

[0025] In the specific example of the above method, the addition of sequence values ​​is specified in a prepared statement for an operation defined in the trigger logic of the corresponding trigger. However, this method may not be suitable for various situations, including when the trigger logic includes multiple insert operations. One problem that may arise is if the trigger logic needs to use more than one internal table. For example, if the trigger logic is used for actions performed based on updates to a source table, it may be desirable to have one internal table for old values ​​and another internal table for new values. Another problem that may arise is that if a single sequence is defined, it may not be possible to parallelize the execution of the operations for "new value" and "old value" because executing the operations in parallel will cause both operations to access the same sequence information (which can be implemented as a sequence object, including as in the technology of SAP SE in Walldorf, Germany), which may cause each process to receive inaccurate sequence information.

[0026] The present disclosure solves these problems by adding a sequence column to each of multiple internal / temporary tables. A prepared statement can be defined for each operation in the trigger logic that involves the use of a temporary table. Therefore, the temporary table already includes the sequence information, which avoids conflicts that may arise if two processes access the same sequence and allows parallelization of the execution of different prepared statements.

[0027] As used herein, "prepared statement" refers to a precompiled SQL statement that can be parameterized and executed multiple times with different input values, as commonly understood in the database art. A prepared statement consists of an SQL template with placeholders for input parameters. These placeholders are typically represented by question marks or named parameters. The template is prepared once and then executed multiple times, allowing different input values ​​to be bound to the parameters.

[0028] Example 2 describes an example database system that can be used to implement the disclosed technology. The database system can be an example of a local database system that performs trigger operations, or an example of a remote database system with objects on which triggers are defined. Example 2 shows an example database environment in which the disclosed technology can be implemented. Example 3 shows existing techniques for processing database triggers with a single insert operation, while Examples 4-7 show how to use the disclosed technology to process database triggers with multiple insert operations.

[0029] Example 2 - Sample database schema

[0030] Figure 1 An example database environment 100 is shown. Database environment 100 may include client 104. Although a single client 104 is shown, client 104 may represent multiple clients. One or more clients 104 may be OLAP clients, OLTP clients, or a combination thereof.

[0031] The client 104 communicates with the database server 106. Through various subcomponents, the database server 106 can process requests for database operations, such as requests to store, read, or manipulate data (i.e., CRUD operations). The session manager component 108 can be responsible for managing connections between the client 104 and the database server 106, such as clients that communicate with the database server using a database programming interface (such as Java Database Connectivity (JDBC), Open Database Connectivity (ODBC), or Database Shared Library (DBSL)). Typically, the session manager 108 can manage connections with multiple clients 104 at the same time. The session manager 108 can perform functions such as creating new sessions for client requests, assigning client requests to existing sessions, and authenticating access to the database server 106. For each session, the session manager 108 can maintain a context that stores a set of parameters related to the session, such as settings related to committing database transactions or transaction isolation levels (such as statement-level isolation or transaction-level isolation).

[0032] For other types of clients 104, such as web-based clients (such as clients using the HTTP protocol or similar transport protocol), the client can interface with the application manager component 110. Although shown as a component of the database server 106, in other implementations, the application manager 110 can be external to the database server 106, but in communication with the database server 106. The application manager 110 can initiate new database sessions with the database server 106 in a manner similar to the session manager 108 and perform other functions.

[0033] Application manager 110 may determine the type of application requesting a database operation and mediate execution of the request at database server 106, such as by invoking or executing a procedure call, generating a query language statement, or converting data between formats usable by client 104 and database server 106. In a particular example, application manager 110 receives a request for a database operation from client 104, but does not store information related to the request, such as state information.

[0034] Once a connection is established between the client 104 and the database server 106, including when established through the application manager 110, the client request is typically executed using a query language, such as structured query language (SQL). In executing the request, the session manager 108 and the application manager 110 may communicate with the query interface 112. The query interface 112 may be responsible for creating a connection with the appropriate execution component of the database server 106. The query interface 112 may also be responsible for determining whether the request is associated with a previously cached statement or stored procedure, and calling a stored procedure or associating a previously cached statement with the request.

[0035] At least some types of requests for database operations (such as statements that write data or manipulate data in a query language) can be associated with a transaction context. In at least some implementations, each new session can be assigned to a transaction. Transactions can be managed by a transaction manager component 114. The transaction manager component 114 can be responsible for operations such as coordinating transactions, managing transaction isolation, tracking running and closed transactions, and managing the submission or rollback of transactions. In performing these operations, the transaction manager 114 can communicate with other components of the database server 106.

[0036] The query interface 112 may communicate with a query language processor 116, such as a structured query language processor. For example, the query interface 112 may forward query language statements or other database operation requests from the client 104 to the query language processor 116. The query language processor 116 may include a query language executor 120, such as a SQL executor, which may include a thread pool 124. Some requests for database operations or components thereof may be executed directly by the query language processor 116. Other requests or components thereof may be forwarded by the query language processor 116 to another component of the database server 106. For example, transaction control statements (such as commit or rollback operations) may be forwarded by the query language processor 116 to the transaction manager 114. In at least some cases, the query language processor 116 is responsible for performing operations (e.g., select, update, delete) that retrieve or manipulate data. Other types of operations (such as queries) may be sent by the query language processor 116 to other components of the database server 106. The query interface 112 and the session manager 108 may maintain and manage context information associated with requests for database operations. In particular embodiments, query interface 112 may maintain and manage context information for requests received via application manager 110 .

[0037] When the session manager 108 or the application manager 110 establishes a connection between the client 104 and the database server 106, a client request such as a query can be assigned to a thread of the thread pool 124, such as using the query interface 112. In at least one embodiment, a thread is associated with a context for performing processing activities. The thread can be managed by the operating system of the database server 106, or by another component of the database server, or in combination with another component of the database server. Typically, at any point, the thread pool 124 contains multiple threads. In at least some cases, the number of threads in the thread pool 124 can be dynamically adjusted, such as in response to the activity level at the database server 106. In certain aspects, each thread of the thread pool 124 can be assigned to multiple different sessions.

[0038] When a query is received, the session manager 108 or application manager 110 may determine whether an execution plan for the query already exists, such as in the plan cache 136. If a query execution plan exists, the cached execution plan may be retrieved and forwarded to the query language executor 120, such as using the query interface 112. For example, the query may be sent to an execution thread of a thread pool 124 determined by the session manager 108 or application manager 110. In a particular example, the query plan is implemented as an abstract data type.

[0039] If the query is not associated with an existing execution plan, the query may be parsed using a query language parser 128. The query language parser 128 may, for example, check the query language statements of the query to ensure that they have the correct syntax and confirm that the statements are otherwise valid. For example, the query language parser 128 may check whether the tables and records specified in the query language statements are defined in the database server 106.

[0040] The query may also be optimized using a query language optimizer 132. The query language optimizer 132 may manipulate elements of a query language statement to allow for more efficient processing of the query. For example, the query language optimizer 132 may perform operations such as unnesting a query or determining an optimized execution order for various operations in a query (such as operations within a statement). After optimization, an execution plan may be generated or compiled for the query. In at least some cases, the execution plan may be cached, such as in a plan cache 136, and the execution plan may be retrieved (such as by a session manager 108 or an application manager 110) if the query is received again.

[0041] Once a query execution plan is generated or received, the query language executor 120 can oversee the execution of the query execution plan. For example, the query language executor 120 can call the appropriate subcomponents of the database server 106.

[0042] When executing a query, the query language executor 120 can call a query processor 140, which can include one or more query processing engines. The query processing engine can include, for example, an OLAP engine 142, a connection engine 144, an attribute engine 146, or a calculation engine 148. The OLAP engine 142 can, for example, apply rules to create an optimized execution plan for an OLAP query. The connection engine 144 can be used to implement relational operators, typically for non-OLAP queries, such as joins and aggregation operations. In a particular embodiment, the attribute engine 146 can implement column data structures and access operations. For example, the attribute engine 146 can implement merge functions and query processing functions, such as scanning columns.

[0043] In some cases, such as if a query involves complex or internally parallelized operations or sub-operations, the query executor 120 may send the operations or sub-operations of the query to a job executor component 154, which may include a thread pool 156. The execution plan for the query may include multiple plan operators. In particular embodiments, each job execution thread of the job execution thread pool 156 may be assigned to a separate plan operator. The job executor component 154 may be used to execute at least a portion of the operators of the query in parallel. In some cases, the plan operators may be further divided and parallelized, such as having the operations access different portions of the same table simultaneously. Using the job executor component 154 may increase the load on one or more processing units of the database server 106, but may improve the execution time of the query.

[0044] The query processing engine of the query processor 140 can access data stored in the database server 106. The data can be stored in a row format in the row store 162, or in a column format in the column store 164. In at least some cases, the data can be transformed between the row-by-row format and the column-by-column format. Certain operations performed by the query processor 140 can access or manipulate data in the row store 162, the column store 164, or at least for certain types of operations (such as joins, merges, and subqueries), access or manipulate data in both the row store 162 and the column store 164. In at least some aspects, the row store 162 and the column store 164 can be maintained in main memory.

[0045] Persistence layer 168 may communicate with row store 162 and column store 164. Persistence layer 168 may be responsible for actions such as committing write transactions, storing redo log entries, rolling back transactions, and periodically writing data to storage to provide persistent data 172.

[0046] When executing a request for a database operation (such as a query or a transaction), the database server 106 may need to access information stored at another location (such as another database server). The database server 106 may include a communication manager 180 component for managing such communications. The communication manager 180 may also coordinate communications between the database server 106 and the client 104 or application manager 110 when the application manager is located outside the database server.

[0047] In some cases, database server 106 may be part of a distributed database system that includes multiple database servers. At least a portion of the database servers may include some or all of the components of database server 106. In some cases, the database servers of the database system may store multiple copies of data. For example, a table may be replicated at more than one database server. Additionally or alternatively, information in the database system may be distributed among multiple servers. For example, a first database server may save a copy of a first table, and a second database server may save a copy of a second table. In other embodiments, information may be divided among the database servers. For example, a first database server may save a first portion of a first table, and a second database server may save a second portion of the first table.

[0048] In executing requests for database operations, database server 106 may need to access other database servers or other information sources within the database system or at external systems (such as external systems where remote data objects are located). Communications manager 180 can be used to coordinate such communications. For example, communications manager 180 can receive and route information requests from components of database server 106 (or from another database server), and receive and route replies.

[0049] The database server 106 may include components for coordinating data processing operations involving remote data sources. In particular, the database server 106 includes a data federation component 190 that at least partially processes requests to access data maintained at a remote system. In performing its functions, the data federation component 190 may include one or more adapters 192, wherein the adapter may include logic, settings, or connection information that can be used to communicate with the remote system, such as for obtaining information to help generate a virtual parameterized data object or use the virtual parameterized data object to perform a request for data (such as issuing a request to the remote system for data accessed using the corresponding parameterized data object of the remote system). Examples of adapters include "connectors" as implemented in technology available from SAP SE of Walldorf, Germany. In addition, the disclosed technology can use the underlying technology of data federation technology, such as SAP SE's Smart Data Access (SDA) and Smart Data Integration (SDI).

[0050] Example 3 - Example computing environment for performing data manipulation operations at a target system based on data manipulation operations at a source system

[0051] Figure 2A computing environment 200 is shown in which the disclosed technology may be implemented. The computing environment 200 includes a source database system 204 and a target database system 208. A scenario for the computing environment 200 includes replicating data from the source database system 204 to the target database system 208.

[0052] The source database system 204 includes a data store 212 that stores data in a plurality of objects, such as different instances of a particular object type, such as an object type representing a relational database table. For purposes of explanation, the object types are shown as relational database tables 216a, 216b, although other object types may be used. A corresponding trigger 220 is defined for the database table 216b, while no trigger is defined for the database table 216a. In some cases, triggers may be defined for all tables in the data store 212, or for a particular database model / schema of the data store. In that case, the data store 212 may omit the table 216a.

[0053] Changes may be made to the table 216 b, such as in response to commands received from one or more applications 224. The changes may be in the form of data to be added, data to be modified, or existing data to be deleted. For any given table 216 b, triggers 220 may be defined for one or more of these operations. For example, one trigger 220 may be defined for updates to the table 216 b, while another trigger may be defined for insert operations on the table.

[0054] The replication service 228 can cause the change information to be propagated to the target database system 208. The replication service 228 can communicate with the target database system 208 through the application programming interface 232 of the target database system. In a specific example, the target database system 208 is a HANA database system of SAP SE of Walldorf, Germany, and the application programming interface 232 is SAP's EAPI (Enterprise Application Integration) interface. An update engine 236 can be used to process changes (inserts, updates, or deletes) received through the application programming interface 232. In a specific example, the update engine 236 is a HANA update engine (HUP) used in the technology of SAP SE.

[0055] The update engine 236 may in turn call functions of a database engine 240 (such as the HANA database engine of SAP SE). In turn, the database engine 240 may make changes to data stored in a data store 244 of the target database system 208, such as making changes to a table 248 of the data store.

[0056] To help improve the performance of the target database system 208, the target database system may include additional components, such as a procedural query language artifact 252. An example of a procedural query language artifact 252 is a prepared statement 256. The procedural query language artifact 252 may be defined in a programming language such as SAP SE's SQLSCRIPT. The prepared statement 256 is shown as being maintained in a plan cache 260. The prepared statement 256 may be maintained or registered in the plan cache 260 and may be accessed during the processing of a change operation, such as by the update engine 236.

[0057] The processing of change operations can also be facilitated by using internal tables 264 (which can also be referred to as temporary tables). Internal tables 264 are typically created dynamically during the processing of database operations, such as when performing a read, insert, or update request. Internal tables 264 are typically created in memory.

[0058] Data changes may be processed using the disclosed techniques, but using different components of the target database system 208, or at least using the components in a different order / different manner. For example, in a specific example, the replication service 228 may directly access the update engine 236 of the target database system 208. In a specific embodiment, the source database system 204 is an instance of SAP's SAP Inventory Collaboration Hub (SAP ICH). In this example, the application 224 may be another database system. For example, an insert operation to data associated with a database application of another computing system may cause the table 216b to be changed, and the replication service 238 (in this case, a component of the SAP_ICH) may directly access the update engine 236 to initiate an update to the table 248 of the data store 244 of the target database system 208.

[0059] As the description proceeds, the use of various components of computing environment 200 will be further described. However, the disclosed techniques may be used in scenarios different from those shown in computing environment 200. For example, in some cases, the disclosed trigger processing techniques may be used with triggers defined and processed within a single database system. That is, the table on which the trigger is defined may be located in the same database system as the table that is the subject of the insert operation defined within the trigger.

[0060] Example 4 - Example Technique for Processing Triggers with a Single Insert Operation

[0061] To aid in understanding the improvements provided by the disclosed innovations, Figure 3 A previous change implementation process 300 is depicted in FIG.

[0062] Process 300 involves triggers 304 defined on a specified source table 306 (or other data source). Triggers 304 may be defined relative to a particular operation 308 of the specified source table 306. For purposes of process 300, consider that operation 308 of the process is an insert operation.

[0063] Trigger 304 includes trigger logic 310. In this case, trigger logic 310 includes a single action 312. Action 312 specifies a particular attribute 314 whose value is to be inserted into a particular target table 316 once the trigger condition is satisfied. In this case, attributes 314 include a value 318 that specifies the next occurrence in a sequence, a timestamp 320, and attributes 322, which may correspond to the value provided in the insert operation that fired trigger 304 (e.g., the column value specified in the insert statement for source table 306).

[0064] Now, consider an operation 330 performed on source table 306. In this particular example, operation 330 is an insert operation. Operation 330 satisfies condition / operation 308. Consider that insert operation 330 provides value 332 specified for attribute 322.

[0065] In response to receiving the operation 330, the data changes of the operation can be applied to the source table 306. The trigger 304 is fired, which causes the insert operation of the trigger to be initiated at 340. In this case, the operation at 340 is shown as a multi-store insert (MultiStoreInsert) operation, as implemented in the technology of SAP SE. The multi-store insert operation can parallelize the insert operation of the attribute 320, such as having concurrent insert operations for each attribute, where each attribute can be associated with a different column partition of a table (such as table 248 of computing environment 200). At 342, the source table is changed.

[0066] Operation 340 also results in generating an internal table 344. Internal table 344 may have the attributes and values ​​as specified in operation 312, or may have values ​​resulting from the operation performed at 342. For example, when condition 308 is an update operation, internal table 344 may contain the old value of source table 306 before the update, the new value of the source table before the update, or both the old value and the new value.

[0067] The pointer for the internal table is passed to the trigger executor component, which executes the trigger at 350, such as by calling a procedural query language execution engine at 352. In a specific example, the trigger executor component associated with the operation at 350 may be a Figure 2 The functionality provided by the update engine 236 of . Executing the trigger at 352 may include calling an application programming interface, such as Figure 2The application programming interface 232 of FIG. 350 is then executed at 354. In the specific example, a stored procedure is defined for the trigger logic 310, specifically corresponding to the operation 312. At 354, the application programming interface can perform the operation shown in the panel 356.

[0068] The operations in panel 356 include, at 358, defining a pointer to a prepared statement when a pointer is set to point to a prepared statement (preparedStatement) defined with respect to a connection object (such as a database connection object, such as a connection object implemented in the technology of SAP SE). During the execution of the trigger, the connection object can be used to interact with the database, such as with Figure 2 The connection object may include methods for executing SQL statements, obtaining results, or managing transactions. Panel 356 also includes an operation 360 for creating columns for the prepared statement. Operation 360 uses the pointer value of the internal table as an argument and causes the prepared statement to use the columns defined for the internal table. Operation 362 causes the prepared statement to be executed.

[0069] The operation at 354 results in an insert operation being performed at 366, such as by Figure 2 The update engine 236 of the trigger logic 310 performs the insert operation at 366. The insert operation at 366 can be a multi-store insert operation. The operation of the update engine at 366 causes the target table of the trigger logic 310 to be updated at 370, such as by using Figure 2 The database engine 240.

[0070] Example 5 - Example technique for handling a trigger with multiple insert operations

[0071] Figure 4 4 is a flow diagram of a process 400 for processing a trigger using the innovations of the present disclosure. The process 400 includes a trigger definition 404. The trigger definition 404 is defined on a source table 406, particularly when an update is performed on the source table. The trigger definition 404 includes a specification 408 that the trigger will reference a newly inserted or updated row, and that "NEW" after "NEW ROW" is used as an alias for the newly updated or inserted row. The "FOR EACH ROW" portion of the trigger definition will cause the update to be performed on each affected row. In this case, NEW ROW is used to override the default alias "NEW" for the new row, and since an alias for the old version of the row is not specified, the default alias "OLD" can be used to access the old version of the row.

[0072] Trigger 404 includes a trigger definition 410. Trigger definition 410 includes multiple statements, including a declaration 412 that increments a unique user counter "iuuc.seq". Select operation 414 obtains the next value in the sequence (where "dummy" acts as a placeholder table that does not store data, which allows operation 414 to conform to standard SQL structure). Operation 416 inserts a value of "1" into the trigger_counter table. Operation 416 can be used to track how many records are affected by an update operation on source table 406.

[0073] While trigger 304 includes a single insert operation, trigger 404 includes two additional insert operations, operations 420, 424. Insert operation 420 inserts values ​​422 into the logging table "logging": the current sequence value, the current timestamp, the value of column "COL_1" of the old version of the row associated with the update operation, the values ​​of the previous row version of the "KEY1" and "KEY2" columns, and a value of "D" (which may indicate that the inserted record corresponds to a delete operation on source table 406). Insert operation 424 is similar to insert operation 420, inserting values ​​426, but using the values ​​of "COL_1", "KEY1", and "KEY2" from the new version of the row. Instead of the value "D", the value "I" is inserted, indicating that the inserted record corresponds to an insert operation on source table 406.

[0074] Now, assume that an update operation 430 is performed on the source table 406. The update operation 430 causes an insert operation to be initiated at 434, which may be a multi-store insert operation, such as Figure 3 The insert operation 340 is shown. The insert operation may cause the source table 406 to be updated at 436.

[0075] As a result of the multi-store insert operation 434, an internal table 438 may be created. The internal table 438 may have the previous value of a given record of the source table 406 and the new value of a given record of the source table. The internal table 438 may be defined to identify the old and new records separately (such as a column indicating an added record or a deleted record), or may be associated with logic (a method of a class implementing the internal table) that may provide selective access to the old or new values.

[0076] The multi-store insert operation at 434 may pass a pointer to the internal table 438 to the trigger executor, which initiates the trigger execution at 448 and, in turn, may cause the procedural query language engine to execute the implementation ("ExectuableImpl") defined for the trigger at 450. That is, a procedural query object such as a SQLSCRIPT object may store logic for a particular trigger. In the case of process 400, the trigger logic may include logic for creating separate internal tables 458, 460 that store old values ​​(itabOld) and new values ​​(itabNew) extracted from the internal table 438, respectively.

[0077] The trigger logic may also call an application programming interface to perform additional processing at 468. The additional processing at 468 may include the operations shown in panel 470. Operation 472 causes a pointer to column "seqCol" to be generated. Operations 474, 476 define a loop that adds values ​​to the sequence column according to the sequence identified in the trigger definition 410 ("SEQ", where ".nextval" obtains the next value in the sequence), where the values ​​are added based on the size of the internal table (where the size function of the internal table may correspond to the number of rows updated, rather than rows associated with the insert portion of the update and rows associated with the delete portion of the update). In operations 478, 480, the sequence column is added to an internal table oldItab with the previous value 458 and an internal table newItab with the new value 460. Note that this results in separate storage of sequence information for the old and new values, which is consistent with Figure 3 This is in contrast to process 300, where sequence values ​​are not stored in an internal table.

[0078] Panel 470 defines operations 486, 488, 490 corresponding to the three insert operations 416, 420, 424 of trigger definition 410. Operations 486, 488, 490 are generally similar to Figure 3 Operations 358, 360, 362 of the example are described in detail in that they involve defining a pointer to the prepared statement based on the connection object, setting the columns of the prepared statement based on the internal table (depending on whether the corresponding prepared statement is dealing with old or new values, or in cases where it does not matter, such as when tracking the total number of updates), and then causing the prepared statement to be executed. Note that the insert statements for operations 488, 490 now reference the sequence column of the corresponding old or new internal table, rather than directly referencing the sequence object (indicated by the comment " / *iiuc_seq* / ").

[0079] Process 400 then continues in a manner similar to operations 366 , 370 of process 300 , where a multiple store insert operation is generated at 494 , and the table that is the target of the insert operations 416 , 420 , 424 is updated at 496 .

[0080] Figure 5 Provide additional information to help explain Figure 4 The process 400. In particular, Figure 5 Table 508 is shown, which may be the subject of a trigger (eg, trigger 404) or a trigger similar thereto.An update operation 512 is received for table 508, which causes the contents of the table to be modified as specified in the update operation.

[0081] Execution of update operation 512 results in the creation of an internal (temporary) table 516 that stores the old value of the updated row and the new value of the updated row for the rows that satisfy the update operation. Internal table 516 may correspond to Figure 4 The internal table 438 of the embodiment of the present invention. The definition of the internal table 516 can make it possible to selectively retrieve the old value or the new value. Therefore, the internal table 516 can be used to generate the internal table 520 containing the old value and the internal table 524 containing the new value.

[0082] According to the techniques of the present disclosure, sequence information from sequence definition 526 (such as a sequence defined in a sequence object) can be used to append sequence columns 528, 530 to internal tables 520, 524, respectively, to generate internal tables 534, 538. Internal tables 534, 538 can correspond to Figure 4 Internal tables 458, 460. The internal tables 534, 538 can then be used to perform trigger operations 542, 546 corresponding to the actions defined in the trigger 404 on the table targeted by the insert statement in the trigger.

[0083] As already described, including sequence columns 528, 530 in internal tables 534, 538 allows trigger operations to be parallelized compared to triggers that are processed directly using a specified sequence.

[0084] Example 6 - Example trigger with different types of trigger conditions with a single insert statement and a similar trigger with multiple insert statements

[0085] Fig. 6A shows insert, update, and delete scenarios that can be handled using existing trigger optimization techniques, while Figure 6B Shown with Fig. 6A Substantially similar triggers, but with modifications that would make them unsuitable for use with trigger optimization techniques, which may nevertheless be processed using the disclosed techniques.

[0086] exist Fig. 6A, operation 608 creates a table "SUBJECT" as a column store table with three attributes (MANDT, COL1, COL2). Operation 610 creates a table "LOGGING", which is also a column store table and includes five attributes (typically, a sequence column, a timestamp column, a MANDT column, and columns COL1 and COL2).

[0087] Fig. 6A Various triggers 616, 618, 620 are shown that can be defined on a subject table (thus, a source table) and cause operations to be performed on a log table (thus, a target table). The conditions of the triggers 616, 618, 620 are insert operations, update operations, and delete operations, respectively. Note that the triggers 616, 618, 620 each include a single insert operation to the target table, and only a single sequence object ("SEQ") is used in a given trigger.

[0088] Figure 6B The operation 650 includes creating a topic table corresponding to the Fig. 6A The subject table is created by operation 608. In this case, two log tables LOGGING1 and LOGGING2 are defined by corresponding operations 652 and 654, but each operation has Fig. 6A The format of operation 610 is similar.

[0089] Figure 6B Also included is an operation 656 for creating a global temporary table (in column storage format) with a single column with an identifier "TRIGGER_COUNT". Operation 658 creates a sequence object "TEST_SEQ". The sequence object starts at the higher of 1 or an integer value higher than the current sequence value associated with the LOGGING1 table.

[0090] Figure 6B Triggers 664, 666, 668 are shown, corresponding to the trigger conditions of insert, update or delete on the subject table (which thus serves as the source table for the trigger). Trigger 664 includes a sequence variable declared at operation 670, and uses the sequence object created in operation 658 to provide the value of the sequence according to operation 672. Trigger 664 includes a constant insert operation 674, and an insert operation 676 that inserts a value into the LOGGING2 table.

[0091] In prior optimization techniques, insert operations 674, 676 would typically be executed serially because insert operation 676 does not "know" how many values ​​were inserted by insert operation 674, at least until after insert operation 674 is executed. Figure 4The functionality described in panel 470 of FIG. 47A is used to process trigger 664. That is, the size (number of records) of the internal table holding the insert information can be determined. A sequence column can be added to the internal table to provide a separate internal table for insert operations 674, 676, and then the prepared statement for the insert operation can be executed using the modified internal table. Therefore, the insert operations 674, 676 can be performed in parallel for each inserted record. Figure 6B An example insert operation 678 to the subject table is shown, which causes trigger 664 to be processed.

[0092] Trigger 666 is generally similar to Figure 4 The difference is that trigger 666 includes insert operations 680, 682 into the LOGGING1 and LOGGING2 tables corresponding to the old and new values, respectively, instead of having operation 630 insert the old and new values ​​into the same table. Otherwise, the same can be done as with respect to Figure 4 Trigger 666 is processed as discussed, including processing insert operation 684, which inserts a constant value. Operation 686 is an example update operation to the subject table that causes trigger 666 to be processed.

[0093] Trigger 668 is generally similar to trigger 664, but logs the old value to the LOGGING1 table on a delete, rather than also logging the new value to the LOGGING2 table in response to an update. Trigger 668 also includes a constant insert operation 688 similar to operation 674. Otherwise, the operation and benefits of trigger 668 are similar to the description of triggers 664, 666. Operation 686 is an example delete operation on the subject table that causes trigger 668 to be processed.

[0094] Example 7 - Example Action to Execute a Trigger with Multiple Insert Operations

[0095] Figure 7 700 is a flow chart of a process of executing a trigger including multiple insert operations. At 705, a database trigger definition for a database trigger for a first table is received. The database trigger includes a condition, a first insert operation to be executed when the condition is met, and a second insert operation to be executed when the condition is met. At least the first insert operation references a sequence that is incremented during trigger execution.

[0096] A database operation that satisfies the condition is received at 710. In response to receiving the database operation that satisfies the condition, a second table is generated at 715. The second table includes a first column that includes a first set of sequence values. The first set of sequence values ​​has a number of elements corresponding to the number of times the first insert operation and the second insert operation will be performed as a result of the database operation.

[0097] In response to receiving a database operation that satisfies the condition, at 720, a third table is generated. The third table includes a second column having a second set of sequence values. The second set of sequence values ​​has a plurality of elements corresponding to the number of times the first insert operation and the second insert operation will be performed as a result of the database operation. At 725, the first insert operation is performed using the first table in response to the database operation. At 730, the second insert operation is performed using the second table in response to the database operation.

[0098] Example 8 - Calculation System

[0099] Figure 8 A generalized example of a suitable computing system 800 is depicted in which the described innovations may be implemented. Computing system 800 is not intended to suggest any limitation as to the scope of use or functionality of the present disclosure, as the innovations may be implemented in various general-purpose or special-purpose computing systems.

[0100] refer to Figure 8 , the computing system 800 includes one or more processing units 810, 815 and memories 820, 825. Figure 8 , the basic configuration 830 is included within the dashed line. Processing units 810, 815 execute computer executable instructions, such as for implementing the database environment and associated methods described in Examples 1-7. The processing unit can be a general-purpose central processing unit (CPU), a processor in an application-specific integrated circuit (ASIC), or any other type of processor. In a multi-processing system, multiple processing units execute computer executable instructions to increase processing power. For example, Figure 8 A central processing unit 810 and a graphics processing unit or co-processing unit 815 are shown. Tangible memory 820, 825 may be volatile memory (e.g., registers, cache, RAM), non-volatile memory (e.g., ROM, EEPROM, flash memory, etc.), or some combination of both accessible by the processing units 810, 815. The memory 820, 825 stores software 880 implementing one or more innovations described herein in the form of computer-executable instructions suitable for execution by the processing units 810, 815.

[0101] The computing system 800 may have additional features. For example, the computing system 800 includes storage 840, one or more input devices 850, one or more output devices 860, and one or more communication connections 870. An interconnection mechanism (not shown) such as a bus, controller, or network interconnects the components of the computing system 800. Typically, operating system software (not shown) provides an operating environment for other software executed in the computing system 800 and coordinates the activities of the components of the computing system 800.

[0102] Tangible storage 840 may be removable or non-removable and include disks, tapes or cassettes, CD-ROMs, DVDs, or any other medium that can be used to store information in a non-transitory manner and that can be accessed within computing system 800. Storage 840 stores instructions for software 880 implementing one or more innovations described herein.

[0103] The input device 850 may be a touch input device (such as a keyboard, mouse, pen, or trackball), a voice input device, a scanning device, or another device that provides input to the computing system 800. The output device 860 may be a display, a printer, a speaker, a CD burner, or another device that provides output from the computing system 800.

[0104] Communication connection 870 enables communication with another computing entity (such as another database server) via a communication medium. The communication medium transmits information, such as computer executable instructions, audio or video input or output, or other data in a modulated data signal. A modulated data signal is a signal that has one or more of its characteristics set or changed in a manner that encodes the information in the signal. By way of example and not limitation, the communication medium may use electrical, optical, RF or other carriers.

[0105] The innovations may be described in the general context of computer executable instructions, such as those included in program modules that are executed in a computing system on a target real or virtual processor. Typically, program modules or components include routines, programs, libraries, objects, classes, components, data structures, etc. that perform specific tasks or implement specific abstract data types. In various embodiments, the functionality of program modules may be combined or split between program modules as desired. Computer executable instructions for program modules may be executed within a local or distributed computing system.

[0106] The terms "system" and "device" are used interchangeably herein. Unless the context clearly indicates otherwise, neither term implies any limitation on the type of computing system or computing device. In general, a computing system or computing device may be local or distributed, and may include any combination of special-purpose hardware and / or general-purpose hardware with software that implements the functionality described herein.

[0107] For purposes of presentation, the detailed description uses terms such as "determine" and "use" to describe computer operations in a computing system. These terms are high-level abstractions for operations performed by a computer and should not be confused with actions performed by a human. The actual computer operations corresponding to these terms vary depending on the implementation.

[0108] Example 9 - Cloud computing environment

[0109] Fig. 9An example cloud computing environment 900 is depicted in which the described techniques may be implemented. Cloud computing environment 900 includes cloud computing services 910. Cloud computing services 910 may include various types of cloud computing resources, such as computer servers, data storage, network resources, etc. Cloud computing services 910 may be centrally located (e.g., provided by a data center of an enterprise or organization) or distributed (e.g., provided by various computing resources located in different locations, such as different data centers and / or located in different cities or countries).

[0110] The cloud computing service 910 is utilized by various types of computing devices (e.g., client computing devices), such as computing devices 920, 922, and 924. For example, the computing devices (e.g., 920, 922, and 924) may be computers (e.g., desktop or laptop computers), mobile devices (e.g., tablet computers or smart phones), or other types of computing devices. For example, the computing devices (e.g., 920, 922, and 924) may utilize the cloud computing service 910 to perform computing operations (e.g., data processing, data storage, etc.).

[0111] Example 10 - Implementation

[0112] Although the operations of some of the disclosed methods are described in a particular sequential order for ease of presentation, it should be understood that this description encompasses rearrangement unless the specific language set forth herein requires a particular ordering. For example, the operations described in sequence may be rearranged or performed simultaneously in some cases. In addition, for simplicity, the accompanying drawings may not show the various ways in which the disclosed methods may be used in conjunction with other methods.

[0113] Any disclosed method may be implemented as computer executable instructions or a computer program product stored on one or more computer readable storage media (such as tangible, non-transitory computer readable storage media) and executed on a computing device (e.g., any available computing device, including a smartphone or other mobile device that includes computing hardware). Tangible computer readable storage media is any available tangible media that can be accessed within a computing environment (e.g., one or more optical media disks such as DVDs or CDs, volatile memory components (such as DRAM or SRAM), or non-volatile memory components (such as flash memory or a hard drive)). As an example and with reference to Figure 8 , computer-readable storage media include memories 820 and 825 and storage 840. The term computer-readable storage medium does not include signals and carrier waves. In addition, the term computer-readable storage medium does not include communication connections (eg, 870).

[0114] Any computer executable instructions for implementing the disclosed technology and any data created and used during the implementation of the disclosed embodiments can be stored on one or more computer readable storage media. Computer executable instructions can be, for example, a dedicated software application or a part of a software application accessed or downloaded via a web browser or other software application (such as a remote computing application). Such software can be executed, for example, on a single local computer (e.g., any suitable commercially available computer) or in a network environment using one or more network computers (e.g., via the Internet, a wide area network, a local area network, a client-server network (such as a cloud computing network) or other such networks).

[0115] For the sake of clarity, only certain selected aspects of software-based implementations are described. Other details known in the art are omitted. For example, it should be understood that the disclosed technology is not limited to any particular computer language or program. For example, the disclosed technology can be implemented by software written in C++, Java, Perl, JavaScript, Python, Ruby, ABAP, structured query language, Adobe Flash, or any other suitable programming language, or in some examples, by a markup language such as html or XML or a combination of suitable programming languages ​​and markup languages. Similarly, the disclosed technology is not limited to any particular computer or hardware type. Certain details of suitable computers and hardware are known and do not need to be elaborated in this disclosure.

[0116] In addition, any software-based embodiments (including, for example, computer-executable instructions for causing a computer to perform any disclosed method) can be uploaded, downloaded, or remotely accessed via suitable communication means. Such suitable communication means include, for example, the Internet, the World Wide Web, an intranet, a software application, cables (including fiber optic cables), magnetic communications, electromagnetic communications (including RF, microwave, and infrared communications), electronic communications, or other such communication means.

[0117] The disclosed methods, apparatus, and systems should not be construed as limiting in any way. Rather, the present disclosure is directed to all novel and non-obvious features and aspects of the various disclosed embodiments, individually and in various combinations and sub-combinations with each other. The disclosed methods, apparatus, and systems are not limited to any particular aspect or feature or combination thereof, nor do the disclosed embodiments require the presence of any one or more specific advantages or problems to be solved.

[0118] The technology from any example can be combined with the technology described in any one or more other examples. In view of the many possible embodiments to which the principles of the disclosed technology can be applied, it should be recognized that the illustrated embodiments are examples of the disclosed technology and should not be considered as limiting the scope of the disclosed technology. On the contrary, the scope of the disclosed technology includes what is covered by the scope and spirit of the appended claims.

Claims

1. A computing system comprising: at least one memory; one or more hardware processor units coupled to the at least one memory; as well as One or more computer-readable storage media storing computer-executable instructions that, when executed, cause the computing system to perform operations comprising: receiving a database trigger definition for a database trigger for a first table, the database trigger comprising a condition, a first insert operation to be performed when the condition is satisfied, and a second insert operation to be performed when the condition is satisfied, wherein at least the first insert operation references a sequence, the sequence being incremented during trigger execution; receiving a database operation that satisfies the condition; In response to receiving a database operation that satisfies the condition, generating a second table, the second table comprising a first column, the first column comprising a first set of sequence values, the first set of sequence values ​​having a number of elements corresponding to a number of times the first insert operation and the second insert operation will be performed as a result of the database operation; in response to receiving the database operation satisfying the condition, generating a third table including a second column, the second column including a second set of sequence values, the second set of sequence values ​​having a number of elements corresponding to the number of times the first insert operation and the second insert operation will be performed as a result of the database operation; performing the first insert operation in response to the database operation using the first table; and The second insert operation is performed in response to the database operation using the second table.

2. The computing system of claim 1, wherein: Executing the first insert operation and executing the second insert operation are performed in parallel.

3. The computing system of claim 1 , wherein the operations further comprise: generating a first prepared statement corresponding to the first insert operation; generating a second prepared statement corresponding to the second insert operation; executing the first prepared statement using the second table; as well as The second prepared statement is executed using the third table.

4. The computing system of claim 3 , wherein executing the first prepared statement using the second table comprises providing a pointer to the second table when the first prepared statement is invoked, and executing the second prepared statement using the third table comprises providing a pointer to the third table when the second prepared statement is invoked.

5. The computing system of claim 3, wherein executing the first prepared statement and executing the second prepared statement are performed in parallel.

6. The computing system of claim 3, wherein executing the first prepared statement is performed independently of executing the second prepared statement. 7 . The computing system of claim 1 , wherein the second table includes values ​​received in the database operation, and the first column is appended to a table including the values ​​to provide the second table.

8. The computing system of claim 1, wherein: The database operation is an update operation, and: forming the second table by appending the first column to a table including old values ​​of the first table; as well as The third table is formed by appending the second column to the table including the new values ​​of the first table.

9. The computing system of claim 1, wherein: During execution of a trigger executed in response to the database operation, the first insert operation inserts a specified value each time the first insert operation is executed.

10. The computing system of claim 1, wherein: The condition is an update operation performed on the first table.

11. The computing system of claim 1, wherein: The condition is an insert operation performed on the first table.

12. The computing system of claim 1, wherein: The condition is a delete operation performed on the first table.

13. The computing system of claim 1, wherein: The first table is in column storage format.

14. The computing system of claim 1, wherein the sequence is defined at least in part using a sequence object defined external to the database trigger.

15. The computing system of claim 1, wherein: The second table and the third table are temporary tables.

16. The computing system of claim 1, the operations further comprising: Determining the size of a table storing the results of executing the database operation; generating a plurality of sequence values ​​according to the size; as well as The plurality of sequence values ​​are provided as values ​​of elements of the plurality of elements of the second table and as values ​​of elements of the plurality of elements of the third table.

17. A method implemented in a computing system, the computing system comprising at least one hardware processor and at least one memory coupled to the at least one hardware processor, the method comprising: receiving a database trigger definition for a database trigger for a first table, the database trigger comprising a condition, a first insert operation to be performed when the condition is satisfied, and a second insert operation to be performed when the condition is satisfied, wherein at least the first insert operation references a sequence, the sequence being incremented during trigger execution; receiving a database operation that satisfies the condition; In response to receiving the database operation that satisfies the condition, generating a second table, the second table comprising a first column, the first column comprising a first set of sequence values, the first set of sequence values ​​having a number of elements corresponding to a number of times the first insert operation and the second insert operation will be performed as a result of the database operation; in response to receiving the database operation satisfying the condition, generating a third table including a second column, the second column including a second set of sequence values, the second set of sequence values ​​having a number of elements corresponding to the number of times the first insert operation and the second insert operation will be performed as a result of the database operation; using the first table, performing the first insert operation in response to the database operation; as well as The second insert operation is performed in response to the database operation using the second table.

18. The method according to claim 17, further comprising: generating a first prepared statement corresponding to the first insert operation; generating a second prepared statement corresponding to the second insert operation; executing the first prepared statement using the second table; as well as The second prepared statement is executed using the third table.

19. One or more non-transitory computer-readable storage media comprising: computer executable instructions that, when executed by a computing system comprising at least one hardware processor and at least one memory coupled to the at least one hardware processor, cause the computing system to receive a database trigger definition for a database trigger for a first table, the database trigger comprising a condition, a first insert operation to be performed when the condition is satisfied, and a second insert operation to be performed when the condition is satisfied, wherein at least the first insert operation references a sequence, the sequence being incremented during trigger execution; computer-executable instructions that, when executed by the computing system, cause the computing system to receive a database operation that satisfies the condition; computer-executable instructions that, when executed by the computing system, cause the computing system to generate, in response to receiving the database operation that satisfies the condition, a second table comprising a first column, the first column comprising a first set of sequence values, the first set of sequence values ​​having a number of elements corresponding to a number of times the first insert operation and the second insert operation are to be performed as a result of the database operation; computer-executable instructions that, when executed by the computing system, cause the computing system, in response to receiving the database operation that satisfies the condition, to generate a third table comprising a second column, the second column comprising a second set of sequence values, the second set of sequence values ​​having a number of elements corresponding to the number of times the first insert operation and the second insert operation will be performed as a result of the database operation; computer-executable instructions that, when executed by the computing system, cause the computing system to perform the first insert operation in response to the database operation using the first table; as well as Computer executable instructions that, when executed by the computing system, cause the computing system to perform the second insert operation in response to the database operation using the second table.

20. The one or more non-transitory computer-readable storage media, wherein performing the first insert operation and performing the second insert operation are performed in parallel, further comprising: computer-executable instructions that, when executed by the computing system, cause the computing system to generate a first prepared statement corresponding to the first insert operation; computer-executable instructions that, when executed by the computing system, cause the computing system to generate a second prepared statement corresponding to the second insert operation; computer-executable instructions that, when executed by the computing system, cause the computing system to execute the first prepared statement using the second table; as well as Computer-executable instructions that, when executed by the computing system, cause the computing system to execute the second prepared statement using the third table.

Images