Manipulating a table with an XML column in a database

Patent metadata

Field	Value
Publication number	US-11860939-B2
Application number	US-201916407220-A
Country	US
Kind code	B2
Filing date	May 9, 2019
Priority date	May 9, 2019
Publication date	Jan 2, 2024
Grant date	Jan 2, 2024

Abstract

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.

First claim

What is claimed is:

1. A computer-implemented method to improve performance of database storage queries through optimization of a database storage for a plurality of logical columns by utilizing only non-null values in XML elements, the computer-implemented method comprising:
reorganizing, by one or more processors, a portion of the plurality of logical columns of a table in the database storage as an XML column of the table, wherein each logical column from the portion of the plurality of logical columns includes a plurality of null values and a plurality of non-null values, wherein the portion of the plurality of logical columns is a subset of logical columns from the plurality of logical columns;
creating, by one or more processors, a new table with a remaining portion of the plurality of logical columns and the XML column for the portion of the plurality of logical columns of the table, wherein creating the XML column consolidates each logical column from the portion of the plurality of logical columns to only include the plurality of non-null values;
receiving, by one or more processors, an SQL statement for the new table, wherein there is at least one XML element in at least one row and in the XML column of the new table, and each of the at least one XML element corresponds to a single non-null value from the plurality of non-null values in a specific row and in a logical column of the portion of the plurality of logical columns of the table;
parsing, by one or more processors, the SQL statement;
transforming, by one or more processors, the SQL statement into a hybrid statement that is able to process the at least one XML element in response to the parsing result indicating that the SQL statement relates to at least one logical column of the plurality of logical columns; and
executing, by one or more processors, the hybrid statement by accessing a plurality of pages with the plurality of logical columns of the new table from the database storage with a single I/O operation and omitting one or more JOIN operations for the plurality of logical columns of the new table.

2. The method of claim 1 , wherein the at least one XML element in the XML column and values in other columns of the new table are stored in a same table space in the database storage.

3. The method of claim 1 , wherein a relationship between the plurality of logical columns and the XML column is stored in a schema of the new table, and wherein the parsing of the SQL statement further comprises:
determining, by one or more processors, that the SQL statement relates to at least one logical column of the plurality of logical columns based on the schema of the new table.

4. The method of claim 1 , wherein the hybrid statement is a composition of part of the SQL statement and an XML expression in response to the SQL statement being further related to at least one of following SQL clauses or clause group: INSERT, SELECT, WHERE, UPDATE and SET, GROUP BY, GROUP BY and HAVING, and ORDER BY.

5. The method of claim 4 , wherein the XML expression is further obtained using at least one of following:
modifying, by one or more processors, the clause INSER in the SQL statement into a clause INSERT with XML format in the hybrid statement;
modifying, by one or more processors, the clause SELECT, clause group SELECT and GROUP BY, or clause group SELECT and ORDER BY in the SQL statement into an XMLQuery function or an XMLTable function in the hybrid statement;
modifying, by one or more processors, clause group UPDATE and SET in the SQL statement into an XMLModify function in the hybrid statement; and
modifying, by one or more processors, the clause WHERE or the clause HAVING in the SQL statement into an XMLExists function in the hybrid statement.

6. The method of claim 1 , wherein the transforming the SQL statement into a hybrid statement comprises:
modifying, by one or more processors, an index definition in the SQL statement into an XML, index function in the hybrid statement, wherein an Xpath is included in the XML, index function to specify which data from the XML column is used for an index key in the XML index.

7. The method of claim 6 , wherein a hybrid index for the new table is built in response to the execution of the hybrid statement comprising the XML index function, and wherein an entry of the hybrid index for the new table comprises row values in the other columns of the new table, XML value and row ID.

8. The method of claim 3 , wherein the transforming the SQL statement into a hybrid statement comprises:
in response to the parsing result further indicating that the SQL statement being related to creating the new table, obtaining, by one or more processors, the at least one logical column of the new table from the SQL statement, wherein each of the at least one logical column is indicated by an indicator in the SQL statement; and
modifying, by one or more processors, a column name of the related logical column of the new table in the SQL statement into a corresponding name of the XML column in the hybrid statement;
wherein the schema of the new table is created during the execution of the hybrid statement.

9. The method of claim 8 , wherein the transforming the SQL statement into a hybrid statement comprises:
in response to the parsing result further indicating that the SQL statement being related to altering at least one logical column of the new table, obtaining, by one or more processors, the at least one logical column of the new table to be altered in the SQL statement, wherein each of the at least one logical column is indicated by an indicator in the SQL statement; and
modifying, by one or more processors, the at least one logical column of the new table in the SQL statement into the XML column related to the at least one logical column of the new table to be altered in the hybrid statement;

wherein the schema of the new table is altered during the execution of the hybrid statement.

10. A system to improve performance of database storage queries through optimization of a database storage for a plurality of logical columns by utilizing only non-null values in XML elements, the system comprising:
one or more processors;
a memory coupled to at least one of the processors; and
a set of computer program instructions stored in the memory and executed by at least one of the processors in order to perform actions of:
reorganizing, by one or more processors, a portion of the plurality of logical columns of a table in the database storage as an XML column of the table, wherein each logical column from the portion of the plurality of logical columns includes a plurality of null values and a plurality of non-null values, wherein the portion of the plurality of logical columns is a subset of logical columns from the plurality of logical columns;
creating, by one or more processors, a new table with a remaining portion of the plurality of logical columns and the XML column for the portion of the plurality of logical columns of the table, wherein creating the XML column consolidates each logical column from the portion of the plurality of logical columns to only include the plurality of non-null values;
receiving, by one or more processors, an SQL statement for the new table, wherein there is at least one XML element in at least one row and in the XML column of the new table, and each of the at least one XML element corresponds to a single non-null value from the plurality of non-null values in a specific row and in a logical column of the portion of the plurality of logical columns of the table;
parsing, by one or more processors, the SQL statement;
transforming, by one or more processors, the SQL statement into a hybrid statemen

Assignees

• IBM

Inventors

• Liu Xin Peng
• Li Shuo
• Wang Xiaobo
• Sun Shengyan
• Man Kwai Hing

Classifications

• G06F16/832 Primary

  Query formulation · CPC title

• G06F16/221

  Column-oriented storage; Management thereof · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/81

  Indexing, e.g. XML tags; Data structures therefor; Storage structures · CPC title

• G06F16/8373

  Query execution · CPC title