jump to navigation

Upcoming Index Seminar Dates (The Tourist) June 8, 2010

Posted by Richard Foote in OOW, Oracle Index Seminar.
add a comment

I’ve just finalised some European dates for my Oracle Index Internals and Best Practices Seminar for later in the year. Location and dates are as follows:

Turkey (Istanbul): 18-19 October 2010

Germany (Munich): 21-22 October 2010

Czech Republic (Prague) : 25-26 October 2010

As usual, always a good idea to book early to avoid disappointment. Last time I taught this in Germany, it was totally booked out.

I’ll also be attending and presenting at Oracle OpenWorld this year. I’ve been asked to present a session for the OpenWorld Oracle User Group Forum on the Sunday (19 September 2010) and with a bit of luck and a few more votes, perhaps the Indexing 11g R1 & R2 New Features presentation I submitted via the Oracle Mix Suggest-A-Session. I’ll also no doubt present something again during the Unconference as I have in past years.

So plenty of opportunity to catch up at some point during the year.

Oracle Mix – OOW and Oracle Develop Suggest-A-Session 2010 Now Open June 4, 2010

Posted by Richard Foote in OOW, Oracle Mix.
add a comment

Just a short note to let everyone know the Oracle Mix – OOW and Oracle Develop Suggest-A-Session facility is now open for the submission and voting of proposals to this year’s Oracle OpenWorld and Oracle Develop conferences.

This is a great opportunity to let the “public” not only submit proposals for presentations and panel sessions but also to vote for those sessions you would really like to see at these conferences. If you’ve got a presentation you think would be of interest to others, I would strongly recommend submitting a proposal and see how it goes. It’s a great opportunity to perhaps share your knowledge and experiences at these big Oracle events and to also have a say in what you would like to see presented.

I’ve submitted a session this year: A Detailed Analyses of Indexing New Features in Oracle 11g R1 and R2. If it’s something that might be of interest to you and you want to see it at Oracle OpenWorld, just click on the VOTE button :)

Bitmap Index Degradation Since 10g (Fix You) June 1, 2010

Posted by Richard Foote in Bitmap Indexes, Index Internals, Oracle Indexes.
6 comments

