OraFAQ Forum: SQL & PL/SQL » Please provide best approach for my requirement.

Home » SQL & PL/SQL » SQL & PL/SQL » Please provide best approach for my requirement. (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please provide best approach for my requirement. [message #604561]

Wed, 01 January 2014 05:16

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi Experts ,

Please provide best approach for my requirement.

We have 30 schemas in our database.
We have to get data from the 2 tables data of 29 schemas and insert and update in 1 schema tables.
every hour will get the updated data.
Initially I have written one procedure to get data from all 29 schemas and scheduled
through one job to run every hour.
But the problem here is the query is fetching 8 lakhs records so the job is taking 6 hours to complete.
So that to improve the performance what I am thinking is create 29 jobs in all 29 schemas
to get data for all schemas separately.
Is it good approach will it cause any dead lock or resource busy error
since data will be inserting and updating in the same table(data is different) simultiniously.
While executing each schema procedure I will pass schema_id.It's different from every schema.

Is there any other best approach.

Procedure for SCHEMA_AT

CREATE OR REPLACE PROCEDURE CUST_TRANS_PROC(P_schema_id)
IS
V_date DATE;
schemaname VARCHAR2(30);
SELECT OWNER_NAME INTO schemaname FROM schema_table  WHERE schema_id = P_schema_id;
SELECT MAX(create_date) INTO V_DATE FROM MEDIS;
DELETE FROM SCHEMA_WS.cust_sale_rev WHERE lasT_update_date < V_DATE;
MERGE INTO SCHEMA_WS.cust_sale_rev target
USING (
SELECT 
    CS.CUST_ID CUST_ID,
    CS.CNAME  CNAME,
    SL.SALESREP  SALESREP,
    SL.SALESREP_ID SALESREP_ID,
    RV.TREVENUE TREVENUE
FROM SCHEMA_AT.CUSTOMER  CS
    LEFT OUTER JOIN SCHEMA_AT.SALES SL
    ON CS.CUST_ID = SL.CUST_ID
    WHERE CS.CREATION_DATE >V_DATE
    AND CS.schema_id = p_schema_id)source
    ON(source.CUST_ID = target.CUST_ID)
WHEN MATCHED THEN
UPDATE SET 
target.CNAME = source.CNAME,
target.SALESREP = source.SALESREP,
target.SALESREP_ID = source.SALESREP_ID, 
target.TREVENUE = source.TREVENUE,
WHEN NOT MATCHED THEN INSERT 
(target.CNAME,
target.SALESREP,
target.SALESREP_ID,
target.TREVENUE);
CUSTOMET_STAT;
END;


Procedure for SCHEMA_BT

CREATE OR REPLACE PROCEDURE CUST_TRANS_PROC(P_schema_id)
IS
V_date DATE;
schemaname VARCHAR2(30);
SELECT OWNER_NAME INTO schemaname FROM schema_table  WHERE schema_id = P_schema_id;
SELECT MAX(create_date) INTO V_DATE FROM MEDIS;
DELETE FROM SCHEMA_WS.cust_sale_rev WHERE lasT_update_date < V_DATE;
MERGE INTO SCHEMA_WS.cust_sale_rev target
USING (
SELECT 
    CS.CUST_ID CUST_ID,
    CS.CNAME  CNAME,
    SL.SALESREP  SALESREP,
    SL.SALESREP_ID SALESREP_ID,
    RV.TREVENUE TREVENUE
FROM SCHEMA_BT.CUSTOMER  CS
    LEFT OUTER JOIN SCHEMA_BT.SALES SL
    ON CS.CUST_ID = SL.CUST_ID
    WHERE CS.CREATION_DATE >V_DATE
    AND CS.schema_id = p_schema_id)source
    ON(source.CUST_ID = target.CUST_ID)
WHEN MATCHED THEN
UPDATE SET 
target.CNAME = source.CNAME,
target.SALESREP = source.SALESREP,
target.SALESREP_ID = source.SALESREP_ID, 
target.TREVENUE = source.TREVENUE,
WHEN NOT MATCHED THEN INSERT 
(target.CNAME,
target.SALESREP,
target.SALESREP_ID,
target.TREVENUE);
CUSTOMET_STAT;
END;


Script for CUSTOMET_STAT PROCEDURE.

PROCEDURE CUSTOMET_STAT(P_schema_id IN NUMBER, schemaname IN VARCHAR2)
IS
    v_sql         LONG;
BEGIN
    v_sql := 'MERGE INTO SCHEMA_WS.cust_sale_rev SR
    USING '||schemaname||'.CUSTOMER CS
    ON(
        SR.SR_ITEMNO = CS.ITEMNO
        AND SR.schema_id = CS.schema_id
    )
    WHEN MATCHED THEN
    UPDATE SET SR.SR_ITEM_STATUS = CS.ITEM_STATUS,
    SR.SR_LASTUPDATEDTIMESTAMP = SYSDATE
    WHERE CS.ITEM_ID > 0
    AND TRUNC(CS.ORDER_DATE) = TRUNC(sysdate)
    AND SR.IS_PROCESSED = ''N''
    AND SR.schema_id = '|| p_schema_id;
    EXECUTE IMMEDIATE v_sql;                                  
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;

Please help me.

Thanks.

Report message to a moderator