Index chosen is wrong index

I'm on SQL Server 2000 SP3.
The execution plan for the query below selects the wrong
index. It selects the index that has the leading column
as SDITIM, which is not even in the where clause nor the
order by clause. The index that it chose is not even the
clustered index. Any ideas on WHY? Is this a BUG?
Index Chosen in Execution Plan
====================================
CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
[SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
GO
SQL Statement
==============
SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
SDOKCO, SDOORN, SDOCTO, SDRKCO,
SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
SDITM, SDLITM, SDAITM, SDLOCN,
SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
SDRLIT, SDRKIT, SDSRP1, SDSRP2,
SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
SDSOCN, SDUPRC, SDAEXP, SDUNCS,
SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
SDCARS, SDMOT, SDZON, SDFRTH,
SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
SDFUP, SDFEA, SDFUC, SDTORG
FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
SDDCTO = 'S2' AND
SDLTTR <= '999'
AND SDMCU = ' 9500' )
UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
SDMCU, SDOKCO,
SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
SDAN8, SDSHAN, SDPA8, SDDRQJ,
SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
SDRSDJ, SDPEFJ, SDVR01, SDITM,
SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
SDNXTR, SDLTTR, SDEMCU, SDRLIT,
SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
SDUORG, SDSOQS, SDSOBK, SDSOCN,
SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
SDPSN, SDDELN, SDCDCD, SDCARS,
SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
SDSWMS, SDCRCD, SDCRR, SDFUP,
SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
<= '999' AND
SDMCU = ' 9500')
It's difficult to say without the other relevant DDL. I would guess that
you don't have other indexes the optimizer considers beneficial here but
some of the other columns in your predicates are part of a clustered index.
In this case, the optimizer might choose a scan of narrow non-clustered
index to retrieve values of clustered index keys instead of a clustered
index (table) scan.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in message
news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
>
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500')
|||Looks fine to me. SDMCU is in the WHERE clause and it is one of the SARGS
where you use an =. If the selectivity of this is low enough (and the
optimizer thinks it is) it can find the rows that match the value of '9500'
and just filter for the other sargs. Just a note, are the columns SDLTTR
and SDMCU really characters or integers?
Andrew J. Kelly SQL MVP
"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in message
news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
>
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500')
|||Jean,
as other have suggested, you have not provided enough information to
support any conclusion. For example, do you have any unique
constraints/indexes, do you have a clustered index (if so, what is its
definition), what is the distribution of the data, etcetera.
In addition, since you are joining two resultsets, there is likely to be
a second index seek/scan, and the union can affect the query plan.
If you post more information, maybe someone can say something with more
confidence.
BTW: I assume obfuscated the query on purpose, it is really
unreadable...
Gert-Jan
Jean Bertrand wrote:
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500')
(Please reply only to the newsgroup)
|||Thanks for your response.
Please note that the query is from JD Edwards, so I have
no control over it. The data types of the columns in
predicates are truly characters, so they do match the
values. No problems there!
So, I don't understand how this could be correct if the
leading column for index F4211_9 is on the column SDITIM,
which isn't in the predicate. I would think that the
optimizer would have chosen another index where the
leading columns are in the predicate. (I didn't realize
that it could use an index when only the middle column is
in the predicate.)

