Hello,
I have following four tables.
/* There are other columns as well but just using the relevent columns.*/
CREATE TABLE COMPANY_ORDERS
(
INTERNALORDERID NUMBER(10) NOT NULL,
ORDERENTRYDATE DATE,
SYSTEMID NUMBER(10)
)
/* There are other columns as well but just using the relevent columns.*/
/* The data in archive table goes after each three months.*/
CREATE TABLE COMPANY_ORDERS_ARCH
(
INTERNALORDERID NUMBER(10) NOT NULL,
ORDERENTRYDATE DATE,
SYSTEMID NUMBER(10)
)
/* There are other columns as well but just using the relevent columns.*/
CREATE TABLE COMPANY_ORDER_HISTORY
(
INTERNALORDERID NUMBER(10) NOT NULL,
ITEMORDER NUMBER(10) NOT NULL,
ENTRYDATE DATE NOT NULL,
UPDATEDATE DATE,
STATUSID NUMBER(10),
INTERNALSTATUSID NUMBER(10)
)
/* The data in archive table goes after each three months.*/
CREATE TABLE COMPANY_ORDER_HISTORY_ARCH
(
INTERNALORDERID NUMBER(10) NOT NULL,
ITEMORDER NUMBER(10) NOT NULL,
ENTRYDATE DATE NOT NULL,
UPDATEDATE DATE,
STATUSID NUMBER(10),
INTERNALSTATUSID NUMBER(10)
)
The data for TABLE COMPANY_ORDER_HISTORY or TABLE COMPANY_ORDER_HISTORY_ARCH can be used as below:
SET DEFINE OFF;
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179058, 8, TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 1360);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179058, 10, TO_DATE('08/03/2009 11:42:16', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:42:16', 'MM/DD/YYYY HH24:MI:SS'), 7, 1430);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179180, 1, TO_DATE('08/03/2009 10:21:30', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:21:30', 'MM/DD/YYYY HH24:MI:SS'), -3, -3);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179180, 3, TO_DATE('08/03/2009 10:25:06', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:25:06', 'MM/DD/YYYY HH24:MI:SS'), 0, 0);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179180, 14, TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 1360);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179180, 16, TO_DATE('08/03/2009 11:42:17', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:42:17', 'MM/DD/YYYY HH24:MI:SS'), 7, 1430);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179184, 1, TO_DATE('08/03/2009 10:21:33', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:21:33', 'MM/DD/YYYY HH24:MI:SS'), -3, -3);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179184, 3, TO_DATE('08/03/2009 10:25:09', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:25:09', 'MM/DD/YYYY HH24:MI:SS'), 0, 0);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179184, 14, TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 1360);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179184, 16, TO_DATE('08/03/2009 11:42:18', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:42:18', 'MM/DD/YYYY HH24:MI:SS'), 7, 1430);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179185, 1, TO_DATE('08/03/2009 10:21:33', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:21:33', 'MM/DD/YYYY HH24:MI:SS'), -3, -3);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179185, 3, TO_DATE('08/03/2009 10:25:09', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:25:09', 'MM/DD/YYYY HH24:MI:SS'), 0, 0);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179185, 14, TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 1360);
Insert into COMPANY_ORDER_HISTORY
(INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
Values
(8179185, 16, TO_DATE('08/03/2009 11:42:17', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:42:17', 'MM/DD/YYYY HH24:MI:SS'), 7, 1430);
COMMIT;
Everyday a number of order is raised via different system (identified by COMPANY_ORDERS.SYSTEMID). Now I have to find per day average time an order took, per SYSTEMID to reach to a final statusid 7 (x), internalstausid 1430(y) from its first entrytime into COMPANY_ORDER_HISTORY (i.e For an orderid, the first entry in COMPANY_ORDER_HISTORY based in updatedate or For an orderid, or the entrytime for an orderid when ITEMORDER =1)
Archive tables can be joined only if the duration false in the period for which orders are archived.
Another suggestion seeked is Archive table is very big.
Will it be advisable to create a materialized view from the archived table having only last month data and then join the query to Materialized view (since the statistics needs to be shown only for last one year...)?
Regards
Edited by: A R on Oct 28, 2009 6:02 AM
Edited by: A R on Oct 28, 2009 2:13 PM