Hi There's probably not much to choose between them but...
If there are lots of records with the same value for field1 then the database will need to scan through lots of index leaf nodes to reach all of the records that you want to select. Since the leaf nodes for itemIdx will be smaller than the leaf nodes for numIdx there will be more leaf nodes per page which means that the database might need to read fewer index pages as it scans through all the nodes with the field1 value that you're selecting and so will generate less I/O using this index. Having said all that, the optimiser is almost certainly smart enough to know this and will pick itemIdx anyhow and if that's the case then it makes little since to add the index hint. The only caveat on this is that AX is probably parameterising this query to make it reusable (i.e. allow different values of _field1 to use the same query plan). This means that the optimiser will take an "on average" view of the number of leaf nodes that have the same value for field1 which is fine if you have a roughly even spread of values for field1 but if the field1 value distribution is actually skewed towards one or two of the possible values then the "on average" view might not produce the best plan and this is the point where you would step in and use a query hint to push the optimiser into picking a specific plan. Malcolm From: [email protected] [mailto:[email protected]] On Behalf Of thomas 'znal' ramdhan Sent: 19 August 2010 03:12 To: [email protected] Subject: Re: [Axapta-Knowledge-Village] when to use Index and index hint Hi Malcolm, >From above question, I'd like to ask another question which related to >indexhint. Let's say I need to query to tableA which field criteria is field1. select * from tableA where tableA.field1 = _field1 indexs for table A are below 1. NumIdx field1 field2 field3 2. ItemIdx field1 field3 In best practice, which index should I use ? Thanks and Regards, Afin On Wed, Aug 18, 2010 at 6:53 PM, Malcolm Burtt <[email protected]<mailto:[email protected]>> wrote: Hi For most queries, the answer is to use neither. "Index Hint" overrides the SQL Query optimiser and forces the use of the specified index - which is ok if you're sure that the optimiser is picking a bad query plan and that using "index hint" allows your query to run faster but most of the time the optimiser is better at picking the right index than you are so you should let it do its job. When you do need to tune the query you should consider other hints (e.g. forcenestedloop, etc) alongside "index hint" to get the most effective query plan for your data. I can't think of a good reason to use "Index" at all. It simply ensures that the data is returned sorted in the index order which you can already achieve with the "Order By" clause with the important advantage that the sort order would not change if someone altered the index definition in some way. Regards Malcolm Burtt Touchstone Group People - Partnership - Solutions -- ne faites pas une limite pour votre competence ------------------------------------------------------------------------- bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit ------------------------------------------------------------------------- non faccia un limite per la vostra abilita ------------------------------------------------------------------------- --(-@
