CODEWITHSUNDEEP
oracle-databaseoracle11gindexing
filippo
filippo

Reputation: 5793

Create index for last two digits of number in Oracle

I have a massive table in which I can't do any more partitioning or sub-partitioning, nor am allowed to do any alter. I want to query its records by batches, and thought a good way would be using the last two digits from the account numbers (wouldn't have any other field splitting records as evenly).

I guess I'd need to at least index that somehow (remember I can't alter table to add a virtual column either).

Is there any kind of index to be used in such situation?

I am using Oracle 11gR2

Upvotes: 1

Views: 154

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

You can use function based index:

create index two_digits_idx on table_name (substr(account_number, -2));

This index will work only in queries like that:

select ...
  from table_name t ...
 where substr(account_number, -2) = '25' -- or any other two digits

For using index, you need to use in a query the same expression like in an index.

Upvotes: 3

Related Questions