1

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?

Improve this question

2 Answers 2

Reset to default
5

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?

Improve this answer
2
  • There might be cases where we use varchar2 type columns with descriptions. If descriptions are long and if each description has some more or lest unique start then it would be convenient to have smaller index that might work faster than full-length index.
    – Centurion
    Commented Dec 8, 2012 at 17:55
  • An example of a useful index that doesn't index the whole value: Suppose 'start dates' are stored as text in the form 'yyyy-mm-dd', but you only want to extract annual recurrences (anniversaries or billing points etc) based on 'mm' matching the current month, or range of months in Q3 say.
    – Gavin Jackson
    Commented Jul 25, 2017 at 13:53
0

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

Improve this answer
3
  • That's pretty radically different from prefix indexes.
    – Mat
    Commented Dec 12, 2012 at 8:38
  • @Mat Why different? Both ways we try to get better performance for wildcard queries. The only thing I not sure in is index size.
    – Centurion
    Commented Dec 12, 2012 at 9:03
  • Index size, how you maintain them (depending on type, there are manual steps, not always up-to-date), how you must write your queries to actually use them, ... Full-text search indexes aren't ordinary database indexes. If what you need in MySQL is a prefix index and not a full-text search index, the equivalent in Oracle is what a_horse_with_no_name posted above, not Oracle Text.
    – Mat
    Commented Dec 12, 2012 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.