Oracle Scratchpad

September 3, 2013

Bitmap / Btree

Filed under: 12c,Indexing,Oracle — Jonathan Lewis @ 7:28 am BST Sep 3,2013

Here’s a little note that came about after I tweeted an idle thought on Twitter yesterday

  • 12c allows you to have multiple indexes on the same columns on a table, although only one of them is allowed to be visible at any one time – you can do the same with any recent versions of Oracle “almost”, and without the invisibility requirements. (Thanks to Jason Bucata for suggesting the critical detail on this one.)
  • 12c allows you to have “partial” indexing on partitioned tables –  you can do the same with earlier versions of Oracle “almost” but only if the indexes are local indexes or globally partitioned.
  • 12c doesn’t officially allow you to create an index that is a bitmap in the past and a btree in the present (yet) – although you can almost do this in any recent versions of Oracle.


How to use (almost) the same column definition for two indexes on the same table:

create index t2_i1 on t2(n1);
create index t2_i2 on t2(n1, null);

The index t2_i2 will be reported as function-based, and will include a hidden column (check user_ind_expressions) for the null. Index entries for t2_i2 will be one byte longer that index entries for t2_i1 because it will have to use a byte to represent the null at the end – but apart from the effects of this change in size the optimizer seems happy to treat this index as if the null weren’t there. (Uniqueness doesn’t behave quite so nicely, though).

How to handle (some) partial indexing – but only for a limited subset:

create bitmap index t2_b1 on t2(n2) local;
alter index t2_b1 modify partition p_latest unusable;

Of course, you have to set parameter “skip_unusable_indexes” to true to make Oracle happy with the state of this index, but that’s been the default setting since 10g.

Now put the two methods together:

create index t1_i1 on t1(n1) local unusable;
create bitmap index t1_i1n on t1(n1, null) local unusable;

alter index t1_i1n rebuild partition p02500;

alter index t1_i1n rebuild partition p05000;
alter index t1_i1n rebuild partition p07500;

alter index t1_i1  rebuild partition p10000;

set autotrace traconly explain

select * from t1 where n1 = 10;

------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |    10 |   380 |     7 |       |       |
|   1 |  VIEW                                | VW_TE_2 |    11 |   726 |     7 |       |       |
|   2 |   UNION-ALL                          |         |       |       |       |       |       |
|   3 |    PARTITION RANGE ITERATOR          |         |     8 |   304 |     3 |     1 |     3 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T1      |     8 |   304 |     3 |     1 |     3 |
|   5 |      BITMAP CONVERSION TO ROWIDS     |         |       |       |       |       |       |
|*  6 |       BITMAP INDEX RANGE SCAN        | T1_I1N  |       |       |       |     1 |     3 |
|   7 |    PARTITION RANGE SINGLE            |         |     3 |   114 |     4 |     4 |     4 |
|   8 |     TABLE ACCESS BY LOCAL INDEX ROWID| T1      |     3 |   114 |     4 |     4 |     4 |
|*  9 |      INDEX RANGE SCAN                | T1_I1   |     3 |       |     1 |     4 |     4 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("N1"=10)
       filter("N1"=10)
   9 - access("N1"=10)

The resulting plan shows “Table Expansion” – a feature that became available in 11.2 (to be checked). We have effectively created an index that is a bitmap for history and a btree for recent data – a strategy I’ve often thought would be useful on a production system.

It’s only an idea, of course, and I haven’t tested it to destruction. On the downside it requires careful manual control to ensure that partitions that are supposed to be unusable don’t accidentally get rebuilt, and that partitions that are supposed to be unusable don’t accidentally become unusable. It also makes it harder to produce draft hints to make the SQL do what you want. Still, for some people it may be worth the effort and risk – and maybe a future release of Oracle will make it a legal mechanism.

Footnote:

Although table expansion only works in very recent versions of Oracle, you could create views on a partitioned table that splits the data into two parts so that the optimizer could optimize different partitions differently. Here’s an example run on Oracle 9.2.0.8


create or replace view v_history as select * from t1 where id < 7501;
create or replace view v_recent as select * from t1 where id >= 7501;
create or replace view v1 as select * from v_history union all select * from v_recent;

explain plan for select * from v1 where id = 10;
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------------------------------------
| Id  | Operation                            |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |    11 |   726 |    10  (20)|       |       |
|   1 |  VIEW                                | V1          |    11 |   726 |            |       |       |
|   2 |   UNION-ALL                          |             |       |       |            |       |       |
|   3 |    PARTITION RANGE ITERATOR          |             |       |       |            |     1 |     3 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T1          |     8 |   296 |     7  (15)|     1 |     3 |
|   5 |      BITMAP CONVERSION TO ROWIDS     |             |       |       |            |       |       |
|*  6 |       BITMAP INDEX RANGE SCAN        | T1_I1N      |       |       |            |     1 |     3 |
|*  7 |    TABLE ACCESS BY LOCAL INDEX ROWID | T1          |     3 |   111 |     3  (34)|     4 |     4 |
|*  8 |     INDEX RANGE SCAN                 | T1_I1       |     3 |       |     2  (50)|     4 |     4 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T1"."ID"<7501)
   6 - access("T1"."N1"=10)
       filter("T1"."N1"=10)
   7 - filter("T1"."ID">=7501)
   8 - access("T1"."N1"=10)

