Plan stability in oracle 11g

I noticed this post in Blogging about oracle, which sounds to me a little bit like a good opportunity for achieving and maintaining plan stability. One more thing to try out.

Installing Oracle 11g Database

Oracle 11g has been out for a couple of months now and I found me some time to play around a bit.

One thing that immediately sprang out to me is the size of the download, 1,7GB zip file. That’s something different from the promise Oracle made when introducing 10g where you only needed one CD and 15 minutes to install an oracle database. That was key-note stuff, back then. Now not so important anymore, apparently.

I do not have that much hardware available to play around with so it had to be a virtual machine. I had created a CentOS 4.4 VM some months back, so that seemed a logical choice. Of course it is wise to upgrade the installed packages when you have not been using a CentOS recently. So Up2date was the first to start in the VM. I had no trouble in starting it, just that it took quite some time to finish downloading and installing all packages. Another evening of not doing very much, just a lot of waiting.

The time I had to wait however was eventually well spent because it gave others the time to finish the same action and blog about it. All I next had to do is follow in the footsteps of Howard to prepare linux and install oracle. Of course the installation failed when a starter database was to be created because there was not enough disk space in the VM. I am not a unix or linux administrator so that was another evening puzzling and googling about. But I succeeded eventually.

The next install (after removing the original one the’hard’ way) went smoothly, so now I have a virtual machine with a DWH11 and a REPO11 Oracele 11g database. Next up; installing Warehouse builder environment in the Kadenza style.

(tip of the veil; it is installed, but the description will have to wait till after preparing and savouring dinner and watching a ’supersunday’ of Dutch soccer (AZ-Ajax and PSV-Feijenoord, no 1-4 currently))

Photograph dates

Today I was trying to get some photographs in the right chronological order. I use a tool, Exif Pilot, that can change the dates that are incorporated in most jpeg’s created with a digital camera. And another one, Flash renamer, that can rename the jpeg files based on the dates contained in the file. The last tool is not so necessary any more as a lot of file managers (both windows and linux world) can detect these dates as well. But when you rename a file and let it start with a date and time, a simple ‘dir’ or ‘ls’ lists the files in the ‘correct’ order.

There is one problem with all this. That is when you, or the person operating the digital camera, forget to set the right date and time in the camere when taking the photographs. Hence Exif pilot to change the dates in the jpeg file.

There is however a quirk in this tool as you can see in the date dialog. When you have multiple files that have an incorrect date but are in the right chronological order, you can add or substract a number of days, hours, minutes and/or seconds. All you then have to do is figure out what the interval is between the date and time the camera has been set to and the actual date the photograph was taken. You probably can find out what the actual date has been. But then calculating the interval! That means finding a calendar and counting the days. Especially fun when the date the camera has been set to is several years back (typically when resetting the camera or batteries have run out).

Now it is by no means easy in SQL, but after several times of leaving through a calender I decided to spend a bit of time to calculate the difference between two dates in days, hours, minutes and seconds. Mainly to use it again after the next holiday or festivity when I have been taking photographs. So finally some Oracle:

SQL> ed
Wrote file afiedt.buf

1 select numtodsinterval(
2 to_date(‘02-jul-2007 14:00:00′, ‘dd-mon-yyyy hh24:mi:ss’) -
3 to_date(‘08-jan-2004 01:41:00′, ‘dd-mon-yyyy hh24:mi:ss’)
4 , ‘day’)
5* from dual
SQL> /

