---------- Original Message -----------
From: "sboydlns" <sboydlns@...>

> Company
> Account
> Division
> Department
> Effective Date
> Control Number
>
> So, in this example you would
> return all records where the key >= 'COACCT' (which is the company and
> account codes strung together).
>

------- End of Original Message -------

alter table gl add concat_key computed by (company || account || division ||
department);

create asc index ix_gl_concat on gl computed by (company || account || division
|| department);

select * from gl where concat_key > ('CO' || 'ACCT');

Because the expression you're sorting/filtering by matches the expression on the
index, the optimizer will (likely) use the expression index. Don't bother trying
to put an index directly on concat_key, that won't currently do what you want.
(see CORE-1173.) This method allows you to avoid storing the concatenated data
(yay!), and avoids the need to repeat the concatenation in every select statement
(yay!).

Also, keep in mind the behavior of concatenation with char and varchar fields,
make sure varchars don't mess up your concatenated sorting.

From a documentation perspective though, as I, for one, don't often see that
construct (all companies and accounts, starting in the middle of a company??) I
would actually prefer to use your original statement, and make very explicit what
it's doing and why.

-Philip