Your description is exactly what I've been thinking - but I couldn't make the
numbers fit that picture.
I've just realised, though that the "rows" for the bitmap operators are
actually "bitmap chunks", not table rows - and that makes all the difference.
Thought experiment
Create a table with 20M rows.
Arrange for rows 10,500,001 to 10,500,010 to have a specific value in column 1
Arrange for rows 10,000,001 to 10,999,999 to have a specific value in column 2
THe ideal is to have one very small bitmap chunk for column 1, and several
chunks for column 2.
Create the indexes with pctfree 95 (or more) to make the column 2 bitmap chunks
cover several leaf blocks
Select where column1 = 'val1' and column2 = 'val2'.
Flush the buffer cache, enable extended trace
Force the index use to be col1 AND col2
Repeat the test forcing the index use to be col2 AND col1
I think you'd find that if the more precise index is accessed first then only
relevant entries / leaf blocks from the second index would be accessed.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Charles Schultz [sacrophyte@xxxxxxxxx]
Sent: 06 January 2016 21:40
To: Jonathan Lewis
Cc: ORACLE-L
Subject: Re: Trying to understand the BITMAP AND operation a bit better
Yeah, I was thinking about that as well. My hypothesis is that different index
blocks are being assessed depending on which bitmap is used first. For example,
in the first case with three bitmap operations, if I force the order of
operations to be different, I get different numbers of rows and different
blocks (rows goes down, blocks goes up):
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
|* 9 | BITMAP INDEX SINGLE VALUE | FACT_DIM2 | 5 | |
140 |00:00:00.05 | 88 |
|* 10 | BITMAP INDEX SINGLE VALUE | FACT_DIM1 | 5 | |
20 |00:00:00.01 | 28 |
|* 11 | BITMAP INDEX SINGLE VALUE | FACT_0 | 5 | |
5 |00:00:00.01 | 13 |
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
|* 9 | BITMAP INDEX SINGLE VALUE | FACT_0 | 5 | |
5 |00:00:00.01 | 13 |
|* 10 | BITMAP INDEX SINGLE VALUE | FACT_DIM1 | 5 | |
20 |00:00:00.01 | 18 |
|* 11 | BITMAP INDEX SINGLE VALUE | FACT_DIM2 | 5 | |
80 |00:00:00.01 | 113 |
So it seemes like it depends on which blocks the rows reside in (quite possible
that two or more "successful" rows might reside in one block), and further it
seems that if I get all the rows from one bitmap, they are used to dynamically
filter the rows in the next bitmap operation, and then that cumulative result
is used to filter the next operation. If anyone knows differently, please let
me know. Like I said, I am somewhat new to bitmap operations and star queries
in general.
On Wed, Jan 6, 2016 at 3:18 PM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> wrote:
I've been staring at it for the last few minutes trying to work out why the
larger set of figures isn't exactly 5 times the smaller set of figures.
One is too large, the other too small
fact_0: 3 -> 13 (short by 2)
fact_dim_1: 4 -> 28 (long by 8)
Some effects of delayed block cleanout / consistent read possibly - how many
times did you run the test, does the order of the tests affect the result.
Some effects of buffer pinning for the synchronised walks of three indexes
maybe - but that would surely be highly coincidental in a small data set.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Charles Schultz [sacrophyte@xxxxxxxxx<mailto:sacrophyte@xxxxxxxxx>]
Sent: 06 January 2016 21:14
To: Sayan Malakshinov
Cc: ORACLE-L
Subject: Re: Trying to understand the BITMAP AND operation a bit better
Sayan,
That is an excellent observation - I knew I had been staring at this for way
too long! :) And now I feel kinda stupid.... oh well. :)
Thanks much.
On Wed, Jan 6, 2016 at 3:08 PM, Sayan Malakshinov
<xt.and.r@xxxxxxxxx<mailto:xt.and.r@xxxxxxxxx>> wrote:
Hi Charles,
Note that 7-11 lines have 5 "Starts" in the first plan, because previous
steps(NL to FTS of DIMENSION2) returned 5 rows, but there is only 1 "Start" in
second plan for these operations.
So each of BITMAP INDEX SINGLE VALUE in first plan executed 5 times instead of
1 in the second plan.
--
Best regards,
Sayan Malakshinov
http://orasql.org
--
Charles Schultz
--
Charles Schultz