NUMTODSINTERVAL(TO_DATE(‘02-JUL-200714:00:00′,’DD-MON-YYYYHH24:MI:SS’)-TO_D
—————————————————————————
+000001271 12:18:59.999999999

SQL>

Bitmap index size, or, Size does matter.

For the past couple of years I have been working with the notion that Oracle’s bitmap indexes are a cool feature. Perfect for an index on low cardinality columns. And even the definition of ‘low’ in this regard has been stretched a lot. We have a datawarehouse where we create an index on every column of the dimension tables and the index is created as a bitmap index when the cardinality is below 50.000. Queries that are restricted on these columns fly.

The biggest downside to using bitmap indexes is (or so I believed) that they tend to grow enormously in size when DML is applied to the table that is underlying the bitmap index. Resulting in frequently rebuilding bitmap indexes to reclaim space (and thus performance by reducing the required I/O) or not using bitmap indexes at all.

It appears I have been sleeping or did not read the ‘What’s New’ guides thoroughly enough, because in the What’s new guide for 10gR1 this is mentioned:

Enhanced Bitmap Index Performance and Space Management

Bitmap indexes now perform better and are less likely to be fragmented when subjected to large volumes of single-row data manipulation language (DML) operations.

Let’s get dirty and do some querying. Here’s what happens in 9.2.0.7 (script shamelessly copied from Jonathan Lewis’s blog):
SQL> select * from v$version where banner like 'Oracle%';BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit ProductionSQL>
SQL>
SQL> create table t1 as
2 select
3 rownum id,
4 mod(rownum,2) bit_col
5 from
6 all_objects -- {or your favourite row source}
7 where
8 rownum <= 10000
9 ;Table created. SQL>
SQL> create unique index t1_pk on t1(id); Index created. SQL> create bitmap index t1_bi on t1(bit_col);

Index created.

SQL>
SQL> — gather statistics on the table
SQL>
SQL> validate index t1_bi;

Index analyzed.

SQL>
SQL> select
2 name, height, lf_blks, lf_rows, btree_space, used_space
3 from
4 index_stats
5 ;

NAME HEIGHT LF_BLKS LF_ROWS BTREE_SPACE USED_SPACE
—————————— ———- ———- ———- ———– ———-
T1_BI 1 1 2 16188 2891

SQL>
SQL> begin — update 250 rows
2 for i in reverse 9500..10000 loop
3 update t1
4 set bit_col = 0
5 where
6 id = i
7 and bit_col = 1;
8 end loop;
9 commit;
10 end;
11 /

PL/SQL procedure successfully completed.

SQL>
SQL> validate index t1_bi;

Index analyzed.

SQL>
SQL> select
2 name, height, lf_blks, lf_rows, btree_space, used_space
3 from
4 index_stats
5 ;

NAME HEIGHT LF_BLKS LF_ROWS BTREE_SPACE USED_SPACE
—————————— ———- ———- ———- ———– ———-
T1_BI 3 82 496 1505836 810761

SQL>
SQL> drop table t1;

Table dropped.

SQL>
SQL>

So what I see here is that the bitmap index has grown from 1 8K block to 0,77M, just by updating 250 rows of a 10.000 row table. Imagine what that will do to your bitmap index on your multi-million row DWH table.
Now, I only have an 10gR2 database available and nothing is mentioned in that documentation regarding this subject, but let’s see what’s happening in 10gR2 (assuming the same will be true for 10gR1):

SQL> select * from v$version where banner like 'Oracle%';BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64biSQL>
SQL>
SQL> create table t1 as
2 select
3 rownum id,
4 mod(rownum,2) bit_col
5 from
6 all_objects -- {or your favourite row source}
7 where
8 rownum <= 10000
9 ;Table created.SQL>
SQL> create unique index t1_pk on t1(id);

Index created.

SQL> create bitmap index t1_bi on t1(bit_col);

Index created.

SQL>
SQL> — gather statistics on the table
SQL>
SQL> validate index t1_bi;

Index analyzed.

SQL>
SQL> select
2 name, height, lf_blks, lf_rows, btree_space, used_space
3 from
4 index_stats
5 ;

NAME HEIGHT LF_BLKS LF_ROWS BTREE_SPACE USED_SPACE
—————————— ———- ———- ———- ———– ———-
T1_BI 1 1 2 7996 2916

SQL>
SQL> begin — update 250 rows
2 for i in reverse 9500..10000 loop
3 update t1
4 set bit_col = 0
5 where
6 id = i
7 and bit_col = 1;
8 end loop;
9 commit;
10 end;
11 /

PL/SQL procedure successfully completed.

SQL>
SQL> validate index t1_bi;

Index analyzed.

SQL>
SQL> select
2 name, height, lf_blks, lf_rows, btree_space, used_space
3 from
4 index_stats
5 ;

NAME HEIGHT LF_BLKS LF_ROWS BTREE_SPACE USED_SPACE
—————————— ———- ———- ———- ———– ———-
T1_BI 1 1 2 7996 2853

SQL>
SQL> drop table t1;

Table dropped.

SQL>
SQL>

The index has not grown at all!

Now don’t go throwing away all your regular indexes and replace them all by bitmap indexes. There still is another downside to updating bitmap indexes, as Jonathan Lewis mentions in his post. They used to create a lot of redo in 9i and they still do use a lot of redo in 10g (Not to self; create script to demonstrate this). But there are certainly more cases where a bitmap index can save your day, or the performance of your datawarehouse.

And, as always, check if the results for this kind of use is the same in your own environment, or in IM speak; YMMV.

Another ‘End to the database as we all know it’?

I’m very interested to see wether this is just another storm in a glass of water or really the start of the downfall of the RDBMS. Michael Stonebraker, the author of the article, has been known to put his views down in strong words. I think you can say he knows about databases really first hand. Is he also a database guru that can play a role in the next generation of databases?

To be continued……