doubt

[Mnemonical]

where substr(col1, 1,5) = 'value';

I have an index on 'col1' column, it's not function based. Is there any possibility of using the index changing the way this clause (where clause) is written?


Tks.

F.

[stancho]

You can use
col1 like 'value%'
that way the optimizer will use an index on col1.

[balajiyes]

col1 like 'value%' this won't solve his problem.

see wht will happen if the table contains record like "VALUES". he needs data which has a value called VALUE only.

[balajiyes]

from tim's site

Why Indexes Aren't Used
The presence of an index on a column does not guarantee it will be used. The following is a small list of factors that will prevent an index from being used:

The optimizer decides it would be more efficient not to use the index. As a rough rule of thumb, on evenly distributed data an index will be used if it restricts the number rows returned to 5% or less of the total number of rows. In the case of randomly distributed data, an index will be used if it restricts the number of rows returned to 25% or less of the total number of rows.
You perform a function on the indexed column i.e. WHERE Upper(name) = 'JONES'
You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001
You concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
You do not include the first column of a concatenated index in the WHERE clause of your statement. For the index to be used in a partial match, the first column (leading-edge) must be used.
It is debatable whether the order of columns referencing the index is important. There are sometimes differences in the performance of the statement depending on column order, especially when using Rule Based Optimization (RBO).
The use of 'OR' statements confuses the Cost Based Optimizer (CBO). It will rarely choose to use an index on column referenced using an OR statement. It will even ignore optimizer hints in this situation. The only way of guaranteeing the use of indexes in these situations is to use the /*+ RULE */ hint.

[TomazZ]

Originally posted by balajiyes
col1 like 'value%' this won't solve his problem.

see wht will happen if the table contains record like "VALUES". he needs data which has a value called VALUE only.

No, he needs data where first 5 characters are 'value'.
This is what he will get with
where substr(col1,1,5)='value'
and the same with
where col1 like 'value%'

The latter could use an index, generally.

[balajiyes]

oh! Sorry I got it wrong.

[Mnemonical]

Originally posted by TomazZ
No, he needs data where first 5 characters are 'value'.

No, I need the exact 5 characters, not data with the first 5 characters, I want from 1 to 5.


F.

[slimdave]

It is possible that given a suitable query, Oracle would choose to use a fast full index scan. If you just wanted ...

select count(*) from my_table
where substr(my_column,3,5) = 'ABCDE'

... for example, then an index on my_column coud be fast ful index scanned to get the result.

[TomazZ]

Originally posted by Mnemonical
No, I need the exact 5 characters, not data with the first 5 characters, I want from 1 to 5.

I don't get you. Aren't the first 5 characters exactly the ones from 1 to 5