Partial indexes - example 1

test.sql

--drop table t2 purge;
create table t2 (
  pkey int not null,
  val  int,
  padding varchar2(100)
)
partition by range(pkey) (
  partition p1_on  values less than (2),
  partition p2_on  values less than (3),
  partition p3_off values less than (4) indexing off,
  partition p4_off values less than (5) indexing off,
  partition p5_on  values less than (6),
  partition p6_off values less than (7) indexing off
);
insert into t2
   with pkeys as (select level pkey from dual connect by level<=6)
       ,gen as (select level n from dual connect by level<=1000)
   select pkey,n,rpad('x',100,'x')
   from pkeys,gen;
create index ix_t2 on t2(pkey,val) local indexing partial;
select partition_name as pname,indexing 
from user_tab_partitions p
where table_name='T2';
alter session set MAX_DUMP_FILE_SIZE = unlimited;
alter session set events 'trace[SQL_Optimizer.*] disk=highest';
alter session set tracefile_identifier='partial_simple_1';
select count(*) from t2 where pkey in (1,2,6) and val=4;
alter session set tracefile_identifier='partial_simple_2';
select count(*) from t2 where pkey in (1,2) and val=4;
alter session set tracefile_identifier='partial_simple_3';
select count(*) from t2 where pkey in (1,2,3) and val=4;
alter session set tracefile_identifier='dummy';
alter session set events 'trace[SQL_Optimizer.*] off';