Home » RDBMS Server » Performance Tuning » query hangs on order by
query hangs on order by [message #304378] Wed, 05 March 2008 00:38 Go to next message
ziadaher
Messages: 70
Registered: October 2007
Location: lebanon
Member

the query hangs when adding "order by class_group_code, class_group_serial"

select * from eqi_equipments where class_group_code||class_group_serial in 
(select a.class_group_code||a.class_group_serial
from eqi_equipments a, eqi_class_groups b, eqi_class_group_types c, 
eqi_brands d, eqi_status e, eqi_change_plate f
where (a.class_group_code=b.class_group_code
and a.class_group_code=c.class_group_code
and a.type_code=c.type_code
and a.brand_code=d.brand_code
and a.status_code=e.status_code
and a.class_group_code=f.class_group_code(+)
and a.class_group_serial=f.class_group_serial(+))
and ( upper(a.class_group_code) like '%'||'SIDES'||'%'
or  upper(a.class_group_serial) like '%'||'SIDES'||'%'
or  upper(b.class_group_desc) like '%'||'SIDES'||'%'
or  upper(a.type_code) like '%'||'SIDES'||'%'
or  upper(c.description) like '%'||'SIDES'||'%'
or  a.brand_code like ''||'SIDES'||''
or  upper(d.brand_desc) like '%'||'SIDES'||'%'
or  a.status_code like ''||'SIDES'||''
or  upper(e.status_desc) like '%'||'SIDES'||'%'
or  upper(a.factory_sn) like '%'||'SIDES'||'%'
or  upper(a.fixed_asset_flag) like '%'||'SIDES'||'%'
or  upper(a.plate_no) like '%'||'SIDES'||'%'
or  a.purchase_price like ''||'SIDES'||''
or  a.odometer_reading like ''||'SIDES'||''
or  upper(a.remark) like '%'||'SIDES'||'%'
or  a.model_year like ''||'SIDES'||''
or  upper(f.old_plate_number) like '%'||'SIDES'||'%'))
Re: query hangs on order by [message #304384 is a reply to message #304378] Wed, 05 March 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A query NEVER hangs.
It is working or it fails.

Regards
Michel
Re: query hangs on order by [message #304391 is a reply to message #304378] Wed, 05 March 2008 01:05 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
i don't understand this part:
Quote:
like '%'||'SIDES'||'%'

why not use
upper(a.class_group_code) like '%SIDES%'



regards,
Re: query hangs on order by [message #304394 is a reply to message #304391] Wed, 05 March 2008 01:07 Go to previous messageGo to next message
ziadaher
Messages: 70
Registered: October 2007
Location: lebanon
Member

it works fine, but when i add "order by class_group_code, class_group_serial” it fails
Re: query hangs on order by [message #304406 is a reply to message #304394] Wed, 05 March 2008 01:25 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
what does "it fails" mean? does it throw any error.can you post that.are your table stats upto-date.


regards,
Previous Topic: Single operation slows down a query
Next Topic: Performance Issue
Goto Forum:
  


Current Time: Sat Dec 03 05:54:34 CST 2016

Total time taken to generate the page: 0.09982 seconds