Database Reference
In-Depth Information
Notice in this function, we are using a keyword,
DETERMINISTIC
. This declares that the preceding function, when
given the same inputs, will always return the exact same output. This is needed to create a function-based index on
a user-written function. We must tell Oracle that the function is
DETERMINISTIC
and will return a consistent result
given the same inputs. We are telling Oracle that this function should be trusted to return the same value, call after
call, given the same inputs. If this were not the case, we would receive different answers when accessing the data via
the index versus a full table scan. This deterministic setting implies, for example, that we cannot create an index on
the function
DBMS_RANDOM.RANDOM
, the random number generator. Its results are not deterministic; given the same
inputs, we'll get random output. The built-in SQL function
UPPER
used in the first example, on the other hand, is
deterministic, so we can create an index on the
UPPER
value of a column.
Now that we have the function
MY_SOUNDEX
, let's see how it performs without an index. This uses the
EMP
table we
created earlier with about 10,000 rows in it:
EODA@ORA12CR1> set autotrace on explain
EODA@ORA12CR1> variable cpu number
EODA@ORA12CR1> exec :cpu := dbms_utility.get_cpu_time
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select ename, hiredate
2 from emp
3 where my_soundex(ename) = my_soundex('Kings')
4 /
ENAME HIREDATE
---------- ---------
Ku$_Chunk_ 17-DEC-13
Ku$_Chunk_ 17-DEC-13
Ku$_Chunk_ 17-DEC-13
Ku$_Chunk_ 17-DEC-13
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1900 | 24 (9)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 100 | 1900 | 24 (9)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MY_SOUNDEX"("ENAME")="MY_SOUNDEX"('Kings'))
EODA@ORA12CR1> set autotrace off
EODA@ORA12CR1> begin
2 dbms_output.put_line
3 ( 'cpu time = ' || round((dbms_utility.get_cpu_time-:cpu)/100,2) );
4 dbms_output.put_line( 'function was called: ' || stats.cnt );
5 end;
6 /
