20 December 2006

Pan's Labyrinth

This movie looks fantastic, in the true sense of the word.

19 December 2006

Warren Buffett is my Lord and Savior

One of my favorite blogs, Get Rich Slowly, has compiled YouTube footage of a CNBC interview with investor Warren Buffett.

I'd encourage everyone to add GRS to your daily reading if you haven't already. And stop using the credit cards, kids.

Bind Variables: Not just for SELECTs!

Jonathan Lewis has a great piece on the importance of using bind variables in INSERT statements as well, avoiding expensive check constraint revalidation.

18 December 2006

NEVER

There is never, NEVER, N E V E R a reason to store passwords as clear text in a database. No matter how convenient it is for your own personal preference.

Any aspiring application developers reading this: NEVER. Salt-n-hash is such an easy thing to do in most any language now, and you won't be publicly ridiculed like spez is now.

12 December 2006

Java5 Formatting Goodness

Back when I was researching how to format strings in java, I was dismayed that there wasn't built-in sprintf() functionality. There was a non-standard class written by some folks at Sun called PrintfFormat. Still, not the same.

Yesterday I was reminded that such a thing was supposed to have been implemented in Java5, and indeed it was. The Formatter class provides just what I want, and it is all made even easier by just calling the static String.format() method. Oh, RAPTURE!

11 December 2006

Monitor RMAN I/O

Yesterday in #oracle, hali pointed out the existance of the v$rman_backup_job_details view, and has since written a nice example post about using it to monitor the I/O done by every RMAN job you've done.

betamax

Just converted over to Blogger BETA! It has labels, not sure how those would jive with my technorati tags. Guess it's play time.

08 December 2006

Apocalypso

This is the idea I had for a new movie where ancient natives perform human sacrifices in order to appease Harry Belafonte.

Movie studios, feel free to call.

06 December 2006

Analytics FTW

Today I was presented with this query:
SELECT *
FROM sid_batch
WHERE(loc, from_sid, sid_bid, type, add_date, batch_no) IN
(SELECT loc,
from_sid,
sid_bid,
type,
add_date,
MAX(batch_no)
FROM sid_batch
WHERE(loc, from_sid, sid_bid, type, add_date) IN
(SELECT loc,
from_sid,
sid_bid,
type,
MAX(add_date)
FROM sid_batch
WHERE loc = '004'
AND from_sid = '60056'
AND status <> 'I'
AND type <> 'A'
GROUP BY loc,
from_sid,
sid_bid,
type)
AND loc = '004'
AND from_sid = '60056'
AND status <> 'I'
AND type <> 'A'
GROUP BY loc,
from_sid,
sid_bid,
type,
add_date)
AND loc = '004'
AND from_sid = '60056'
AND status <> 'I'
AND type <> 'A'
ORDER BY loc,
from_sid DESC,
sid_bid DESC,
type DESC,
batch_no DESC;
Now I know almost nothing of analytics, but I did recognize that this sort of thing is exactly what they are for. In this case, the user wants to get the records that contain the highest batch_no for the highest add_date for a given loc, from_sid, sid_bid and type.

After a bit of research and chatting with hali, I trial-and-errored my way to this:
SELECT *
FROM
(SELECT sid_batch.*,
rank() over(PARTITION BY loc,from_sid,sid_bid,type
ORDER BY add_date DESC, batch_no DESC) rank
FROM sid_batch
WHERE loc = '004'
AND from_sid = '60056'
AND status <> 'I'
AND type <> 'A')
WHERE rank = 1
ORDER BY loc,
from_sid DESC,
sid_bid DESC,
type DESC,
batch_no DESC;
The first thing you should notice is that it is a LOT cleaner looking. Basically all the work is done in the one query, I just use the outer query to pick the #1 ranked rows and sort them.

The key is the rank() function. What rank() does is provide a numerical ranking based on a group of criteria (the PARTITION BY clause) and an order (the ORDER BY clause). In this case I say that for every set of loc, from_sid, sid_bid and type, rank the records of that set first by most recent add_date, and then by highest batch_no. This means the the highest batch_no for the highest add_date in that set will be ordered first, with a rank of 1. In my outer query I can then just choose the records with rank=1 and be on my way.

The query plan is MUCH cleaner as well. No messy hashes or nested loops, and, while the cost wasn't too bad at all (6), I cut it in half.

The Internet!

05 December 2006

#oracle-wtf


[11:42] <Aleksey2> How do you say is not bigger then
[11:42] <Aleksey2> AND updated_at !>= TO_DATE('12-01-2006','MM-DD-YYYY') does not work
[11:43] <rizzo> uhh.
[11:43] <rizzo> <
[11:43] <rizzo> ?
[11:43] * rizzo waits for it

04 December 2006

Keep Oracle Docs Handy

As I just shared in #oracle, it really helps to have a handy bookmark to the Oracle documentation library for your release. Oracle really does a good job with its documentation and making it easily available and searchable.

03 December 2006

Leaving Gallery

I just emailed my resignation from the Gallery project. I had been brought on nearly a year ago after writing the Ratings module for Gallery2. Unfortunately I haven't been able to commit any sort of time to the project, and with my recent devotion to Oracle, I don't see that changing. Given that, I felt it was just best to leave the project rather than just be a hanger-on.

Gallery2 is seriously the most well-architected piece of software I have ever had the pleasure to work on. Thanks to bharat and mindless and many others for the good times!