Re: Using Generic Comparator in Oracle ORDER BY

On Wed, 26 Oct 2005 14:03:25 -0700, casey.kirkpatrick interested us by writing:

> Notice, however, that my function would fail if I every received
> "A-10000". It is also computationally more expensive than a direct
> comparison function would have been.

In this case, possibly. And that might indicate it's a byproduct of chosing an inefficient or incorrect design for this environment? Whereas in other environments it might be superior. (Which I find is often a byproduct of designing based on target development rather than designing based on business needs.)

> And finally, it just seems rather
> obfuscous, IMO... it doesn't have the "elegant" feel that I would get
> from a
> "ORDER BY C1 COMPARING WITH F" construct... but then, maybe I'm just
> weird that way.
>

Not wierd. The more I look at it, the more I like the idea of Oracle (or any RDBMS designer) exposing their sort API that way, that is using a Java-like compareTo method in the comparable interface. As far as I know, none have done so.

And I don't see it happening quickly unless there is a compelling reason - either because the standard has changed or because a customer has put in a decent business case. Changing things "'cause it's neat", or "'cause you and/or I want it" doesn't justify the cost of creating and maintaining the test cases needed to verify that there is no (or minimum) impact on existing functionality.

Answering your other question: AIN is shorthand for AIS.1 Identification Number (see http://asn1.elibel.tm.fr/en/), which is a standard way of creating a unique identifier that has the following characteristics:

• globally unique primary key
• implied but not enforced heirarchy
• potential for embedding meaning at any level of the heirarchy
• support for distributed assignment while ensure global uniqueness

It's the scheme behind such monstrosities as LDAP and it's horrid Object ID and Attribute ID. It's is becoming quite popular in various global environments. One problem I have with it is the tendancy for developers and designers to want to put meaning on the 'fields'. (Hint - there are no fields!)

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting guarantees I will not respond further ***