 Join query gettin poor performance [message #276106] |
Wed, 24 October 2007 00:30  |
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  |
Frank
Messages: 7901
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
