Deej implemented Expression Indices several years ago -- it's still in the
codebase with an #ifdef (#ifdef EXPRESSION_INDICES, I think). It worked,
but
we never got test resources to develop a test suite for it.

Incidently, one way to get what you want, right now.
Create calculated column with the expression you want.
Create an index on that column.
For the optimizer to use the index, you must use the calculated column
in the where clause eg: WHERE the_sum = 10 instead of WHERE (a+b) = 10
when the_sum is defined as (a+b)

Dave

-----Original Message-----
From: Jan Mikkelsen [mailto:janm@...]
Sent: Wednesday, March 29, 2000 2:24 PM
To: IB-Architect@onelist.com
Subject: Re: [IB-Architect] Syntax for case insensitive sort


From: "Jan Mikkelsen" <janm@...>


The database has always had the ability to collate on the output of a UDF,
so "order by ucase(field)" is a valid way to get case insensitive collation.
The issue is efficiency. Shock, horror! :-)

I'd like to be able to define an index on the output of a UDF. Like:
"Create [unique] index on tablename (translate(fieldname))".

That way I can easily define my own collation order without needing involve
the engine. I can index and collate in the order "McArthur", "MacDonald",
"McMurtrie", "Salamander", "St. Etienne", "St. George", "Something", as they
are meant to be sorted for phone books. Who knows what the phonebook order
is in Botswana? If I did, I could write the function and get the ordering.

No new order by syntax is required, and the fundamental behaviour of
operators doesn't need to change. Now, I'd like to be able to create new
datatypes and redefine operator behaviour, but that is a whole separate
issue.

There are other advantages:

create table locations (longitude real, latitude real, ...);
create index on locations (distance_from_depot(longitude, latitude));
select blah from locations where distance_from_depot(longitude, latitude) <
whatever;

(Maybe not a great example, but you get the idea.)

Clearly more intelligence is required in the optimiser. It must look for
indexes on expressions, not just columns in base tables.

This is the approach used in Illustra/Postgres influenced systems like
Informix and Tandem NonStop-SQL.

The other option might be to allow user defined collations, and use the
"collate" clause. A standard UDF interface where the field value is passed
in, and a version that sorts correctly using a binary comparison is
returned. Then you need a way to define indexes using a given collation ...

Regards,

Jan.



------------------------------------------------------------------------
@Backup- Protect and Access your data any time, any where on the net.
Try @Backup FREE and recieve 300 points from mypoints.com Install now:
http://click.egroups.com/1/2345/3/_/_/_/954368689/
------------------------------------------------------------------------

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com