News, views, tips and tricks on Oracle and other fun stuff

User Defined Operators

Going through Oracle documentation (yes, I do that sometimes), I found the CREATE OPERATOR command. This command allows you to create user-defined operators. To quote psoug reference:

Oracle allows developers of object-oriented applications to extend the list of built-in relational operators (for example, +, -, /, *, LIKE, AND, OR) with domain specific operators (for example, Contains, Within_Distance, Similar) called user-defined operators. A user-defined operator can be used anywhere built-in operators can be used, for example, in the select list or the where clause. Similar to built-in operators, a user-defined operator may support arguments of different types, and that it may be evaluated using an index. Similar to built-in operators, user-defined operators allow efficient content-based querying and sorting on object data.

I have never created a user-defined operator before, so being the “always-trying-something-new” type of guy, I quickly put together a simple test. Here it is:

First, I wrote a simple function that, given two employee numbers, returns true if the rank of the first employee is greater than the second, and false if it is lower.

scott@eddev> select empno, ename, mgr
  2  from emp
  3  /

     EMPNO ENAME             MGR
---------- ---------- ----------
      7369 SMITH            7902
      7499 ALLEN            7698
      7521 WARD             7698
      7566 JONES            7839
      7654 MARTIN           7698
      7698 BLAKE            7839
      7782 CLARK            7839
      7788 SCOTT            7566
      7839 KING
      7844 TURNER           7698
      7876 ADAMS            7788
      7900 JAMES            7698
      7902 FORD             7566
      7934 MILLER           7782

14 rows selected.

scott@eddev> create or replace function
  2  is_rank_higher_fn (
  3      first_empno_in in number,
  4      second_empno_in in number)
  5  return number
  6  as
  7      emprank_v pls_integer;
  8  begin
  9      with main as (
 10          select empno, level emprank
 11          from emp
 12          where empno in
 13                 (first_empno_in,second_empno_in)
 14          connect by mgr = prior empno
 15          start with mgr is null
 16          )
 17      select emprank
 18      into emprank_v
 19      from main
 20      where empno = first_empno_in
 21      and emprank > (
 22          select emprank
 23          from main
 24          where empno = second_empno_in);
 25      return 1; -- true
 26  exception
 27      when no_data_found then
 28          return 0; -- false
 29  end is_rank_higher_fn;
 30  /

Function created.

Note: I wrote this function quickly and just as an example, however, if you have a better or a different way of writing it, please feel free to comment below.

scott@eddev> select is_rank_higher_fn(7566, 7788)
  2  from dual
  3  /


I then created an operator is_rank_higher and mapped it to the function.

scott@eddev> create or replace operator is_rank_higher
  2  binding(number, number)
  3  return number using is_rank_higher_fn
  4  /

Operator created.

scott@eddev> select is_rank_higher(7566, 7788)
  2  from dual
  3  /


It looks to me that there is almost no difference between using the user-defined operator is_rank_higher and the function is_rank_higher_fn that implements it in the example above. So, I conclude that this may not be the ideal use of user-defined operators.

Based on the documentation, user-defined operators are mainly used in the development of data cartridges (a.k.a. “components” in the OO terminology), and that its real advantage is when a domain index of a user-defined index type is used to efficiently evaluate these user-defined operators. Ok, fine, what about function based indexes? don’t they offer the same advantage?

I am wondering how many of you have developed data cartridges or created user-defined operators or index types!

Related links: reference
Oracle9i Data Cartridge Developer’s Guide
Oracle9i SQL Reference

Filed in Oracle on 29 Aug 05 | Tags: ,

Comments are closed.