The two-table union all at the end is an approach I’ve used a couple of times in the past for special handling of partitioned data. It was a little fragile, and not left to end-user ad-hoc queries. Be very cautious with these techniques.

6 Comments »

  1. After tweeting you back, I later realized that table expansion (I didn’t know it had a name… learned something today) was a recent feature, so you could have the disjoint indexing but you couldn’t really take advantage of it very well! So I’m really interested in this UNION ALL trick… quite clever, I must say (even if potentially fussy). I’m starting to think of where I might be able to use it in my work.

    Comment by Jason Bucata — September 3, 2013 @ 5:00 pm BST Sep 3,2013 | Reply

    • Table expansion also comes with its own pair of hints: EXPAND_TABLE, NO_EXPAND_TABLE.

      The UNION ALL is fussy, and doesn’t always do what you want – but it’s a lot cheaper than the partitioning option, and works no Standard Edition. Sometimes we have to expend human effort to get the effects we want.

      Comment by Jonathan Lewis — September 3, 2013 @ 7:34 pm BST Sep 3,2013 | Reply

  2. A few months ago I did the (…, null) trick for creating a bitmap index on a column already indexed by a btree index … well, OK, I actually did a (…, 1) trick, but I guess that does not make much difference for a bitmap index. (I haven’t tested the actual difference, though.)

    The tricky part, though, was then forcing the optimizer (11.2.0.3 RAC) to use the (…, 1) bitmap index along with several other (non-function-based) bitmap indexes in a complex (and dynamically generated) query condition with several AND-ed predicates, some of which were OR-ed predicates of another AND-ed predicates. (All atomic predicates were “=”.) The CBO refused to use the FBIs along with the non-FBIs, used only the non-FBIs whenever the condition implied use of both index types. I got it ultimately working with a huge and ugly index_combine() hint with all the table’s index names in it, I am still very ashamed for that kind of query “tuning”, though.

    What I am implying: The (…,null) trick is really nice, but it might need a further investigation as for how the CBO behaves with more complex query conditions which should involve these bitmap FBIs along with “classic” bitmap indexes.

    Comment by Peter Hraško — September 27, 2013 @ 10:19 am BST Sep 27,2013 | Reply

    • Peter,

      Thanks for the follow-up. I’ve run a couple of little tests based on you description.

      This looks like a defect in the optimizer (even in 12c). With a btree index on (colX) and a bitmap index(colX, null) a 10053 trace file showed that the bitmap index was not even considered in the bitmap plans, while the btree index was used in a btree/bitmap conversion even though it was significantly more expensive to use than the extended bitmap index.

      I found that I only had to list the extended bitmap indexes (rather than all of them) in the index_combine() hint to make sure that they were included in the plan – but, as you say, that’s not a good way of tuning a query.

      In passing, (,null) increases the index entry by one byte (,0) by two bytes and (,1) by three bytes. Given that the bitmap content can be about 3KB in a single entry the impact on size and cost is generally likely to be small – but I would expect some people will see it occasionally.

      Despite being only slightly larger the extended index (colX,null) has a larger cost associated with it than the pure (colX) bitmap index, presumably because it involves a bitmap range scan and bitmap merge. This may mean that plans which would use the single column bitmap index will not use the extended one.

      Comment by Jonathan Lewis — September 27, 2013 @ 5:13 pm BST Sep 27,2013 | Reply

      • I wonder if it changes anything to go with my original suggestion to use a dummy column, something that’s always null or some constant. (Much more likely to happen in packaged or other inherited apps, so maybe not always an option.) Does it recognize from the column stats that there’s only the one value (or lack thereof, if we’re being pedantic about the definition of null) and cost it any better?

        What about if you make the Btree be the one with the extra column?

        And, relatedly, what if you do a Btree of (null,colX)? Will a skip scan come into play and does that change the cost any?

        Comment by Jason Bucata — September 27, 2013 @ 5:41 pm BST Sep 27,2013 | Reply

        • Jason,
          The initial problem doesn’t seem to be one of costing – the optimizer simply doesn’t consider the option (unless hinted).

          Similarly (colX, null_column) is ignored unless hinted; and (null, colX) and (null_column, colX) can’t even be hinted – it looks like a skip scan and index_combine() don’t work together.

          I don’t have time to do an exhaustive review of all the possible variations – but my guess would be that there are more likely to be limitations when you try to fake subvert bitmap indexes, and b-tree indexes may allow for a greater variety of options.

          Comment by Jonathan Lewis — September 27, 2013 @ 6:25 pm BST Sep 27,2013


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,086 other followers