Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure Tuning.
Stored Procedure Tuning. [message #37621] Sun, 17 February 2002 21:13 Go to next message
Shankar Balakrishnan
Messages: 2
Registered: February 2002
Junior Member
Of the 8 procedures, 1 was instantly improved after indexing the transaction_id column of the table. The second procedure has a NOT IN (select * ...) type of query which is very slow. Indexing also doesn't seem to help.

The remaining 6 procedures are 2 identical sets of 3 procedures. These 3 procedures do the following -
1 & 3 are identical - here's the pseudocode
Open a cursor of t_id, account_no
Open another cursor with rows of type 'ADD' and
t_id, account_no
Compute price of 'ADD' for that t_id, account_no
Open another cursor with rows of type 'REMOVE' and
t_id, account_no
Compute price of 'REMOVE' for that t_id, account_no
DBMS_OUTPUT if REMOVE price > ADD price indicating
a downgrade order!
Close the main cursor.

The 2nd one is - Counts distinct account_nos from the table - uses Index-By Table.

Right now, I don't have the exact code but will put it on display ASAP ...

Please let me know if anyone has any inputs looking at the algorithm above.

Thanks!

Regards,
Shankar Balakrishnan.
Re: Stored Procedure Tuning. [message #37638 is a reply to message #37621] Mon, 18 February 2002 08:32 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
NOT IN doesn't use index - each candidate entry in the outer look needs to be compared to the NOT in subquery. The bigger the NOT IN list gets, the slower. Re-write using NOT EXISTS or something like this - it all depends on the quantities od data involved and the % to exclude.

Try replacing this:
select * from emp where emp_no not in (select emp_no from... where...);
with something like this:
select * from emp where emp_no
minus
select * from emp where emp_no in (select emp_no from... where...);
Previous Topic: appending to a result cursor? possible?
Next Topic: How to sum the fields in the oracle?????
Goto Forum:
  


Current Time: Fri Mar 29 07:17:15 CDT 2024