Hi,

I am trying to otpimize my stored procedure.

FOR rec IN (select max(q.store_number) as psn,q.master_account_id,q.account_type_id from
(select max(count_per_master) ,p.store_number,p.master_account_id,p.account_type_id from
(select master_account_id, account_type_id, store_number, count(transaction_header_id) count_per_master
from gcdb.gcdb_transaction_header where master_account_id is not null and transaction_date > tmp_run_date
group by master_account_id, store_number,account_type_id)p group by p.store_number,p.master_account_id,
p.account_type_id) q group by q.master_account_id,q.account_type_id)
LOOP

SELECT primary_store_number INTO tmp_store_number from gcdb.gcdb_master_account
where master_account_id = rec.master_account_id and account_type_id = rec.account_type_id;

IF (tmp_store_number <> rec.psn) THEN
UPDATE gcdb_master_account
SET primary_store_number = tmp_store_number
WHERE master_account_id = rec.master_account_id
and account_type_id = ec.account_type_id;
END IF;

END LOOP;

I am trying to write the above proc into a single update statement something like the below

update gcdb_master_account set primary_store_number =

(select max(q.store_number) as psn,q.master_account_id,q.account_type_id from
(select max(count_per_master) ,p.store_number,p.master_account_id,p.account_type_id from
(select master_account_id, account_type_id, store_number, count(transaction_header_id) count_per_master
from gcdb.gcdb_transaction_header
where master_account_id is not null and transaction_date > tmp_run_date
group by master_account_id, store_number,account_type_id)p group by p.store_number,p.master_account_id,
p.account_type_id) q group by q.master_account_id,q.account_type_id) r


can anyone help...

Thanks in advance,

regards
kumar.

Hi Kumar,

>> can anyone help...

Sure!

Do you have a question for us, or is this a request for consulting services?

**************************************************
>> I am trying to write the above proc into a single update statement something like the below

That may make it run longer, not shorter. . . .

Since you are computing multiple max values, I would re-write the SQL in PL/SQL:

http://www.dba-oracle.com/sql_class/rewrite_sql_plsql.htm

That way, you only malke one pass against the table and you get all of the max values . . .



Wow, this ia a bizarre query.

Depending on your release, this could run forever . . .

I would use the WITH clause, or rewrite it as PL/SQL.

**************************************************
>> I am trying to otpimize my stored procedure.



Then get Dr. Hall's book "Oracle PL/SQL tuning secrets":


http://www.rampant-books.com/book_2006_1_plsql_tune.htm