query optimization [message #23676] |
Wed, 25 December 2002 06:05 |
Shilpa
Messages: 59 Registered: November 2001
|
Member |
|
|
How to optimize query to improve performance?
e.g:
The query is :
select balance,note from table1
where account_id = '82620021' and bracnch_id = '3'
and transaction_date =(select max(transaction_date) from table1 where transaction_date <= '19-AUG-2002' and account_id = '82620021' and bracnch_id = '3')
Here, we are interested in seeing the latest transaction on or before the entered date for the given account_id.
Can anyone optimize this anymore?
Thanx
|
|
|
Re: query optimization [message #23677 is a reply to message #23676] |
Wed, 25 December 2002 10:59 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Not an optimization, but I would structure this as:
select balance, note
from table1 t1
where account_id = '82620021'
and branch_id = '3'
and transaction_date =
(select max(transaction_date)
from table1 t2
where t2.transaction_date <= to_date('19-AUG-2002', 'dd-mon-yyyy')
and t2.account_id = t1.account_id
and t2.branch_id = t1.branch_id);
Note the explicit date conversion and the correlated references.
As far as performance, I would suggest an index on (branch_id, account_id, transaction_date) - or at least the first two columns.
|
|
|