>Hi,
>
>Let's take a select:
>select * from A where A.id like 'text%'
>We know the:
>A is indexed on column ID with index T_A_ID
>Size of table is 134000 lines
>
>I have the situations:
>if text='PH01' there are 1000 lines returned in 1 second (index is used)
>if text='PH0' there are 9900 lines returned in 12 seconds (index is not used)
>if text='PH0' and add the clause 'with (index (T_A_ID))' to query same 9900 line gets in 2 seconds
>
>Why the engine is not using index in second case and loose performance?
>
>I don't want to write in every select 'with (index...' to optimize the query, it's stupid.
>
>Thanks.

This is a statistic issue.
The engine supposes that the query is a little selective,
and that the use of an index penalizes the performances.

look

select id from A where A.id like 'PH0%'