Link: http://jonathanlewis.wordpress.com/2009/12/30/simple-scripts/

Most recent addition (30th Dec 2009): Segment Scans.
Since many of my customers are fairly hot on security I’m often banned from plugging USB drives into local PCs, or getting my laptop onto the local network, or downloading from the Internet, so I’ve often had to type up ad-hoc queries whenever I’ve had a thought about [...]

Link: http://blog.tanelpoder.com/2009/12/30/null-is-not-zero/

Some time ago I wrote a post about how COUNT(*) and COUNT(column) are semantically different things (link). Such queries may return different results if the column counted has NULLs in it. And the difference comes from that NULL is not a value, it’s rather a state which says “value unknown” or “no value entered”.

So, you better understand how NULLs interact with your SQL constructs if you call yourself a DBA or a database developer ;-)

Here’s another example about how misunderstanding NULLs may cause your application to return different results than what was intended.

I will create a little table with TWO rows in it:

SQL> create table t(a int);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> insert into t values(null);

1 row created.

SQL> select avg(a) from t;

AVG(A)
----------
1

When I take an average of the 2 values in these rows I get average of 1.

Now lets update the NULL (no value) to 0 (an actual value of zero).

SQL> update t set a=0 where a is null;

1 row updated.

SQL> select avg(a) from t;

AVG(A)
----------
.5

As you see, as we now have an actual value in the other row (as opposed to “no value&#8221, the AVG function takes that zero into account.

Hopefully this illustrates once more that NULL does not mean zero or any other value, it means NO value. If you do aggregation functions (count,avg) over NULLs then you must understand that Oracle treats NULLs as no value and doesn’t account these “no values”, thus your queries may behave differently than what your intuition might say (and yes its always good to read documentation about what exactly a given SQL construction/function does in the given database engine instead of relying on “common sense&#8221.

Link: http://kevinclosson.wordpress.com/2009/12/30/hot-oracle-related-search-engine-terms-for-2009-carry-on-wayward-googler/

2009 is drawing to a close so I just took a gander at the year-end search term statistics for my blog. It’s interesting to see the search terms that send readers to this blog. Some are surprising, others not so surprising. So, for trivial pursuit sake, the following list shows the top 50 search terms [...]