CODEWITHSUNDEEP
sqloracle-databasefull-text-searchoracle-text
user49126
user49126

Reputation: 1863

Query optimization and concatenation

I'm running often this query and I would like to optimize it.

select
    number || ' ' || name
from tasks
where upper(number || ' ' || name) like '%VALUE%'

I've created an index, but the it took the same time as without the index

create index name on tasks (upper(number || ' ' || name))

Are there any other options ?

Upvotes: 0

Views: 138

Answers (2)

David Aldridge
David Aldridge

Reputation: 52386

If you're sure that the condition is selective enough to justify using an index then you might like to try promoting an index fast full scan with the following:

select /*+ INDEX_FFS(tasks your_index_name) */
       number || ' ' || name
from   tasks
where  upper(number || ' ' || name) like '%VALUE%'

Edit:

I seem to recall that another way of promoting an IFFS is:

select number || ' ' || name
from   tasks
where  rowid in (
         select rowid
         from   tasks
         where  upper(number || ' ' || name) like '%VALUE%')

Surprisingly barely less efficient that the former method.

Upvotes: 2

Lajos Veres
Lajos Veres

Reputation: 13725

You should consider using text indexes: http://docs.oracle.com/cd/E11882_01/text.112/e24435/overview.htm#i1007403

Upvotes: 3

Related Questions