SQL & PL/SQL

Hello,

I am in process of writing a query to calculate (daily/or weekly/or monthly)average time taken by orders raised from different system. Order creation date is in one table, and order finalization date is in another table.

Now both these tables are getting archived every three months, so in order to get the average for orders before these three months, i am forced to make an union of two archive tables for entry date, and two archives table for the end date. And this query is taking a lot of time to be executed. I am not a DB guy and have less idea of running the execution plan. Any help on this will be a great help for me.

My current query is as below:

SELECT DOP.SYSTEMID,AVG( H1.ENTRYDATE - DOP.ENTRYDATE) AS AVERAGETIME,
            TO_CHAR(DOP.ENTRYDATE ,'DD/MM/YY')  AS CREATIONDATE 
              FROM (
                 SELECT SYSTEMID,ENTRYDATE, INTERNALORDERID FROM ORDERS_TAB WHERE 
                    ENTRYDATE 
                    BETWEEN TO_DATE('01/10/2008','DD/MM/YY') 
                    AND TO_DATE('21/10/2009','DD/MM/YY') 
                 UNION  ALL 
                 SELECT SYSTEMID,ENTRYDATE, INTERNALORDERID FROM ORDERS_TAB_ARCH 
                    WHERE ENTRYDATE 
                    BETWEEN TO_DATE('01/10/2008','DD/MM/YY')
                    AND TO_DATE('21/10/2009','DD/MM/YY')
                 )  DOP,  
                 (
                 SELECT ENTRYDATE,INTERNALORDERID FROM ORDERS_HEAD_HISTORY 
                 WHERE 
                    ENTRYDATE >TO_DATE('01/10/2008','DD/MM/YY')  AND STATUSID = 7 
                 UNION ALL 
                 SELECT ENTRYDATE,INTERNALORDERID FROM ORDERS_HEAD_HIST_ARCH
                    WHERE ENTRYDATE >TO_DATE('01/08/2008','DD/MM/YY')  AND STATUSID = 7  
                 ) H1
                   WHERE DOP.INTERNALORDERID=H1.INTERNALORDERID
                   GROUP BY DOP.MASTERSYSTEMID, TO_CHAR(DOP.ENTRYDATE ,'DD/MM/YY')

Regards,

Edited by: A R on Oct 23, 2009 9:37 AM