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:
http://docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm#i1007102