Oracle Query optimization

[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_dctpc ode||'-'||a.ledg_idjoi nt ClientID,
ltrim(b.clms_ti tle||' '||clms_initial s||' '||clms_name) name,
a.ledg_cfamt,le dg_lgtpcode


from BBOS.stledger_m ovement a,
BBOS.smclient_m aster b,
BBOS.smclient_f indetails 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_transeqn o = (select max(d.ledg_tran seqno)
from BBOS.stledger_m ovement 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.l edg_trandate),' dd/mm/yyyy')
from BBOS.stledger_m ovement 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.led g_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.

[febyfelix]

can u send the structure of the tables...?

[Medhatithi]

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_dctpc ode||'-'||a.ledg_idjoi nt ClientID,
ltrim(b.clms_ti tle||' '||clms_initial s||' '||clms_name) name,
a.ledg_cfamt,le dg_lgtpcode


from BBOS.stledger_m ovement a,
BBOS.smclient_m aster b,
BBOS.smclient_f indetails 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_transeqn o = (select max(d.ledg_tran seqno)
from BBOS.stledger_m ovement 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.l edg_trandate),' dd/mm/yyyy')
from BBOS.stledger_m ovement 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.led g_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.

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.l edg_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

[debasisdas]

Hi
kalulum
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow posting guidelines and use code tags to make your post more readable.

[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.l edg_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

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.c om/pls/asktom/f?p=100:11:0::N O::P11_QUESTION _ID:30832869708 77

[kalukum]

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.c om/pls/asktom/f?p=100:11:0::N O::P11_QUESTION _ID:30832869708 77

Thank you verymuch. you are correct. thers no that type of index.
trunc(tran_date )

cheers..
kalukum