Oracle Scratchpad

December 7, 2010

Index Join – 3

Filed under: Execution plans,Index Joins,Indexing — Jonathan Lewis @ 6:01 pm BST Dec 7,2010

I’ve recently been writing about the index join mechanism and ways of emulating it. Those notes were originally inspired by an example of an index join that appeared on OTN a little while ago.

It was a plan that combined “bitmap/btree conversion” with the basic index join strategy so, with hindsight, it was an “obvious” and brilliant execution plan for a certain type of query. The query in the original posting was a simple select (with no predicates) against a huge table in a data warehouse – presumably extracting a small number of columns from a much wider row.

SELECT DISTINCT
	ECP_ITEM_MASTER_DIM.ORGANIZATION_ID,
	ECP_ITEM_MASTER_DIM.INV_MFG_PRODUCTION_LINE,
	ECP_ITEM_MASTER_DIM.INV_PRODUCT_FAMILY,
	ECP_ITEM_MASTER_DIM.INV_SEGMENT_3,
	ECP_ITEM_MASTER_DIM.INV_SEGMENT_4,
	ECP_ITEM_MASTER_DIM.INV_SEGMENT_5
FROM
	ecp.ECP_ITEM_MASTER_DIM
;

(I really hate reading SQL where the whole table name has been repeated as the alias all the way through the SQL – it makes the code so hard to read, especially when it’s all in upper case. It’s important to use aliases, of course, but 3 or 4 letters is a sensible length.)

Here’s the execution plan:


---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |  1799K|    42M|       | 51279   (1)| 00:10:16 |
|   1 |  HASH UNIQUE                       |                              |  1799K|    42M|   151M| 51279   (1)| 00:10:16 |
|   2 |   VIEW                             | index$_join$_001             |  1799K|    42M|       | 38227   (1)| 00:07:39 |
|*  3 |    HASH JOIN                       |                              |       |       |       |            |          |
|*  4 |     HASH JOIN                      |                              |       |       |       |            |          |
|*  5 |      HASH JOIN                     |                              |       |       |       |            |          |
|*  6 |       HASH JOIN                    |                              |       |       |       |            |          |
|*  7 |        HASH JOIN                   |                              |       |       |       |            |          |
|   8 |         BITMAP CONVERSION TO ROWIDS|                              |  1799K|    42M|       |   485   (1)| 00:00:06 |
|   9 |          BITMAP INDEX FULL SCAN    | ECP_ITEM_MASTER_DIM_IMPL_BMX |       |       |       |            |          |
|  10 |         BITMAP CONVERSION TO ROWIDS|                              |  1799K|    42M|       |   230   (0)| 00:00:03 |
|  11 |          BITMAP INDEX FULL SCAN    | ECP_ITEM_MASTER_DIM_IPF_BMX  |       |       |       |            |          |
|  12 |        BITMAP CONVERSION TO ROWIDS |                              |  1799K|    42M|       |   229   (0)| 00:00:03 |
|  13 |         BITMAP INDEX FULL SCAN     | ECP_ITEM_MASTER_DIM_IS3_BMX  |       |       |       |            |          |
|  14 |       BITMAP CONVERSION TO ROWIDS  |                              |  1799K|    42M|       |   228   (0)| 00:00:03 |
|  15 |        BITMAP INDEX FULL SCAN      | ECP_ITEM_MASTER_DIM_IS4_BMX  |       |       |       |            |          |
|  16 |      BITMAP CONVERSION TO ROWIDS   |                              |  1799K|    42M|       |   201   (0)| 00:00:03 |
|  17 |       BITMAP INDEX FULL SCAN       | ECP_ITEM_MASTER_DIM_IS5_BMX  |       |       |       |            |          |
|  18 |     BITMAP CONVERSION TO ROWIDS    |                              |  1799K|    42M|       |   207   (0)| 00:00:03 |
|  19 |      BITMAP INDEX FULL SCAN        | ECP_ITEM_MASTER_DIM_OI_BMX   |       |       |       |            |          |
---------------------------------------------------------------------------------------------------------------------------

Isn’t it brilliant! The optimizer has seen that all the required columns can be found in indexes (six of them) – but they happen to be bitmap indexes so the optimizer has done a “bitmap conversion to rowid” on all six indexes one after the other with five consecutive hash joins – carrying the column values with each conversion and hash join.

