LIKE and CONTAINS
385160May 11 2005 — edited May 12 2005I create index:
SQL> CREATE INDEX UM2_ATTRS_STR_I_ctx ON UM2_ENTRIES_ATTRS
(STRING_VALUE) INDEXTYPE IS CTXSYS.CONTEXT;
2
Index created.
I've got 2 query:
SQL> select count(e.id) from um2_entries e , um2_entries_attrs a where e.id = a.entry_id
and a.name = 'user.name'
and a.string_value like '%__%';
2 3
COUNT(E.ID)
-----------
1734
94 msec!!!!!!!!
SQL> select count(e.id) from um2_entries e , um2_entries_attrs a where e.id = a.entry_id
and a.name = 'user.name'
and contains(a.string_value, '%__%', 1) > 0;
2 3
COUNT(E.ID)
-----------
1734
7 sec!!!!!!!!!!!!!!
Why does one of them (with LIKE) goes much faster than the other?