Home » RDBMS Server » Performance Tuning » Outer Join is very very slow (Oracle, 10g, 10.2.0.2.0)
icon8.gif  Outer Join is very very slow [message #285822] Wed, 05 December 2007 15:22 Go to next message
klkoona
Messages: 1
Registered: December 2007
Location: USA
Junior Member
Hi,
I have been trying to execute the following query using TOAD/SQL plus. I am getting terrible performence. Since I am quite new to this SQL environment, I couldn't figure it out problem with either data base side ( no proper indexes etc) or SQL. Please review the sql and suggest me to improve it.

SQL:
select a.ab_nbr, from can_sal_summ a
left outer join coo_msg b
on
to_char(ab_create_dt_tm,'mm/yyyy') = to_char(add_months(sysdate,-1), 'mm/yyyy')
and
to_char(msg_rcv_gmt,'yyyymm') >= to_char(add_months(sysdate,-1),'yyyymm')
and to_char(msg_rcv_gmt,'yyyymm') <= to_char(add_months(sysdate,-0),'yyyymm')
and msg_type = 'FAA'
and dup_ind = 'N'
and a.prod_type not in ('AB', 'CA')
AND a.HSURR_ID = '*'
AND a.comdty_desc not like '%CLOTH%' AND a.COMDTY_DESC not LIKE 'COMAT'
and a.shipper_nm not like 'CALI%'
and a.shipper_nm not like 'ILLI%'
******************************************************
Re: Outer Join is very very slow [message #285839 is a reply to message #285822] Wed, 05 December 2007 20:42 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
I am not surprised by your statement that it is slow.
The use of function in the WHERE clause precludes use of any index.
Use of sting wildcard ("%") prevent use of index.

Follow the suggestions given in the URL below:
http://www.orafaq.com/forum/t/84315/74940/

generate & post EXPLAIN_PLAN back here nicely formatted by using <code tags>
Re: Outer Join is very very slow [message #285841 is a reply to message #285839] Wed, 05 December 2007 20:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Trace it, run it through TK*Prof, post the results here including the Explain Plan and stepwise row counts.

Ross Leishman
Previous Topic: Can you explain to me?
Next Topic: Query with aggregate function not using index
Goto Forum:
  


Current Time: Mon Dec 05 09:11:54 CST 2016

Total time taken to generate the page: 0.06840 seconds