Ticket #11017: index_testing.sql

drop table testdata;

create table testdata (
  id number primary key,
  streetname varchar2(100),
  streetno varchar2(10)  
);

-- Create testdata
declare
  type name_va is varray(5) of VARCHAR2(100);
  v_street1 name_va;
  v_street2 name_va;
  id number;
begin
  id := 1;
  v_street1 := name_va('Fancy', 'Django', 'Pink Pony');
  v_street2 := name_va('Street', 'Way', 'Alley');
  for a in v_street1.first..v_street1.last
  loop
    for b in v_street2.first..v_street2.last
    loop
      for c in 1..100
      loop
        insert into testdata(id, streetname, streetno) values (id, v_street1(a) || ' ' || v_street2(b), c);
        id := id + 1;
      end loop;
      commit;
    end loop;
  end loop;
end;
/

commit;

create index ix_streetname on testdata(streetname);
create index ix_streetno on testdata(streetno);

-- Make sure that statistics are computed.
analyze table testdata compute statistics;

-- Clean plan table
delete from plan_table;

-- Explain what happens
explain plan for select * from testdata where streetname like 'Fancy%';

-- Show results
select 
  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", 
  object_name                                                              "Object"
from 
  plan_table 
start with id = 0 
connect by prior id=parent_id;

-- Should return:
--SELECT STATEMENT ()   
-- TABLE ACCESS (BY INDEX ROWID)        TESTDATA
--  INDEX (RANGE SCAN)  IX_STREETNAME

-- Clean plan table
delete from plan_table;

-- Explain what happens
explain plan for select * from testdata where streetname likec 'Fancy%';

-- Show results
select 
  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", 
  object_name                                                              "Object"
from 
  plan_table 
start with id = 0 
connect by prior id=parent_id;

-- Should return:
-- SELECT STATEMENT ()  
--  TABLE ACCESS (FULL) TESTDATA