Oracle Sunrise

Looking at the Oracle headquarter during sunrise from my hotel room was one of the highlights last week in California. Just want to share it with you.

Sunrise over Oracle HQ

Oracle SQL - NULL Values and Zero Strings

Following ANSI SQL 1992 standard empty or zero lenght strings and NULL are different. So I was a little bit surprised after executing the following queries on an Oracle 11R2 database last days:

a) As a Result:
select length(null) from dual; -- NULL as expected
select length('') from dual;     -- NULL too, what was a bit unexpected
select length(' ') from dual;    -- 1 as expected

 b) In a Comparison:
select dummy from dual where '' = '';         -- Instead of 'X' no result, comparison failed
select dummy from dual where '' = NULL;  -- Same as above, not a real surprise
select dummy from dual where '' IS NULL; -- The expected result 'X'


Searching at "Ask Tom" I found the following statement:

and we said...
A zero length varchar is treated as NULL.
'' is not treated as NULL.
'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).
 '' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no long '')


In the Oracle SQL Language Reference I found in addition the following comment regarding the difference of VARCHAR and VARCHAR2:

VARCHAR Datatype
Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.


So what does that all mean from a practical point of view:

1. Oracle does not handle null values for VARCHAR ANSI SQL 92 conform as the empty string is converted to a null value.
2. Always use VARCHAR2 out of compatibility reasons as for VARCHAR Oracle has reserved the right to change the comparison semantics at a later stage to become ANSI SQL 92 compatible.
3. For all CHAR types due to padding for '' everything is padded with spaces.
4. The behaviour of empty or zerlo length Strings described is the same in all Oracle releases.

This behaviour could become important when we think about query design for Database Optimization and Tuning. Enjoy & experiment on your own!

Oracle Wait Event Forcasting now implemented!

The forcast functionality has been released for QueryAdvisor. It is now possible to collect and analyze Oracle Wait Events on Oracle instance and session level over long periods of time.

The results of the already existing funtionality for advanced Oracle instance and session analysis are now collected and automatical sorted by database instance in the background. This offers the possiblity to dedect changes in the type, amount and execution-time of Oracle Wait Events on instance and session level.

As possible restarts of the database instance are automatical dedected, the quality of the calculation is exceptional as ambiguous data could be filtered out upfront. The results are graphical presented and could be exported in addition.

A trial copy of our release of QueryAdvisor is waiting for You!

QueryAdvisor now for Mac OSX available too!

QueryAdvisor is now available as a real 64Bit app on Mac OSX. The final changes has been made and everything has been bundled and packed ready for rollout!

The most time consuming process had been the changes to get the real Mac feeling. Accelerators had been added. All Popups are working. The Dock and the Menu Bar behave as it should be for real Mac software.

If you own an Intel based Mac with Snow Leopard or Lion installed, why not be part of the test team and work with QueryAdvisor for free? Just let me know. If you know a DBA with an Intel based Mac - just let him know.

A free copy of our new Mac OSX release QueryAdvisor is waiting for You! Just drop me a line and I will send you a link for downloading.

Support QueryAdvisor on Google PlusOne!

The new Google PlusOne button is now available on all content related pages of our QueryAdvisor site. The button is placed on most pages in the right column below the twitter and the other social bookmarking icons.

If you like what we are doing, why not supporting us with a CLICK on one of the Google PlusOne buttons on our QueryAdvisor site?

This will help us to detect the areas you are interested in and - yes - will help us in promoting our site a bit.

Thanks in advance thinking about supporting us!

Newsletter 1/2011 now available!

Our newsletter 1/2011 is now published in a new fresh design. You will find there information about the exciting new enhancements available in QueryAdvisor V2.90.

The most important new feature - the 360° Tracefile View is described in more detail with a detailed screenshot. There is nothing similar available for Oracle Tracefile Analysis so far!

The newsletter 1/2011 is available here for reading now!

Registration for QueryAdvisor Betatest started!

We are starting our official public betatest of QueryAdvisor now. Our target audience are Oracle database administrators independent from the releases they have worked with and the level of individual experience.

If you like to apply for becoming an official betatester with all benefits involved you will find additional information here.

The betatest will start with our new QueryAdvisor V2.90 release offering amazing new functionality in dealing with hugh amounts of Oracle event 10046 tracefiles.

Don't miss this opportunity and learn more here now!