Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Optimalize SQL Query Oracle R12

3706549May 22 2018 — edited May 23 2018

All,

I would like to optimalize join between following tables, please advise.

Table rows count:

  • inv.mtl_material_transactions = 17.466.581
  • inv.mtl_transaction_lot_numbers = 13.680.350
  • gme.gme_material_details = 5.302.282
  • gme.gme_batch_header = 790.828
  • inv.mtl_system_items_b = 516.148
  • GMD.fm_matl_dtl = 280.056

Query which I wrote (run for 1 minute first 50 rows):

SELECT
gbh.batch_no,
gbh.batch_id,
msi.segment1 as ITEM,
--msi.description,
lot.lot_number as LOT,
gmd.line_type,
msi.inventory_item_id
,fmd.attribute1 as SIGNIFICANT_ITEM
--,msi.inventory_item_id||lot.lot_number as THING_ID
FROM
gme.gme_batch_header gbh,
gme.gme_material_details gmd,
inv.mtl_material_transactions mmt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_lot_numbers lot
,GMD.fm_matl_dtl fmd
WHERE 1=1
--AND gmd.line_type = -1
AND gbh.batch_id = gmd.batch_id
AND gbh.organization_id = 1509
AND gbh.formula_id = fmd.formula_id
AND msi.inventory_item_id = fmd.inventory_item_id
AND msi.organization_id = fmd.organization_id
AND gmd.organization_id = gbh.organization_id
AND gmd.organization_id = msi.organization_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.trx_source_line_id = gmd.material_detail_id
AND mmt.transaction_source_id = gmd.batch_id
AND mmt.transaction_id = lot.transaction_id
AND lot.TRANSACTION_SOURCE_ID = gbh.Batch_Id
AND lot.organization_id = mmt.organization_id
AND lot.inventory_item_id = mmt.inventory_item_id

Indexes for tables:

  

COLUMN_NAMETABLE_NAME
ITEM_IDFM_MATL_DTL
LINE_TYPEFM_MATL_DTL
FORMULA_IDFM_MATL_DTL
FORMULALINE_IDFM_MATL_DTL
FORMULA_IDFM_MATL_DTL
LINE_TYPEFM_MATL_DTL
LINE_NOFM_MATL_DTL
ORGANIZATION_IDGME_BATCH_HEADER
BATCH_NOGME_BATCH_HEADER
BATCH_STATUSGME_BATCH_HEADER
BATCH_CLOSE_DATEGME_BATCH_HEADER
BATCH_IDGME_BATCH_HEADER
ORGANIZATION_IDGME_BATCH_HEADER
BATCH_NOGME_BATCH_HEADER
BATCH_TYPEGME_BATCH_HEADER
ORGANIZATION_IDGME_MATERIAL_DETAILS
INVENTORY_ITEM_IDGME_MATERIAL_DETAILS
LINE_TYPEGME_MATERIAL_DETAILS
BATCH_IDGME_MATERIAL_DETAILS
PHANTOM_IDGME_MATERIAL_DETAILS
MATERIAL_DETAIL_IDGME_MATERIAL_DETAILS
BATCH_IDGME_MATERIAL_DETAILS
LINE_NOGME_MATERIAL_DETAILS
LINE_TYPEGME_MATERIAL_DETAILS
INVENTORY_ITEM_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
COSTED_FLAGMTL_MATERIAL_TRANSACTIONS
TRANSACTION_GROUP_IDMTL_MATERIAL_TRANSACTIONS
PARENT_TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SET_IDMTL_MATERIAL_TRANSACTIONS
PM_COST_COLLECTEDMTL_MATERIAL_TRANSACTIONS
PM_COST_COLLECTOR_GROUP_IDMTL_MATERIAL_TRANSACTIONS
COMPLETION_TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
PICK_SLIP_NUMBERMTL_MATERIAL_TRANSACTIONS
MOVE_ORDER_LINE_IDMTL_MATERIAL_TRANSACTIONS
PICKING_LINE_IDMTL_MATERIAL_TRANSACTIONS
RCV_TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
PROJECT_IDMTL_MATERIAL_TRANSACTIONS
TO_PROJECT_IDMTL_MATERIAL_TRANSACTIONS
SOURCE_PROJECT_IDMTL_MATERIAL_TRANSACTIONS
TRANSFER_TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
INVOICED_FLAGMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_IDMTL_MATERIAL_TRANSACTIONS
OPM_COSTED_FLAGMTL_MATERIAL_TRANSACTIONS
TRANSACTION_EXTRACTEDMTL_MATERIAL_TRANSACTIONS
TRX_SOURCE_LINE_IDMTL_MATERIAL_TRANSACTIONS
XML_DOCUMENT_IDMTL_MATERIAL_TRANSACTIONS
SUBINVENTORY_CODEMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
VENDOR_LOT_NUMBERMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
SHIPMENT_NUMBERMTL_MATERIAL_TRANSACTIONS
ACCT_PERIOD_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_NAMEMTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
SEGMENT1MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
PLANNER_CODEMTL_SYSTEM_ITEMS_B
WEB_STATUSMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
CUSTOMER_ORDER_ENABLED_FLAGMTL_SYSTEM_ITEMS_B
SERVICE_ITEM_FLAGMTL_SYSTEM_ITEMS_B
VENDOR_WARRANTY_FLAGMTL_SYSTEM_ITEMS_B
USAGE_ITEM_FLAGMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
WIP_SUPPLY_LOCATOR_IDMTL_SYSTEM_ITEMS_B
BASE_ITEM_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
STYLE_ITEM_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
EAM_ITEM_TYPEMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
DESCRIPTIONMTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_STATUS_CODEMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
AUTO_CREATED_CONFIG_FLAGMTL_SYSTEM_ITEMS_B
WH_UPDATE_DATEMTL_SYSTEM_ITEMS_B
ITEM_CATALOG_GROUP_IDMTL_SYSTEM_ITEMS_B
CATALOG_STATUS_FLAGMTL_SYSTEM_ITEMS_B
PRODUCT_FAMILY_ITEM_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
COMMS_NL_TRACKABLE_FLAGMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
BUYER_IDMTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
TRANSACTION_IDMTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_IDMTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_DATEMTL_TRANSACTION_LOT_NUMBERS
LOT_NUMBERMTL_TRANSACTION_LOT_NUMBERS
INVENTORY_ITEM_IDMTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_IDMTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_SOURCE_IDMTL_TRANSACTION_LOT_NUMBERS
PRODUCT_CODEMTL_TRANSACTION_LOT_NUMBERS
PRODUCT_TRANSACTION_IDMTL_TRANSACTION_LOT_NUMBERS
SERIAL_TRANSACTION_IDMTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_IDMTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_IDMTL_TRANSACTION_LOT_NUMBERS
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 20 2018
Added on May 22 2018
#oracle, #performance, #sql
16 comments
4,555 views