--*********************************************************************
--*** Separate into four scripts, some code duplicated              ***
--*********************************************************************
-- SQL script to create function-based index
drop index manyemp_ename_idx;
create index manyemp_ename_idx 
   on many_emps(upper(ename))
   tablespace adv_oraclass_idx_space;
analyze table many_emps
  compute statistics;
analyze index manyemp_ename_idx      -- not really needed 
  compute statistics;
commit;
--*********************************************************************
-- Sample SQL script to test use of Function-based index
--
--  First test should do a tablespace scan
--  Second test should use the available index
--
-- Alter session to make sure function-index not used (default)
  alter session set query_rewrite_enabled = false;
-- Alter tablespaces offline/online to flush cache
  alter tablespace adv_oraclass_space offline;
  alter tablespace adv_oraclass_idx_space offline;
  alter tablespace adv_oraclass_space online;
  alter tablespace adv_oraclass_idx_space online;
-- turn on SQL*Plus tracing tool
  set autotrace on
  select ename,job
     from many_emps
     where upper(ename) = 'CLARK'
        and deptno = 10;
-- Alter session so that function-index may be used
  alter session set query_rewrite_enabled = true;
-- Alter tablespaces offline/online to flush cache
  alter tablespace adv_oraclass_space offline;
  alter tablespace adv_oraclass_idx_space offline;
  alter tablespace adv_oraclass_space online;
  alter tablespace adv_oraclass_idx_space online;
  select ename,job
     from many_emps
     where upper(ename) = 'CLARK'
        and deptno = 10;
--*********************************************************************
-- SQL to create deterministic function
--
create or replace function make_upper (instring in varchar2)
   return varchar2 deterministic  -- must be deterministic function
is
begin
   return upper(instring);
end;
/
-- SQL to create index based upon use of user function
drop index manyemp_ename_ufidx;
create index manyemp_ename_ufidx 
   on many_emps(substr(make_upper(ename),1,10))
   tablespace adv_oraclass_idx_space;
analyze index manyemp_ename_ufidx
  compute statistics;
--*********************************************************************
-- SQL*Plus script to test user-defined function in index
--
--  The first test should use the available index
--  The second test should perform a tablespace scan
--
-- Turn on SQL*Plus tracing
  set autotrace on
-- Define SQL*Plus bind variable and load it
  variable empname varchar2(10)
  execute :empname := 'CLARK';
-- Alter tablespaces offline/online to flush cache
  alter tablespace adv_oraclass_space offline;
  alter tablespace adv_oraclass_idx_space offline;
  alter tablespace adv_oraclass_space online;
  alter tablespace adv_oraclass_idx_space online;
-- Alter session so that function-based index might be used
  alter session set query_rewrite_enabled = true;
-- Alter session so that user-function-based index might be used
  alter session set query_rewrite_integrity = trusted;
  select ename,job
     from many_emps
     where substr(make_upper(ename),1,10) = :empname
        and deptno = 10;
-- Alter tablespaces offline/online to flush cache
  alter tablespace adv_oraclass_space offline;
  alter tablespace adv_oraclass_idx_space offline;
  alter tablespace adv_oraclass_space online;
  alter tablespace adv_oraclass_idx_space online;
-- Alter session to disallow function-based index
  alter session set query_rewrite_enabled = false;
-- Alter session to disallow user-function-based index
  alter session set query_rewrite_integrity = enforced;
  select ename,job
     from many_emps
     where substr(make_upper(ename),1,10) = :empname
        and deptno = 10;