Re: optimizing SQL's w/ multiple (and variable) range based predicates against very wide table
Date: Sat, 26 Jan 2019 12:59:51 -0500
Message-ID: <a4a8e32b-e85e-38e6-00d5-705d7b1ba809_at_gmail.com>
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
ENAME JOB
Explain Plan
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
1 - filter("JOB"<='E%' AND "JOB">='A%')
Statistics
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%';
---------- ---------
SMITH CLERK
SCOTT ANALYST
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
6 rows selected.
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 |
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-lReceived on Sat Jan 26 2019 - 18:59:51 CET