Manipulating a table with an XML column in a database

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 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.

A computer program product 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 program product comprising a computer readable storage medium having program instructions embodied therewith, the program instructions executable by a processor to cause the processor to:

• reorganize 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;
• create 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;
• receive 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;
• parse the SQL statement;
• transform 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
• execute 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.