When you use the index keyword in a select statement the kernel will translate this to the "order by" command and the database optimizer will choose the best index to actually use. When you chose to use the index hint keyword in your select statement, Ax will force the database to use the chosen index.
Index:
When you use the index keyword in a select statement the kernel will translate this to order by command and the database optimizer will choose 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.