Oracle Scratchpad

February 4, 2014

Quiz

Filed under: Indexing,Oracle — Jonathan Lewis @ 1:33 am BST Feb 4,2014

To create an index on a table (with no existing indexes) Oracle has to start by doing a tablescan.

What’s the difference between the tablescan it uses for a B-tree index and the tablescan it uses for a bitmap index ? Why ?

Update:

I was going to give a hint that if you answered the “why” first that might lead you to the right idea and a test for the “what”, but we already have an answer, with a sample of proof.

9 Comments »

  1. This might be fun to play with on the plane to RMOUG tomorrow.

    Comment by jkstill — February 4, 2014 @ 1:47 am BST Feb 4,2014 | Reply

  2. Curiosity got the best of me, so I spent a few minutes looking at this.
    So far the only difference I have seen in the tablescan is an extra physical read for BITMAP indexes.
    Several tests show that this is a consistent behavior, provided the buffer cache is flushed before building the index.
    If the cache is not flushed, my tests show the same number of physical reads for each.
    I am sure that is a clue here, but so far it is not leading me in the right direction.

    BTREE: p19559
    STAT #140682378639320 id=3 cnt=10000 pid=2 pos=1 obj=101189 op='TABLE ACCESS FULL IDXTEST (cr=20 pr=17 pw=0 time=3592 us cost=7 size=22308 card=1716)'
    
    BITMAP: p19514
    STAT #140045337741280 id=5 cnt=10000 pid=4 pos=1 obj=101189 op='TABLE ACCESS FULL IDXTEST (cr=20 pr=18 pw=0 time=1743 us cost=7 size=22308 card=1716)'
    

    This difference seems vaguely familiar, though after a few moments pondering I can’t recall just what it is.

    Oddly enough, an strace shows fewer preads on BITMAP(5) than for BTREE(7) create.
    That is something to ponder later, been a long day already.

    Perhaps someone else will see what I am missing here.

    SQL used for testing:
    (previous index dropped and buffer cache flushed for each index build)

    create table idxtest 
    as
    select level id, mod(level,100) value
    from dual
    connect by level <= 10000;
    
    alter session set tracefile_identifier=BTREE;
    alter session set events '10046 trace name context forever, level 12';
    create index idxtest_btree on idxtest(value);
    
    alter session set tracefile_identifier=BITMAP;
    alter session set events '10046 trace name context forever, level 12';
    create bitmap index idxtest_bitmap on idxtest(value);
    

    Comment by jkstill — February 4, 2014 @ 3:42 am BST Feb 4,2014 | Reply

    • It seems you are right. For BITMAP all reads are ordered.

      [oracle udump]$ cat orcl_ora_12696_BITMAP.trc|grep 'db file scattered read'
      .............
      WAIT #4: nam='db file scattered read' ela= 126 file#=28 block#=2530 blocks=7 obj#=4586980 tim=1358899201783663
      WAIT #4: nam='db file scattered read' ela= 151 file#=28 block#=2537 blocks=8 obj#=4586980 tim=1358899201784033
      WAIT #4: nam='db file scattered read' ela= 134 file#=28 block#=2546 blocks=7 obj#=4586980 tim=1358899201784410
      WAIT #4: nam='db file scattered read' ela= 128 file#=28 block#=2553 blocks=8 obj#=4586980 tim=1358899201784753
      WAIT #4: nam='db file scattered read' ela= 117 file#=29 block#=2058 blocks=7 obj#=4586980 tim=1358899201785116
      WAIT #4: nam='db file scattered read' ela= 153 file#=29 block#=2065 blocks=8 obj#=4586980 tim=1358899201785488
      WAIT #4: nam='db file scattered read' ela= 281 file#=29 block#=2187 blocks=16 obj#=4586980 tim=1358899201786053
      WAIT #4: nam='db file scattered read' ela= 279 file#=29 block#=2203 blocks=16 obj#=4586980 tim=1358899201786753
      .......
      [oracle udump]$ cat orcl_ora_12696_BTREE.trc|grep 'db file scattered read'
      ................
      WAIT #1: nam='db file scattered read' ela= 8820 file#=28 block#=2553 blocks=8 obj#=4586980 tim=1358899201248566
      WAIT #1: nam='db file scattered read' ela= 5722 file#=29 block#=2058 blocks=7 obj#=4586980 tim=1358899201255324
      WAIT #1: nam='db file scattered read' ela= 761 file#=29 block#=2065 blocks=8 obj#=4586980 tim=1358899201256430
      WAIT #1: nam='db file scattered read' ela= 14253 file#=28 block#=2187 blocks=16 obj#=4586980 tim=1358899201271340
      WAIT #1: nam='db file scattered read' ela= 430 file#=28 block#=2203 blocks=16 obj#=4586980 tim=1358899201272604
      WAIT #1: nam='db file scattered read' ela= 167 file#=28 block#=2219 blocks=6 obj#=4586980 tim=1358899201273190
      WAIT #1: nam='db file scattered read' ela= 446 file#=29 block#=2187 blocks=16 obj#=4586980 tim=1358899201274291
      ...........
      

      Comment by Andrey — February 4, 2014 @ 11:40 am BST Feb 4,2014 | Reply

  3. I guess tablescan for b-tree index scans extends in dba_extents-internal order (order by extent_id?).
    Tablescan for bitmap index might benefit from pre-sorting extents by (file, block). Extents do not overlap, thus just comparing of first block is enough.
    Ordered-by-extents scan returns ordered by rowids results, providing a way to build bitmaps with no additional sorting.
    I see no profit for b-tree of this ordering, thus it can just skip sorting extents to improve for-b-tree-scan times.

    Comment by Vladimir Sitnikov — February 4, 2014 @ 6:14 am BST Feb 4,2014 | Reply

    • Vladimir,

      Correct – when you trace the tablescan (after constructing a suitable table) you can see that the bitmap creation scans the extents in order of file_id and block_id number while the btree creation does it in extent_id order.

      Comment by Jonathan Lewis — February 4, 2014 @ 11:42 am BST Feb 4,2014 | Reply

    • Ah, there’s the bit I missed: the ordered reads. Thanks!

      Comment by jkstill — February 4, 2014 @ 3:25 pm BST Feb 4,2014 | Reply

  4. just guessing: maybe for the bitmap index the Hakan factor has to be (re)identified in the context of the full scan?

    Comment by Martin Preiss — February 4, 2014 @ 9:04 am BST Feb 4,2014 | Reply

  5. just guessing also : may be it is because bitmap index includes null values whereas b-tree doesn’t and hence the full table scan needs to exclude null values in the later case

    Comment by Mohamed — February 4, 2014 @ 12:29 pm BST Feb 4,2014 | 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,089 other followers