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 /
IS_RANK_HIGHER_FN(7566,7788)
----------------------------
0
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 /
IS_RANK_HIGHER(7566,7788)
-------------------------
0
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:
psoug.org reference
Oracle9i Data Cartridge Developer’s Guide
Oracle9i SQL Reference
Related articles:
Filed in Oracle on 29 Aug 05 | Tags: operator, user-defined
