| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Possible Tune-up?
Peace all,
This is actually my friend's question. He is the developer here. The main problem is the PL/SQL script written below. It will be very slow when trying to fetch a lot of rows, in the range of thousands.
The Oracle version is still 8.1.7 on AIX 4.3.
v_PREQ_STK_CODE_221 msf221.PREQ_STK_CODE%TYPE;
v_PREQ_STK_CODE_OLD msf221.PREQ_STK_CODE%TYPE;
v_po_no_221 msf221.po_no%TYPE;
v_po_item_no_221 msf221.po_item_no%TYPE;
v_po_no_231 msf231.po_no%TYPE;
v_po_item_no_231 msf231.po_item_no%TYPE;
v_preq_no_231 msf231.preq_no%TYPE;
v_preq_item_no_231 msf231.preq_item_no%TYPE;
v_count1 NUMBER(6);
CURSOR c_po_no_231 IS
SELECT b.PREQ_STK_CODE, b.po_no, b.po_item_no,
a.po_no, a.po_item_no,
a.preq_no, a.preq_item_no
FROM msf231 a,msf221 b
WHERE (a.po_no <> b.po_no OR a.po_item_no <> b.po_item_no)
AND b.status_221 <> '3'
AND b.PO_ITEM_TYPE = 'P'
AND a.PURCHASE_REQ_3 = b.PREQ_STK_CODE
AND a.dstrct_code = b.dstrct_code
AND a.dstrct_code = '0460'
ORDER BY a.PURCHASE_REQ_3;
BEGIN
DBMS_OUTPUT.PUT_LINE('TEST1');
v_PREQ_STK_CODE_OLD := ' ';
OPEN c_po_no_231;
DBMS_OUTPUT.PUT_LINE('TEST2');
LOOP
FETCH c_po_no_231
INTO v_PREQ_STK_CODE_221, v_po_no_221, v_po_item_no_221,
v_po_no_231, v_po_item_no_231,
v_preq_no_231, v_preq_item_no_231;
IF c_po_no_231%FOUND THEN
IF (v_PREQ_STK_CODE_221 <> v_PREQ_STK_CODE_OLD) THEN
v_PREQ_STK_CODE_OLD := v_PREQ_STK_CODE_221;
DBMS_OUTPUT.PUT_LINE('TEST');
SELECT COUNT (*) INTO v_count1
FROM msf221
WHERE PREQ_STK_CODE = v_PREQ_STK_CODE_221
AND PO_ITEM_TYPE = 'P'
AND dstrct_code = '0460'
AND status_221 <> '3';
IF (v_count1 = 1) THEN
UPDATE msf231
SET po_no = v_po_no_221,
po_item_no = v_po_item_no_221
WHERE dstrct_code = '0460'
AND preq_no = v_preq_no_231
AND preq_item_no = v_preq_item_no_231;
ELSE
DBMS_OUTPUT.PUT_LINE('DUPLICATE PO Items for PR Item No: '
||
v_PREQ_STK_CODE_221 || ': ' ||
v_count1);
END IF;
END IF;
END LOOP;
CLOSE c_po_no_231;
END;
The tables in concern are as follows:
SQL> desc ellipse.msf221
Name Null? Type ----------------------------------------- -------- ---------------------------- PO_NO NOT NULL CHAR(6) PO_ITEM_NO NOT NULL CHAR(3)
...
PREQ_STK_CODE NOT NULL CHAR(9)
SQL> desc ellipse.msf231
Name Null? Type ----------------------------------------- -------- ---------------------------- DSTRCT_CODE NOT NULL CHAR(4) PREQ_NO NOT NULL CHAR(6) PREQ_ITEM_NO NOT NULL CHAR(3)
...
PO_NO NOT NULL CHAR(6) PO_ITEM_NO NOT NULL CHAR(3)
Here's a bit of index info:
INDEX_NAME TABLE_NAME COLUMN_NAME
COLUMN_POSITION COLUMN_LENGTH
--------------- ---------- -----------------------------------
--------------- -------------
MSF22101IX0 MSF221 PO_NO
1 6
MSF22101IX0 MSF221 PO_ITEM_NO
2 3
MSF22101IX1 MSF221 RECEIPT_STATUS
1 1
MSF22101IX1 MSF221 CURR_DUE_DATE
2 8
MSF22101IX1 MSF221 PO_NO
3 6
MSF22101IX2 MSF221 PREQ_STK_CODE
1 9
MSF22101IX2 MSF221 REVSD_CRT_DATE
2 8
MSF22101IX2 MSF221 PO_NO
3 6
MSF22101IX2 MSF221 PO_ITEM_NO
4 3
MSF22101IX3 MSF221 DSTRCT_CODE
1 4
MSF22101IX3 MSF221 RECEIPT_STATUS
2 1
MSF22101IX3 MSF221 EXPEDITE_DATE
3 8
MSF22101IX3 MSF221 PO_NO
4 6
MSF22101IX4 MSF221 STATUS_221
1 1
MSF22101IX4 MSF221 DSTRCT_CODE
2 4
MSF22101IX4 MSF221 PO_NO
3 6
MSF22101IX4 MSF221 PO_ITEM_NO
4 3
MSF23101IX0 MSF231 DSTRCT_CODE
1 4
MSF23101IX0 MSF231 PREQ_NO
2 6
MSF23101IX0 MSF231 PREQ_ITEM_NO
3 3
MSF23101IX1 MSF231 DSTRCT_CODE
1 4
MSF23101IX1 MSF231 STATUS_231
2 1
MSF23101IX1 MSF231 SUPPLIER_NO
3 6
MSF23101IX1 MSF231 PURCH_OFFICER
4 10
MSF23101IX1 MSF231 FRT_DLV_RUN_NO
5 19
MSF23101IX2 MSF231 DSTRCT_CODE
1 4
MSF23101IX2 MSF231 PRINT_RUN_NO_2
2 4
MSF23101IX2 MSF231 STATUS_231
3 1
MSF23101IX2 MSF231 PO_TAG_SUP
4 18
MSF23101IX2 MSF231 PURCHASE_REQ_3
5 9
MSF23101IX3 MSF231 STATUS_231
1 1
MSF23101IX3 MSF231 PO_TAG_SUP
2 18
MSF23101IX3 MSF231 PURCHASE_REQ_3
3 9
*************************
SQL> sho parameter size
NAME TYPE VALUE ------------------------------------ ------- ------------------------------ bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 db_block_size integer 8192 hash_area_size integer 131072 java_max_sessionspace_size integer 0 java_pool_size string 20000K large_pool_size string 0 max_dump_file_size string 10240 object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 oracle_trace_collection_size integer 5242880 parallel_execution_message_size integer 2148 shared_pool_reserved_size string 1280000 shared_pool_size string 25600000 sort_area_retained_size integer 0 sort_area_size integer 65536 *********************
Is there something wrong with the PL/SQL script above?
Thanks,
=adley=
Received on Wed Jul 26 2006 - 23:19:18 CDT
 |
 |