Indexes

index op NAME ?

UPPER(NAME) = ..
or
UPPER(NAME) like ..
This will never use an index because of the UPPER-function

You could try to make a function based index on UPPER(NAME)

Regards
Gert

I agree with Panda here, Panda there :)
If you use LIKE , the index will not be used.

well, if you have UPPER , you could still create a function based index and use it, but if you use LIKE , there's no way you'll use the index, coz it does have to look at every row and compare...

ya thanx a lot.....
i just saw the plan and i saw that the culprit was LIKE

and i cannot avoid it...it has to be there
thanx a lot guys...

IMHO,
the real culprit isn't LIKE itself, but LIKE '%name%'.
Oracle has no chance to use index for such template - it can't compare values in index with something that has no definite beginning.
You should use LIKE 'name%' or if you really need to search a column for values from its middle, cut the column into more colums.

Ales

Using a funtion within a select statement on an indexed column will ALWAYS cause the index to be dis-regarded (unles you have a function based index).

Denis.

Just for clarification:

- WHERE function(column) = 'x' will *not* use an index *unless* you have created a function-based index on function(column).

- WHERE column LIKE 'x%' *will* use an index

- WHERE column LIKE '%x%' will *not* use an index *by default* but *will* run faster if you hint the index in the query.

- Using the InterMedia functionality might be a valid alternative as someone mentioned. It allows you to do indexed searches for words *within* strings, but has some restrictions. Worth looking into, however.

- Chris

A work around and it works

SELECT * FROM table_name WHERE UPPER(name) LIKE '%ABC%'
AND (name LIKE 'ABC%' OR name LIKE 'abc%' );

Well, this is logicaly not the same query as you provided before. Previously you said you want to use

... WHERE UPPER(name) LIKE '%ABC%',

which means the query would return anything that *includes* string "ABC" in any case mixture. So it should return 'ABC', 'abc', 'AbCdef', 'xyaBcc' etc...

With this workaround of yours it will return only strings that *begins* with 'abc' or 'ABC'. So it will return 'ABC', 'abc', but not something like 'AbCdef', 'xyaBcc' etc...

And BTW, in your workaround you have redundant condition, you don't need the first condition. IT is the same as:

SELECT * FROM table_name WHERE name LIKE 'ABC%' OR name LIKE 'abc%' ;

SELECT * FROM table_name WHERE UPPER(name) LIKE '%ABC%'
AND (name LIKE '%ABC%' OR name LIKE '%abc%' );

actually even this work.....
sorry...at first i forgot % signs
:p