OraFAQ Forum: Performance Tuning » Need help in tuning this query

Home » RDBMS Server » Performance Tuning » Need help in tuning this query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need help in tuning this query [message #354700]

Mon, 20 October 2008 21:59

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.

Report message to a moderator

Re: Need help in tuning this query [message #354821 is a reply to message #354700]

Tue, 21 October 2008 07:36

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

Report message to a moderator

Re: Need help in tuning this query [message #354883 is a reply to message #354700]

Tue, 21 October 2008 16:14

michael_bialik
Messages: 621
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

Report message to a moderator

Re: Need help in tuning this query [message #354968 is a reply to message #354883]

Wed, 22 October 2008 01:41

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.

Report message to a moderator

Re: Need help in tuning this query [message #355001 is a reply to message #354700]

Wed, 22 October 2008 03:17

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Yes, it will.

Read about multicolumn/concatenated indexes.

Report message to a moderator

Re: Need help in tuning this query [message #355003 is a reply to message #354968]

Wed, 22 October 2008 03:20

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

Report message to a moderator

Previous Topic:	Query Rewrite in Materialised Views (merged)
Next Topic:	response time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 02:27:25 CST 2024