Browse Prior Art Database

Method And System For Eliminating Columns From Temporary Tables During Query Execution

IP.com Disclosure Number: IPCOM000235016D
Publication Date: 2014-Feb-24
Document File: 4 page(s) / 175K

Publishing Venue

The IP.com Prior Art Database

Abstract

A method and system is disclosed for eliminating columns from temporary tables during query execution. The method and system determines when the temporary table is created and then eliminates non-referenced columns from the temporary table.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 44% of the total text.

Page 01 of 4

Method And System For Eliminating Columns From Temporary Tables During Query Execution

Disclosed is a method and system for eliminating columns from temporary tables during query execution.

The method and system ensures that whenever a Structured Query Language (SQL) optimizer creates a run time temporary table, a scan thru operation is performed for all column references to the temporary table in a parent query. Here, the parent query includes one or more of, but not limited to, an in projection, a where clause, a group by clause and an order clause. In addition, the method and system eliminates non-referenced columns from the temporary table in response to the SQL optimizer creating the run time temporary table. Further, the method includes a step of adjusting a column offset and projection maps. In some cases, the columns are projected multiple times and materialized multiple times before projecting required columns due to a model hierarchy. Therefore, there is a need of a recursive second step for eliminating the non-referenced columns.

In the recursive second step, the non-referenced columns are determined at each level of materialization and corresponding information is pushed down to a lower level of materialization. There are certain exceptions to a rule of pushing down the corresponding information, such as, when there are groupings or aggregates in one

level of materialization, then corresponding columns are projected even if they are not referenced later on.

Fig.1 illustrates a block diagram for executing a query which is based on multiple nested views V1 to V6.

Figure 1

1


Page 02 of 4

As shown in fig.1, each view is complex and requires materialized temporary table to process a result set. At the first stage, shown in yellow block, only 3 columns vc1, vc2 and vc3 are required from the parent query which is using view V1. At a prepare time, there is no information on required projected columns at every level and these columns are maintained till end of execution of the query. Columns shown in red color represent the non-referenced columns at every level that contribute to a larger materialized temporary table size.

Fig. 2 illustrates a block diagram of executing the query and obtaining an optimized column map that shows eliminated non-referenced columns of the temporary table at every level of the query.

Figure 2

A server, for instance, but not limited to, an Informix server* identifies and removes the non-referenced columns and keeps only required projected columns at each query level. The required projected columns are shown in green color in fig.2. For the required projected columns, a query performance is gained based on a reduced column size for materialized temporary tables from V1 to V6. The server dynamically takes a decision to eliminate these columns at execution time.

In an exemplary scenario, the method and system utilizes an algorithm to eliminate the non-referenced columns. A query with multiple nested le...