>--Original Message--
>Looks fine to me. SDMCU is in the WHERE clause and it
is one of the SARGS
>where you use an =. If the selectivity of this is low
enough (and the
>optimizer thinks it is) it can find the rows that match
the value of '9500'
>and just filter for the other sargs. Just a note, are
the columns SDLTTR
>and SDMCU really characters or integers?
>--
>Andrew J. Kelly SQL MVP
>
>"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in
message[vbcol=seagreen]
>news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
wrong[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
AND
>
>.
>
|||Thanks for the response, and I apologize for the lack of
detail. This is a JD Edwards query, which I have no
control over. In addition, they have lots of indexes on
this table... (They love to over index!) So including
them would be quite lengthy.
Anyway, there are other indexes that exist where the
leading column is in the predicate. For example, there is
an index on SDLTTR. Why didn't it use that one?
I don't understand why it would think the scan of an
index whose leading column isn't "filled-in" would be
more efficient than just doing a full table scan (or
using one of the other indexes in the predicate.)

>--Original Message--
>It's difficult to say without the other relevant DDL. I
would guess that
>you don't have other indexes the optimizer considers
beneficial here but
>some of the other columns in your predicates are part of
a clustered index.
>In this case, the optimizer might choose a scan of
narrow non-clustered
>index to retrieve values of clustered index keys instead
of a clustered
>index (table) scan.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in
message[vbcol=seagreen]
>news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
wrong[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
AND
>
>.
>
|||Thanks for the response.
1. There is a unique contraint/clustered index on
columns [SDDOCO], [SDDCTO], [SDKCOO], and [SDLNID]
2. I had to chuckle at your comment "I assume you
obfuscated the query on purpose, it is really
unreadable..."
The answer is "no". This is JD Edwards... I have no
control over their database nor their code.
Deciphering one of there queries is like reading a
foreign language...

>--Original Message--
>Jean,
>as other have suggested, you have not provided enough
information to
>support any conclusion. For example, do you have any
unique
>constraints/indexes, do you have a clustered index (if
so, what is its
>definition), what is the distribution of the data,
etcetera.
>In addition, since you are joining two resultsets, there
is likely to be
>a second index seek/scan, and the union can affect the
query plan.
>If you post more information, maybe someone can say
something with more
>confidence.
>BTW: I assume obfuscated the query on purpose, it is
really[vbcol=seagreen]
>unreadable...
>Gert-Jan
>
>Jean Bertrand wrote:
wrong[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
AND
>--
>(Please reply only to the newsgroup)
>.
>
|||I'm sorry but I thought the leading column in the index you posted was
SDMCU. Because of the way it wrapped I missed that it was really the second
column in the index. Is there another index where SDMCU is the first column
in the index? How many rows match the value of 9500 for SDMCU? If it's
not too many the partial index scan could still be the most efficient. You
mentioned in the other post that the first column in this index isn't filled
in. That means it is all the same value so it's pretty easy to see where
the 9500 values are in the second column of the index. Even if the number
of rows are more than would normally be used for a seek it can seek the
first matching row in the index and scan from there until it gets to the
last one. That's actually pretty efficient over scanning a whole large
table if the numbers are right.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:0f5b01c46ec6$123f8830$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks for your response.
> Please note that the query is from JD Edwards, so I have
> no control over it. The data types of the columns in
> predicates are truly characters, so they do match the
> values. No problems there!
> So, I don't understand how this could be correct if the
> leading column for index F4211_9 is on the column SDITIM,
> which isn't in the predicate. I would think that the
> optimizer would have chosen another index where the
> leading columns are in the predicate. (I didn't realize
> that it could use an index when only the middle column is
> in the predicate.)
> is one of the SARGS
> enough (and the
> the value of '9500'
> the columns SDLTTR
> message
> wrong
> column
> the
> the
> AND
|||jean.bertrand@.sbcglobal.net wrote:
> Thanks for the response.
> 1. There is a unique contraint/clustered index on
> columns [SDDOCO], [SDDCTO], [SDKCOO], and [SDLNID]
> 2. I had to chuckle at your comment "I assume you
> obfuscated the query on purpose, it is really
> unreadable..."
> The answer is "no". This is JD Edwards... I have no
> control over their database nor their code.
> Deciphering one of there queries is like reading a
> foreign language...
Hahaha, I guess JD Edwards did an excellent job at obfuscating :-)
So let's see what we have, and take it apart...
1. Is the index F4211_9 still used if you remove the "UNION SELECT"
part. The UNION (as opposed to UNION ALL) forces the resultset to be
merged, and so this can influence the query plan.
After removing the UNION SELECT part, I simplified the query to the
following:
SELECT SDLTTR, SDDCTO, SDMCU -- used in WHERE clause
, SDDOCO, SDKCOO, SDLNID -- covered by clustered index
, SDITM, SDDRQJ -- covered by index F4211_9
-- and many other columns not covered by any index
FROM PRODDTA.F4211 (NOLOCK)
WHERE SDLTTR BETWEEN '520' AND '999'
AND SDDCTO = 'S2'
AND SDMCU = ' 9500'
-- CLUQ = (SDDOCO, SDDCTO, SDKCOO, SDLNID)
-- IX F4211_9 = (SDITM, SDMCU, SDDRQJ)
So now, let's explore a few options that SQL-Server can choose from:
a) Seek the clustered index: not possible
b) Seek index F4211_9: not possible
c) Scan clustered index: always possible. Seems a fairly good choice,
but the F4211 table seems to be a very wide table (many bytes per row)
d) Scan index F4211_9 and perform bookmark lookups: this will cover the
SDMCU and SDDCTO predicates, but not the SDLTTR predicate. The index
F4211_9 is a narrow index (few bytes per row).
So now it depends on the estimated data distribution. If SQL-Server
estimates that there are many rows where SDDCTO = 'S2' AND SDMCU =
' 9500', then it will not choose scenario d. If it estimates that
there will just be a few rows, then it will.
2. How many pages does the table and the individual indexes occupy?
This can be queried with:
select indid,left(name,30),dpages from sysindexes where
id=object_id("F4211") and name not like '[_]WA%'
3. Most likely there are statistics on the relevant columns. These
statistics are used to estimate how many rows will have a SDDCTO of 'S2'
and how many rows will have a SDMCU of ' 9500'.
This can be queried with:
dbcc show_statistics ("F4211", 'SDDCTO')
dbcc show_statistics ("F4211", 'SDMCU')
Now that you have all the numbers, let's make your own estimation. For
this example, I made up some numbers. You can replace them with the real
numbers. Also, I guessed some internal SQL-Server estimation
percentages.
My example numbers:
- Rows in table: 100,000
- Size of table F4211 in pages: 10,000
- Non-leaf level of clustered index in pages: 700
- Size of index F4211_9 in pages: 900
- Occurrence of value 'S2' in column SDDCTO: 0.3%
- Occurrence of value ' 9500' in column SDMCU: 0.1%
- Assume a 60% overlap between the rows where SDDCTO='S2' and
SDMCU=' 9500'
Cost list for use of index F4211_9:
a) scan entire F4211_9 index: 900 reads
b) lowest occurrence column (SDMCU, 0.1%) * overlap percentage (60%) *
rows in table = 60 rows that need to be looked up: 60 reads
Total estimated cost: 960 reads
Cost list for use of clustered index scenario 1 (full scan):
a) scan the entire clustered index: 10,000 reads
Total estimated cost: 10,000 reads
Cost list for use of clustered index scenario 2 (partial scan):
a) scan tree (non-leaf level) for SDDCTO='S2': 700 reads
b) rows found approximately 0.3% of 100,000 = 300. Estimation with
respect to leaf level: 300 reads
Total estimated cost: 1,000 reads
In this example, using index F4211_9 would in fact be the fastest
estimated solution!
Hope this helps,
Gert-Jan
(Please reply only to the newsgroup)
|||> Anyway, there are other indexes that exist where the
> leading column is in the predicate. For example, there is
> an index on SDLTTR. Why didn't it use that one?
The optimizer estimates the number of qualifying rows based on index
statistics. If many rows satisfy your SDLTTR range criteria, it may be more
efficient to use another technique.

