CODEWITHSUNDEEP
sqlperformanceoracle-databaseindexing
Woot4Moo
Woot4Moo

Reputation: 24316

Generate index on a specific value

Is it possible to generate an index for a specific column value in a database? For example I have a column called status that has the values {0,1,2,3}, but status 2 is most often called so I want to specifically index that value. Is this possible? I imagine the syntax would be similar to the way you can index on descending like so: 

create index foo on table(bar desc);

I could potentially see this working: 

 create index foo on table(status 2);

This generates a missing right parenthesis error in SQLdeveloper against an Oracle database.

Upvotes: 4

Views: 2549

Answers (1)

David Aldridge
David Aldridge

Reputation: 52376

A function based index ...

create index foo on table(case status when 2 then status end)

... and a predicate ...

...
where case status when 2 then status end = 2

... is the thing for this.

This takes advantage of the omission of null values from btree indexes. The case expression returns "2" if the status is 2, and null otherwise, so the index only contains entries for status = "2". You do need a different predicate, as you see. Some other RDBMS's do a bit of a better job of this, frankly.

Upvotes: 8

Related Questions