title: Avoid product join in teradata in sql
body: <p>I have below query in tera data which seems to be taking lot of CPU utilization, i am looking for some more alternatives to achieve the same in an efficient way. Please let me know if you have any thoughts</p>
  
  <pre><code> CREATE TABLE users AS (
   SELECT a.*, b.col1, b.col2, b.col3, b.col4
      FROM (SEL col1, col2, col3 AS c_id, id , col4, flight_id
      FROM t1
      WHERE flight_id IN (1234, 5678, 8910, 3456, 1265, 9876, 6544, 4321) 
      AND ym &lt;&gt; '201501') a
      LEFT JOIN t2 b
      ON a.c_id = b.customer_id)
  WITH DATA
  PRIMARY INDEX(id);
  </code></pre>
  
  <p>Now the no. of records in t1(with where conditions no of rows) and t2 are <strong>17,000,000</strong> and <strong>1,055,123,456</strong> respectively. The query took approx 10 mins but more challenge is it takes lot of CPU usage, I understand its because of product join that is being done. but i am not getting how to handle it by different way. Any inputs are much appreciated.</p>
  
  <p>As per suggestion, i have updated the query as shown below and i got quite better performance almost query time has been reduced to half. I have only one concern now i am getting message from TeraData Prod team which says my query is inefficient in terms of Inefficiency is expressed by the amount of CPU the system uses per I/O. I am not able to understand how i can make it efficient for it as i have such a huge dataset ?</p>
  
  <pre><code>CREATE TABLE users AS (
  SELECT a.col1, a.col2, a.c_id , a.id, a.col4, a.flight_id, b.col1, b.col2, 
   b.col3, b.col4 
  FROM t1 a
  LEFT JOIN t2 b
  ON a.c_id = b.customer_id
  WHERE flight_id IN (1234, 5678, 8910, 3456, 1265, 9876, 6544, 4321) AND ym 
   &lt;&gt; '201501' a)
  WITH DATA
  PRIMARY INDEX(id);
  </code></pre>