Unfortunately the owner of this plan wasn’t happy with the resulting plan because a full tablescan turned out to be faster – nevertheless, it’s a very clever concept as the size of the table was measured in Gigabytes while the indexes were only a few megabytes each, allowing for a significant saving in I/O time.

I was a little curious, though, about the final join strategy. It’s annoying that Oracle didn’t report any costs on the hash join lines themselves because that could be very revealing. It’s remarkable that the value in the Bytes column for the final view (which is six columns of data) is the same as the bytes column for each index conversion (and remember that the projection from each conversion is just one data column with an accompanying rowid) – there’s clearly something wrong with the arithmetic.

This may explain why the optimizer has decided to run the 6-way join using only two running hash joins (rather than first setting up five hash tables in memory than passing the last table through them). If you think about this, when Oracle gets to the last hash join (lines 3, 4 and 18) it has to build a hash table from the result of the previous four joins and (in this case) that’s going to need a similar amount of memory as five in-memory hash tables. With that thought in mind I was puzzled that Oracle hadn’t just built five in-memory hash tables then walked through each in turn with the sixth.

Still – it’s not my (or my client’s) problem; maybe one day I’ll need to look more closely at a similar case.

[Further reading on Index Joins]

8 Comments »

  1. How would the plan look if it did pass the last table through the five hashes? Do you have an example query that would do this?

    Comment by Scott — December 7, 2010 @ 10:40 pm BST Dec 7,2010 | Reply

    • Scott,

      I thought I’d be able to point a link to an article I had written that would be the perfect answer to your question – but I haven’t written on yet.

      Give me a couple of days.

      Comment by Jonathan Lewis — December 8, 2010 @ 6:51 am BST Dec 8,2010 | Reply

    • Scott, I think all what you need is to read previous article regarding INDEX JOINS and SWAP_JOIN_INPUTS hint. Let’s try it yourself, or wait for Jonathan’s “Index Join -4 ” :-)

      Comment by Pavol Babel — December 9, 2010 @ 12:28 am BST Dec 9,2010 | Reply

  2. I suppose one might try infering the costs of the various hash joins from the change in cost of the view on line 2 observed when selectively dropping columns out of the query.

    What do you think accounts for the variation in cost of the BITMAP CONVERSION TO ROWIDS? Would that be based on the number of distinct values for the column, or the size of the index?

    Comment by David Aldridge — December 8, 2010 @ 7:02 am BST Dec 8,2010 | Reply

    • David,

      I think you might get some ideas by dropping columns, and a few more by rearranging the order of the indexes (using the “pseudo” index join method from the previous article).

      The variation in conversion cost looks like an easier one to test – my first guess would be that it depends on the average column length of the column introduced, my second on the number of distinct keys. Both ideas would be quite easy to test in isolation.

      It’s always possible that the 10053 trace has a number of costs in it that don’t get into the plan, though (that’s been an irritant with bitmap indexes for years) – so that might be the first place to look.

      Comment by Jonathan Lewis — December 8, 2010 @ 7:46 am BST Dec 8,2010 | Reply

  3. Jonathan, does oracle consider swapping in this particular case? I hope I’ll try to find a while to check 10053.

    Comment by Pavol Babel — December 8, 2010 @ 10:20 pm BST Dec 8,2010 | Reply

    • Pavol,

      That’s a good question, and it would be easy to spend a few hours checking what the optimizer may have done – especially since I would have to design a data set to emulate the original query. I have a note to myself explaining the circumstances when a swap will be considered – but the plan doesn’t show the information I need to decide whether any swaps were considered for this join order.

      Comment by Jonathan Lewis — December 8, 2010 @ 11:17 pm BST Dec 8,2010 | Reply

      • So you’ve just saved my time :-) I intended to make my testcase to get this very interesting execution plan. But it would be obviously a waste of time, thank you for doing that.

        BTW I have spent quite a long time (but probably not so long as you) examining 10053 when bitmap indexes were in place and was irritated, too.

        Comment by Pavol Babel — December 9, 2010 @ 12:23 am BST Dec 9,2010 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,266 other followers