RE: Trying to understand the BITMAP AND operation a bit better
Date: Wed, 6 Jan 2016 21:47:33 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282BAB64_at_EXMBX01.thus.corp>
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
_at_jloracle
From: Charles Schultz [sacrophyte_at_gmail.com] 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_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> 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
_at_jloracle
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Charles Schultz [sacrophyte_at_gmail.com<mailto:sacrophyte_at_gmail.com>] 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_at_gmail.com<mailto:xt.and.r_at_gmail.com>> 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
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 06 2016 - 22:47:33 CET