Forum Stats

  • 3,741,782 Users
  • 2,248,475 Discussions
  • 7,861,993 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

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,266 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

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

    No, not necessarily.

    The key thing here is that the estimated 'Rows' value is reasonably accurate -- if that is badly out, then the optimizer can make the wrong choice, resulting in a suboptimal execution step (like opting for a Nested Loop Join where a Hash Join may have been much better), and that's why it helps to see both the 'estimated' and 'actual' row counts at each step of the execution plan. Up-to-date statistics play a central role here, but it's also possible that other factors are at play, like conditions spanning multiple columns of a single table, or a pipelined function, etc.

    In your plan you have a series of Nested Loop Joins, for example, and perhaps those might  be better remodelled alternatively, and also bear in mind that with 12c (and 11gr2), execution plans can change from one invocation to the next, due to the adaptive feedback mechanisms.

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

    OK,

    I think I got my excepted result thanks for EXPLAIN PLAN FOR (I have never used it before):

    Have you any idea how to make it little bit better? Some join I missed?

    end.png

    EXPLAIN PLAN FOR

        SELECT

        gbh.batch_type,

        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 --MOZNA BUDE POTREBA ODSTRANIT!

        AND gmd.organization_id = msi.organization_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_type = 0

       -- AND gbh.BATCH_STATUS in (3, 4) --4 CLOSED, 3-COMPLETED

        AND gbh.BATCH_STATUS = 4

        AND gbh.organization_id = 1509

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

    No, not really, because this is not an execution plan (what actually happened), it's an explain plan (what the database thinks will happen), and without knowing the details here it's very difficult to estimate accurately. For example, it might be that the Full Table Scan on FM_MATL_DTL is suboptimal, but it's not possible to say definitively without seeing the bigger picture.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited May 23, 2018 4:10AM
    3706549 wrote:Cookiemonster76,There is the list of columns which are indexed.

    I know there's a list - I referred to it in reply #6 when I pointed out it needs to include index names so we can see which columns are in which index.

    If you are accessing a table using three columns then a single index on all three columns is going to be much more useful than three indexes each covering just one of the three columns.

    We can't tell which of those cases apply here because you haven't supplied index names as part of the list.

  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited May 23, 2018 4:43AM
    SeánMacGC wrote: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 - Please do this.

  • 3706549
    3706549 Member Posts: 15
    edited May 23, 2018 7:39AM

    SeanMacGC,

    you are right, maybe I should remove this table and use it in final result.

    Cookiemonster,

    can you give me some example which join will be better than? I did not know that about indexes.

    See list below:

        

    COLUMN_NAMEUNIQUENESSINDEX_NAMETABLE_NAME
    ITEM_IDNONUNIQUEFM_MATL_DTLI1FM_MATL_DTL
    LINE_TYPENONUNIQUEFM_MATL_DTLI1FM_MATL_DTL
    FORMULA_IDNONUNIQUEFM_MATL_DTLI1FM_MATL_DTL
    FORMULALINE_IDUNIQUEFM_MATL_DTL_PKFM_MATL_DTL
    FORMULA_IDUNIQUEFM_MATL_DTL_U1FM_MATL_DTL
    LINE_TYPEUNIQUEFM_MATL_DTL_U1FM_MATL_DTL
    LINE_NOUNIQUEFM_MATL_DTL_U1FM_MATL_DTL
    ORGANIZATION_IDNONUNIQUEGME_BATCH_HEADER_N1GME_BATCH_HEADER
    BATCH_NONONUNIQUEGME_BATCH_HEADER_N1GME_BATCH_HEADER
    BATCH_STATUSNONUNIQUEGME_BATCH_HEADER_N1GME_BATCH_HEADER
    BATCH_CLOSE_DATENONUNIQUEGME_BATCH_HEADER_N2GME_BATCH_HEADER
    BATCH_IDUNIQUEGME_BATCH_HEADER_PKGME_BATCH_HEADER
    ORGANIZATION_IDNONUNIQUEGME_BATCH_HEADER_U1GME_BATCH_HEADER
    BATCH_NONONUNIQUEGME_BATCH_HEADER_U1GME_BATCH_HEADER
    BATCH_TYPENONUNIQUEGME_BATCH_HEADER_U1GME_BATCH_HEADER
    ORGANIZATION_IDNONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
    INVENTORY_ITEM_IDNONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
    LINE_TYPENONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
    BATCH_IDNONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
    PHANTOM_IDNONUNIQUEGME_MATERIAL_DETAILS_N2GME_MATERIAL_DETAILS
    MATERIAL_DETAIL_IDUNIQUEGME_MATERIAL_DETAILS_PKGME_MATERIAL_DETAILS
    BATCH_IDUNIQUEGME_MATERIAL_DETAILS_U1GME_MATERIAL_DETAILS
    LINE_NOUNIQUEGME_MATERIAL_DETAILS_U1GME_MATERIAL_DETAILS
    LINE_TYPEUNIQUEGME_MATERIAL_DETAILS_U1GME_MATERIAL_DETAILS
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N1MTL_SYSTEM_ITEMS_B
    SEGMENT1NONUNIQUEMTL_SYSTEM_ITEMS_B_N1MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N10MTL_SYSTEM_ITEMS_B
    PLANNER_CODENONUNIQUEMTL_SYSTEM_ITEMS_B_N10MTL_SYSTEM_ITEMS_B
    WEB_STATUSNONUNIQUEMTL_SYSTEM_ITEMS_B_N11MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N11MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    CUSTOMER_ORDER_ENABLED_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    SERVICE_ITEM_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    VENDOR_WARRANTY_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    USAGE_ITEM_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N13MTL_SYSTEM_ITEMS_B
    WIP_SUPPLY_LOCATOR_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N13MTL_SYSTEM_ITEMS_B
    BASE_ITEM_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N14MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N14MTL_SYSTEM_ITEMS_B
    STYLE_ITEM_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N15MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N15MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N16MTL_SYSTEM_ITEMS_B
    EAM_ITEM_TYPENONUNIQUEMTL_SYSTEM_ITEMS_B_N16MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N2MTL_SYSTEM_ITEMS_B
    DESCRIPTIONNONUNIQUEMTL_SYSTEM_ITEMS_B_N2MTL_SYSTEM_ITEMS_B
    INVENTORY_ITEM_STATUS_CODENONUNIQUEMTL_SYSTEM_ITEMS_B_N3MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N4MTL_SYSTEM_ITEMS_B
    AUTO_CREATED_CONFIG_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N4MTL_SYSTEM_ITEMS_B
    WH_UPDATE_DATENONUNIQUEMTL_SYSTEM_ITEMS_B_N5MTL_SYSTEM_ITEMS_B
    ITEM_CATALOG_GROUP_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N6MTL_SYSTEM_ITEMS_B
    CATALOG_STATUS_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N6MTL_SYSTEM_ITEMS_B
    PRODUCT_FAMILY_ITEM_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N7MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N7MTL_SYSTEM_ITEMS_B
    COMMS_NL_TRACKABLE_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N8MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N8MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N9MTL_SYSTEM_ITEMS_B
    BUYER_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N9MTL_SYSTEM_ITEMS_B
    INVENTORY_ITEM_IDUNIQUEMTL_SYSTEM_ITEMS_B_U1MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDUNIQUEMTL_SYSTEM_ITEMS_B_U1MTL_SYSTEM_ITEMS_B
    TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N1MTL_TRANSACTION_LOT_NUMBERS
    ORGANIZATION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N2MTL_TRANSACTION_LOT_NUMBERS
    TRANSACTION_DATENONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N2MTL_TRANSACTION_LOT_NUMBERS
    LOT_NUMBERNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N3MTL_TRANSACTION_LOT_NUMBERS
    INVENTORY_ITEM_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N3MTL_TRANSACTION_LOT_NUMBERS
    ORGANIZATION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N3MTL_TRANSACTION_LOT_NUMBERS
    TRANSACTION_SOURCE_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N4MTL_TRANSACTION_LOT_NUMBERS
    PRODUCT_CODENONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N5MTL_TRANSACTION_LOT_NUMBERS
    PRODUCT_TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N5MTL_TRANSACTION_LOT_NUMBERS
    SERIAL_TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N7MTL_TRANSACTION_LOT_NUMBERS
    TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N7MTL_TRANSACTION_LOT_NUMBERS
    ORGANIZATION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N7MTL_TRANSACTION_LOT_NUMBERS
This discussion has been closed.