Home » RDBMS Server » Performance Tuning » Need help in tuning this query
Need help in tuning this query [message #354700] Mon, 20 October 2008 21:59 Go to next message
apps_user
Messages: 35
Registered: May 2008
Member
This query is taking a long time.
I need your advice and suggestion to tune this query.
We are tuning this query in Oracle 8i.
so_lines has a million rows.
The driving table is so_lines.
so_lines and mtl_system_item has index on the following columns.
inventory_item_id
s10

SELECT line_id
       FROM mtl_system_items si,
   so_lines lin
       WHERE  p_header_id = lin.header_id
  and  si.inventory_item_id = lin.inventory_item_id
  and  si.organization_id = lin.warehouse_id
  and  lcl_product_code = si.attribute10 -- lcl_product_code is variable
  and lin.s10= 18
         and to_date(lin.date_requested_current,'DD-MON-RRRR') <=
   to_date(p_usage_date,'DD-MON-RRRR') -- p_usage_date is a variable
  and p_unit_code = lin.unit_code -- p_unit_code is a variable
  and     nvl(to_date(lin.attribute4,'DD-MON-RRRR'),
                 to_date(p_usage_date,'DD-MON-RRRR')+100) >=       to_date(p_usage_date,'DD-MON-RRRR')
  and rownum = 1

I have the following questions
1) Can I use the /*+ FIRS_ROW +/ optimizer hint?
2) As so_lines is the driving table and has a milion rows, Do I need to reorder the where clause?
3) What are the other indexes I have to build?Do I have to build indexes on date_requested_current and attribute10 on so_lines table?

Thanks in advance.
Re: Need help in tuning this query [message #354821 is a reply to message #354700] Tue, 21 October 2008 07:36 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Having date fields stored as date data type should be a general rule.

Anyway I suggest you to create two indexes one on lin.unit_code and an other one on si.inventory_item_id and si.organization_id.

Bye Alessandro
Re: Need help in tuning this query [message #354883 is a reply to message #354700] Tue, 21 October 2008 16:14 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.
As Alessandro already wrote:
definig date_requested_current and attribute4 columns as DATEs is a very good idea.

If you can not do it, but you can change the query then
1. Define following index:

CREATE INDEX ... ON so_lines (
  header_id,
  unit_code,
  s10,
  TO_DATE(NVL(attribute4,'31-DEC-3999'),'DD-MON-RRRR'),
  to_date(date_requested_current,'DD-MON-RRRR') ) ...


2. Rewrite the query as:

SELECT line_id
       FROM mtl_system_items si,
   so_lines lin
       WHERE  p_header_id = lin.header_id
  and  si.inventory_item_id = lin.inventory_item_id
  and  si.organization_id = lin.warehouse_id
  and  lcl_product_code = si.attribute10 -- lcl_product_code is variable
  and lin.s10= 18
         and to_date(lin.date_requested_current,'DD-MON-RRRR') <=
   to_date(p_usage_date,'DD-MON-RRRR') -- p_usage_date is a variable
  and p_unit_code = lin.unit_code -- p_unit_code is a variable
  and to_date(NVL(lin.attribute4,'31-DEC-3999'),'DD-MON-RRRR') >=       to_date(p_usage_date,'DD-MON-RRRR')
  and rownum = 1


If you can NOT change the query than just try

CREATE INDEX ... ON so_lines (
  header_id,
  unit_code,
  s10  ) ...


HTH.
Michael

Re: Need help in tuning this query [message #354968 is a reply to message #354883] Wed, 22 October 2008 01:41 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Thanks Alessandro and Micheal.
I have a question.
If I create one index per column instead of having one for all what will be the difference in performance ?

CREATE INDEX I1 ON so_lines (
  header_id);
CREATE INDEX I2 ON so_lines (
  unit_code);
 CREATE INDEX I3 ON so_lines (
  s10);


instead of

CREATE INDEX ... ON so_lines (
  header_id,
  unit_code,
  s10,
  TO_DATE(NVL(attribute4,'31-DEC-3999'),'DD-MON-RRRR'),
  to_date(date_requested_current,'DD-MON-RRRR') ) ...





Thanks in advance.



Re: Need help in tuning this query [message #355001 is a reply to message #354700] Wed, 22 October 2008 03:17 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Yes, it will.

Read about multicolumn/concatenated indexes.

Re: Need help in tuning this query [message #355003 is a reply to message #354968] Wed, 22 October 2008 03:20 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
You should build two indexes for the reason that it's possible to use only one index for each table access, and your query needs two table accesses, so you need:

- index on lin.unit_code and lin.s10 because it it the starting predicate
( p_unit_code = lin.unit_code and lin.s10= 18 )


- index on si.inventory_item_id and si.organization_id because it is the index for the join predicate
( si.inventory_item_id = lin.inventory_item_id and  si.organization_id = lin.warehouse_id )


On the first table you don't have many alternatives, while on the second one, the other columns can be ignored, just because I suppose they're not as selective as the ones I suggested to index.

On my first message I missed lin.s10 on the first index but it should be there.

Bye Alessandro
Previous Topic: Query Rewrite in Materialised Views (merged)
Next Topic: response time
Goto Forum:
  


Current Time: Fri Dec 09 11:48:17 CST 2016

Total time taken to generate the page: 0.24237 seconds