As discussed in my earlier post on Bitmap Index Degradation After DML Prior To 10g, Oracle wasn’t particularly efficient in the manner it maintained Bitmap Indexes after DML operations. During insert operations, if an existing Bitmap index entry didn’t cover the rowid range of a new row to be inserted, Oracle would create a new Bitmap index entry with a default range of 8 rowids and all the overheads this entails (and all the overheads this entails. As such, Bitmap indexes prior to 9i would often explode in size.

Thankfully since 10g, these issues have been largely addressed.

To illustrate, I’m going to run the exact same demo as I did in 9i, this time specifically on a 10.2.0.4 windows database, although you should get similar results in all versions of 10g/11g as well.

As before, I’m going to create the same table and populate it with the same data, creating a Bitmap index on the CODE column which has 1000 distinct values:

SQL> create table bowie as select mod(rownum,1000)+1 id, mod(rownum,1000)+1 code,'BOWIE' name from dual connect by level <= 1000000;
 
Table created.
 
SQL> create bitmap index bowie_code_i on bowie(code) compute statistics;
 
Index created.
 
SQL> select index_name, blevel, leaf_blocks, num_rows from dba_indexes where index_name='BOWIE_CODE_I';
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS
------------------------------ ---------- ----------- ----------
BOWIE_CODE_I                            1         500       1000

 

A freshly created Bitmap index has the same leaf blocks and index entries as in 9i.

Looking at a partial block dump of the first leaf block in the index:

row#0[5013] flag: ------, lock: 0, len=3019
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 42 1e 8c 00 e0
col 2; len 6; (6):  01 42 28 ad 01 7f
col 3; len 2998; (2998):
 03 c1 eb 01 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c1 eb 01 c4 bd 01 c2 eb 01
 c5 bd 01 c3 eb 01 c1 eb 01 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c1 eb 01 c4
 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb
 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c2 eb 01 c5 bd 01 c3 eb 01
 c7 bd 01 c5 eb 01 c3 eb 01 c6 bd 01 c4 eb 01 c7 bd 01 c5 eb 01 c3 eb 01 c6
...

row#1[1994] flag: ------, lock: 0, len=3019
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 42 1e 8a 00 00
col 2; len 6; (6):  01 42 28 ab 00 9f
col 3; len 2998; (2998):
 00 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c2 eb 01 c5 bd 01
 c3 eb 01 c6 bd 01 c4 eb 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c2
 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c3 eb 01 c6 bd 01 c4 eb 01 c7 bd
 01 c5 eb 01 c3 eb 01 c6 bd 01 c4 eb 01 c7 bd 01 c5 eb 01 c3 eb 01 c6 bd 01
 c4 eb 01 c0 be 01 c6 ea 01 c4 eb 01 c7 bd 01 c5 eb 01 c0 be 01 c6 ea 01 c4
...

 

We’ll look back and compare differences within this leaf block in a moment but for now just note there are 2 index entries within the leaf block, with 1 index entry for each distinct value of the CODE column (as with the 9i example).

OK, next we insert a new row and see what happens. Remember in 9i, Oracle created a new index entry as no existing bitmap index entries had a rowid range that span the rowid of this newly inserted row:

SQL> insert into bowie values (1, 1, 'ZIGGY');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index bowie_code_i compute statistics;
 
Index analyzed.
 
SQL> select index_name, blevel, leaf_blocks, num_rows from dba_indexes where index_name='BOWIE_CODE_I';
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS
------------------------------ ---------- ----------- ----------
BOWIE_CODE_I                            1         500       1000

OK, we notice a significant difference here from the 9i example. Although we’ve just inserted a new row, Oracle has not created a new Bitmap index entry (still 1000 rows in the index). Clearly, Oracle has managed to reuse the existing index entry for the newly inserted CODE value “1″, rather than add a new index entry.

A partial leaf block dump reveals what has happened:

row#0[1992] flag: ------, lock: 2, len=3021
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 42 1e 8c 00 e0
col 2; len 6; (6):  01 42 28 ae 00 07
col 3; len 3000; (3000):
 03 c1 eb 01 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c1 eb 01 c4 bd 01 c2 eb 01
 c5 bd 01 c3 eb 01 c1 eb 01 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c1 eb 01 c4
 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb
 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c2 eb 01 c5 bd 01 c3 eb 01
 c7 bd 01 c5 eb 01 c3 eb 01 c6 bd 01 c4 eb 01 c7 bd 01 c5 eb 01 c3 eb 01 c6
...

 

Rather than create a new index entry for the CODE value “1″, Oracle has made a couple of key changes to the existing index entry. Firstly, we notice it has changed the end rowid range (col 2) from a value of 01 42 28 ad 01 7f  to  01 42 28 ae 00 07 so that the rowid range now includes the rowid associated with the newly inserted row.

Additionally, it has modified the bitmap index string column (col 3) to incorporate the location of the newly inserted row within the increased rowid range. This has resulted in the bitmap string column increasing from 2998 to 3000 bytes, thus increasing the overall size of the index entry by the 2 additional bytes (3021 up from 3019).

So since 10g, Oracle is significantly more efficient and where possible will simply adjust the current rowid range of the Bitmap index entry and modify the bitmap string accordingly to accommodate a new row value (resulting in an overall increase of just 2 bytes overall in this example) rather than create a totally new index entry (which required an additional 21 bytes in the 9i example).

If we were to populate this Bitmap index from scratch as we did in the 9i example:

SQL> create table radiohead (id number, code number, name char(5));
 
Table created.
 
SQL> create bitmap index radiohead_code_i on radiohead(code);
 
Index created.
 
SQL> begin
  2  for i in 1..1000 loop
  3    for j in 1..1000 loop
  4     insert into radiohead values (j, j, 'BOWIE');
  5     commit;
  6    end loop;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL> analyze index radiohead_code_i compute statistics;
 
Index analyzed.
 
SQL> select index_name, blevel, leaf_blocks, num_rows from dba_indexes where index_name='RADIOHEAD_CODE_I';
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS
------------------------------ ---------- ----------- ----------
RADIOHEAD_CODE_I                        2         781       1000

  

We notice the index has not deteriorated anywhere near to the same degree as the 9i bitmap index example. Previously, the 9i bitmap index grew to a massive 5,347 leaf blocks but this 10g version has only become a moderate 781 leaf blocks, just 281 leaf blocks greater than a freshly rebuilt bitmap index. It has only grown by some 56%, (of which much has to do with the free space associated with subsequent index block splits), whereas the 9i version of the index grew by a massive 969%.

In summary, bitmap indexes in currently “supported” versions of Oracle are maintained in a much more efficient manner than they were previously, to the point where the need for frequently rebuilds has been much reduced, even in tables in which such indexes are not dropped during heavy loads.

That said, Bitmap indexes are still unsuitable in OLTP type environments (even in 11g)  due to the locking implications associated with them. Perhaps a discussion for another day.

Follow

Get every new post delivered to your Inbox.

Join 1,703 other followers