Home » RDBMS Server » Performance Tuning » Tunned querry
Tunned querry [message #222922] Tue, 06 March 2007 23:44 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Is there any way to write the below querry in a tuned manner.


MST_ITEMBOMROUTING ( No of records 90984).
PRIMARY KEY ("SCENARIO_ID", "ROUTINGID", "BOMID", "EFFSTARTDATE", "PRODUCEDITEMID", "PRODUCEDLOCATIONID")
Indexes
1.("PRODUCEDITEMID", "PRODUCEDLOCATIONID", "SCENARIO_ID")
2.("ROUTINGID", "PRODUCEDLOCATIONID", "SCENARIO_ID")
3 ("BOMID", "PRODUCEDLOCATIONID", "SCENARIO_ID")



MST_ROUTINGHEADER (No of records 91029)
PRIMARY KEY ("SCENARIO_ID", "ROUTINGID", "LOCATION_ID")
Indexes
1.("LOCATION_ID", "SCENARIO_ID")


SELECT ibr.*
FROM MST_ITEMBOMROUTING ibr,MST_ROUTINGHEADER rhr
WHERE ibr.produceditemid='1076-6318'
AND ibr.producedlocationid='CRS'
AND ibr.routingid=rhr.routingid
AND ibr.producedlocationid=rhr.location_id
AND rhr.routingtype='TEST'
AND rhr.sys_ent_state='ACTIVE'
AND ibr.sys_ent_state='ACTIVE';

Thanks in advance
Re: Tunned querry [message #222944 is a reply to message #222922] Wed, 07 March 2007 00:41 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

1. Consider creating an index for MST_ITEMBOMROUTING table on (PRODUCEDITEMID,PRODUCEDLOCATIONID,sys_ent_state) columns.

2. The column SCENARIO_ID exists in both tables. Is it possible to use it in JOIN condition?
If YES - rewrite the query as:
SELECT ibr.*
FROM MST_ITEMBOMROUTING ibr,MST_ROUTINGHEADER rhr
WHERE ibr.produceditemid='1076-6318'
AND ibr.producedlocationid='CRS'
AND ibr.routingid=rhr.routingid
AND ibr.producedlocationid=rhr.location_id
AND ibr.scenario_id = rhr.scenario_id
AND rhr.routingtype='TEST'
AND rhr.sys_ent_state=ibr.sys_ent_state
AND ibr.sys_ent_state='ACTIVE';

Use EXPLAIN to verify that MST_ROUTINGHEADER table is accessed via NESTED LOOP join on it's primary key
(performing UNIQUE SCAN).

3. If NOT - then create an index for MST_ROUTINGHEADER table
ON (routingid,location_id,sys_ent_state,routingtype).

Use EXPLAIN to verify that MST_ROUTINGHEADER table is accessed via NESTED LOOP join on it's new index
(performing RANGE SCAN).

HTH.
Michael
Previous Topic: query hangs
Next Topic: Doubt on "SQL Ordered by Parse Calls"
Goto Forum:
  


Current Time: Fri Dec 02 12:42:39 CST 2016

Total time taken to generate the page: 0.14265 seconds