Fun with SQL

My group held it's first Peer Code Review today. It was my code (the code I've screwed up a couple of times). One of the requirements (I stuck to them this time, yeah for me!) was to limit the data we were pulling from another system to only the last 48 months. There were three different procedures that needed this date but I didn't want to break the existing automated job so I defaulted everything to:

p_date_from DATE DEFAULT ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), -48 )

I wanted the last full 48 months and this worked just fine.

This is a fairly long process which takes anywhere from 8 to 12 hours to run, depending on system resources. One of my colleagues asked what would happen in the event of a crash (on the last day of the month) and the required re-start (on the first day of the month). One set of data would have everything going back 49 months and the other 48 months which would create a lot of errors. A great catch.

Since we're in maintenance mode now (re-architecting the whole thing), we decided not to stop the deployment, we looked at the calendar to see when the first occurence was in which the 1st fell on a Sunday (the job is scheduled weekly on Sunday)...which now that I think about it, was probably wrong. We should have been looking for a Sunday in which was also the last day of the month. Anyway...

I like trying to answer questions with SQL. So I wanted to find the occurences on which Sunday was also the first day of the month (despite while writing this realizing that we should have been looking for the last day, again, digression). Here's my resultant query:

SELECT
  ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum )first_day_on_sunday
FROM
  ( 
    SELECT rownum myrownum
    FROM dual
    CONNECT BY LEVEL < 100
  )
WHERE TO_CHAR( ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum ), 'D' ) = 1
  AND TO_CHAR( ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum ), 'DD' ) = 1
/

FIRST_DAY_
----------
06/01/2008
02/01/2009
03/01/2009
11/01/2009
08/01/2010
05/01/2011
01/01/2012
04/01/2012
07/01/2012
09/01/2013
12/01/2013
06/01/2014
02/01/2015
03/01/2015
11/01/2015

I'm not so sure it impressed my colleagues, but it certainly reinforced the notion that I'm a nerd.

Humor in the Workplace

In an effort to deal with my recent screwup (one of two recent ones), I decided to try and laugh at it a bit.

Our organization is very young and since the on-boarding of our new CIO, our directive has been to stabilize current processes. In that regard, the VP who heads up the Infrastructure team has placed placards on his office window signifying the number of days one or another system has been up with no interruption in service.

I ran into him in the hallway and suggested he put one up for me too, "Days since Chet 'messed' up Production code." He got a hearty laugh out of that.

I've always been able to laugh at myself, if not immediately, then soon after. So I put up a hand made sign in my cube that read the same thing; I'm at 21 days. Hopefully it will remind me as I'm pouring through code not to touch that which is out of scope with the current requirements. That was my mistake on both occurences.

It's definitely a talking piece and hopefully people can laugh at it (as I can...sortof), but it will be a constant reminder to me as well.

Why I Blog

I never really had a true mentor, well, a physical one anyway. I could ask questions to my first boss, but it was apparent rather quickly that they better be good ones.

So I took to reading AskTom everyday. Mostly the new posts at first but as time went on, there were fewer and fewer of those. Tom was mostly answering old questions. But they were fun to read and you could really get insight to his thinking. That's what I needed the most. The Why of doing things. I think this post illustrates more the Why, I still read that one from time to time.

Then a couple of years ago he began blogging. That really gave me a view into his mindset. I learned about instrumentation there, how to ask questions, how to answer them and the rule of thumb, to name but a few.

I think that was the first blog I read consistently.

I found his advice and his technical expertise inspiring. More than anything though, I liked have a view into his world. I believe that alone helped me get over the technical hurdle, to begin to really understand what was going on and why I should do things a particular way.

I hope I can do the same. I want to share my experiences, challenges and mistakes so that others may learn from them. I also want to provide more technical content, even though much of it is answered in one place or another, just because I have a different take. Someone might relate to my style and find me inspiring (that would be way cool).

