[jira] [Created] (IMPALA-9974) Join elimination based on referential integrity

["Shant Hovsepian (Jira)" <ji...@apache.org>]

Shant Hovsepian created IMPALA-9974:
---------------------------------------

             Summary: Join elimination based on referential integrity
                 Key: IMPALA-9974
                 URL: https://issues.apache.org/jira/browse/IMPALA-9974
             Project: IMPALA
          Issue Type: Sub-task
          Components: Frontend
            Reporter: Shant Hovsepian


A join can be eliminated if the following conditions are met.

1. An FK/PK relationship exists between two joined table.

2. Only the PK columns are used and no other columns from the PK table.

3. The query predicates are conjunctive

4. The PK column can only appear in the equality predicate of the WHERE clause.

If the above conditions are met then the join can be eliminated, all PK column reference will be substituted by their FK counterparts, and an IS NOT NULL condition will be added for the FK columns.

This can be applied to INNER and OUTER joins. Assuming f.id -> d.id

SELECT f.* FROM fact f INNER JOIN dim d ON (f.id = d.id); 
=>
SELECT f.* FROM fact f WHERE f.id IS NOT NULL; 

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)