Tuning The Sql Query | Newbie | | Join Date: Sep 2007
Posts: 2
| |
Hi,
This is Bhargav and I am trying to tune this query that is being used in a discoverer report and the columns in the select statement are the ones that are to be displayed in the discoverer output.
Can somebody help me as top how to tune this query. Any sort of help is greatly appreciated , as this is of high priority.
Regards,
Bhargav. - select
-
cost_orgs.organization_code || ' - ' || cost_orgs.organization_name cost_org,
-
on_hand_orgs.organization_code || ' - ' || on_hand_orgs.organization_name on_hand_org,
-
msi.segment1 item_name,
-
msi.description item_desc,
-
mcb.segment1 cost_cat_1,
-
mcb.segment2 cost_cat_2,
-
mcb.segment3 cost_cat_3,
-
mcb.segment1 || '.' || mcb.segment2 || '.' || mcb.segment3 cost_category,
-
upper(ctf1.cost_type) cost_type_1,
-
cicf1.item_cost item_cost_1,
-
cicf1.material_cost mtl_cost_1,
-
upper(ctf2.cost_type) cost_type_2,
-
cicf2.item_cost item_cost_2,
-
cicf2.material_cost mtl_cost_2,
-
sum(moh.transaction_quantity) on_hand_qty,
-
decode(msi.planning_make_buy_code,1,'M','B') make_buy_flag
-
from
-
-- mtl_parameters mp,
-
cst_item_costs cicf1,
-
cst_cost_types ctf1,
-
cst_item_costs cicf2,
-
cst_cost_types ctf2,
-
mtl_onhand_quantities moh,
-
inv.mtl_categories_b mcb,
-
inv.mtl_item_categories mic,
-
-- inv.mtl_category_sets_tl mcst,
-
org_organization_definitions cost_orgs,
-
org_organization_definitions on_hand_orgs,
-
inv.mtl_system_items_b msi
-
where 1=1
-
and cost_orgs.organization_id = msi.organization_id
-
and cicf1.organization_id = msi.organization_id
-
and cicf1.inventory_item_id = msi.inventory_item_id
-
and ctf1.cost_type_id = cicf1.cost_type_id --this should be set to frozen
-
and cicf2.organization_id = msi.organization_id --cross-product between sets will be resolved by Discoverer parameters
-
and cicf2.inventory_item_id = msi.inventory_item_id
-
and ctf2.cost_type_id = cicf2.cost_type_id
-
and moh.organization_id = on_hand_orgs.ORGANIZATION_ID
-
and moh.inventory_item_id = msi.inventory_item_id
-
and mic.organization_id (+) = msi.organization_id
-
and mic.inventory_item_id (+) = msi.inventory_item_id
-
and mic.category_set_id (+) = 1100000006 --inventory cost category
-
and mcb.category_id (+)= mic.category_id -- the inventory cost category consisists of mcb. segment1,2 and 3
-
--and mcst.CATEGORY_SET_ID = mic.CATEGORY_SET_ID
-
--and mcst.CATEGORY_SET_NAME = 'QC INVENTORY CATEGORY'
-
--and msi.inventory_item_id = 512
-
--and cicf1.COST_TYPE_ID = 1
-
--and cicf2.COST_TYPE_ID = 1101
-
--and msi.ORGANIZATION_ID = 89
-
group by
-
cost_orgs.organization_code || ' - ' || cost_orgs.organization_name,
-
on_hand_orgs.organization_code || ' - ' || on_hand_orgs.organization_name,
-
msi.segment1,
-
msi.description,
-
mcb.segment1,
-
mcb.segment2,
-
mcb.segment3,
-
mcb.segment1 || '.' || mcb.segment2 || '.' || mcb.segment3,
-
upper(ctf1.cost_type),
-
cicf1.item_cost,
-
cicf1.material_cost,
-
upper(ctf2.cost_type),
-
cicf2.item_cost,
-
cicf2.material_cost,
-
decode(msi.planning_make_buy_code,1,'M','B')
-
--order by msi.SEGMENT1
/
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Tuning The Sql Query
Make use of HINTS in the query (HINT - INDEX,PARALLEL will be helpful)
| | Newbie | | Join Date: Sep 2007
Posts: 2
| | | re: Tuning The Sql Query
Hi Amit,
I am not so familiar with using Hints in the sql query.
Can you pls give a small example and tell me which hint to use in this scenario, where so many joins are there.
Will appreciate your response in the earliest possible time.
Thanks,
Bhargav.
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Tuning The Sql Query
Quote:
Originally Posted by orasolve Hi Amit,
I am not so familiar with using Hints in the sql query.
Can you pls give a small example and tell me which hint to use in this scenario, where so many joins are there.
Will appreciate your response in the earliest possible time.
Thanks,
Bhargav. Hi Bhargav,
You can make us of HINTS in your sql query to improve the performance of the Query.
There are many HINTS availabe with oracle that can be used but to make use of
the appropriate one will improve the performance.
See below example on making use of INDEX HINT in SQL Query: -
SELECT /* INDEX(<table_alias>,<index_name>) */ FROM table_name table_alias
-
Find more about INDEX HINT here
Another HINT PARALLEL can be used for parallel execution of your Query.
See more about usage of PARALLEL HINT here |  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
