Mailing-List: contact dev-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hive.apache.org
Received-SPF: pass (athena.apache.org: domain of
 sukhendu.chakraborty@gmail.com designates 209.85.192.174 as permitted sender)
MIME-Version: 1.0
Date: Tue, 3 Dec 2013 17:45:11 -0800
Message-ID: 
 <CADgokzHar7cRckiSpw=7HkBdfJB7xUf-WqTJ61afJ=n_FWuTQw@mail.gmail.com>
Subject: map join in subqueries
From: Sukhendu Chakraborty <sukhendu.chakraborty@gmail.com>
To: hive-dev@hadoop.apache.org
Content-Type: multipart/alternative; boundary=047d7bd8fc224b592d04ecab9010

--047d7bd8fc224b592d04ecab9010
Content-Type: text/plain; charset=ISO-8859-1

Hi,

Is there anyway mapjoin works on the subquery(not the underlying table). I
have the following query:

select external_id,count(category_id) from
catalog_products_in_categories_orc pc inner join (select * from
catalog_products_orc where s_id=118) p on pc.product_id=p.id   group by
external_id;


Now, even though catalog_products_orc is a big table, after filtering
(s_id=118) it results in very few number of rows which can be easily
optimized to a mapjoin (with catalog_products_in_categories_orc as the big
table and the subquery result as the small table) . However, when I try to
specify /*+MAPJOIN(p)*/ to enforce this, it results in a mapjoin for the
table catalog_products_orc (and not on the subquery after filtering).

Any ideas to achieve mapjoin on a subquery (and not the underlying table)?


-Sukhendu

--047d7bd8fc224b592d04ecab9010--