12c in-memory column store vs. bitmap index: bitmap wins!

By: Brian Fitzgerald

This article compares response time for Oracle Database 12.1.0.2 in memory database compares with response time for a bitmap index.

The configuration for this instance is:


*.audit_file_dest='/u01/app/oracle/admin/gp12102/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.control_files='+DATARDBMS10/GP12102/CONTROLFILE/current.300.860076553','+FRARDBMS10/GP12102/CONTROLFILE/current.272.860076555'
*.db_block_size=8192
*.db_create_file_dest='+DATARDBMS10'
*.db_domain=''
*.db_name='gp12102'
*.db_recovery_file_dest='+FRARDBMS10'
*.db_recovery_file_dest_size=4560M
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=gp12102XDB)'
*.inmemory_size=9961472000
*.local_listener='LISTENER_GP12102'
*.memory_max_target=0
*.memory_target=0
*.open_cursors=300
*.pga_aggregate_limit=8589934592
*.pga_aggregate_target=4294967296
*.processes=300
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=11744051200
*.sga_target=11744051200
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='ONLY'

First, let’s create a table with a billion rows

create table tmod (
 n number,
 m2 number,
 m3 number,
 m5 number,
 m7 number,
 m11 number,
 m13 number,
 m17 number,
 m19 number,
 m23 number,
 m29 number
)
compress
inmemory;

declare
 maxnumber number := 1000000000;
 offset number;
 mult number := 100000;
begin
 for i in 0..maxnumber/mult-1
 loop
 offset := i * mult;
 insert /*+ append */
 into tmod (
 n,
 m2,
 m3,
 m5,
 m7,
 m11,
 m13,
 m17,
 m19,
 m23,
 m29
 )
 select
 level + offset n,
 mod( level + offset, 2 ) n2,
 mod( level + offset, 3 ) n3,
 mod( level + offset, 5 ) n5,
 mod( level + offset, 7 ) n7,
 mod( level + offset, 11 ) n11,
 mod( level + offset, 13 ) n13,
 mod( level + offset, 17 ) n17,
 mod( level + offset, 19 ) n19,
 mod( level + offset, 23 ) n23,
 mod( level + offset, 29 ) n29
 from dual
 connect by level <= mult
 ;

 commit;
 end loop;
end;
/

create bitmap index tmod_m2_idx on tmod( m2 );
create bitmap index tmod_m3_idx on tmod( m3 );
create bitmap index tmod_m5_idx on tmod( m5 );
create bitmap index tmod_m7_idx on tmod( m7 );
create bitmap index tmod_m11_idx on tmod( m11 );
create bitmap index tmod_m13_idx on tmod( m13 );
create bitmap index tmod_m17_idx on tmod( m17 );
create bitmap index tmod_m19_idx on tmod( m19 );
create bitmap index tmod_m23_idx on tmod( m23 );
create bitmap index tmod_m29_idx on tmod( m29 );

begin
dbms_stats.gather_table_stats
(
 ownname => null,
tabname => 'TMOD'
);
end;
/
alter table tmod inmemory priority critical;
alter table tmod inmemory memcompress for capacity high;
Table dropped.

Table created.

PL/SQL procedure successfully completed.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

PL/SQL procedure successfully completed.

Table altered.

Table altered.

Check that the in-memory column store is populated.

set linesize 200
set trimspool on
column owner format a10
column segment_name format a30

select owner, segment_name,
bytes / 1024 / 1024 mb,
round ( ( bytes - bytes_not_populated ) / 1024 / 1024 ) mbpop,
round ( ( bytes - bytes_not_populated ) * 100 / bytes ) pctpop,
round ( ( inmemory_size ) / 1024 / 1024 ) mbinmem,
inmemory_priority pri,
inmemory_compression,
populate_status
from
gv$im_segments
;
OWNER SEGMENT_NAME MB MBPOP PCTPOP MBINMEM PRI INMEMORY_COMPRESS POPULATE_
---------- ------------------------------ ---------- ---------- ---------- ---------- -------- ----------------- ---------
BRIAN TMOD 20608 20608 100 2731 NONE FOR CAPACITY HIGH COMPLETED

100% populated!

Let’s try it!

select
min(n) + 1 euclid
from tmod
where m2 = 0
and m3 = 0
and m5 = 0
and m7 = 0
and m11 = 0
and m13 = 0
and m17 = 0
and m19 = 0
and m23 = 0
and m29 != 0
 EUCLID
----------
 223092871

1 row selected.

