Subject Plan question
Author Milan TomeŇ° - Position
Hi all,

I have this query:

SELECT ID_ZLEVELS
FROM ZLEVELS SRC
JOIN STREETS STR
ON (STR.LINK_ID = SRC.LINK_ID) AND
(STR.ALTSTREETS = 'F')
WHERE
(SRC.SRC_DIR_NAME IN ('C1', 'C4')) AND
(SRC.ID_CSYMBOL IN (2000, 2010)) AND
(STR.ID_CSYMBOL = 1360)

and these indexes on table ZLEVELS:

1. CREATE INDEX IX_ZLEVELS_DIR_NODE ON ZLEVELS (SRC_DIR_NAME, NODE_ID);
2. CREATE INDEX IX_ZLEVELS_DIR_SYM ON ZLEVELS (SRC_DIR_NAME, ID_CSYMBOL);

Why this query produces this plan:
PLAN JOIN (SRC INDEX (IX_ZLEVELS_DIR_NODE, IX_ZLEVELS_DIR_NODE), STR
INDEX (IX_STREETS_LINK_ALT, FK_STREETS_CSYMBOL))
?

I know about effect of index selectivity (in this case is selectivity of
index 1 0.0000007 and selectivity of index 2 0.5, but thats because I
haven't already final data - they are prepared in previous step) but I
can't understand why optimizer chooses first one instead of second one
which perfectly fits both where conditions.

Thanks for any hint

M. Tomes