title: Oracle - Query Optimization - Query runs for a long time
body: <p>I have a oracle query which is executed once a month to get the order details processed. This query is taking a painfully lot of time to execute. ( More than thirty mins ). Therefore I am trying to optimize this. I have a decent knowledge in Oracle and I will explain what I have tried so far. Still, it takes around 20 minutes to complete. This is the query. Oracle version is 11g.</p>
  
  <pre><code>SELECT store_typ, store_no, COUNT(order_no) FROM
  (
      SELECT DISTINCT(order_no), store.store_no, store.store_typ FROM 
      (
          SELECT trx.order_no,trx.ADDED_DATE, odr.prod_typ, odr.store_no FROM daily_trx trx 
          LEFT OUTER JOIN 
          (
              SELECT odr.order_no,odr.prod_typ,prod.store_no FROM order_main odr 
              LEFT OUTER JOIN ORDR_PROD_TYP prod
              on odr.prod_typ = prod.prod_typ  
          ) odr
          ON trx.order_no=  odr.order_no
      ) daily_orders ,  
      (SELECT store_no,store_typ FROM main_stores ) store
  
      WHERE 1=1 
      and daily_orders.order_no !='NA'
      and store.store_no = daily_orders.store_no
      AND to_timestamp(to_char(daily_orders.ADDED_DATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') &gt;= to_date('01-05-2020 00:00:00','DD-MM-YYYY HH24:MI:SS')
      AND to_timestamp(to_char(daily_orders.ADDED_DATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') &lt;= to_date('31-05-2020 23:59:59','DD-MM-YYYY HH24:MI:SS')
  )
  GROUP BY store_typ, store_no
  </code></pre>
  
  <p>Background </p>
  
  <ul>
  <li>order_main - This table has over 4 million records</li>
  <li>I introduced index for order_no column which reduced time to execute.</li>
  </ul>
  
  <p>My questions are as follows. </p>
  
  <p>1) Will it help if I move date validation inside the inner query like this ? </p>
  
  <pre><code>SELECT store_typ, store_no, COUNT(order_no) FROM
  (
      SELECT DISTINCT(order_no), store.store_no, store.store_typ FROM 
      (
          SELECT trx.order_no,trx.ADDED_DATE, odr.prod_typ, odr.store_no FROM daily_trx trx 
  
          LEFT OUTER JOIN 
          (
              SELECT odr.order_no,odr.prod_typ,prod.store_no FROM order_main odr 
              LEFT OUTER JOIN ORDR_PROD_TYP prod
              on odr.prod_typ = prod.prod_typ  
          ) odr
          ON trx.order_no=  odr.order_no
          WHERE  to_timestamp(to_char(daily_orders.ADDED_DATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') &gt;= to_date('01-05-2020 00:00:00','DD-MM-YYYY HH24:MI:SS')
          AND to_timestamp(to_char(daily_orders.ADDED_DATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') &lt;= to_date('31-05-2020 23:59:59','DD-MM-YYYY HH24:MI:SS')
      ) daily_orders ,  
      (SELECT store_no,store_typ FROM main_stores ) store
  
      WHERE 1=1 
      and daily_orders.order_no !='NA'
      and store.store_no = daily_orders.store_no
      --AND to_timestamp(to_char(daily_orders.ADDED_DATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') &gt;= to_date('01-05-2020 00:00:00','DD-MM-YYYY HH24:MI:SS')
      --AND to_timestamp(to_char(daily_orders.ADDED_DATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') &lt;= to_date('31-05-2020 23:59:59','DD-MM-YYYY HH24:MI:SS')
  )
  GROUP BY store_typ, store_no
  </code></pre>
  
  <p>2) Could someone please suggest any other improvements that can be done to this query? </p>
  
  <p>3) Additional indexing would help in any other tables / columns ? Only daily_trx  and order_main tables are the tables that contains huge amount of data. </p>