Elapsed: 00:00:04.25

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gbbjq4yhfhf2j, child number 0
-------------------------------------
select min(n) + 1 euclid from tmod where m2 = 0 and m3 = 0 and m5 = 0
and m7 = 0 and m11 = 0 and m13 = 0 and m17 = 0 and m19 = 0 and m23 = 0
and m29 != 0

Plan hash value: 1674668081

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35849 (100)| |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TMOD | 4 | 148 | 35849 (1)| 00:00:02 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 4 | BITMAP AND | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | TMOD_M23_IDX | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | TMOD_M19_IDX | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE | TMOD_M17_IDX | | | | |
|* 8 | BITMAP INDEX SINGLE VALUE | TMOD_M11_IDX | | | | |
-----------------------------------------------------------------------------------------------------

Outline Data
-------------

 /*+
 BEGIN_OUTLINE_DATA
 IGNORE_OPTIM_EMBEDDED_HINTS
 OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
 DB_VERSION('12.1.0.2')
 ALL_ROWS
 OUTLINE_LEAF(@"SEL$1")
 BITMAP_TREE(@"SEL$1" "TMOD"@"SEL$1" AND(("TMOD"."M23") ("TMOD"."M19") ("TMOD"."M17")
 ("TMOD"."M11")))
 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TMOD"@"SEL$1")
 END_OUTLINE_DATA
 */

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - filter(("M13"=0 AND "M7"=0 AND "M5"=0 AND "M3"=0 AND "M2"=0 AND "M29"<>0))
5 - access("M23"=0)
6 - access("M19"=0)
7 - access("M17"=0)
8 - access("M11"=0)

47 rows selected.

The optimizer chose a bitmap index access path. There were 24 such test runs using bitmap indexes. Average 4.21s

Now let’s try the in-memory column store.

alter index tmod_m2_idx invisible;
alter index tmod_m3_idx invisible;
alter index tmod_m5_idx invisible;
alter index tmod_m7_idx invisible;
alter index tmod_m11_idx invisible;
alter index tmod_m13_idx invisible;
alter index tmod_m17_idx invisible;
alter index tmod_m19_idx invisible;
alter index tmod_m23_idx invisible;
alter index tmod_m29_idx invisible;
Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

Index altered.

EUCLID
----------
 223092871

1 row selected.

Elapsed: 00:00:04.71

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gbbjq4yhfhf2j, child number 0
-------------------------------------
select min(n) + 1 euclid from tmod where m2 = 0 and m3 = 0 and m5 = 0
and m7 = 0 and m11 = 0 and m13 = 0 and m17 = 0 and m19 = 0 and m23 = 0
and m29 != 0

Plan hash value: 17535997

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 68899 (100)| |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS INMEMORY FULL| TMOD | 4 | 148 | 68899 (59)| 00:00:03 |
------------------------------------------------------------------------------------

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TMOD"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - inmemory(("M23"=0 AND "M19"=0 AND "M17"=0 AND "M13"=0 AND "M11"=0
AND "M7"=0 AND "M5"=0 AND "M3"=0 AND "M2"=0 AND "M29"<>0))
filter(("M23"=0 AND "M19"=0 AND "M17"=0 AND "M13"=0 AND "M11"=0 AND
"M7"=0 AND "M5"=0 AND "M3"=0 AND "M2"=0 AND "M29"<>0))
38 rows selected.

 altered.

There were 9 such in-memory tests. 4.68s average.

Bitmap index was 9% faster!

Bitmap wins!

Notes:

1. In-memory population took 2 hours! For faster repopulation, try other memcompress options or no memcompress.

2. Repopulation happens after instance restart or alter table, so plan carefully! For higher availability, try RAC!

3. The first bitmap index test run after instance restart read the index from disk, took much longer, and was not considered in the bitmap average.

4. In-memory test runs during the repopulation runs ran much longer and were not considered in the in-memory average.

5. Note that the optimizer did not choose to use indexes tmod_m2_idx, tmod_m3_idx, tmod_m5_idx, and tmod_m7_idx. The indexes are not selective enough!


TABLE_NAME COLUMN_NAME NUM_NULLS NUM_DISTINCT
------------------------------ ------------------------------ ---------- ------------
TMOD N 0 1000000000
TMOD M2 0 2
TMOD M3 0 3
TMOD M5 0 5
TMOD M7 0 7
TMOD M11 0 11
TMOD M13 0 13
TMOD M17 0 17
TMOD M19 0 19
TMOD M23 0 23
TMOD M29 0 29

6. The purpose of this blog posting was to compare the in-memory column store to bitmap indexes, not to calculate Euclid numbers. There are simpler ways to calculate Euclid numbers. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s