US 11,860,939 B2
Manipulating a table with an XML column in a database
Xin Peng Liu, Beijing (CN); Shuo Li, Beijing (CN); Xiaobo Wang, Beijing (CN); ShengYan Sun, Beijing (CN); and Kwai Hing Man, Fremont, CA (US)
Assigned to International Business Machines Corporation, Armonk, NY (US)
Filed by INTERNATIONAL BUSINESS MACHINES CORPORATION, Armonk, NY (US)
Filed on May 9, 2019, as Appl. No. 16/407,220.
Prior Publication US 2020/0356594 A1, Nov. 12, 2020
Int. Cl. G06F 16/832 (2019.01); G06F 16/81 (2019.01); G06F 16/22 (2019.01); G06F 16/835 (2019.01)
CPC G06F 16/832 (2019.01) [G06F 16/221 (2019.01); G06F 16/2282 (2019.01); G06F 16/81 (2019.01); G06F 16/8373 (2019.01)] 20 Claims
OG exemplary drawing
 
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.