How bracketing works for indexes

[gmdurga]

Hi,
Could any one of you help me in understanding how bracketing works for indexes.

Example:
I have two tables.

Table1 has the index as ind1 - fld 1 + fld2(date field).
Table2 has the index as ind2 - fld2(date field) + fld1.

Loop1 :
For each archive,
each table1 where table1.fld2 < 02/02/2008
and table1.fld1 = archive.fld1:
This query is very slow.

Loop2:
For each archive,
each table2 where table2.fld1 = archive.fld1
AND table1.fld2 < 02/02/2008 :
This query is working faster.

Note:- Archive table has some 132 districts. So loop1 & loop2 will execute the query for 132 times.

In the first loop (loop1), I heard that the reason is the bracketing will happen for the date range first then checking for all archive id every time.

In the second loop (loop2), the bracketing will happen first on the archive.fld1, because it’s the first element, and then it will bracket on the date.

Whether the bracket will happen in the order how the index fields defined or it will be decided based on the order used in the query.

Please provide if any document available which explain how bracketing works.

Thanks in advance.

 

[TomBascom]

Index selection depends on the order of fields in the index definition NOT the order of comparisons in the WHERE clause.

For a detailed explanation of index selection download Pick an Index, Any Index... from Allegro Consultants.