Index:
When you use the index keyword in a select statement the kernel will translate this to a order by command and the database optimizer will chose the best index to actually use.
Index hint:
When you chose to use the index hint keyword in your select statement, Ax will force the database to use the chosen index.
When you use the index keyword in a select statement the kernel will translate this to a order by command and the database optimizer will chose the best index to actually use.
Example: select * from InventTable index GroupItemIdx will generate the following SQL statement to the database:
SELECT A.ITEMGROUPID, A.ITEMID, A.ITEMNAME,.... FROM INVENTTABLE A ORDER BY A.ITEMGROUPID, A.ITEMID
The Index ItemGroupIdx of the InventTable exactly contains the two fields ItemGroupID and ItemId (in that order). Using "index", you still give the control of which index to use to the database optimizer. So, if the optimizer finds a better index to use, it will use it.
Index hint:
When you chose to use the index hint keyword in your select statement, Ax will force the database to use the chosen index.
Example: select * from InventTable index hint GroupItemIdx will generate the following SQL statement to the database:
SELECT /*+ INDEX(A I_175GROUPITEMIDX) */ A.ITEMGROUPID, A.ITEMID, A.ITEMNAME,.... FROM INVENTTABLE A
Using "index hint", you take away the control of which index to use from the database optimizer. So, if there may be a better index, the database will not use it.
Conclusion:
Adding the "index" statement to an Axapta select, it does NOT mean that this index will be used by the database. What it DOES mean is that Axapta will send an "order by" to the database.
Adding the "index hint" statement to an Axapta select, it DOES mean that this index will be used by the database (and no other one).
No comments:
Post a Comment