| How to use optimizer hint in a recursivly called procedure [message #249915] |
Fri, 06 July 2007 09:01  |
kmkan28
Messages: 14
Registered: December 2005
|
Junior Member |
|
|
hi all,
A recursive procedure is given here.Based on the condition it self the procedure called itself.my concern is how to set a optimizer hint in order to reduce the execution time.it works only for the little amount of data but not for huge volume of data.any one please assist me to get this problem resolved.
the procedure is
CREATE OR REPLACE PROCEDURE CHILD_TO_PARENT(P_ITEM_CODE VARCHAR2)
IS
M_LEVEL VARCHAR2(12);
M_PARENT_ITEM VARCHAR2(12);
M_PARENT_ITEM1 VARCHAR2(12);
M_CHILD_ITEM VARCHAR2(12);
M_NULL VARCHAR2(12);
M_DUMMY VARCHAR2(1) DEFAULT NULL;
CURSOR C1 IS
SELECT /*+ ALL_ROWS */ LEVEL BOM_LEVEL , BOM_ITEM_CODE BOM_PARENT_ITEM ,
BI_ITEM_CODE BOM_CHILD_ITEM
FROM ( SELECT BOM_CODE , BOM_ITEM_CODE ,
BI_ITEM_CODE , BI_BOM_CODE
FROM OM_BOM,OM_BOM_ITEM_DETAIL
WHERE BOM_CODE = BI_BOM_CODE
)
WHERE BI_BOM_CODE = BOM_CODE AND LEVEL=1
CONNECT BY PRIOR BI_ITEM_CODE = BOM_ITEM_CODE
START WITH BI_ITEM_CODE = P_ITEM_CODE
ORDER BY LEVEL;
CURSOR C2 IS
SELECT 'X' FROM OM_BOM_ITEM_DETAIL
WHERE BI_ITEM_CODE=M_PARENT_ITEM;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO M_LEVEL ,M_PARENT_ITEM,M_CHILD_ITEM;
IF C1%FOUND THEN
CHILD_TO_PARENT(M_PARENT_ITEM);
IF M_LEVEL=1 AND M_PARENT_ITEM IS NOT NULL THEN
OPEN C2;
FETCH C2 INTO M_DUMMY;
IF M_DUMMY IS NULL THEN
DBMS_OUTPUT.PUT_LINE('TRUE '||M_LEVEL||'--'||M_PARENT_ITEM||'--'||M_CHILD_ITEM);
INSERT INTO TEMP5 VALUES(M_PARENT_ITEM);
END IF;
CLOSE C2;
END IF;
ELSE
EXIT;
END IF;
END LOOP;
COMMIT;
CLOSE C1;
END;
/
|
|
|
|
|
|
| Re: How to use optimizer hint in a recursivly called procedure [message #250187 is a reply to message #249949] |
Mon, 09 July 2007 01:05  |
kmkan28
Messages: 14
Registered: December 2005
|
Junior Member |
|
|
micheal,
thanks for your reply.i just forget to mentioned the version of the oracle that i am using.the oracle version is 9i.
The procedure CHILD_TO_PARENT is called in side the program with the input of processed cursor value.
The value which is brought in to the procedure AGAIN execute the same procedure.
The Execution time is reduced after the use of hint.
Without the hint it will work but the Time to execute the procedure pays a lot time.that's why i requested the expert's suggestion.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
