Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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?

share|improve this answer
    
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 Dec 8 '12 at 17:55

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

share|improve this answer
    
That's pretty radically different from prefix indexes. –  Mat Dec 12 '12 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 Dec 12 '12 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 Dec 12 '12 at 10:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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