Mladen,
Bitmap indexes work perfectly fine with range predicates. Your example is
just a small table with high selectivity filter: it’s cheaper to full table
scan than use indexes to read a fifth of the table.
In our 26 Tb datawarehouse, we use a mix of partitioning (essentially list
partitioning on a column which is always used in an equality filter) and
bitmap indexes on columns that are frequently filtered on, these include
date columns which are often used as main driving filters in range based
predicates. The CBO opts for merging bitmap range scans and any other
bitmap scans it decides is worthwhile. We’ve not had any problems with this.
Thanks,
Andrew
On Sat, 26 Jan 2019 at 18:01, Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:
Hi Mark!
What good would bitmap indexes do for range predicates? Bitmap indexes can
only work with equality predicates. Look at an example:
SQL> create bitmap index i_job on emp(job);
Index created.
Elapsed: 00:00:00.092
QL> set autotrace on
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
SQL> select ename,job from emp where job between 'A%' and 'E%';
ENAME JOB
---------- ---------
SMITH CLERK
SCOTT ANALYST
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
6 rows selected.
Explain Plan
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value:
3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 98 | 4 (0)| 00:00:01
|
|* 1 | TABLE ACCESS FULL| EMP | 7 | 98 | 4 (0)| 00:00:01
|
--------------------------------------------------------------------------
Predicate Information (identified by operation
id):
---------------------------------------------------
1 - filter("JOB"<='E%' AND
"JOB">='A%')
Statistics
-----------------------------------------------------------
1 CPU used when call started
2 DB time
40 Requests to/from client
40 SQL*Net roundtrips to/from client
10 buffer is not pinned count
1207 bytes received via SQL*Net from client
79837 bytes sent via SQL*Net to client
6 calls to get snapshot scn: kcmgss
3 calls to kcmgcs
8192 cell physical IO interconnect bytes
1 cluster key scan block gets
1 cluster key scans
15 consistent gets
5 consistent gets examination
5 consistent gets examination (fastpath)
15 consistent gets from cache
10 consistent gets pin
9 consistent gets pin (fastpath)
1 enqueue releases
1 enqueue requests
5 execute count
16 file io wait time
1 free buffer requested
2 index fetch by key
1 index scans kdiixs1
122880 logical read bytes from cache
9 no work - consistent read gets
53 non-idle wait count
5 opened cursors cumulative
1 opened cursors current
1 parse count (hard)
4 parse count (total)
2 parse time elapsed
1 physical read IO requests
8192 physical read bytes
1 physical read total IO requests
8192 physical read total bytes
1 physical reads
1 physical reads cache
10 recursive calls
1 rows fetched via callback
1 session cursor cache hits
15 session logical reads
1 shared hash latch upgrades - no wait
2 sorts (memory)
1818 sorts (rows)
2 table fetch by rowid
6 table scan blocks gotten
28 table scan disk non-IMC rows gotten
28 table scan rows gotten
1 table scans (short tables)
41 user calls
Elapsed: 00:00:00.303
SQL>
As you can see, the bitmap index was not used for a range predicate. The
database version is 12.2.
Regards
On 1/25/19 11:59 AM, Mark J. Bobak wrote:
If the table really does have no DML (or extremely infrequent DML), then
I'd consider bitmap indexes, one per column eligible for predicates, and
let the optimizer do it's bitmap operations to come up with an optimal plan.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
