Blog

General

Please visit our QueryAdvisor Blog at BlogSpot.com for adding comments!

Oracle SQL - NULL Values and Zero Strings

Published Sunday, September 29, 2013 at 10:37

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!

categorizedInCategory: General

Oracle Wait Event Forcasting now implemented!

Published Saturday, April 7, 2012 at 19:57

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!

categorizedInCategory: Technology

QueryAdvisor now for Mac OSX available too!

Published Saturday, February 4, 2012 at 16:47

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.

categorizedInCategory: General

Support QueryAdvisor on Google PlusOne!

Published Sunday, July 3, 2011 at 10:48

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!

categorizedInCategory: General

Newsletter 1/2011 now available!

Published Sunday, June 12, 2011 at 17:54

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!

categorizedInCategory: History

Registration for QueryAdvisor Betatest started!

Published Saturday, May 14, 2011 at 11:35

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!

categorizedInCategory: General

New QueryAdvisor V2.90 Release End of April!

Published Sunday, April 17, 2011 at 9:18

The new QueryAdvisor V2.90 release will be online end of April. Right now there are the final tests on bulk processing of Oracle trace files runnning.

This release should be the foundation of the overdue betatest. The new 360° views across all processed Oracle event 10046 trcfiles are available there for the first time. I think that crossreferencing all tracefiles and open cursors is a great feature and it was worth waiting!

Before I forget to mention: All licensekeys remain valid! An upgrade to QueryAdvisor V2.90 will therefor be straight forward:
1) Download the new QueryAdvisor release.
2) Deinstall the old installed software. Modified/New data is left untouched!
3) Install the current release of QueryAdvisor. Copy your own data to the same direcory.
4) During the start the used parameterfile will be updated on the fly but only stored after confirmation.

And Voilà... Upgrade completed!

There will be a Tweet with a download link provided when our tests had been completed!

categorizedInCategory: General

SQL*Net and Transparent Network Substrate

Published Wednesday, March 9, 2011 at 20:03

SQL*Net is the software provided by Oracle to establish connections between local Oracle database clients and the related Oracle database instance or between two different Oracle database instances via database link. It is based on the Transparent Network Substrate (TNS) protocol.

TNS is a generic logical protocol which could be executed on top of all common physical network protocols such as TCP/IP. It is based on individual logic packets which are mapped transparently to physical packets.

TNS must be able to handle the different Oracle database server connectivity scenarios:
- shared server database instance
- dedicated server database instance
- dedicated server bequeath connection
  (database client and database instance must be on the same hardware)

Visit the QueryAdvisor Knowledge Center to read more about the Transparent Network Substrate.

categorizedInCategory: Technology

YouTube Channel now Online!

Published Sunday, January 16, 2011 at 5:44

We have loaded new videos to our QueryAdvisor Channel at YouTube. It would be great if you have the time to browse through. Probably there is room for improvement...

categorizedInCategory: History

QueryAdvisor now on Blogspot.com!

Published Saturday, January 15, 2011 at 19:02

We have now our own blog at QueryAdvisor.blogspot.com. We will inform you there about new features and ideas. Your comments are highly welcomed! Come & See...

categorizedInCategory: General


KnowledgeBanner
QueryAdvisor trial.
QueryAdvisor in 30 Seconds

QueryAdvisor quote.
QueryAdvisor support forum.
QueryAdvisor Twitter channel.
social icons Facebook Twitter Blogspot RSS