ForceNestedLoops

Views (254)

Hossein.K

6,648

Like(0)

Report

This keyword forces the database server to use a nested-loop algorithm to process
a given SQL statement that contains a join. This means that a record from the
first table is fetched before trying to fetch any records from the second table.
Generally other join algorithms like hash-joins, merge-joins, and others are also
considered. This keyword is frequently combined with the forceSelectOrderkeyword.
Review the previous example with the tables InventTrans and InventDim. You
could risk that the database finds all InventTrans records by the index ItemIdx
and all the InventDim records by the BatchId. (If you hint the index DimIdIdx
this will be used for this search.) The two collections of records are hashed
together. For the database to find the inventTrans and then the inventDim for
each inventTrans, specify forceNestedLoops, as shown in the following
example.

static void DemoForceSelectOrder()
{
  InventTrans inventTrans;
  InventDim inventDim;

  while select forceSelectOrder 
  forceNestedLoop
  inventTrans
  index hint ItemIdx
  where inventTrans.ItemId == 'X'
  join inventDim
  where inventDim.inventDimId ==inventTrans.inventDimId&& inventDim.inventBatchId == 'Y'
  {
  }
}