| Tunned querry [message #222922] |
Tue, 06 March 2007 23:44  |
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  |
michael_bialik
Messages: 621
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
