Database Reference
In-Depth Information
ENAME HIREDATE
---------- ---------
Ku$_Chunk_ 17-DEC-13
Ku$_Chunk_ 17-DEC-13
Ku$_Chunk_ 17-DEC-13
Ku$_Chunk_ 17-DEC-13
Plan hash value: 1897478402
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ...
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3300 | 12 ...
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 100 | 3300 | 12 ...
|* 2 | INDEX RANGE SCAN | EMP_SOUNDEX_IDX | 40 | | 1 ...
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SUBSTR("EODA"."MY_SOUNDEX"("ENAME"),1,6)="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 /
cpu time = .01
function was called: 1
PL/SQL procedure successfully completed.
If we compare the two examples (unindexed versus indexed), we find that the insert into the indexed table was
affected by a little more than twice the runtime. However, the select went from two-tenths of a second to effectively
instantly. The important things to note here are the following:
•
The insertion of 9,999 records took approximately two times longer. Indexing a user-written
function will necessarily affect the performance of inserts and some updates. You should
realize that any index will impact performance, of course. For example, I did a simple test
without the
MY_SOUNDEX
function, just indexing the
ENAME
column itself. That caused the
INSERT
to take about one second to execute—the PL/SQL function is not responsible for the
entire overhead. Since most applications insert and update singleton entries, and each row
took less than 1/10,000 of a second to insert, you probably won't even notice this in a typical
application. Since we insert a row only once, we pay the price of executing the function on the
column once, not the thousands of times we query the data.
•
While the insert ran two times slower, the query ran many times faster. It evaluated the
MY_SOUNDEX
function a few times instead of almost 10,000 times. The difference in
performance of our query here is measurable and quite large. Also, as the size of our table
grows, the full scan query will take longer and longer to execute. The index-based query will
always execute with nearly the same performance characteristics as the table gets larger.
