hi,
i am writing an update query and was trying to find the most optimum way.
Assume i have 3 tables
1) summary - this table contains the values only in the primary key columns. approx rows = 150,000 - contains columns cust_type, period, purchase, sales
2) trans - this table contains transaction date. approx rows = 500,000 - contains cust_code along with other pk & non pk columns - contains solumns cust_code, period, purchase, sales
3) cust_mast (cmast) - this is the customer master and contains cust_code + cust_type. this table has an unique index on cust_code + cust_type so fast full scans are possible.
approx rows = 300,000
now suppose if i want to update the summary table with all the transactions with the foll query
update summ set (purchase, sales) =
(select sum(purchase), sum(sales) from trans, cmast
where trans.cust_code = cmast.cust_code and
cmast.cust_type = summ.cust_type
and trans.period = summ.period)
what would be the best execution plan for this query( NL, hash or merge) ? or is it better if i fetch the data into a cursor and then update the summ table in a loop ?
satish
