Home » RDBMS Server » Performance Tuning » Join query gettin poor performance
icon4.gif  Join query gettin poor performance [message #276106] Wed, 24 October 2007 00:30 Go to next message
Messages: 9
Registered: October 2007
Location: india
Junior Member


This is my join query to retriving data , i had a problem from this, its getting very slow to retrive data, even i used in report builder, it could not build the report.
From this select statement i've using three tables ,
please help and have suggestion to tune my query better fast and give some new idea , but i'm using oracle 8i.

select a.customer_code customer, c.name name, c.place place, a.product_code product, b.quantity ord_qty, nvl(b.delivery_district_code,c.district_code) district, nvl(b.delivery_town_code,c.town_code) town
from order_book a, order_book_detail b, customer c
where a.region_code = b.region_code
and a.order_book_form_no = b.order_book_form_no
and a.customer_code = c.customer_code
and c.division_code = 34
and a.region_code = 10
and c.state_code = 1
and a.order_book_form_date = '18-OCT-2007'
and nvl(c.classification_code,'N') = 'S'
order by 1;


Re: Join query gettin poor performance [message #276109 is a reply to message #276106] Wed, 24 October 2007 00:42 Go to previous messageGo to next message
Messages: 7880
Registered: March 2000
Senior Member

nd a.order_book_form_date = '18-OCT-2007'
and nvl(c.classification_code,'N') = 'S'

The first line most probably contains an error: '18-OCT-2007' is a string, not a date. If your column order_book_form_date has DATE as its datatype, you should compare it to a date.
The second line can be rewritten as
and classification_code = 'S'

If there are any indexes on classification_code, they can be used when you leave the nvl. The nvl doesn't add anything anyway.

Show us rowcounts, index-design and explain plan
Re: Join query gettin poor performance [message #276113 is a reply to message #276106] Wed, 24 October 2007 00:58 Go to previous message
Michel Cadot
Messages: 65084
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

And read How to Identify Performance Problem and Bottleneck


[Updated on: Wed, 24 October 2007 00:58]

Report message to a moderator

Previous Topic: Cached Cursor
Next Topic: How to creating index? for best performance
Goto Forum:

Current Time: Sat Jul 22 23:53:27 CDT 2017

Total time taken to generate the page: 0.14188 seconds