Home » RDBMS Server » Performance Tuning » Outer Join is very very slow (Oracle, 10g,
icon8.gif  Outer Join is very very slow [message #285822] Wed, 05 December 2007 15:22 Go to next message
Messages: 1
Registered: December 2007
Location: USA
Junior Member
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.

select a.ab_nbr, from can_sal_summ a
left outer join coo_msg b
to_char(ab_create_dt_tm,'mm/yyyy') = to_char(add_months(sysdate,-1), 'mm/yyyy')
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
Messages: 25476
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:

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
Messages: 3727
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: Tue Jun 27 00:32:55 CDT 2017

Total time taken to generate the page: 0.14285 seconds