Optimizing a query using a non-covering join index

A database system for accessing a database, the database system including

• a massively parallel processing system including
• one or more nodes;
• a plurality of CPUs, each of the one or more nodes providing access to one or more CPUs;
• a plurality of virtual processes each of the one or more CPUs providing access to one or more processes;
• each process configured to manage data stored in one of a plurality of data-storage facilities;
• an optimizer for optimizing a plan for executing a query, the query including a FROM clause and a WHERE clause, the optimizer including
• a process for evaluating whether a non-covering join index partially but not completely covers the query, and if it does,
• adding the join index to the FROM clause of the query without removing the partially covered base tables;
• modifying the WHERE clause of the query by:
• (1) mapping a query condition to the join index for its partially covered base tables;
• and
• (2) adding a join back condition from the join index to a base table from which the join index was formed.

A computer program, stored on a tangible storage medium, for use in optimizing a query including a FROM clause and a WHERE clause, the program including executable instructions that cause a computer to

• evaluate whether a non-covering join index partially but not completely covers the query, and if it does
• add the join index to the FROM clause of the query without removing the partially covered base tables;
• modify the WHERE clause of the query by:
• (1) mapping a query condition to the join index for its partially covered base tables;
• and
• (2) adding a join back condition from the join index to a base table from which the join index was formed.