Forum Stats

  • 3,752,114 Users
  • 2,250,460 Discussions
  • 7,867,723 Comments

Discussions

Optimalize SQL Query Oracle R12

3706549
3706549 Member Posts: 15
edited May 23, 2018 7:39AM in SQL & PL/SQL

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):

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">SELECT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/>gbh</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">batch_no</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>gbh</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">batch_id</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>msi</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">segment1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> ITEM</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="com" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #858c93;">--msi.description,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>lot</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">lot_number </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LOT</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>gmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">line_type</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>msi</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">inventory_item_id<br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">fmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">attribute1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> SIGNIFICANT_ITEM<br/></span><span class="com" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #858c93;">--,msi.inventory_item_id||lot.lot_number as THING_ID</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">FROM</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/>gme</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">gme_batch_header gbh</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>gme</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">gme_material_details gmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>inv</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">mtl_material_transactions mmt</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>inv</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">mtl_system_items_b msi</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>inv</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">mtl_transaction_lot_numbers lot<br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">GMD</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">fm_matl_dtl fmd<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">WHERE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">1</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="com" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #858c93;">--AND gmd.line_type = -1 </span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gbh</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">batch_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">batch_id <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gbh</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">organization_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">1509</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gbh</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">formula_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> fmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">formula_id<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> msi</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">inventory_item_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> fmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">inventory_item_id<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> msi</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">organization_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> fmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">organization_id<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">organization_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gbh</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">organization_id <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">organization_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> msi</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">organization_id<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> mmt</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">inventory_item_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> msi</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">inventory_item_id<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> mmt</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">trx_source_line_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">material_detail_id<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> mmt</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">transaction_source_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gmd</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">batch_id <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> mmt</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">transaction_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> lot</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">transaction_id<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> lot</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">TRANSACTION_SOURCE_ID </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> gbh</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">Batch_Id<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> lot</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">organization_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> mmt</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">organization_id<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">AND</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> lot</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">inventory_item_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> mmt</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">inventory_item_id</span>

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
BEDE
«1

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,276 Gold Trophy
    edited May 22, 2018 1:19AM

    Explain plan?

    The fact that the first 50 rows are fetched after more than 1 minute, would indicate that a hash join is performed: that makes the first rows be fetched quite late, but afterwards the rows will just flow fast. Although, without seeing the explain-plan, it's only a guess.

    Still, I'm quite surprised that the only filtering I may see is by gbh.organization_id = 1509 and there is not filter for some period of tine (one would expect you would like to see the transactions within a period of tine).

    And what would you do with millions of records?

    To have some idea how much the whole thing would last, do something like below:

    with tb as (

    select ... --- heder whould be placed your query

    )

    select count(*)

    from tb

    ;

    To have the first rows fetched sooner the explain-plan should have nested loops followed by index scan (unique scan or range scan).

    Some hints like leading and use_nl may change the execution plan for the query.

    It's good that you mentioned the indexes. Still, without an explain-plan, that doesn't help much.

    Have you read https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231 ?

    And also: https://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#i8327

  • 3706549
    3706549 Member Posts: 15
    edited May 22, 2018 2:13AM

    Bede,

    I apologize that I missed EXPLAIN-PLAN (I have never used it before, thank you for tip), please see below my result (I need get all batches to find product/ingredient), more details in question :

    sql.png

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       4 - access("GBH"."BATCH_ID"="GMD"."BATCH_ID" AND "GMD"."ORGANIZATION_ID"="GBH"."ORGANIZATION_ID" AND

                  "GMD"."ORGANIZATION_ID"="MSI"."ORGANIZATION_ID")

       5 - access("GBH"."FORMULA_ID"="FMD"."FORMULA_ID")

       6 - access("MSI"."INVENTORY_ITEM_ID"="FMD"."INVENTORY_ITEM_ID" AND

                  "MSI"."ORGANIZATION_ID"="FMD"."ORGANIZATION_ID")

       8 - access("MSI"."ORGANIZATION_ID"=1509)

       9 - filter("FMD"."ORGANIZATION_ID"=1509)

      10 - filter("GBH"."ORGANIZATION_ID"=1509)

      11 - filter("GMD"."ORGANIZATION_ID"=1509)

      12 - filter("MMT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND

                  "MMT"."INVENTORY_ITEM_ID"="MSI"."INVENTORY_ITEM_ID" AND "MMT"."TRANSACTION_SOURCE_ID"="GMD"."BATCH_ID")

      13 - access("MMT"."TRX_SOURCE_LINE_ID"="GMD"."MATERIAL_DETAIL_ID")

           filter("MMT"."TRX_SOURCE_LINE_ID" IS NOT NULL)

      14 - access("MMT"."TRANSACTION_ID"="LOT"."TRANSACTION_ID")

      15 - filter("LOT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND "LOT"."TRANSACTION_SOURCE_ID"="GBH"."BATCH_ID"

                  AND "LOT"."ORGANIZATION_ID"="MMT"."ORGANIZATION_ID" AND "LOT"."INVENTORY_ITEM_ID"="MMT"."INVENTORY_ITEM_ID")

    Now I remove one table and sort condition and I got better result, 1 sec - is it due to sort of conditions + remove 1 table?

        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 fmd.organization_id = 1509

        AND msi.inventory_item_id = fmd.inventory_item_id

        AND msi.organization_id = fmd.organization_id

        AND fmd.organization_id = gbh.organization_id

        AND gbh.formula_id = fmd.formula_id

        AND gbh.BATCH_STATUS = 4 --4 CLOSED, 3-COMPLETED

        AND gbh.batch_id = gmd.batch_id

        AND gmd.organization_id = gbh.organization_id

        AND gmd.organization_id = msi.organization_id

        AND lot.TRANSACTION_SOURCE_ID = gbh.Batch_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.organization_id = mmt.organization_id

        --AND lot.inventory_item_id = mmt.inventory_item_id

    sql.png

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited May 22, 2018 5:12AM

    What sort condition?

    BEDE
  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    edited May 22, 2018 5:57AM

    Hello, why not use ANSI Joins, they'd make your JOIN conditions clearer:

    FROM

             gme.gme_batch_header gbh

    JOIN gme.gme_material_details gmd ON (gbh.batch_id = gmd.batch_id AND gmd.organization_id = gbh.organization_id)
    JOIN inv.mtl_material_transactions mmt ON (mmt.trx_source_line_id = gmd.material_detail_id AND mmt.transaction_source_id = gmd.batch_id)
    JOIN inv.mtl_system_items_b msi ON (gmd.organization_id = msi.organization_id AND mmt.inventory_item_id = msi.inventory_item_id)
    JOIN inv.mtl_transaction_lot_numbers lot ON (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)
    JOIN GMD.fm_matl_dtl fmd ON (gbh.formula_id = fmd.formula_id AND msi.inventory_item_id = fmd.inventory_item_id AND msi.organization_id = fmd.organization_id)
    WHERE gbh.organization_id = 1509





  • 3706549
    3706549 Member Posts: 15
    edited May 22, 2018 8:41AM

    SeánMacGC,

    I tried you suggestions and I think I got even worst result:

    err.png

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       4 - access("GMD"."ORGANIZATION_ID"="MSI"."ORGANIZATION_ID" AND "GBH"."BATCH_ID"="GMD"."BATCH_ID" AND

                  "GMD"."ORGANIZATION_ID"="GBH"."ORGANIZATION_ID")

       5 - access("GBH"."FORMULA_ID"="FMD"."FORMULA_ID")

       6 - access("MSI"."INVENTORY_ITEM_ID"="FMD"."INVENTORY_ITEM_ID" AND

                  "MSI"."ORGANIZATION_ID"="FMD"."ORGANIZATION_ID")

       8 - access("MSI"."ORGANIZATION_ID"=1509)

       9 - filter("FMD"."ORGANIZATION_ID"=1509)

      10 - filter("GBH"."ORGANIZATION_ID"=1509)

      11 - filter("GMD"."ORGANIZATION_ID"=1509)

      12 - filter("MMT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND

                  "MMT"."INVENTORY_ITEM_ID"="MSI"."INVENTORY_ITEM_ID" AND "MMT"."TRANSACTION_SOURCE_ID"="GMD"."BATCH_ID")

      13 - access("MMT"."TRX_SOURCE_LINE_ID"="GMD"."MATERIAL_DETAIL_ID")

           filter("MMT"."TRX_SOURCE_LINE_ID" IS NOT NULL)

      14 - access("MMT"."TRANSACTION_ID"="LOT"."TRANSACTION_ID")

      15 - filter("LOT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND "LOT"."TRANSACTION_SOURCE_ID"="GBH"."BATCH_ID"

                  AND "LOT"."ORGANIZATION_ID"="MMT"."ORGANIZATION_ID" AND "LOT"."INVENTORY_ITEM_ID"="MMT"."INVENTORY_ITEM_ID")

    for now my best results here:

        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 gbh.BATCH_STATUS = 4 --4 CLOSED, 3-COMPLETED

    best.png

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       5 - access("GBH"."FORMULA_ID"="FMD"."FORMULA_ID")

       6 - access("MSI"."INVENTORY_ITEM_ID"="FMD"."INVENTORY_ITEM_ID" AND

                  "MSI"."ORGANIZATION_ID"="FMD"."ORGANIZATION_ID")

       8 - access("MSI"."ORGANIZATION_ID"=1509)

       9 - filter("FMD"."ORGANIZATION_ID"=1509)

      10 - filter("GBH"."ORGANIZATION_ID"=1509 AND "GBH"."BATCH_STATUS"=4)

      11 - filter("GMD"."ORGANIZATION_ID"=1509)

      12 - access("GBH"."BATCH_ID"="GMD"."BATCH_ID")

      13 - filter("MMT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND

                  "MMT"."INVENTORY_ITEM_ID"="MSI"."INVENTORY_ITEM_ID" AND "MMT"."TRANSACTION_SOURCE_ID"="GMD"."BATCH_ID")

      14 - access("MMT"."TRX_SOURCE_LINE_ID"="GMD"."MATERIAL_DETAIL_ID")

           filter("MMT"."TRX_SOURCE_LINE_ID" IS NOT NULL)

      15 - access("MMT"."TRANSACTION_ID"="LOT"."TRANSACTION_ID")

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited May 22, 2018 9:04AM

    You haven't answered my question about the sort condition.

    You list of indexed columns isn't particularly helpful since it doesn't include the index name, so we can't tell what columns are in what index.

    What does

    select count(*) from gme_batch_header where organization_id = 1509;

    return?

    An index on fm_matl_dtl (fmd.formula_id, fmd.inventory_item_id, organization_id) may help.

  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    edited May 22, 2018 9:27AM
    3706549 wrote:SeánMacGC,I tried you suggestions and I think I got even worst result:

    Not necessarily, there's something else at play here; please run:

    SELECT /*+ gather_plan_statistics*/ ...

    then this immediately after the query runs: SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));

    And post the output here.

  • 3706549
    3706549 Member Posts: 15
    edited May 23, 2018 1:22AM

    Cookiemonster76,

    It's my bad about sort condition, please skip it.

    There is the list of columns which are indexed.

    Query will return 431.995 rows. Without org condition returns 790.828 rows.

    select count(1) from gme.gme_batch_header where organization_id = 1509 and BATCH_STATUS <> -1 ;

    this query returns even less (I don't need cancalled batches) = 408.584

    Sadly I cannot create indexes - I have just read rights.

    SeanMacGc,

    Alright, I ran query which you mentioned here:

    EXPLAIN PLAN FOR   

    SELECT /*+ gather_plan_statistics*/

        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

    FROM

             gme.gme_batch_header gbh

    JOIN gme.gme_material_details gmd ON (gbh.batch_id = gmd.batch_id AND gmd.organization_id = gbh.organization_id)

    JOIN inv.mtl_material_transactions mmt ON (mmt.trx_source_line_id = gmd.material_detail_id AND mmt.transaction_source_id = gmd.batch_id)

    JOIN inv.mtl_system_items_b msi ON (gmd.organization_id = msi.organization_id AND mmt.inventory_item_id = msi.inventory_item_id)

    JOIN inv.mtl_transaction_lot_numbers lot ON (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)

    JOIN GMD.fm_matl_dtl fmd ON (gbh.formula_id = fmd.formula_id AND msi.inventory_item_id = fmd.inventory_item_id AND msi.organization_id = fmd.organization_id)

    WHERE gbh.organization_id = 1509

    And I got error about rights

    User has no SELECT privilege on V$SESSION

  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    edited May 23, 2018 1:41AM

    Well, you really need to have privileges on V$SESSION, that is if you're supposed to do your job properly!

    The reason you need those, is that it's important that we can see the optimizer's estimated cardinalities (row counts) at each execution step, as opposed to the actual cardinalities, which will help to determine why the optimizer may be making a bad choice, and that query on DBMS_XPLAN will retrieve those figures for you.

    But, you need basic privileges and permissions (otherwise, as you've seen, the gather_plan_statistics won't work, as you've seen ).

  • 3706549
    3706549 Member Posts: 15
    edited May 23, 2018 3:01AM

    Yes, you are right.

    I am still trying to get better performance. I would like to ask you if its better to get lower rows number in column named rows, right?

    sql-23_5_2018.png

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       4 - access("GBH"."FORMULA_ID"="FMD"."FORMULA_ID")

       5 - access("MSI"."INVENTORY_ITEM_ID"="FMD"."INVENTORY_ITEM_ID" AND

                  "MSI"."ORGANIZATION_ID"="FMD"."ORGANIZATION_ID")

       7 - access("MSI"."ORGANIZATION_ID"=1509)

       8 - filter("FMD"."ORGANIZATION_ID"=1509)

       9 - filter("GBH"."ORGANIZATION_ID"=1509 AND "GBH"."BATCH_STATUS"=4)

      10 - filter("GMD"."ORGANIZATION_ID"=1509)

      11 - access("GBH"."BATCH_ID"="GMD"."BATCH_ID" AND "GMD"."LINE_NO"="FMD"."LINE_NO" AND

                  "GMD"."LINE_TYPE"="FMD"."LINE_TYPE")

      12 - access("LOT"."TRANSACTION_SOURCE_ID"="GBH"."BATCH_ID")

           filter("LOT"."TRANSACTION_SOURCE_ID" IS NOT NULL)

      13 - filter("LOT"."ORGANIZATION_ID"=1509 AND "LOT"."INVENTORY_ITEM_ID"="GMD"."INVENTORY_ITEM_ID")

    SQL:

        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

        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 gbh.batch_id = gmd.batch_id

        AND gbh.organization_id = gmd.organization_id

       

        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.LINE_TYPE = fmd.LINE_TYPE

        AND gmd.LINE_NO = fmd.LINE_NO --MUZE BYT NESMYSL PRO VICE LAJN

        AND gmd.organization_id = msi.organization_id

        --AND lot.inventory_item_id = msi.inventory_item_id

        AND lot.transaction_source_id = gbh.batch_id

        AND lot.organization_id = gmd.organization_id

        AND lot.inventory_item_id = gmd.inventory_item_id

        --AND lot.lot_number = '1EP17171590' AND msi.inventory_item_id = 1765716

        AND gbh.BATCH_STATUS = 4 --4 CLOSED, 3-COMPLETED

        AND gbh.organization_id = 1509

This discussion has been closed.