| Procedure Performance [message #288535] |
Mon, 17 December 2007 23:41  |
Mannu
Messages: 4
Registered: December 2007
|
Junior Member |
|
|
Hi All,
Please help us in optimising the following query,
The record count in each of this table (A,B,C,G) is around 60 lakhs.
This query is basically reqd to fetch correct user details and store in table TAB1 which will ultimately help in providing security. This query takes around 1.5 to 2 hrs for each user and we'll have around 600 users to be uploaded every month.
The load frequency for thi table is monthly
CREATE OR REPLACE PROCEDURE SAMPLE
(USERID IN VARCHAR2)
AS
BEGIN
DELETE FROM TAB1 WHERE OPRID = USERID;
INSERT INTO TAB1
SELECT DISTINCT A.C1, A.C2, B.C3, C.C4
FROM
(
SELECT R.C1 C1, F.C2 C2
FROM R, F
WHERE
R.C1 = USERID
AND R.JOIN = F.JOIN
) A,
(
SELECT R.C1 C1, E.C3 C3
FROM R, E
WHERE
R.C1 = USERID
AND R.JOIN = E.JOIN
) B,
(
SELECT R.C1 C1, U.C4 C4
FROM R, U
WHERE
R.C1 = USERID
AND R.JOIN = U.JOIN
) C,
(
SELECT DISTINCT C2, C3, C4
FROM G
) G,
WHERE
A.C1 = B.C1
AND
B.C1 = C.C1
AND
A.C2 = G.C2
AND
B.C3 = G.C3
AND
C.C4 = G.C4;
COMMIT;
END;
Guess the logic is clear here, kindly help us in tuning this query for better performance
|
|
|
|
|
|
|
|
|
|
| Re: Procedure Performance [message #288540 is a reply to message #288535] |
Mon, 17 December 2007 23:55  |
Mannu
Messages: 4
Registered: December 2007
|
Junior Member |
|
|
Will look into the guide and get back,
As of now we jus ran this procedure which i had shared and it took around 1,5 to 2 hrs for each user ( as i had already mentioned)
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
