Home » SQL & PL/SQL » SQL & PL/SQL » query optimization
query optimization [message #23676] Wed, 25 December 2002 06:05 Go to next message
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 Go to previous message
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.
Previous Topic: Mutating Table problem ...
Next Topic: How to optimize COST of quiery containg ORDER BY
Goto Forum:
  


Current Time: Mon May 20 19:26:57 CDT 2024