Hi Malcolm, Thanks for great explanation :)
On Thu, Aug 19, 2010 at 4:53 PM, Malcolm Burtt < malcolm.bu...@touchstone.co.uk> wrote: > > > 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:* Axapta-Knowledge-Village@yahoogroups.com [mailto: > axapta-knowledge-vill...@yahoogroups.com] *On Behalf Of *thomas 'znal' > ramdhan > *Sent:* 19 August 2010 03:12 > *To:* Axapta-Knowledge-Village@yahoogroups.com > *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 < > malcolm.bu...@touchstone.co.uk> 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 > ------------------------------------------------------------------------- > --(-@ > > > -- ne faites pas une limite pour votre competence ------------------------------------------------------------------------- bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit ------------------------------------------------------------------------- non faccia un limite per la vostra abilita ------------------------------------------------------------------------- --(-@
