Database Reference
In-Depth Information
We would see the same sort of query plan we did with the base table. All we have done here is hide the
SUBSTR( F(X), 1, 6 )
function call in the view itself. The optimizer still recognizes that this virtual column is, in
fact, the indexed column and so does the right thing. We see the same performance improvement and the same query
plan. Using this view is as good as using the base table—better even, because it hides the complexity and allows us to
change the size of the
SUBSTR
later.
In Oracle 11
g
Release 1 and above, we have another choice for implementation. Rather than using a view with a
“virtual column,” we can use a real virtual column. Using the feature involves dropping our existing function-based index:
EODA@ORA12CR1> drop index emp_soundex_idx;
Index dropped.
And then adds the virtual column to the table and indexing that column:
EODA@ORA12CR1> alter table emp
2 add
3 ename_soundex as
4 (substr(my_soundex(ename),1,6))
5 /
Table altered.
EODA@ORA12CR1> create index emp_soundex_idx
2 on emp(ename_soundex);
Index created.
Now we can just query the base table—no extra view layer involved at all:
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
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 = 0
function was called: 1
PL/SQL procedure successfully completed.
