Posted by Gert-Jan Strik on 11/26/07 22:13

lsllcm,

Is there a join in the query? I get the feeling you did not post all
relevant parts of the query. Why would you get a merge join? And what
does "The system choose index prefix" mean?


There is no hard or fast rule for this. Although I could imagine that
too many predicates would disqualify index seeks, in general it is all
about selectivity. During compilation the optimizer will try to
determine whether index seeks (followed by bookmark lookups) are faster
than (partially) scanning the (clustered) index, based on the estimate
of the number of qualifying rows.

Please note that there is a certain point at which the compilation time
grows a lot for each addition predicate you add to the WHERE clause. If
the compilation time exceeds the estimated gains, the optimizer will
stop compilation and simply choose a "good enough" plan.

If the performance of this query is very important to you, and the
structure of the predicates is as "simple" and predictable as your
example, then you could consider rewriting the query as below:

SELECT C1, C2, C3, C4
FROM TT
WHERE C1 = 'TEST'
AND C2 = '07RES'
AND C3 = '00000'
AND C4 IN ('02383','02382','02381','02380','02379', ...)
UNION ALL
SELECT C1, C2, C3, C4
FROM TT
WHERE C1 = 'TEST'
AND C2 = '07COM'
AND C3 = '00000'
AND C4 IN ('00618','00617','00616', ...)

--
Gert-Jan



lsllcm wrote:
>
> Hi All,
>
> I have one question about many "or" operation make system choose
> incorrect index
>
> There is one table TT (
> C1 VARCHAR(15) NOT NULL,
> C2 VARCHAR(15) NOT NULL,
> C3 VARCHAR(15) NOT NULL,
> C4 VARCHAR(15) NOT NULL
> C5 VARCHAR2(200),
> )
>
> Primary Key TT_PK (C1, C2, C3, C4)
>
> SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND ((C2 =
> '07RES' AND C3 = '00000' AND C4 = '02383') OR (C2 = '07RES' AND
> C3 = '00000' AND C4 = '02382') OR (C2 = '07RES' AND C3 = '00000'
> AND C4 = '02381') OR (C2 = '07RES' AND C3 = '00000' AND C4 =
> '02380') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02379') OR
> (C2 = '07RES' AND C3 = '00000' AND C4 = '02378') OR (C2 = '07RES'
> AND C3 = '00000' AND C4 = '02377') OR (C2 = '07RES' AND C3 =
> '00000' AND C4 = '02376') OR (C2 = '07RES' AND C3 = '00000' AND
> C4 = '02375') OR (C2 = '07RES' AND C3 = '00000' AND C4 =
> '02374') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02373') OR
> (C2 = '07RES' AND C3 = '00000' AND C4 = '02372')
> ... about 100 or operations
> OR (C2 = '07COM' AND C3 = '00000' AND C4 = '00618') OR (C2 =
> '07COM' AND C3 = '00000' AND C4 = '00617') OR (C2 = '07COM' AND
> C3 = '00000' AND C4 = '00616') OR (C2 = '07COM' AND C3 = '00000'
> AND C4 = '00608') )
>
> The system choose index prefix, and query all index leaf with
> C1='TEST'
>
> Prefix: [dbo].[TT].C1 = 'TEST'
>
> After I reduce the OR operators to 50, it use choose
>
> Prefix: [dbo].[TT].C1, [dbo].[TT].C2,[dbo].[TT].C3,[dbo].[TT].C4=
> 'TEST, '07RES', '00000', '02383'
> Then Merge Join, it is very quick,
>
> Can anyone help on this, do I have to reduce the OR operator to 50?
>
> Thanks in advance!

[Back to original message]