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
hb_venkatesh
Messages: 9
Registered: October 2007
Location: india
Junior Member

Hi,

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;

regards
venki

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
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

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: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also:
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

Regards
Michel

[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 Dec 03 11:52:58 CST 2016

Total time taken to generate the page: 0.08168 seconds