Re: Index on partial field?

Barbara Hiles wrote:
>
> Is it possible to create an index using part of a field?
> For example: 9 character ssn.
> I need an index on last 4 first 5.
> Can I do this if I've defined a 9 char ssn field? (and if so - how? substr?)
>
> tia - barb.

Hi Barb,

My first choice would be to define another field, and populate using a row trigger
that reorders the ssn in the format you described (last 4, first 5). Index this
field as well. eg:

  create or replace trigger yourtable_ssn     after insert or update of ssn
    on yourtable
    for each row
  begin
    if rtrim(:new.ssn) is not null then
      :new.ssn_reordered := substr(:new.ssn,6,4) || :substr(:new.ssn,1,5);

    end if;
  end;
  /

Yes, it would be nice of Oracle allowed for indexing of partial fields or
expressions, but we make do with what we've got.

Hope this helps....

-- 
Barry P. Grove                                  BCTEL  (604)432-4468
barry_grove_at_mindlink.bc.ca                      Human Resources
Consulting
Developer, Oracle DBA, Unix Sysadmin            6-3777 Kingsway,
Burnaby, BC