Friday, May 12, 2017

The many faces of DISTINCT in PostgreSQL

Here's a neat little post, called The many faces of DISTINCT in PostgreSQL. It goes over DISTINCT, DISTINCT ON and IS DISTINCT FROM. Good to understand this functionality if you're a Postgres user!

Saturday, April 08, 2017

New Features Coming in PostgreSQL 10

Robert Haas has posted an article New Features Coming in PostgreSQL 10.  Kind of sad that they had to rename the log dirs because they were treated as disposable by some ignorant DBAs, but hey it's good that they're in touch with their user base enough to know that it's been a problem! Overall it looks like several good steps forward, but little for me to get excited about. The improved partitioning may be nice.

Wednesday, March 29, 2017

MATCH_RECOGNIZE with Tom Kyte

Not using Oracle for work lately, I'm not up on all the latest features as much as I'd like to be...But one 12c feature I've been checking out lately is row pattern matching, aka MATCH_RECOGNIZE. Tom Kyte has a good demonstration on his column (from before his sad retirement)...

Monday, March 20, 2017

AskTOM

So now that Tom Kyte is retired, AskTOM stands for Ask The Oracle Masters. I was checking out some classic AskTom/AskTOM posts, and while some are of course informative, others are are just funny. Here's one that I find more humorous than I should maybe, I bring you...[drumroll]... "What If I forget the name of SavePoint?"

Monday, March 13, 2017

Oracle installation text rendering issue

Here's an issue I encountered when installing Oracle on OpenSUSE Linux. Just leaving this here as kind of a bookmark... (This is someone else's post and their environment is different from mine.) https://community.oracle.com/thread/3992606

Friday, March 03, 2017

Oracle 12cR2: Real-Time Materialized Views

Oracle 12cR2 is available for download - woohoo! One feature I'm excited about is the real-time materialized view, which allows a stale MV to be used by query rewrite. It sort of applies the changes from the underlying tables in real time. I was going to write up a demonstration of the feature, but a fellow named Uwe Hesse has already provided a very nice one on his blog. Check it out!

Thursday, November 10, 2016

MySQL - numeric types don't let you specify "length"

In MySQL I often see developers asking themselves, "Should I make this column an int(10) or int(11)? Should this bigint be bigint(15) or bigint(20)?" The answer is it probably doesn't matter! That number in parentheses does NOT indicate the maximum length of the number. For example, smallint(3) does not max out at 999, but rather it has a range of -32768 to -32767, just like any other smallint (which is 16 bits). The ONLY thing this parenthetical number has anything to do with is an esoteric, display-related feature called ZEROFILL.

ZEROFILL, if enabled, pads the number with zeroes for display purposes -- for example a int(10) with a value of 12345 will come out in your resultset as 0000012345. That's it. That's all it does. Read up more on it here: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html  
  

So, in my opinion you should NEVER even bother to define a column as "int(10)" or "bigint(20)" etc., etc., but rather just define an "int" or a "bigint". The only way to express a max length is in terms of which type to use in the first place: tinyint, smallint, int, bigint. (I think that's all of them - int types anyway. :)) So save yourself the clutter of useless additional information, and save yourself the waste of brainpower thinking about max lengths that aren't even real.