Major

Today I did something Donald Duck would do. A couple years ago I built a house. Here are two doors to leave the house, one in front and also a back door. There are wooden stairs in the front door and a wooden terrace outside the back door. I just oiled the front stairs and just after that continued oiling at the back terrace. Now I am stuck inside for a while. It is time to open a beer and think of something else done today. Karhu has some virtual fishing to do.

Julian Dontcheff mentioned a while ago about 11.2.0.2 that it should actually be called 11R3. I was reading my oracle support. The issue in my spontaneous online demonstration in What is bugging me presentation is noticed. My demonstration was about "No results with function based indexes and OR expansion". Now there is a document in MOS "Things to Consider Before Upgrade to 11.2.0.2 Database Performance [ID 1320966.1]" updated 7.6.2011. On off patch is recommended as Oracle does not want to interfere optimizer with CPU or PSU patching. The fifth number in the version numbering. So are those four letters actually something that should be considered major version. Well 11.2.0.2 has so many other changes than the optimizer ones that it could be a 11R3. Just see the list of new features in 11.2.0.2 list in the documentation. OPTIMIZER_FEATURES_ENABLED It has already been from version 10.1.0.3 and 9.2.0.8 that the fourth number have had a meaning. Actually 11.2.0.2 is missing there. Yet another place to submit a user comment about the documentation.

While writing this post it seems like the 1320966.1 document is vanishing or is it just something about the flashy interface... The patch recommended to install was 9776940. Contact support before installing... Another thing mentioned there was the 11.2.0.3 patchset due out later this year.

Yet another thing. During last week there has been a slight peak in the visitor count of my blog. After Jonathan Lewis has made an Argh! issue about merge ignores check constraint and someone linking that post to his pages. Yes it actually is an Argh issue.

Actually yet another Argh! issue might be visualized as a snip

A week on the other side (part3 DB2)

Missing Oracle function based indexes. Got involved in a project having a DB2 database environment. There is a need for case insensitive predicate in a where clause. In Oracle it is possible to create function based index:

create table t(n number(8) primary key, last_name varchar(100));

insert into t (n,last_name)

select rownum, object_name

from all_objects;

select * from t where upper(last_name) = :ln;

Performs a full table scan to table t.

create index t_last_name_upper on t(upper(last_name));

select * from t where upper(last_name) = :ln;

And the index is used if the table is big enough.

In DB2 there is no such thing as a function based index. But in a article on IBM site there is described alternatives to do this. Generated columns and index extensions. Generated columns seems like a valid approach. Even though data is duplicated on a row. No need for triggers seems like a good thing and the original query do not need any modifications.

This was about the first time that I am using db2cc Control Center to do things in DB2 environment. Execution plans were found easily from Access Plan page. At least with a small table implementation and tests went ok. Allthough adding a column in a table was not so straight forward because the column was a GENERATED ALLWAYS AS column.

SET INTEGRITY FOR t OFF;

alter table t
add column last_name_u varchar(100)
GENERATED ALWAYS AS ( UPPER(last_name));

SET INTEGRITY FOR t IMMEDIATE CHECKED FORCE GENERATED;

create index t_lastname_upper_idx on t(last_name_u);


How much easier would that have been in Oracle 11g. Just add a virtual column...

alter table t add last_name_u as (upper(last_name));

create index t_last_name_upper on t(last_name_u);

select * from t where upper(last_name) = :ln;


Similarily no need to change the query. Index is used. And the column is virtual, no need for storage. The index extensions approach might be the way to avoid unneeded storing of the data in DB2.

Needed also reduce a size of column and got surpriced how often REORG TABLE is needed in DB2. Or did I miss something in the documentation.