Re: optimizing SQL's w/ multiple (and variable) range based predicates against very wide table

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_at_gmail.com> 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
>
>

--
http://www.freelists.org/webpage/oracle-l