demo09.sql

Expert Oracle Database Architecture 9i and 10g sql源码

create table emp
as
select *
  from scott.emp
 where 1=0;
insert into emp
(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;
create index emp_upper_idx on emp(upper(ename));
begin
   dbms_stats.gather_table_stats
   (user,'EMP',cascade=>true);
end;
/
set autotrace traceonly explain
select *
  from emp
 where upper(ename) = 'KING';
create or replace package stats
as
        cnt number default 0;
end;
/
create or replace
function my_soundex( p_string in varchar2 ) return varchar2
deterministic
as
    l_return_string varchar2(6) default substr( p_string, 1, 1 );
    l_char      varchar2(1);
    l_last_digit    number default 0;

    type vcArray is table of varchar2(10) index by binary_integer;
    l_code_table    vcArray;

begin
    stats.cnt := stats.cnt+1;

    l_code_table(1) := 'BPFV';
    l_code_table(2) := 'CSKGJQXZ';
    l_code_table(3) := 'DT';
    l_code_table(4) := 'L';
    l_code_table(5) := 'MN';
    l_code_table(6) := 'R';


    for i in 1 .. length(p_string)
    loop
        exit when (length(l_return_string) = 6);
        l_char := upper(substr( p_string, i, 1 ) );

        for j in 1 .. l_code_table.count
        loop
        if (instr(l_code_table(j), l_char ) > 0 AND j <> l_last_digit)
        then
            l_return_string := l_return_string || to_char(j,'fm9');
            l_last_digit := j;
        end if;
        end loop;
    end loop;

    return rpad( l_return_string, 6, '0' );
end;
/
set timing on
set autotrace on explain
select ename, hiredate
  from emp
 where my_soundex(ename) = my_soundex('Kings')
/

set autotrace off
set timing off
set serveroutput on
exec dbms_output.put_line( stats.cnt );
create index emp_soundex_idx on
emp( substr(my_soundex(ename),1,6) )
/

REM reset our counter
exec stats.cnt := 0
 
set timing on
set autotrace on explain
select ename, hiredate
  from emp
 where substr(my_soundex(ename),1,6) = my_soundex('Kings')
/
 
set autotrace off
set timing off
set serveroutput on
exec dbms_output.put_line( stats.cnt );

create or replace view emp_v
as
select ename, substr(my_soundex(ename),1,6) ename_soundex, hiredate
  from emp
/
 
exec stats.cnt := 0;
 
set timing on
select ename, hiredate
  from emp_v
 where ename_soundex = my_soundex('Kings')
/
set timing off
exec dbms_output.put_line( stats.cnt )