What are the alternatives for prefixed indexes in Oracle?

Posted on

Question :

You can create an index and define prefix length, so the index will store only first starting symbols from each of column value. It looks like this in MySQL:

CREATE INDEX table_idx ON emp(last_name(8));

Are there an alternative how to do that in Oracle?

Answer :

You can create an index on an expression (which you cannot do in MySQL):

CREATE INDEX table_idx ON emp(substr(last_name,1,8));

But that won’t help anything unless you also use that expression in the where clause, e.g.

where substr(last_name,1,8) = 'foobar'

What exactly is the usage of an index in MySQL that doesn’t index the whole value?

I found there are special “Oracle Text” indexes that improve performance for wildcard queries. Such index construction requires a little bit more work but still is a way how to create prefix indexes:


Leave a Reply

Your email address will not be published. Required fields are marked *