adiw_db schrieb:

>
>
> Hi, I have one table that contains 110.000 records.
>
> I ran a query to this table :
> select knokat from lib_judul where k245a like '%MATEMATIKA%'
> But it doesn't use available indexes (both fields are indexed).
> PLAN (LIB_JUDUL NATURAL)
> It took almost 14 seconds to finish.

An index can't be used if you use a LIKE within a search criteria.

>
> If I changed the query :
> select knokat from lib_judul where k245a like 'MATEMATIKA'

Which is chnaged to "where k245a = 'MATEMATIKA'" by the optimizer, so
the index will be used.

> or
> select knokat from lib_judul where k245a = 'MATEMATIKA'
> It used index, and finished less than a second.
> PLAN (LIB_JUDUL INDEX (IDX_LIB_JUDUL_K245A))

In general:
Like doesn't user an index unless it can be optimized to a statetment
without LIKE, which are:

WHERE FIELD = VALUE

or

WHERE FIELD STARTING WITH VALUE

(So using LIKE 'MATEMATIKA%' will use the index, too since it can be
changed to a STARTING WITH).



Luc.