Oracle Goodness

I've spent years in SQL Land, and I consider myself fairly well informed about data. But after working with some of the people I have, I realize that there is so much that I don't know. I do know enough to do what I need to do without causing smoke and fire and other general mayhem, but just about everything I know now I've had to discover and learn myself. I've had a few good mentors that pointed out ways to tell if I'm writing slow, inefficient queries (and I've done plenty of that), and my views have shifted towards DB performance in my queries. I've also come to realize why software developers and DBAs often seem to be at odds with each other. Some of the worst queries I ever wrote were when I was writing in-code queries and didn't understand their impact. One of my previous databases had several related tables with 10s of millions of rows each. That's not BIG data in the modern sense, but that's A LOT of data. More than enough to let me easily see the difference between my code-based reporting query that ran in 2 hours and the DBA-optimized SQL that ran in 2 seconds. The SQL looked weird, but it was MUCH faster. Big lesson learned there. A database isn't just a place to store and retrieve data. You can get lots more from it by knowing how to ask nicely rather than trying to brute force the data you want. It's about getting SETS of data rather than simply trying to filter data. And it's about letting the database do the job it was made to do.

I like T-SQL, and I learned quite a bit about it in my reporting. And now that I'm back in Developer Land and making the move to an Oracle system, I'm seeing a few things I don't like about PL-SQL, but that's likely because I'm just not used to it. Only time will tell.

However, there are a few things I do like quite a bit. For instance, when making a new stored procedure (my current task), I used to have to do something like this:

IF OBJECT_ID('xxxxx') IS NULL EXEC('CREATE PROCEDURE xxxxx ...') GO 
ALTER PROCEDURE xxxxx ...

Or do several lines of logic with checking sproc existence in a system view. It wasn't hard, but it was long and involved.

MySQL was a bit better, but that wasn't my primary environment:

DROP PROCEDURE IF EXISTS xxxxx ;
CREATE PROCEDURE xxxxx ...

Though dropping and recreating db objects is never nice to existing permissions. :-/

With Oracle, I get to do:

CREATE OR REPLACE xxxxx ...

It's nice to have that kind of functionality already built in without having to worry about killing permissions. SO much easier.

Now, on to build some sprocs....