title: How to reduce execution time of my query in Oracle database?
body: <p>I extracted those table names from different views and functions. Execution time takes 1day+ to finish. My goal is to reduce the time from 1day to 30minutes or less. My boss suggested me to fine tuned all the queries inside the views under views. Any tips or suggestions on how to reduce the time?</p>
  
  <p><strong>Query</strong></p>
  
  <pre><code>SELECT to_char(con.originalstartdate, 'MM/DD/YY') TXNDATEIN,
         to_char(con.originalstartdate, 'HH24:MI:SS') TIMEIN,
         to_char(CON.LASTACTIVITYDATE, 'MM/DD/YY') TXNDATEOUT,
         to_char(con.lastactivitydate, 'HH24:MI:SS') TIMEOUT,
         pack.mes_packagename, prodfam.productfamilyname,
         con.containername RELATEDCONTNAME, shd.qty QTYIN, 
         con.qty QTYOUT, qtyhist.qty, lossrea.lossreasonname,
         resdef.resourcename WS, emp.fullname EMPLOYEENAME,
         oprtn.operationname, hml.txndate,
         (   SELECT GET_DIFFUSION_CONTAINER_APP(con.containername) 
             FROM DUAL) AS DIFFUSION
  FROM container con, package pack, productfamily prodfam, 
       lossreason lossrea, resourcedef resdef, employee emp, 
       operation oprtn, qtyhistory qtyhist, splithistorydetails shd,
       historymainline hml
  where con.lastactivitydate &gt;=  to_date('06/23/2014 6:00:00', 'MM/DD/YYYY HH:MI:SS')
  and con.lastactivitydate &lt; to_date('06/24/2014 6:00:00', 'MM/DD/YYYY HH:MI:SS')
  and hml.txndate &gt;= to_date('06/23/2014 6:00:00', 'MM/DD/YYYY HH:MI:SS') 
  and to_char(hml.txndate, 'MM/DD/YYYY') &lt;= to_char(con.lastactivitydate, 'MM/DD/YYYY')
  ORDER BY RELATEDCONTNAME;
  </code></pre>
  
  <p><strong>ORIGINAL QUERY</strong></p>
  
  <pre><code>SET ECHO OFF
  SET COLSEP ,
  SET PAGES 0
  SET LINESIZE 1000
  SET TRIMSPOOL ON
  SET FEEDBACK OFF
  SET UNDERLINE OFF
  SET VER OFF
  
  column NCURRENT NEW_VALUE VFILE NOPRINT
  column NYEST NEW_VALUE VFILE2  
  column NNOW NEW_VALUE VFILE3  
  column NCURRENTYEAR NEW_VALUE VFILE4  
  column WEEKCODE NEW_VALUE VFILE5 
  
  select to_char(sysdate -2 , 'RRRR-MM-DD')||' 06:00:00' NYEST
  --select '2013-05-18 12:00:00' NYEST
  from dual;
  
  select to_char(sysdate-1, 'RRRR-MM-DD')||' 06:00:00' NNOW
  --select '2013-05-19 12:00:00' NNOW
  from dual;
  
  select to_char(sysdate, 'RRRR') NCURRENTYEAR
  from dual;
  
  select 'FName_'||to_char(sysdate-1, 'MONDDRRRR')||'.csv' NCURRENT
  from dual;
  
  spool [Filepath];
  
  select 
         TXNDATEIN||','||TIMEIN||','||TXNDATEOUT||','||TIMEOUT||','||  MES_PACKAGENAME||','||MINICOMPANYNAME||','||PRODUCTFAMILYNAME||','||RELATEDCONTNAME||','||
         QTYIN||','||QTYOUT||','||QTY||','||LOSSREASONNAME||','||WS||','||REPLACE(EMPLOYEENAME,',',' ')||','||OPERATIONNAME||','||
         (SELECT GET_DIFFUSION_CONTAINER_APP(RELATEDCONTNAME) FROM DUAL) AS DIFFUSION
  from 
   vw_power_overallyield_batchfe
  where 
  txndate &gt;=  to_date('&amp;&amp;VFILE2', 'yyyy-mm-dd HH24:MI:SS')
  and txndate &lt; to_date('&amp;&amp;VFILE3', 'yyyy-mm-dd HH24:MI:SS')
  and scraptxndate &gt;= to_date('&amp;&amp;VFILE2', 'yyyy-mm-dd HH24:MI:SS') 
  and to_char(scraptxndate, 'MM/DD/YY') &lt;= TXNDATEOUT 
  ORDER BY RELATEDCONTNAME;
  spool off;
  exit
  </code></pre>
  
  <ul>
  <li>"vw_power_overallyield_batchfe" has many views</li>
  </ul>
  
  <p><strong>EXPLAIN PLAN</strong></p>
  
  <pre><code>    PLAN_TABLE_OUTPUT
  
  ----------------------------------------------------------------------------------------------------------------
  | Id  | Operation                              | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)|
  ----------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                       |                          |    18E|    15E|       |    18E  (0)|
  |   1 |  FAST DUAL                             |                          |     1 |       |       |     2   (0)|
  |   2 |  SORT ORDER BY                         |                          |    18E|    15E|    15E|    18E  (0)|
  |   3 |   MERGE JOIN CARTESIAN                 |                          |    18E|    15E|       |    18E  (0)|
  |   4 |    MERGE JOIN CARTESIAN                |                          |    18E|    15E|       |    18E  (0)|
  |   5 |     MERGE JOIN CARTESIAN               |                          |    18E|    15E|       |   204P  (1)|
  |   6 |      MERGE JOIN CARTESIAN              |                          |    54T|  5457T|       |   521G  (1)|
  |   7 |       MERGE JOIN CARTESIAN             |                          |    84G|  7259G|       |   942M  (1)|
  |   8 |        MERGE JOIN CARTESIAN            |                          |    17M|  1258M|       |   210K  (1)|
  |   9 |         MERGE JOIN CARTESIAN           |                          |  8713 |   536K|       |    87   (3)|
  |  10 |          MERGE JOIN CARTESIAN          |                          |    16 |   816 |       |    16  (13)|
  |  11 |           MERGE JOIN                   |                          |     1 |    43 |       |    13  (16)|
  |  12 |            SORT JOIN                   |                          |     3 |    24 |       |     5  (20)|
  |* 13 |             INDEX RANGE SCAN           | HISTORYMLBYTXNDATEANDRES |     3 |    24 |       |     4   (0)|
  |* 14 |            SORT JOIN                   |                          |     4 |   140 |       |     8  (13)|
  |  15 |             TABLE ACCESS BY INDEX ROWID| CONTAINER                |     4 |   140 |       |     7   (0)|
  |* 16 |              INDEX RANGE SCAN          | CONTAINERBYLADATE        |     5 |       |       |     3   (0)|
  |  17 |           BUFFER SORT                  |                          |    25 |   200 |       |     9  (23)|
  |  18 |            TABLE ACCESS FULL           | PACKAGE                  |    25 |   200 |       |     3   (0)|
  |  19 |          BUFFER SORT                   |                          |   552 |  6624 |       |    84   (3)|
  |  20 |           TABLE ACCESS FULL            | RESOURCEDEF              |   552 |  6624 |       |     4   (0)|
  |  21 |         BUFFER SORT                    |                          |  2020 | 24240 |       |   210K  (1)|
  |  22 |          TABLE ACCESS FULL             | PRODUCTFAMILY            |  2020 | 24240 |       |    24   (0)|
  |  23 |        BUFFER SORT                     |                          |  4814 | 81838 |       |   942M  (1)|
  |  24 |         TABLE ACCESS FULL              | EMPLOYEE                 |  4814 | 81838 |       |    54   (2)|
  |  25 |       BUFFER SORT                      |                          |   638 | 12122 |       |   521G  (1)|
  |  26 |        TABLE ACCESS FULL               | OPERATION                |   638 | 12122 |       |     6   (0)|
  |  27 |      BUFFER SORT                       |                          |   411K|  2009K|       |   204P  (1)|
  |  28 |       TABLE ACCESS FULL                | SPLITHISTORYDETAILS      |   411K|  2009K|       |  3785   (1)|
  |  29 |     BUFFER SORT                        |                          |  1171K|  5720K|       |    18E  (0)|
  |  30 |      TABLE ACCESS FULL                 | QTYHISTORY               |  1171K|  5720K|       |  4690   (1)|
  |  31 |    BUFFER SORT                         |                          |  1382 | 13820 |       |    18E  (0)|
  |  32 |     TABLE ACCESS FULL                  | LOSSREASON               |  1382 | 13820 |       |     4   (0)|
  ----------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
    13 - access("HML"."TXNDATE"&gt;=TO_DATE(' 2014-06-23 06:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
                "HML"."TXNDATE" IS NOT NULL)
    14 - access(TO_CHAR(INTERNAL_FUNCTION("HML"."TXNDATE"),'MM/DD/YYYY')&lt;=TO_CHAR(INTERNAL_FUNCTION("CON".
                "LASTACTIVITYDATE"),'MM/DD/YYYY'))
         filter(TO_CHAR(INTERNAL_FUNCTION("HML"."TXNDATE"),'MM/DD/YYYY')&lt;=TO_CHAR(INTERNAL_FUNCTION("CON".
                "LASTACTIVITYDATE"),'MM/DD/YYYY'))
    16 - access("CON"."LASTACTIVITYDATE"&gt;=TO_DATE(' 2014-06-23 06:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
                "CON"."LASTACTIVITYDATE"&lt;TO_DATE(' 2014-06-24 06:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  
  Note
  -----
     - 'PLAN_TABLE' is old version
  
  enter code here
  </code></pre>