International Business Machines Corporation

ShengYan Sun

Shuo Li

Xiaobo Wang

Peng Hui Jiang

Hong Mei Zhang

Hao Feng

Xin Yang

Xin Peng Liu

Xiao Xiao Chen

Ming Wang

Jia Tian Zhong

Jing Wang

Ping Wang

Yuan Wang

Xiao Ming Ma

Ke Wei Wei

Meng Wan

Hui Jiang

Jin Fang Yang

Fang Chen

Kwai Hing Man

Jin Rong Wang

Ying Ying Xu

Ya Nan Mo

Yan Peng Guo

Ye Tao

Xiao Hui Xh Wang

Yu Han Zhang

Methods that provide dynamic index management for a set of computing storage resources are disclosed herein. One method includes collecting, by a processor, a set of current performance data for a set of storage resources storing data and implementing a set of indexes for the data stored on the set of storage resources based on an optimized performance predicted for the set of storage resources based on the collected set of current performance data and a set of predicted performance data that identifies the set of indexes. Also disclosed herein are apparatus, systems, and computer program products that can include, perform, and/or implement the methods for providing dynamic index management for a set of computing storage resources.

Dynamic index management for computing storage resources

Methods and systems for reducing database maintenance effort are disclosed. A method includes: collecting, by a computing device, a history of statistics for database objects; predicting, by the computing device, statistics using the history of statistics; testing, by the computing device, a workload using the predicted statistics; determining, by the computing device, at least one database object to maintain based on the testing the workload; and maintaining, by the computing device, the determined at least one database object.

Reducing database maintenance effort

Methods, computer program products, and systems are presented. The methods include, for instance: receiving a request for a lock on a page from a virtual database amongst two or more virtual databases, the virtual database including a number of containers respectively corresponding to the same number of database components of the virtual database. A copy of the page is refreshed with a latest copy of the page in an overall cache prior to granting the lock based on ascertaining that the page is not locked by any other virtual database. The virtual database is granted with the lock and have an exclusive access to the page.

Container-based virtualization for testing database system

A method, system, and computer program product for providing data security through database map restructuring are provided. The method identifies related data tables within a set of data tables within a database. The method identifies a set of related data types within the related data tables. A set of similarities is determined among the set of related data types. The related data types are mapped based on the set of similarities. Based on the mapping, method restructures one or more data tables of the set of data tables to generate a restructured data table.

Database map restructuring for data security

Embodiments of the disclosure provide methods, systems, computer program products for manipulating a table with an XML column in database. According to the method, a SQL statement for a table is received first, wherein the table comprises an XML column which is a representation of a plurality of logical columns of the table, there is at least one XML element in at least one row and in the XML column of the table, and each of the at least one XML element corresponds to a non-null value in a specific row and in a logical column of the plurality of logical columns of the table. Then the SQL statement is parsed. And then the SQL statement is transformed into a hybrid statement being able to process the XML column in response to the parsing result indicating that the SQL statement relates to at least one logical column of the plurality of logical columns. At last the hybrid statement is executed.

Manipulating a table with an XML column in a database

Query content-based data generation includes obtaining a query having an outer query and one or more subqueries, converting predicates of a subquery to be predicates of the outer query, and thereby obtain a transformed query, generate from the transformed query a query block having a list of predicate(s) selected from the transformed query, obtaining column information based on the list of predicate(s) and using the column information to select data for a dataset such that data records from the dataset are produced as results to executing the obtained query against the dataset.

Query content-based data generation

A DBMS receives a database-access request that includes an instruction to non-destructively read a database table row. The DBMS assigns the request a TSN identifier and creates a TSN image that identifies all TSNs assigned to transactions that are not yet committed. The DBMS traverses a linked list of log entries that identifies a chronological history of transactions performed on the same row. The DBMS infers that the table row currently contains data stored by the most recently logged transaction that is not contained in the TSN image and that has thus been committed. The DBMS then continues to process statements of the transaction based on the assumption that the row contains the inferred value. The DBMS performs this procedure without acquiring a shared lock on the data page or on the index leaf page that points to the table row.

Preventing DBMS deadlock by eliminating shared locking

In an approach, a processor, responsive to requesting to perform a plurality of applications including a first application and a second application, determines that the first application and the second application have been performed sequentially during a previous time period. A processor, responsive to determining that the first and second applications have been performed in sequence during the previous time period, obtains, a first set of database operations associated with the first application and a second set of database operations associated with the second application. A processors, responsive to determining that the first set of database operations and the second set of database operations are free of conflict, generates an execution schedule indicating that the first application and the second application are to be performed in parallel. A processors performs the plurality of applications based on the execution schedule.

Parallel execution of applications

An approach is provided for evaluating a performance of a query. A risk of selecting a low performance access path for a query is determined. The risk is determined to exceed a risk threshold. Based on the risk exceeding the risk threshold and using a machine learning optimizer, first costs of access paths for the query are determined. Using a cost-based database optimizer, second costs of the access paths are determined. Using a strong classifier operating on the first costs and the second costs, a final access path for the query is selected from the access paths.

Evaluating query performance

In an approach for database index optimization, a processor detects a pattern of characters and numeric digits of an index key. The index key provides an access to a database through an index access without a sorting operation. A processor determines a converting format based on the pattern of the index key. The pattern includes a string of the numeric digits. A processor converts the index key into a converted key by converting the string of the numeric digits into an integer. A processor saves the converted key into a storage.

Database index optimization

Disclosed is a computer-implemented method to adjust partition keys. The method includes identifying a target table that is a target of a query, the target table including a set of initial partitions. The method also includes determining a set of common queries, wherein each of the common queries are configured to retrieve data from the target table. The method further includes identifying a plurality of core ranges. The method includes merging the core ranges into a new set of partitions. The method further includes setting, in response to the merging, updated partition keys. Further aspects of the present disclosure are directed to systems and computer program products containing functionality consistent with the method described above.

Partition key adjustment based on query workload

An approach is provided for optimizing a join operation that includes receiving, by one or more processors of a computer system, a join request associated with a fact table and a plurality of related dimension tables; identifying, by the one or more processors of the computer system, a join relationship from the fact table and the plurality of related dimension tables; matching, by the one or more processors of the computer system, different tables of the fact table and the plurality of related dimension tables; filtering, by the one or more processors of the computer system using data parallelism, the fact table and the plurality of related dimension tables, wherein the filtering occurs prior to performing the join request; and performing, by the one or more processors of the computer system, the join operation pursuant to the join request.

Optimizing a join operation

Functional verification of a new database feature is provided. A set of SQL query seeds are evolved using information contained in a knowledge map that was generated based on a real SQL query workload of an entity corresponding to a database. Real SQL queries are identified in the database that are similar to the evolved set of sample SQL query seeds. A new feature to be implemented in the database is injected into the real SQL queries that are similar to the evolved set of sample SQL query seeds. Multiple runtime access paths are generated for each of the real SQL queries injected with the new feature for output cross validation. Each of the real SQL queries injected with the new feature is run in a test environment using each of the multiple runtime access paths corresponding to a real SQL query injected with the new feature for functional verification.

Structured query language runtime access path generation for deep test in a database

Embodiments are disclosed for a method for dynamic access paths. The method includes generating real-time statistics (RTS) estimates based on a log of a database. Further, the method includes generating access paths based on a structured query language command and the RTS estimates. The method also includes training a machine learning model to map the RTS estimates to the access paths.

Growing community of inventors

Beijing, China

ShengYan Sun