Partitioned table / local index
create table tq84_tab_part (
id number(5),
val number(5,1),
txt varchar2(10),
grp char(1) not null check (grp in ('A', 'B', 'C')),
--
constraint tq84_tab_part_pk primary key (id)
)
partition by list (grp) (
partition tq84_list_partition_A values ('A'),
partition tq84_list_partition_B values ('B'),
partition tq84_list_partition_C values ('C')
);
begin
dbms_random.seed(42);
end;
/
insert into tq84_tab_part
select
level,
round(dbms_random.value(0, 1000), 1),
case round(dbms_random.value, 4)
when 0.0111 then 'x'
when 0.0222 then 'y'
when 0.0333 then 'z'
else chr(ascii('a') + dbms_random.value(0, 23))
end,
chr(ascii('A') + dbms_random.value(0, 3))
from
dual connect by level <= 30000;
create bitmap index tq84_tab_part_ix_bmp on tq84_tab_part(txt) local;
begin
dbms_stats.gather_table_stats(
user,
'tq84_tab_part',
method_opt => 'for columns txt size 26' -- size 26: Make sure histograms are created
);
end;
/
explain plan for
select
val,
txt,
grp
from
tq84_tab_part
where
grp in ('A', 'B') and
txt in ('x', 'y', 'z')
;
select * from table(dbms_xplan.display(format=>'typical -bytes -cost'));
--
-- ---------------------------------------------------------------------------------------------------------------
-- | Id | Operation | Name | Rows | Time | Pstart| Pstop |
-- ---------------------------------------------------------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 1 | 00:00:01 | | |
-- | 1 | PARTITION LIST INLIST | | 1 | 00:00:01 |KEY(I) |KEY(I) |
-- | 2 | INLIST ITERATOR | | | | | |
-- | 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TQ84_TAB_PART | 1 | 00:00:01 |KEY(I) |KEY(I) |
-- | 4 | BITMAP CONVERSION TO ROWIDS | | | | | |
-- |* 5 | BITMAP INDEX SINGLE VALUE | TQ84_TAB_PART_IX_BMP | | |KEY(I) |KEY(I) |
-- ---------------------------------------------------------------------------------------------------------------
--
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--
-- 5 - access("TXT"='x' OR "TXT"='y' OR "TXT"='z')
drop table tq84_tab_part;
Combine BITMAP INDEX SINGLE VALUE with BITMAP OR
BITMAP INDEX SINGLE VALUE can perfectly be combined with
BITMAP OR:
create table tq84_bitmap_or (
id integer,
val number(7,2),
flg_1 varchar2(10),
flg_2 varchar2(10),
payload varchar2(2222)
);
create bitmap index tq84_bitmap_or_ix_1 on tq84_bitmap_or(flg_1);
create bitmap index tq84_bitmap_or_ix_2 on tq84_bitmap_or(flg_2);
begin
insert into tq84_bitmap_or
select
level,
dbms_random.value(10000, 99999),
case mod(level, 1000)
when 123 then 'foo'
when 456 then 'bar'
when 789 then 'baz' end,
case mod(level, 1234)
when 222 then 'x'
when 444 then 'y' end,
lpad('x', 2222, 'x')
from
dual connect by level <= 10000;
commit;
dbms_stats.gather_table_stats(user, 'tq84_bitmap_or');
end;
/
explain plan for
select
id,
val
from
tq84_bitmap_or
where
flg_1 in ('foo', 'bar', 'baz') or
flg_2 in ('x', 'y');
select * from dbms_xplan.display(format => 'basic');
--
-- -------------------------------------------------------------------
-- | Id | Operation | Name |
-- -------------------------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TQ84_BITMAP_OR |
-- | 2 | BITMAP CONVERSION TO ROWIDS | |
-- | 3 | BITMAP OR | |
-- | 4 | BITMAP INDEX SINGLE VALUE | TQ84_BITMAP_OR_IX_1 |
-- | 5 | BITMAP INDEX SINGLE VALUE | TQ84_BITMAP_OR_IX_1 |
-- | 6 | BITMAP INDEX SINGLE VALUE | TQ84_BITMAP_OR_IX_1 |
-- | 7 | BITMAP INDEX SINGLE VALUE | TQ84_BITMAP_OR_IX_2 |
-- | 8 | BITMAP INDEX SINGLE VALUE | TQ84_BITMAP_OR_IX_2 |
-- -------------------------------------------------------------------
drop table tq84_bitmap_or;