I now consume about 40 or 50 different blogs daily. Most of them are Oracle related, but I also read many business blogs, opinion blogs, security blogs and data warehousing blogs (though I haven't found one yet that really inspires).

I believe it is a good thing to be well rounded and I try to be.

So that's why I blog. I want to share. Hopefully you'll find something you can relate to here.

Good Day to Worse Day

Today I got the opportunity to have lunch with my CIO.

A few weeks ago I sent him a manifesto, which I would now classify as more of a philosophy. He kept promising me he would articulate a response via email. Being the CIO of a Fortune 400 company, I figured he had better things to do than to write out an email to me. So I offered him up a trade, lunch in exchange for the email. Surprisingly, he agreed.

Today was the day but I fully expected him to cancel; surely something else would come up. Nothing did, but he did move it back by 15 minutes.

Down to the cafeteria we went (I was really hoping to go out to lunch, just to get a ride in his Porsche Gemballa). We sat in a booth and started talking. We discussed everything from my group (excellent group of people, talented and fun), using MySQL databases for one-off projects (I was for putting them in a single Oracle database), to the state of our current OLTP application (crud).

He mentioned user-defined fields (OLTP) and I told him about one instance where someone in our company created those in an internal application. I didn't say anything at the time because it was not in my group and I didn't want to call someone out for something I thought was wrong. He told me I should have, that it would be in the best interests of the company. Then he said something that I have heard him say in our All-Hands meeting, "Let me be the one that makes the wrong decision."

That sealed the deal for me. I have liked him and what he has done since he got here, but that one comment told me that he took his job as leader seriously. I was thoroughly impressed.

So that was the good (great) part of the day. I felt great because our CIO listened to me prattle on for an hour and listened to me. He even used one of my analogies (well, not mine really) in his management meeting a short time later.

Now on to the worse part.

The application I have been working on for the last few months required an Emergency Fix (EFIX) because they had detected a bug. I realized quickly that I was the culprit. Something that had worked previously was changed by me in an effort to re-factor the code. It wasn't broken. There was no specific business requirement to re-factor it, I just did it...and screwed it up. The ironic part is I had just sent out an article to my co-workers about discipline making good developers.

I told this to the Business folks, what I had done; and apparently I hadn't earned any brownie points with them because they escalated it to my boss and VP. Which of course had to go to the CIO as well...

I definitely screwed up. There's no way around that. I know better than that. Oh well. It was certainly a lesson in humility. I'm just thankful I still have a job...

SQL Puzzle, Sort of...

My group was once asked, "What's the first number that contains an 'A' in it?"

This has become a once a week exercise for fun.

I of course started counting from one to see where it might happen. Then I decided to use the magic of SQL to help me go through all the numbers.

On 10g:

SELECT num
FROM
  (
    SELECT
      TO_CHAR(TO_DATE('01/01/4712 BC', 'MM/DD/YYYY BC') + (rownum - 1), 'JSP' ) num
    FROM DUAL
      CONNECT BY LEVEL < 1000
  )
WHERE INSTR( num, 'A' ) > 0;

NUM
-------------------
ONE THOUSAND

I found the format model in the documention and moved on from there. I think my original query was a bit more elegant, but this works.


Pretty cool stuff.

How I Got Started...

It all started 5 1/2 years ago, my first IT job as a reports developer. I was given a tnsnames file and SQL*Plus. What? What the hell am I supposed to do with this?

I learned about databases through Microsoft's Access. While I would never want to go back to it, I am thankful it was there or I probably would never have learned. I was basically a secretary (administrative assistant for the PC). I got tired of entering in the same thing every month and knew there had to be a better way. So I put it in Access to speed things up a bit. I began to annoy the crap out of the IT department. I now wanted raw feeds of the data so that I wouldn't have to type in anything...

Finally a friend came along and offered me a job.

That's when I met Oracle.

So there I sat on my first day wondering where the tables where. I couldn't "see" them. I needed to "see" them dammit. Within a week I had set up an Access database to be my front end to Oracle; I couldn't stand not "seeing" the tables.

That got old real quick though. The sheer volume of data caused that. Within weeks, I was using SQL*Plus and beggining to learn the data dictionary.

I'd ask my cube mate, "How do I see the source of the view?"

SELECT text FROM user_views WHERE view_name = 'MY_VIEW';

Oh yeah; SET LONG 100000

I was fortunate enough to have a helpful cube mate.

And so began my obsession...

I would say it took a good year before I really started to feel comfortable with SQL*Plus and Oracle, and 5 years later, I'm still using it.

I'm glad I was forced to learn Oracle that way. I believe that I have a far better understanding of how Oracle works because of my experience.

What I Read...

Database

Oracle Documentation.

For over 5 years now I have been reading Tom Kyte's AskTom site hosted on Oracle's site. This is the first place I go when I have an Oracle related problem.

For the past 2 years or so, I have also been reading his blog.

The second source of information concerning Oracle is Metalink. I mostly find all my answers on AskTom, but for the more obscure problems, this is the best place to go.

Third would be the OTN forums, mainly the SQL and PL/SQL forum.

The C.D.O.S. group is next followed by a general search on Google. Most of those lead to Morgan's library on the PSOUG site.

Of late, I have found many blogs as well. Particularly Dratz, who has a very informative series on How Oracle Really Works. We've exchanged a few emails over the past couple of months and I believe we have a similar belief about the use of the database (particularly Oracle).

Lewis Cunningham is another good source of information. He talks about Oracle, EnterpriseDB and PostgresSQL. He is currently utilizing Streams (Advanced Queueing), has used XML in Oracle and is/was a database architect. I have been reading his blog for a couple of years now.

I also read Howard Rogers, David Aldridge, Mark Rittman, and Jonathan Lewis (though I don't understand much of what he posts).

APEX - Application Express (formerly HTMLDB)

The OTN forum is my first choice. I contributed quite a bit there before moving to Tampa late last year.

John Scott - He's a big contributor to the APEX community. In fact, he received the Oracle Ace award last year. Mainly though he runs a site called shellprompt which allows you to host your APEX application. I was able to utilize his service for about a year with my previous business and I still have a site hosted with shellprompt. John's service was second-to-none!

Patrick Wolf - I've been reading his blog for a few months now, but unfortunately, I don't work with APEX (at work) anymore.

Carl Backstrom - He works on the APEX team at Oracle. His sample/example stuff is excellent.

Business

37Signals - Very transparent business that posts about how they run a business, their development philosophy (getting real), and design decisions. I have used their Ta-da List (to do list) and Writeboard (collaborative writing).

I'll continue to add more links here as time goes on; or you can just check out my delicious links to Oracle.

Swapping Partitions

Recently I learned a pretty cool trick, swapping partitions.

I'm sure I read it originally on AskTom, but I never had a need for it. Now that I am in data warehousing though, moving millions and millions of records around takes time...a lot of time.

On this recent project, I had a process which brought in the data from a different database. There were some minor transformations on that data including the use of an analytic function to de-dup the data (I would assign a 1 to the first record matching my criteria and that's what I would use for everything downstream).

So I have essentially six tables. One which is the target for the source data, four which handle the transformed data and another one which holds all of that transformed data. Initially I would perform a INSERT INTO...SELECT FROM...but it would take upwards of three hours for this one particular set of data (~26 million records), and I was doing that twice (six hours).

Sitting with a colleague trying to figure out a way to speed up the process, we came across an article on AskTom which mentioned swapping (or exchanging) partitions with a regular table. The final table (of the six) is partitioned by line of business, a perfect candidate.

So I issue the statement found there:

ALTER TABLE target_table
EXCHANGE PARTITION gmd_lob
WITH TABLE source_table;

That didn't work because the table structures did not match. So I went about changing the table structure to match that of the target table.

I issued the statement again:

ALTER TABLE target_table
EXCHANGE PARTITION gmd_lob
WITH TABLE source_table;

Success! I then went on to change the other three tables structure to match that of the target table. What had taken three to four hours now took less than five minutes!

My Manifesto

I am an Oracle Nerd.

I am not an Oracle fanatic.

I believe that SQL Server, DB2, MySQL, et. al. are all fine databases, but I don't know them like I know Oracle.

Regardless of your database, use it. Stop treating it like a bucket for your data. If you are going to do so, you might as well use flat files...or better yet, XML! Oracle is an incredibly powerful database and I believe in using what I (well, my employer) paid for.

At my current employer, we have numerous Oracle databases on site: Operational Data Store (ODS - mostly raw data from outside agencies), our production OLTP system and 2 data warehouse databases (we're currently trying to re-architect into one). Out of all those, we use exactly zero built in features. No CDC (Change Data Capture), no Advanced Queueing (Streams), hardly any analytics.

I try to keep things as simple as possible. My experience has been that most people want to overcomplicate things...hopefully I can help to change that at my workplace now. It won't be easy, but I'll certainly try.

I want to share my experiences; in my new role as a data warehouse developer, things I have learned designing, developing and maintaining OLTP systems, and my experience with the incredibly powerful APEX (formerly HTMLDB).