The other option is to try UNION ALL or UNION depending on the nature of the result set
SELECT rs.col1, rs,col2 , FROM ( SELECT t.col1, t.col2, .. FROM t WHERE t.update_time > '2015-05-04' UNION ALL SELECT t8.col1, t8.col2,.. FROM t8 WHERE length(t8.end_user_id) > 0 ) rs This may work. HTH Mich Talebzadeh http://talebzadehmich.wordpress.com Author of the books "A Practitioners Guide to Upgrading to Sybase ASE 15", ISBN 978-0-9563693-0-7. co-author "Sybase Transact SQL Guidelines Best Practices", ISBN 978-0-9759693-0-4 Publications due shortly: Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and Coherence Cache Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume one out shortly NOTE: The information in this email is proprietary and confidential. This message is for the designated recipient only, if you are not the intended recipient, you should destroy it immediately. Any information in this message shall not be understood as given or endorsed by Peridale Ltd, its subsidiaries or their employees, unless expressly so stated. It is the responsibility of the recipient to ensure that this email is virus free, therefore neither Peridale Ltd, its subsidiaries nor their employees accept any responsibility. -----Original Message----- From: Gopal Vijayaraghavan [mailto:go...@hortonworks.com] On Behalf Of Gopal Vijayaraghavan Sent: 11 May 2015 18:14 To: user Cc: r7raul1...@163.com Subject: Re: hive sql on tez run forever Hi, > I change the sql where condition to (where t.update_time >= >'2015-05-04') , the sql can return result for a while. Because >t.update_time > >= '2015-05-04' can filter many row when table scan. But why change >where condition to > (where t.update_time >= '2015-05-04' or length(t8.end_user_id)>0) ,the >sql run forever as follows: The OR clause is probably causing the problems. We¹re probably not pushing down the OR clauses down to the original table scans. This is most likely a hive PPD miss where you do something like select a.*,b.* from a,b where a.x = b.x and (a.y = 1 or b.z = 1); where it doesn¹t get planned as select a1.*, b1.* from (select a.* from a where a.y=1) a1, (select b.* from b where b.z = 1) b1 where a1.x = b1.x; instead gets planned as a full-scan JOIN, then a filter. Can you spend some time and try to rewrite down your case to something like the above queries? If that works, then file a JIRA. Cheers, Gopal
