Database Reference
In-Depth Information
SUBSTR in our query. This is not as nice as just coding
WHERE MY_SOUNDEX(ename)=MY_SOUNDEX( 'King' ) , but we can easily get
around that, as we will see shortly.
We had to use
So, the insert was affected, but the query ran incredibly fast. The payoff for a small reduction in insert/update
performance is huge. Additionally, if you never update the columns involved in the MY_SOUNDEX function call, the
updates are not penalized at all ( MY_SOUNDEX is invoked only if the ENAME column is modified and its value changed).
Let's see how to make it so the query does not have to use the SUBSTR function call. The use of the SUBSTR call
could be error-prone—our end users have to know to SUBSTR from 1 for six characters. If they use a different size,
the index will not be used. Also, we want to control in the server the number of bytes to index. This will allow us to
reimplement the MY_SOUNDEX function later with 7 bytes instead of 6 if we want to. We can hide the SUBSTR with a
virtual column in Oracle Database 11 g Release 1 and above—or a view in any release quite easily as follows:
EODA@ORA12CR1> create or replace view emp_v
2 as
3 select ename, substr(my_soundex(ename),1,6) ename_soundex, hiredate
4 from emp
5 /
View created.
EODA@ORA12CR1> exec stats.cnt := 0;
PL/SQL procedure successfully completed.
EODA@ORA12CR1> exec :cpu := dbms_utility.get_cpu_time
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select ename, hiredate
2 from emp_v
3 where ename_soundex = 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
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 /
cpu time = .01
function was called: 1
PL/SQL procedure successfully completed.
Search WWH ::




Custom Search