Vineet Garg created HIVE-24087:
----------------------------------
Summary: FK side join elimination in presence of PK-FK constraint
Key: HIVE-24087
URL: https://issues.apache.org/jira/browse/HIVE-24087
Project: Hive
Issue Type: Improvement
Components: Query Planning
Reporter: Vineet Garg
Assignee: Vineet Garg
If there is PK-FK join FK join could be eliminated by removing FK side if
following conditions are met
* There is no row filtering on FK side.
* No columns from FK side is required after JOIN.
* FK join columns are guranteed to be unique (have group by)
* FK join columns are guranteed to be NOT NULL (either IS NOT NULL filter or
constraint)
*Example*
{code:sql}
EXPLAIN
SELECT customer_removal_n0.*
FROM customer_removal_n0
JOIN
(SELECT lo_custkey
FROM lineorder_removal_n0
WHERE lo_custkey IS NOT NULL
GROUP BY lo_custkey) fkSide ON fkSide.lo_custkey =
customer_removal_n0.c_custkey;
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
