I need to tune the following statement. I'm a new dba. I tried to create
some indexes on "cost_revenue" and "action_no" fields but it didnt do any effect. Oracle doesnt let me to create an index on grouping functions either.
you can find the SQL statement and its plan below.
select cost_revenue.action_no, max(decode(cost_revenue.surcharge_type, 'SHIPMENT', cost_revenue.company_code, '')) supplier, sum(cost_revenue.le_amount) amount, max(cost_revenue.le_currency_code) currency
where cost_revenue.cost_revenue = 'C'
group by cost_revenue.action_no;
How are the statistics on the cost_revenue table? Is the cardinality of the cost_revenue table sufficiently high to warrant an index? Lastly, how much diskspace is allocated to the cost_revenue table? If it has been recently had a large delete, then you may need to reorganize the data to be closer on disk. Hard to say.