Oracle Database Discussions

Hi guys!

I yesterday posted the problem below, for which I got helpfull answers, this is now kind of follow up, I hope someone can help me again!

The answer was that the index is not used, because TEXTFIELD1 is the leading collumn of the index, and in the SQL there are multiple values of TEXTFIELD1 queried, for which the index must be accessed each time, so after a certain number of values the optimizer decides to prefer a full table scan.

Now I found out, that in other environments the index is always used nevertheless, because it performs an INDEX SKIP SCAN, although not all possible values of TEXTFIELD1 are queried in the SQL.

So the question:
Does anybody know what could be the reason, that it performs an INDEX SKIP SCAN in one environment and a FULL TABLE SCAN in the other?
Is there a possibillity to force an INDEX SKIP SCAN, so that I would not have to adapt the index or set up a new one?

Thank you in advance!

-------

I have a table, an index on it and a SQL statement:

Table name:
TEST_TBL
Collumns:
TEXTFIELD1
TEXTFIELD2
TEXTFIELD3
TEXTFIELD4
NUMFIELD1
NUMFIELD2

Index name:
IX#TEST_TBL#1
Collumns:
TEXTFIELD1
TEXTFIELD2
NUMFIELD1

SQL:
SELECT * FROM TEST_TBL
WHERE TEXTFIELD3 = 'A'
AND TEXTFIELD1 IN ('A01', 'A02', 'A03', 'A04', 'A05', 'A06', 'A07', 'A08', 'A09', 'A10')
AND TEXTFIELD2 = 'TEST'
ORDER BY TEXTFIELD4 ASC, NUMFIELD2 ASC, TEXTFIELD1 ASC;

The problem is that the SQL does not use the index.

If I remove one or more values from this part:
"AND TEXTFIELD1 IN ('A01', 'A02', 'A03', 'A04', 'A05', 'A06', 'A07', 'A08', 'A09', 'A10')"
like
"AND TEXTFIELD1 IN ('A01', 'A02', 'A03', 'A04', 'A05', 'A06', 'A07', 'A08', 'A09')"
then the index is used.

In another environment with same table, index and statement, it uses the index..