title: How to check if a set of values exist in item table in Oracle
body: <p>I have two table- 'Order' and 'Order Item'.</p>
  
  <ul>
  <li>Order table contains-
  Order Number, Order Date, etc.</li>
  <li>Order Item table contains-
  Order Number, Order Item Number, Product Name, etc.</li>
  </ul>
  
  <p>The joining condition between these two tables is on Order Number.</p>
  
  <p>In my target table I need orders and a flag. The flag should tell, if there is a predefined set of products which has been ordered as part of that order then it should be set to 'Yes'.</p>
  
  <p>E.g., Suppose an order 'ORD-01' contains three products in Order Item table - 'Mobile', 'PC' and 'Tablet', then my resulting table should contain Order Number as ORD-01 and Flag as 'Yes'.
  In the same way, if order 'ORD-02' contains only two prods 'Mobile' an 'Tablet', then the resulting table should contains 'ORD-02' and Flag 'No'.
  Similarly, if order 'ORD-03' contains three different prods '<strong>Notebook</strong>', 'PC' an 'Tablet', then the resulting table should contains 'ORD-03' and Flag 'No'.</p>
  
  <p>As per my understanding, I have written below query-</p>
  
  <pre><code>SELECT order_number,(SELECT CASE WHEN COUNT(DISTINCT product_name)&gt;=3     
  THEN 'Yes' ELSE 'No' END Prod_Flag 
  FROM order_item b
  WHERE a.order_number=b.order_number
  AND b.product_name IN ('Mobile','PC','Tablet'))
  FROM order a
  WHERE order_date&gt;last_run_date;
  </code></pre>
  
  <p>But it takes too much of time, as the order item is a very big table (>1 Billion rows). However I need incremental data based upon order date from Order table. Even if there is an index of order number in both tables, it takes time.</p>