> I don't understand why it would think the scan of an
> index whose leading column isn't "filled-in" would be
> more efficient than just doing a full table scan (or
> using one of the other indexes in the predicate.)
A non-clustered index is usually much smaller than the underlying table so
it can take less time to scan the entire non-clustered index than to scan
the entire clustered index (table).
AsGert-Jan mentioned, the UNION may play a role in the index choice and
query plan. I see from your other post that you have a unique clustered
index on SDDOCO, SDDCTO, SDKCOO and SDLNID. This means the F4211 index
contains SDITM, SDMCU and SDDRQJ as well as SDDOCO, SDDCTO, SDKCOO and
SDLNID. The combination of these values will be unique and all are in your
SELECT list so SQL Server may have chosen it to help the UNION.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:0f7501c46ec6$afa2d280$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the response, and I apologize for the lack of
> detail. This is a JD Edwards query, which I have no
> control over. In addition, they have lots of indexes on
> this table... (They love to over index!) So including
> them would be quite lengthy.
> Anyway, there are other indexes that exist where the
> leading column is in the predicate. For example, there is
> an index on SDLTTR. Why didn't it use that one?
> I don't understand why it would think the scan of an
> index whose leading column isn't "filled-in" would be
> more efficient than just doing a full table scan (or
> using one of the other indexes in the predicate.)
>
> would guess that
> beneficial here but
> a clustered index.
> narrow non-clustered
> of a clustered
> message
> wrong
> column
> the
> the
> AND