Teradata Us, Inc.

Manjula Koppuravuri

Bhashyam Ramesh

Douglas Paul Brown

Jeetendra Chaware

Chandrasekhar Tekur

Donald Raymond Pederson

Michael Warren Watzke

Rangavajjula K N Sai Krishna

James Ronald Carlson

In some examples, a database system includes a storage medium to store a materialized view (MV) that includes data satisfying an MV condition. At least one processor is to receive a query including a query condition, determine that the query condition partially matches the MV condition, and access a part of the data in the MV partially satisfy the query.

Using materialized views to respond to queries

A database table is defined as a load isolated table (LDI table) that supports a current table version number, multiple row versions, and special load protocols. An isolated loading statement on the LDI table is identified in a Data Manipulation Language (DML), causing the table to be placed in an isolated loading state associated with an incremented new value of the table's current version number. A designated group of DML operations in one or more sessions are then used to load/modify rows in the table, and affected rows are marked with the uncommitted new version. Read-committed queries supported for LDIs ignore the uncommitted rows during an isolated load, seeing only previously committed versions of the rows. When an end isolated loading on the table is identified, the table's version is updated to the new incremented value, causing the newly loaded/modified rows to be recognized as committed by subsequent read-committed queries.

Load committed isolation processing

A method, database system and computer program are disclosed for optimizing a SQL query, in which the SQL query seeks to aggregate temporal database information. The method includes determining whether two rows of information have a common grouping value, and if so, determining both temporal overlap and temporal non-overlap components of the two rows, aggregating each of the temporal overlap components of the two rows, and separating the temporal non-overlap components of the two rows.

Method and system for temporal aggregation

A SEQUENCED request a) to apply a temporal Source table to a temporal Target table under a predicate is received. The Source table includes a plurality of rows that qualify to apply to a single Target table row under the predicate. The predicate specifies a Source table join column. Each of the plurality of Source table rows and the single Target table row include a ValidTime dimension. The plurality of Source table rows is b) ordered by the Source table join column and a period-to-be-modified. The next of the ordered plurality of Source table rows is c) applied by determining that the Target table row has a TransactionTime and, in response, closing out the TransactionTime of the Target table row. Applying the next of the ordered plurality of Source table rows includes identifying an RDIFF range as the period-to-be-modified of the Source table row being applied that is later than the ValidTime range of the single Target table row. Applying the next of the ordered plurality of Source table rows includes creating an RDIFF row being the same as the Target table row except the ValidTime of the RDIFF row is set to RDIFF range. A second of the ordered plurality of Source table rows d) is applied by applying c) to the RDIFF row as the Target table row.

Sequenced modifications of multiple rows in a temporal table

A method, computer program, and database system are disclosed. A plurality of instances of a request to a database system are received. All instances of the request have a same set of variables to use in executing the request. The values of the variables are supplied by data in a data source. A specific plan for executing one instance of the request is generated. Generating the specific plan includes taking into account data in the data source. The specific plan is executed on the database system. A system run-time metric and a parsing time for executing the specific plan are captured and saved. A generic plan is generated and cached based on a comparison of the system run-time metric and the parsing time for executing the specific plan.

Deciding whether to cache a generic plan for executing a request based on a system performance metric

A method, system, and computer program for optimizing execution of a DML statement on a temporal database are disclosed. A first execution package is provided to an execution engine. The first execution package includes a plurality of operations to be executed if the contents of a row in a table in the temporal database indicate the row has a first temporal condition. A second execution package is provided to the execution engine. The second execution package includes a plurality of operations to be executed if the contents of the row in the table in the temporal database indicate the row has a second temporal condition. The execution engine is run to produce a result. The result is saved.

Optimizing DML statement execution for a temporal database

A base table temporal row affected by a transaction is timestamped. The temporal row has a row ID. The temporal row in the base table is modified according to the transaction. The row ID for the modified temporal row is associated with an identifier for the transaction in a temporal work table. Upon committing the transaction: the temporal work table is searched to determine the row ID of the temporal row modified by the transaction, and a commit-time is saved into the temporal row identified by the row ID.

Commit-time timestamping of temporal rows

A computer system programmed as a database receives a query Q. Qincludes a predicate. The predicate includes a reference to a current timestamp of time TT. The database has R data structures. The computer system creates a plan, Plan, to execute Q. Planincludes the identification of a first set of data structures, P, to be accessed in executing Plan, at the current timestamp of time TT. The number of data structures in Pis less than R. The computer system determines that Planis valid for a period of time. The computer system stores in a cache Planand an indication that Planis valid for the period of time.

Caching execution plans for queries that use timestamps

A method, database system and computer program are disclosed for performing a temporal join between two temporal database tables. The method includes determining a selected table intersection between a period of interest and a time period value for a selected table row, scanning an inner table for a row that matches the selected table on the join conditions and overlaps the selected table intersection, and determining the remainder time period values from the selected table intersection.

Method, database system and computer program for joining temporal database tables

An index selection mechanism allows for efficient generation of index recommendations for a given workload of a database system. The workload includes a set of queries that are used to access tables in a database system. The index recommendations are validated to verify improved performance, followed by application of the indexes. Graphical user interface screens are provided to receive user input as well as to present reports to the user.

Growing community of inventors

Secunderabad, India

Manjula Koppuravuri