Oracle Query optimization

Originally Posted by kalukum

Hi Friend.............

I am a oracle developer. I have a
question, hope you would reply me.Thanks in advance for your time.

I tried the following query :

select c.cldt_status,
a.ledg_clntidno,
a.ledg_dctpcode,
a.ledg_idjoint,
a.ledg_clntidno||'-'||a.ledg_dctpcode||'-'||a.ledg_idjoint ClientID,
ltrim(b.clms_title||' '||clms_initials||' '||clms_name) name,
a.ledg_cfamt,ledg_lgtpcode


from BBOS.stledger_movement a,
BBOS.smclient_master b,
BBOS.smclient_findetails c
where a.ledg_clntidno = b.clms_clntidno
and a.ledg_dctpcode = b.clms_dctpcode
and a.ledg_idjoint = b.clms_idjoint
and b.clms_clntidno = c.cldt_clntidno
and b.clms_dctpcode = c.cldt_dctpcode
and b.clms_idjoint = c.cldt_idjoint
and a.ledg_cfamt > 0
and a.ledg_transeqno = (select max(d.ledg_transeqno)
from BBOS.stledger_movement d
where d.ledg_clntidno = a.ledg_clntidno
and d.ledg_dctpcode = a.ledg_dctpcode
and d.ledg_idjoint = a.ledg_idjoint
and to_char(d.ledg_trandate,'dd/mm/yyyy') = (select to_char(max(e.ledg_trandate),'dd/mm/yyyy')
from BBOS.stledger_movement e
where e.ledg_clntidno = a.ledg_clntidno
and e.ledg_dctpcode = a.ledg_dctpcode
and e.ledg_idjoint = a.ledg_idjoint
and e.ledg_trandate < :p_date+1))

order by to_number(a.ledg_clntidno)
But the above query did not work. Query keeps on running, not ending.
Above tables has over 20,000 records.
Can you please help me in solving my problem.
Thank you,

Kumudu.

Originally Posted by Medhatithi

I can give you a small suggestion, though I'm not sure this will help or not.
In your query, there is a clause:

and to_char(d.ledg_trandate,'dd/mm/yyyy') = (select to_char(max(e.ledg_trandate),'dd/mm/yyyy')

Here, is there any index on the d.ledg_trandate field? In that case, this query will not use it. Just try to avoid the to_char function on the date field to enable Oracle CBO to use the index

Originally Posted by Medhatithi

There is another small suggestion. You are using correalted subqueries in your code. You can override them also in your code. For reference, you can see this site:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:3083286970877