Oracle Scratchpad

May 8, 2009

IOUG Day 4

Filed under: CBO,Execution plans,Tuning — Jonathan Lewis @ 5:18 pm BST May 8,2009

Not so much a little gem today as a little surprise and a few consequential thoughts. In a presentation on optimising star transformations the presenter pointed out that bitmap indexes are only available in Oracle Enterprise Edition.

Bitmap indexes were originally tied to the parallel query option (PQO), but I was so sure that the two features had been decoupled that I asked the presenter if he was sure that you still needed Enterprise Edition for bitmap indexes – and he was sure, even in 10g.

So this is where my thoughts went: if you can’t use bitmap indexes in Standard Edition, does the optimiser refuse to do btree/bitmap conversions (i.e. something like the following) in Standard Edition:

--------------------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |       |     1 |   424 |     3 |
|*  1 |  TABLE ACCESS BY INDEX ROWID     | T1    |     1 |   424 |     3 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |       |       |       |       |
|   3 |    BITMAP AND                    |       |       |       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |       |
|*  5 |      INDEX RANGE SCAN            | T1_N1 |       |       |     1 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |       |
|*  7 |      INDEX RANGE SCAN            | T1_N2 |       |       |     1 |
|   8 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |       |
|*  9 |      INDEX RANGE SCAN            | T1_N3 |       |       |     1 |
--------------------------------------------------------------------------

 

If Standard Edition does allow the conversion that would be a bit odd, given the licensing requirements for using bitmaps. On the other hand, if Standard Edition doesn’t allow for btree/bitmap conversion, then how can Oracle deprecate the and-equal access method, viz. something like:

-----------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |         |       |       |     6 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | T1      |     1 |   424 |     6 |           |
| 2   |   AND-EQUAL                  |         |       |       |       |           |
| 3   |    INDEX RANGE SCAN          | T1_N1   |   196 |       |     1 |           |
| 4   |    INDEX RANGE SCAN          | T1_N2   |   200 |       |     1 |           |
| 5   |    INDEX RANGE SCAN          | T1_N3   |   200 |       |     1 |           |
| 6   |    INDEX RANGE SCAN          | T1_N4   |   200 |       |     1 |           |
| 7   |    INDEX RANGE SCAN          | T1_N5   |   200 |       |     1 |           |
-----------------------------------------------+-----------------------------------+

 
The only good argument for deprecating this path is that the btree/bitmap conversion is more powerful and flexible, and probably more efficient, making this path redundant and a waste of programmer effort to maintain.

Whichever way you look at it, making bitmaps an Enterprise feature seems to lead to a possible inconsistency.

Trouble is – I don’t want to have to install a copy of Standard Edition just to answer the question.

10 Comments »

  1. I happened to have 10.2.0.3 standard edition laying around.

    So I ran the example from: http://jonathanlewis.wordpress.com/2007/02/08/index-combine/

    And got the following plan:

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1755794669
    --------------------------------------------------------------------------------
    | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |       |     5 |   125 |    40   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                |       |     5 |   125 |    40   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     3 |    24 |     4   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | T1_N1 |     3 |       |     1   (0)| 00:00:01 |
    |*  4 |   TABLE ACCESS FULL          | T2    |     2 |    34 |    12   (0)| 00:00:01 |
    --------------------------------------------------------------------------------

    No “BITMAP CONVERSION TO ROWIDS” in sight :)

    Same test on Enterprise Edition gave the expected plan you posted in the “Index Combine” blog post.

    Comment by prodlife — May 8, 2009 @ 6:45 pm BST May 8,2009 | Reply

  2. Chen,

    That’s interesting; thanks for running the test. It really does suggest that the whole bitmap code path might be blocked in Standard Edition.

    If you have a few minutes could you try a simpler query just to see if that works, e.g: select * from t2 where b1 = 20 and b2 = 20, with the same index_combine() hint.

    Comment by Jonathan Lewis — May 8, 2009 @ 8:55 pm BST May 8,2009 | Reply

  3. Still no bitmap conversion:

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1513984157

    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |   117 |    13   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T2   |     1 |   117 |    13   (0)| 00:00:01 |
    --------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter("B1"=20 AND "B2"=20)

    Comment by Chen Shapira — May 8, 2009 @ 9:07 pm BST May 8,2009 | Reply

  4. Chen,

    Fantastic, I think that’s beginning to look fairly conclusive – and I think it highlights an interesting case.

    I think earlier versions of Oracle would probably do the AND-EQUAL at that point, but 10.2 won’t because it’s deprecated, but SE can’t do the expected bitmap conversion to compensate.

    Comment by Jonathan Lewis — May 8, 2009 @ 9:24 pm BST May 8,2009 | Reply

  5. I couldn’t resist and tried it on the 9.2.0.7 that was on the “must upgrade this quarter” plan for the last 2 years:

    Execution Plan
    ----------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=117)
      1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=4 Card=1 Bytes=117)
      2    1     AND-EQUAL
      3    2       INDEX (RANGE SCAN) OF 'T2_B1' (NON-UNIQUE)
      4    2       INDEX (RANGE SCAN) OF 'T2_B2' (NON-UNIQUE) (Cost=1 Card=60)

    You got it :)
    AND-EQUAL is used, not full-scan.

    Quiet amazing, really.

    Comment by Chen Shapira — May 8, 2009 @ 10:40 pm BST May 8,2009 | Reply

  6. Chen,

    Thanks for that – I think you’ve covered all the bases.

    Tentative conclusion: if you’re using 9i Standard Edition, then some of your queries may stop using and_equal() execution plans when you upgrade to 10g Standard Edition and find some other less efficient plan.

    Anyone in this position might want to run a few scans of v$sql_plan before upgrading checking for “operation = ‘AND-EQUAL'” to find the SQL at risk.

    “I couldn’t resist …”I shall, however, resist mentioning a few of the other little things I’ve been waiting to test against SE – otherwise you might end up spend all week-end at your computer running my experiments ;)

    Comment by Jonathan Lewis — May 9, 2009 @ 10:17 am BST May 9,2009 | Reply

  7. I checked Chen’s results on an 11.1.0.7 Standard Edition and came out with the same plans. So nothing has changed there. None of the bitmap system parameters (public or hidden) are set to false, so it isn’t simply down to different defaults.

    Comment by Gary — May 10, 2009 @ 11:00 pm BST May 10,2009 | Reply

  8. [...] start with Jonathan Lewis’s report from IOUG Day 4: ” Not so much a little gem today as a little surprise and a few consequential thoughts. In a [...]

    Pingback by Log Buffer #146: a Carnival of the Vanities for DBAs | Pythian Group Blog — May 15, 2009 @ 5:00 pm BST May 15,2009 | Reply

  9. [...] pretty smart guy when it comes to Oracle knowledge) posted about some things he discovered for the first time.  I strongly encourage everyone to attend at least one event if possible every year – to [...]

    Pingback by Collaborate09 thoughts… | Oracle — August 11, 2009 @ 9:49 am BST Aug 11,2009 | Reply

  10. [...] important case where this is always true – when you’re running Standard Edition, which doesn’t support bitmap indexes. So if you’re running SE and want to do star transformations – you now know [...]

    Pingback by Star Transformation – 2 « Oracle Scratchpad — August 26, 2011 @ 6:02 am BST Aug 26,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers