--

-- Setup script for demo of adaptive cursor sharing with SPM
-- Author: Allison Lee

connect hr/hr

drop table employees_acs;
create table employees_acs as select * from employees;
insert into employees_acs 
select * from employees_acs where job_id not in ('AD_VP','AD_PRES');
insert into employees_acs 
select * from employees_acs where job_id not in ('AD_VP','AD_PRES');
insert into employees_acs
select * from employees_acs where job_id not in ('AD_VP','AD_PRES');
insert into employees_acs
select * from employees_acs where job_id not in ('AD_VP','AD_PRES');
insert into employees_acs
select * from employees_acs where job_id not in ('AD_VP','AD_PRES');
insert into employees_acs
select * from employees_acs where job_id not in ('AD_VP','AD_PRES');
insert into employees_acs
select * from employees_acs where job_id not in ('AD_PRES');
insert into employees_acs
select * from employees_acs where job_id not in ('AD_PRES');

create index EMP_DEPARTMENT_ACS_IX on employees_acs (department_id);

Rem induce histogram creation on job_id
explain plan for select * from employees_acs where job_id = :b;

begin
 dbms_stats.gather_table_stats(null, 'employees_acs');
end;
/

Rem display histograms
select column_name, num_buckets 
 from user_tab_columns 
 where table_name='EMPLOYEES_ACS'
   and histogram != 'NONE';

--