ForceSelectOrder

This keyword forces the database server to access the tables in a join in the given
order. If two tables are joined the first table in the statement is always accessed
first. This keyword is frequently combined with the forceNestedLoop keyword.
One situation where it can be interesting to force a select order is when you useindex hint on a join. The following construction is an example of theForceSelectOrder.

1 2 3 4 5 6 7 8 9 10 11 12

static void DemoForceSelectOrder() { InventTrans inventTrans; InventDim inventDim; while select inventTrans index hint ItemIdx where inventTrans.ItemId == 'X' join inventDim where inventDim.inventDimId ==inventTrans.inventDimId && inventDim.inventBatchId == 'Y' { } }

Give the database a hint on using the index ItemIdx on the table InventTrans.
This works well if the database searches this table first. But if the database, with
the help of the generated statistics, starts with the table InventDim and then finds
records in InventTrans for each occurrence of InventDimId, the use of the
index ItemIdx may not be an appropriate approach. To hint indexes in a join,
consider specifying forceSelectOrder, as shown in the following example.

1 2 3 4 5 6 7 8 9 10 11 12 13

static void DemoForceSelectOrder() { InventTrans inventTrans; InventDim inventDim; while select forceSelectOrder inventTrans index hint ItemIdx where inventTrans.ItemId == 'X' join inventDim where inventDim.inventDimId ==inventTrans.inventDimId && inventDim.inventBatchId == 'Y' { } }

Best Regards,
Hossein Karimi