Database Reference
In-Depth Information
We are now ready to test the performance of the table with the index on it. We would like to monitor the effect
of the index on
INSERT
s as well as the speedup for
SELECT
s to see the effect on each. In the unindexed test case, our
queries take over one second, and if we were to run
SQL_TRACE
and
TKPROF
during the inserts, we could observe that
without the index, the insert of 9,999 records took about 0.30 seconds:
insert into emp NO_INDEX
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
select rownum empno,
initcap(substr(object_name,1,10)) ename,
substr(object_type,1,9) JOB,
rownum MGR,
created hiredate,
rownum SAL,
rownum COMM,
(mod(rownum,4)+1)*10 DEPTNO
from all_objects
where rownum < 10000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.08 0 0 0 0
Execute 1 0.15 0.22 0 2745 13763 9999
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.23 0.30 0 2745 13763 9999
But with the index, it takes about 0.57 seconds:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.39 0.49 131 2853 23313 9999
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.46 0.57 131 2853 23313 9999
This was the overhead introduced in the management of the new index on the
MY_SOUNDEX
function—both in the
performance overhead of simply having an index (any type of index will affect insert performance) and the fact that
this index had to call a stored procedure 9,999 times.
Now, to test the query, we'll just rerun the query:
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> set autotrace on explain
EODA@ORA12CR1> select ename, hiredate
2 from emp
3 where substr(my_soundex(ename),1,6) = my_soundex('Kings')
4 /
