Fun with SQL - My Birthday

This year is kind of fun, my birthday is on November 12th (next Tuesday, if you want to send gifts). That means it will fall on 11/12/13. Even better perhaps, katezilla's birthday is December 13th. 12/13/14. What does this have to do with SQL?

Someone mentioned to me last night that this wouldn't happen again for 990 years. I was thinking, "wow, I'm super special now (along with the other 1/365 * 6 billion people)!" Or am I? I had to do the math. Since date math is hard, and math is hard, and I'm good at neither, SQL to the rescue.

select 
  to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
  to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
  to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
  sysdate + ( rownum - 1 ) actual
from dual
  connect by level <= 100000

(In case you were wondering, 100,000 days is just shy of 274 years. 273.972602739726027397260273972602739726 to be more precise.)

That query gives me this:

MONTH_OF DAY_OF YEAR_OF ACTUAL   
-------- ------ ------- ----------
11       06     13      2013/11/06 
11       07     13      2013/11/07 
11       08     13      2013/11/08 
11       09     13      2013/11/09 
11       10     13      2013/11/10 
11       11     13      2013/11/11 
...

So how can I figure out where DAY_OF is equal to MONTH_OF + 1 and YEAR_OF is equal to DAY_OF + 1? In my head, I thought it would be far more complicated, but it's not.

select *
from
(
  select 
    to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
    to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
    to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
    sysdate + ( rownum - 1 ) actual
  from dual
    connect by level <= 100000
)
where month_of + 1 = day_of
  and day_of + 1 = year_of
order by actual asc

Which gives me:

MONTH_OF DAY_OF YEAR_OF ACTUAL   
-------- ------ ------- ----------
11       12     13      2013/11/12 
12       13     14      2014/12/13 
01       02     03      2103/01/02 
02       03     04      2104/02/03 
03       04     05      2105/03/04 
04       05     06      2106/04/05 
05       06     07      2107/05/06 
...

OK, so it looks closer to 100 years, not 990. Let's subtract. LAG to the rescue.

select
  actual,
  lag( actual, 1 ) over ( partition by 1 order by 2 ) previous_actual,
  actual - ( lag( actual, 1 ) over ( partition by 1 order by 2 ) ) time_between
from
(
  select 
    to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
    to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
    to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
    sysdate + ( rownum - 1 ) actual
  from dual
    connect by level <= 100000
)
where month_of + 1 = day_of
  and day_of + 1 = year_of
order by actual asc

Which gives me:

ACTUAL     PREVIOUS_ACTUAL TIME_BETWEEN
---------- --------------- ------------
2013/11/12                              
2014/12/13                          396 
2103/01/02                        32161 
2104/02/03                          397 
2105/03/04                          395 
2106/04/05                          397 
2107/05/06                          396 
2108/06/07                          398 
2109/07/08                          396 
2110/08/09                          397 
2111/09/10                          397 
2112/10/11                          397 
2113/11/12                          397 
2114/12/13                          396 
2203/01/02                        32161

So, it looks like every 88 years it occurs and is followed by 11 consecutive years of matching numbers. The next time 11/12/13 and 12/13/14 will appear is in 2113 and 2114. Yay for SQL!

caveats

I always find myself putting an asterisk (if only mentally) next to certain statements. I shall now put all those statements here and link back.

1. I don't know everything
2. I'm not the best developer in the world, but I constantly work at getting better...
3. If I make a statement about something, that's been my experience. Your results may vary.
4. I am not a salesman.
5. I do not work for <insert company name which I just pitched here>

That's it...for now.

The Internet

Have you seen this State Farm ad?



I think it's hilarious.

Riding to batting practice with LC, he starts up with me...

LC: (in response to some statement I made) "Where'd you hear that?"
Me: "The Internet"
LC: "And you believed it?"
Me: "Yeah, they can't put anything on the internet that isn't true."
LC: "Where'd you hear that?"
Together: "The Internet"

We also do the "And then...?" skit from Dude, Where's My Car?. He used to be able to rattle off the saying from Tommy Boy, "You can get a good look at a t-bone by sticking your head up a bull's..." - I'm pretty sure this is better than that.

Run Scripts in SQL Developer

I finally decided to save a script that cleans out a couple of tables for me.

Now I have a script, how do I run it in SQL Dev? In SQL*Plus, I would run it like @clean_tables. Two things to note there, 1, I didn't have to put the extension on the file and b, I assumed SQL*Plus was running from the directory where my file was located. If I was running the script from a different directory, I would have to use either a relative path...or something, but I digress.

I wanted to be able to run my script in a SQL Developer worksheet. How?

@clean_tables

Error starting at line 38 in command:
@clean_tables
Error report:
Unable to open file: "clean_tables.sql"

Twitter. Jeff Smith hangs out there, a lot. He supposedly has a real job as the Senior Assistant Principal Skinner Product Dude for SQL Developer at Oracle. Crazy title, I know. Back to Twitter.

@oraclenerd working directory would be directory of parent file

— Jeff Smith (@thatjeffsmith) February 22, 2013

Since he lives there (Twitter) (and I'm glad he does), I got an immediate response. Yay for Jeff.

Wait, what? Parent file? WTF are you talking about?

(I then remove the snark and try to put more details)

(oh, and I don't like that I can't just embed a single tweet...sorry, their fault, not mine)

@thatjeffsmith "@ test(.sql)" I just want to know where to put test.sql.

— oraclenerd (@oraclenerd) February 22, 2013

Two seconds later:

@oraclenerd i think this, Tools > Preferences > Database > Worksheet > Select default path to look for scripts. Please test :)

— Jeff Smith (@thatjeffsmith) February 22, 2013

Tested, and it works. Yay for me. Yay for Jeff.



In case it isn't obvious, I'm being sarcastic. Jeff is a fantastic advocate for SQL Developer. Yes, he gets paid to do it, but he goes above and beyond on a daily basis. Oracle is lucky to have him.

Fun with Date Math

(First off, sorry Mike, I'm hoping this will break my writer's block...)

On Friday I was asked to look at a report that wasn't returning all of the data. Sample:

Year/Month  Total Sales Total Sales (YAGO)
------------------------------------------
01/31/2013   $1,000,000           $900,000                
03/31/2013                        $950,000

For reference, YAGO is "Year Ago."

Notice anything funny there?

Yeah, February is missing. The (OBIEE) report has a filter on Jan, Feb and Mar of 2013. But it wasn't showing up. I confirmed via manual SQL (hah!) that there was (YAGO) data in there for February. Any ideas?

I immediately suspected one of two things:
- If the Date (month) dimension had a "year ago" column it was wrong.
- The join in OBIEE was doing it wrong.

I checked the date dimension first. It was fine. It didn't even have a YAGO column, so nothing to see there. I looked at the join between the date dimension and the fact table...

(YEAR ("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE" ) - 1 ) * 10000 
+  MONTH ("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE" )  * 100
+ CASE WHEN DayOfMonth("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE") = 29 THEN 28 ELSE
DayOfMonth("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE")  END 
= "DW".""."DW"."My_Fact_Table"."MONTH_DIM_KEY"

I want to tear my eyes out when I see stuff like that. I don't even want to know what it does. * 1000? * 100? Shoot me.

OK, so the MONTH_DIM_KEY is in the YYYYMMDD format. MONTHEND_DATE is a date data-type that corresponds to the last day of the month. For February 2013, it's 20130228, For February 2012, it should be 20120229. <<< Leap Year!!! I'm going to make a wild guess and say that the formula up there isn't working. How to test it though? That's logical SQL (OBIEE), it doesn't run in the database. I just ran the report and grabbed the SQL submitted to the database. This is what it looked like:

          AND ( TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'yyyy' ), '9999' ) - 1 ) * 10000 +
          TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'MM' ), '99' ) * 100 +
          CASE
            WHEN TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' ) = 29
            THEN 28
            ELSE TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' )
          END = MONTH_DIM_KEY
  AND( MONTHEND_DATE IN( TO_DATE( '2013-01-31', 'YYYY-MM-DD' ), TO_DATE(
  '2013-02-28', 'YYYY-MM-DD' ), TO_DATE( '2013-03-31', 'YYYY-MM-DD' ) ) ) 

Eyes are burning again. This is also the "prettified" SQL after I hit Ctrl + F7 in SQL Developer. The very first thing I do with OBIEE generated SQL.

One part of that wouldn't be so bad, but it's three formulas adding up to some mysterious number (presumably the last day of the month, for the previous year, in YYYYMMDD format). So I moved all those formulas up into the SELECT part of the statement. Let's see what they are doing.

SELECT
  ( TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'yyyy' ), '9999' ) - 1 ) * 10000 part_1,
  TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'MM' ), '99' ) * 100 part_2,
  CASE
    WHEN TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' ) = 29
    THEN 28
    ELSE TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' )
  END part_3
FROM my_month_dim
WHERE MONTHEND_DATE IN ( TO_DATE( '2013-01-31', 'YYYY-MM-DD' ), 
                         TO_DATE( '2013-02-28', 'YYYY-MM-DD' ), 
                         TO_DATE( '2013-03-31', 'YYYY-MM-DD' ) )

That resulted in this:

PART_1         PART_2         PART_3
20120000       100            31
20120000       200            28
20120000       300            31

So PART_3 is definitely incorrect. Am I going to bother to figure out why? I have some serious issues inside of my brain which simply do not allow me to do date math. I avoid it at all costs...instead choosing to use whatever the system provides me.

One of my favorites, especially when dealing with leap years, is ADD_MONTHS.

If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month

That's why. Add -12 months to February 28, 2013. You don't get back February 28, 2012, you get back the 29th, as it should be. Do the same thing starting with February 29th, 2012. Add 12 months, subtract 12 months. It's always right. Yay for someone figuring this out so I don't ever have to do so.

Sadly, OBIEE doesn't have the equivalent of ADD_MONTHS (or LAST_DAY), you have to build out a string and then concatenate it all together, not pleasant. So I cheated, I used EVALUATE. Here's my working solution.

TO_NUMBER( TO_CHAR( ADD_MONTHS( MONTHEND_DATE, -12 ), 'YYYYMMDD' ) ) = DW_MONTH_DIM_KEY

Oops, that's the physical SQL. How about the OBIEE SQL:

CAST( EVALUATE( 'TO_NUMBER( TO_CHAR( ADD_MONTHS( %1, %2 ), %3 ) )',
 "EDW".""."DW"."Dim_DW_MONTH_DIM_CBM"."MONTHEND_DATE", 
-12, 'YYYYMMDD' ) AS INTEGER ) = "DW".""."DW"."My_Fact_Table"."MONTH_DIM_KEY"

Oracle OpenWorld - Swim in the Bay 2012

Last Monday at 7:30 AM PST, 17 brave souls joined me in the First Annual Oracle OpenWorld Swim in the Bay, a refreshing swim in San Francisco waters.

To be precise, it was at the Aquatic Part near the Dolphin Swim & Boat Club. OK, here's the map to make it easier.



And here's a pretty panoramic:

From Oracle Open World 2012

At my very first OOW, I did this swim 3 or 4 times. I was in much better shape back then. This time, I just thought it would be a fun, easy diversion with a chance to bond with fellow travelers. On Saturday, Jeff Smith and I wandered over to the "beach" and got our feet wet. Here's my interview with him:



Here's part of the crew before disrobing



Note John Hurley's cow hat, that's John in the lime green tank top over to the left. Others pictured here from left to right: Kent Graziano, John Hurley, Bjoern Rost, Gustavo Rene Antunez, I believe the yellow green jacket is Henning Voss, Stanley the ACE Director sitting on Debra Lilley's lap, another Bjorn (help please?) Björn Ole Voß, Connor McDonald and Mogens Norgaard. Now Connor had an unfair advantage, he's currently training for an English Channel Crossing, so I'll photoshop all future pics of him to make him look less like a superhero.

Now, here's the big group shot, disrobed this time. If you have an issue with bright lights, cover your eyes now.



From left to right: Me, Gustavo, Alex Crane, Jeremiah Wilton, Connor McDonald (who we made stand in the back so we wouldn't look so bad), Debra Lilley and Stanley the ACE Director, Bjoern Rost, Bjorn (help again), Sheeri Cabral, Henning Voss, John Hurley, I can't remember his name, help Björn Ole Voß and finally Mogens again. Not pictured: Kent Graziano and Tom Wurzbach (the guy who gave you me, i.e. my first boss).

Debra getting her feet wet...sparing Stanley the cold. In the background, you can see Sheeri Cabral and Alex Crane (I think).



And finally Kent Graziano all wet.



Good times.

Did you notice I said First Annual up at the top? Well, I did. We're doing this again next year and we have sponsors! Nothing terribly fancy, perhaps a ribbon for your badge saying, "I swam in the Bay." Or ORACLENERD swim caps. Or coffee, lots of coffee after the swim to warm up. The goal is 50 people. If you're planning on going to OOW next year, join us!

My First Triathlon?

Not sure why I felt this post-worthy...

A friend and co-worker of mine is doing her first triathlon this weekend. I think she saw some of my posts about my first (in a million years) being in October, so she called me out. I told her I would sign up for one on July 28th, but I waited to long and the entrance fee was close to a million dollars.

What follows is our (edited) conversation.

Erica: I'm going to need a triathlon pep talk before saturday.
Me: is it the 21st? I'm sure I can do that.
Erica: yeah, I'm nervous as hell. I want to be able to finish the race and not humiliate myself
Me: wait...I've got something.
Me: http://www.anytimehealth.com/blog/12252-tara-costa-you-are-an-ironman and you're not even close to where she was during the ironman

Side note, I taped and watched the 2011 Hawaii Ironman recently. I cried almost the entire show. I do that.

Erica: I can't imagine doing a 14 hour race. that's crazy you're crazy.
Me: yeah, i know. the point is, you can manage < 2 hours doing a swim/bike/run no problem.
Erica: 2 hours is my goal.
Me: things to prepare for: 1. as soon as you get in the water, you'll think to yourself, "WTF am I doing?" Just keep swimming just keep swimming.
Erica: thanks Dory
Me: You'll be out of the water in 20 minutes or so.
You'll get out of the water and your legs will be wobbly, sea legs. You'll think to yourself, "WTF am I doing?"
but you'll jog, or walk up to your bike, put on your shoes and get on anyway.
About 5 minutes into the bike you'll be thinking, "so that's why they have aero bars, so they can rest their arms after the swim."
followed quickly by, "WTF am I doing?"
But you'll keep going...enjoying the breeze and freedom the bike provides.
About 30 minutes into the bike, you'll wonder, again, why you're doing this. as you're passed by someone twice your age (you'll know, as everyone has their age painted on the back of their calf..at least they used to do that).
Erica: they still do
Me: and you'll be embarrassed and want to quit
then you'll think...wait, if this old effer can do it, why can't I?!!
and you'll keep pedaling
ah...you hear the roar of the crowd...or not, but you realize you're about a mile out of the bike/run transition.
you drop into a lower (easier) gear and spin so that your legs won't be so full of lactic acid after doing squats (pedaling) for an hour.
you'll cruise into transition, park your bike and sit down to put your shoes on.
after your shoes are tied, or slipped on (like I do, I hate socks, and tying shoes)
you'll start to jog out of the transition area
and your heart will explode...
and you'll think to yourself, "WTF am I doing?"
you'll then decide to give it a minute or two and slow down your pace so you can slow down your heartrate
but your legs are screaming at you...saying "WTF are YOU doing!??"
and "I effing hate you!"
"stop moving!!"
but you'll keep moving forward
Erica: omg, you're not helping. :p
Me: about every 4 minutes on the run this will happen. "WTF am I doing?"
The pain from the bike will leave you after about 10 minutes...
then it's just a matter of willing yourself, Green Hornet style, to keep going.
If you have to walk, walk. Do it for a minute or 2 minutes. It's OK.
Stop for water.
Then you'll think...well this is a convenient place to stop...
but you won't.
Best part about most runs is they are out-and-back...makes it harder to quit as you have to get back somewhere, you might as well finish.
Actually, every minute or so you'll be thinking to yourself, "WTF am I doing?" which is quickly followed by, "Hey, I'm on the run already, awesome!"
Drink water at the stations.
Pour water on your head.
It will help, physically.
But I doubt the physical will be the hard part...the hard part is ALWAYS the mental. If you're goal is to finish, just finish. Take your sweet ass time out there. Enjoy the scenery, hot bodies scantily clad.
Erica: LOL
Me: And realize that everyone is going through EXACTLY the same thing as you...because they are.
Erica: yeah, but they all get to enjoy the beer at the end

And then it devolved from there, shocking, I know.

For whatever reason, I found it an intriguing chat session. You're the (un?)lucky recipient.

I'll post Erica's results here when she finishes. Wish her luck!

For the record, I'll be doing my first triathlon in over 11 years this October.

Kscope 2012 Recap

The Venue


The event took place this year in hotter-than-Hades San Antonio, Texas. Fortunately for all of us, the hotel and conference were in a single (Texas-sized) building; the JW Marriott San Anonio.

Just 20 minutes from downtown San Antonio and the airport, the JW Marriott Resort is situated in Texas Hill Country in Cibolo Canyons, where crystal clear streams and towering oak and cedar trees meet the majesty of the hills. The elements of authentic roots, hacienda style, beautiful views and healing waters serve as the inspiration in bringing this magnificent resort to life. The Hill Country resort features 265,000-square-feet of meeting and event space, the 36 hole TPC San Antonio and the sophisticated Lantana Spa.

Let's not forget the Lazy River, where many of my compatriots...lost themselves.

The River Bluff Water Experience offers six acres of heated pools, fountains, waterfalls, rivers, and an abundance of poolside lounges to soak in the sun.

Pretty awesome.

One other thing about the venue, I don't recall a single complaint or tweet about wi-fi. I've not been to a conference where someone doesn't complain about that. Well done Marriott, well done.

The People
If you known me for any length of time, you probably know this is my absolutely favorite part. To me, people are bright shiny objects. This sums it up nicely as well, a friend said this:

OH: "I don't think the world has enough people for me to feel like I have enough friends - all are welcome"

— oraclenerd (@oraclenerd) March 16, 2012

Tough to argue with that.

Day -1 I met Jeff Smith and Dan McGhan at the airport. Well, we didn't plan on meeting at the airport, but we happened to be there at the same time. Anyway, Jeff gave us a ride to the Marriott.

In the lobby we met Patrick Barel and Galo Balda, who we had been planning (via Facebook) on going to The Riverwalk and The Alamo that day. Unfortunately for Patrick, he's of the Dutch persuasion and I had recently seen Austin Powers in Goldmember. Now, I really have no context for the "hatred" of the Dutch, but it's funny, so I went with it. I'm sure others would be proud.



Where do you get a chance to publicly torture Don Seiler? Kscope. I'm sure this battle will escalate.



You can see all the pictures I took at Kscope here.

The Party
Armadillo racing? Check.
Sitting on a Texas Longhorn. Check.
Barbeque. Check.
Bull Riding (mechanical). Check.
Rodeo. Check!

Last year it was on the Queen Mary with Busta Move artist Young MC. This year, a full blown rodeo at the Knibbe Ranch. I'll spare you the picture of my war-wound from being thrown by the bull. In my defense, it was night. I may or may not have imbibed an adult beverage or two.



A friend and co-worker was even spotted chasing after a small bovine for a chance to win a pass to Kscope 2013. You should see the video. Fun. Speaking of videos, there are videos of Lisa Dobson riding the bull, Jeff Smith, Tim Gorman and Kellyn Pot'Vin out there. If you're nice, maybe I'll share.

My favorite moment though is definitely this one:



That's Danny Bryant awesomesauce.

The Staff


The YCC staff, minus Kathleen McCasland. From left to right: Aimee, Brianne, Larissa, Lori, Bo (err, Lauren), Melissa, Heather and Crystal (the new Executive Director).

It's difficult to say enough about this team. They're excellent at what they do and have a lot of fun doing it.

Let's not forget the ODTUG Board.

• Monty Latiolais (pronounced like Frito-lay) - President
• Barbara Morris - Vice President
• Jerry Ireland - Treasurer
• Bambi Price - Secretary
• John King - Director
• Cameron Lackpour - Director
• Mike Riley - Director (and former President)
• Tim Tow - Director
• Martin D'Souza - Director
• Edward Roske - Not Sure (site's been updated, and I'm lazy right now. I just know that he's everywhere and part time comedian.)

The Board members are all volunteers. They give up a significant amount of their own time to help put this event together. They are one of the bravest groups too...they got on stage for the General Session in singlets, you know those things wrestlers wear? I know way too much about some of them. Consider yourself lucky I am not including a picture.

I think that's part of what makes them great though, their ability to put themselves out there like that.

So maybe now you're starting to think, "Man, that would be a great conference to geek out at." You're in luck because early bird registration is open and next year's event is in New Orleans.

Fun with OBIEE SQL

I posted a couple of tweets yesterday about this crazy query I had to work with. I won't Storify you to death, so I'll sum it up quickly. 1269 lines (formatted, of course). 13 WITH clauses. 8 base tables.

The error that was occurring was an Oracle one: ORA-00937, "not a single-group function."

That's interesting because OBIEE, the BI Server, generates/builds the SQL based on the logical model you've created. It's doing it wrong, in this instance. I'm willing to wager that it's a bug, in one way or another.

Now, if the logical model doesn't support this particular combination of columns, grain, or whatever, it will tell me so immediately. It will usually tell me while working in the RPD, so I won't even get this far (presentation layer).

Anyway, I found the offending piece of SQL, right at the bottom in the SELECT statement. There were 2 analytic functions, MIN and SUM and no GROUP BY. So I took the non-analytic columns, created a GROUP BY and added them in. It worked.

That didn't solve my problem though. Sure, I could run it in SQL Developer, but that doesn't do the end-user much good.

I tried to trace those 2 columns back up through the SQL...and then my eyes bled. With help from the user, I identified the column (measure) that was breaking the report. Now, could I trace this back through the SQL and figure out where? Not likely, but I tried.

I needed a visual representation, so I tried out FreeMind, a mind mapping software. This is what I came up with:



Nodes in Yellow share the same base tables. Ditto for the other color coded nodes. I couldn't figure out a way to get them to connect or share those child nodes.

Ultimately a fun little exercise (in futility?). Anyone else tried to do something like this?

The Exadata Mini - Exclusive Picture!

Early this morning ZDNet's Larry Dignan reported that Oracle would be announcing the "Exadata Mini" edition: Oracle's 'Exadata mini' would aim for midmarket

Well, through tireless research and a lot of hacking, I've found a picture of said machine:



Vern Troyer, the actor who plays "Mini-me" from the Austin Powers movies, stands 32 inches tall, which for the rest of the world, is 81.28 centimeters.

It stands at 20" tall, and as Mr. Dignan reports, this could fit under your desk.

If you're curious to know how Oracle was able to reduce the size...check out this video:

TFJ: New Bike and Stuff

TFJ = Tired of the Fat Jokes Since purchasing a spinner a few months ago, I've been pretty consistent. I've got a full page of entries now on my workout sheet. I've lost, maybe, 5 or 6 pounds...not great, but I feel a hell of a lot better. I can actually go up a flight of stairs without getting winded. Yay for me. Last weekend I bought a mountain bike, so I could ride around with LC. No Jeff, no cool pedals yet. I'm just getting started. Need I remind you of my heyday? I picked the bike up early Friday morning. LC had asked me to come down to the Y(MCA) to see him swim that afternoon, so I thought that would be a perfect opportunity to break in the new bike. The Y is only a couple of miles away. For normal people. I decided to take the "back" way. Only there wasn't a back way. But I kept going...thinking that at some point I would find a street (I was on the trails, in a park). This park was underwater...well, the roads were. See, I took the back entrance into the park, so I missed the "Road Closed" sign. Every 100 meters or so there was a stretch of trail underwater, for about 100 meters. Did I mention this was my first outdoor ride in ~10 years? After the 2nd or 3rd one, my heart was racing and my arms were killing me. I stopped. Then walked a little. Got back on. Got off. Back on. Finally, the park entrance...and more importantly, a road, a paved road. OMG! I'm like 2 miles north of where I want to be. Make it about a mile. Stop. Half mile. Stop. Finally just push forward, I knew they had sustenance at the Y. I was supposed to be there at 2. I arrived sometime after 3. Bought 2 bottles of Gatorade. Said hi to LC. Went outside and sat down. Exhausted. After about 20 minutes, I gathered up my remaining strength and rode (straight) home. Took 10 minutes. Total time: 2 hours For perspective, this is what I did: Blue line is the direct route to the Y. The red line is the route I took. What can I say? I had no sherpa!

Things That Are The Devil

I can't remember the first time I was introduced to the term, I'm guessing it was via The Waterboy and Mama Boucher:

Bobby Boucher: [after Reading A Question About Benjamin Franklin] Ben Franklin
Young Bobby Boucher: [Flashback To Bobby's Childhood] Mama, When Did Ben Franklin Invent Electricity?
Mama Boucher: That's Nonsense, I Invented Electricity. Ben Franklin Is The Devil!

There's a thread on Google+ related to my last post, Managing Database Entries (tnsnames.ora) that rekindled the word for me.

Today I posted something to the effect on Twitter. I've decided to start adding things I would consider The Devil, in Mama Boucher's terms, here.

Cary Millsap is The Devil. Why? A year or 2 ago he posted a link on Twitter about why we don't need to double-space anymore after a period. Each and every time I go to double-tap the space bar, I think of that post and curse Cary appropriately.

Triggers are probably The Devil. Rarely have I seen them implemented in a halfway decent manner. Usually, they're used as some work-around because someone was too lazy to update their PL/SQL...or just couldn't figure out a way to accomplish their goal without them (say, like removing direct INSERT/UPDATE/DELETE privileges on the source table...?).

From that last post, tnsnames.ora can be The Devil.

I would say that commas in front of the line are The Devil.

My friend Jason Baer is The Devil. Go to 2:40 in:



That's all I have for now.

What do you consider The Devil?

Update
Me. I can think of at least 47 people who believe I am The Devil. Man...how could I forget something like that.

The Podcast

Last year at OOW I met one Mr. Christian Screen from The Art of Business Intelligence.

We've managed to keep in touch over that time and about a month ago he asked if I'd be interested in doing a podcast with him.

What? You want me to talk? Sure! I love to talk!

After some back and forth, we finally settled on a date and time...

Christian did a great job with the sound, I don't sound as scary as I normally do.

Here are some highlights:
- I managed to say "America's Wang" in reference to Florida within the first 5 minutes. I probably need a new joke, I seemed to have said that a lot last year at OOW.
- I talk about how John Piwowar (@jpiwowar) p0wned oraclenerd with 4 posts (out of 727).
- Data Scientists: This was a topic Christian brought up. I've read a little bit about the term, hopefully I'll pick up some more of it in the near future.
- I did not talk about Gwen Shapira's "Faxing" comment.
- OBIEE 10g vs 11g
- I'm sure I mentioned Jake (@jkuramot) in there too, possibly a kidney reference.
- I almost forgot, I got to mention Oracle's Person of the Year (OPY award?), again (not sure if that will get old to me).

Hopefully I didn't get myself into too much trouble.

You can listen to the podcast here.

Going Green

One thing I believe everyone can agree on, it's going green, saving the planet.

Oracle Corporation believes that to be the case too.

Save the planet, dump your Teradata



See the press release here.

Dream Job

I saw it posted somewhere recently but I can't remember where.

I originally thought it would be something technology related, but I recently procured myself one of these:



See it in action here:



As a pitcher, I spent a lot of time doing nothing. Take a 3 hour practice. I throw for about 10 or 15 minutes, maybe a little bullpen work, I'm up to 25 minutes. Maybe some PFP (pitchers fielding practice), 40 minutes now. I might be asked to be a runner in situational practice, 1 hour. That leaves 2 hours. That time is spent shagging baseballs during batting practice (BP). While it can be fun, it can also be incredibly boring, especially if you're stuck on home run duty (all alone across the street).

I fought that boredom by becoming really good at hitting ground balls to infielders during BP. Freshman year, coach had wooden fungo bats. He wouldn't let anyone use them because we might break them. What did I do? I used them. I didn't break them either.

The pinnacle of being good with a fungo bat is probably the ability to hit pop-ups for the catcher, you know, the ones straight up that curve back towards the field. I can do that.

I've been hitting fungo to the 7-8 year olds where I'm the assistant coach. Sometimes I'll hit them hard, mostly soft, but I just remember how much fun it was.

Wait, one more thing to go along with that. BP pitcher. I throw great batting practice (I'm not sure if that's was a good or bad thing career wise, obviously doesn't matter now). It's fulfilling to be able to throw strikes and see how far people can hit them.

There it is, my dream job. Fungo hitter and BP pitcher.

I wonder if the Rays are hiring and if they'll pay me enough to support my family?

The &DBA

Pronounced, Ampersand DBA.

I'm hearby naming all my future DBAs that meet certain criteria, &DBA.

Over the years, at about a million different organizations, I have met many DBAs. Many, at first glance, fit the DBA stereotype; cranky, condescending, arrogant, etc. After further review, many of those traits still apply. Some however turn out to be the opposite; they just put on a game face for the new guy or gal because they've been through this before...probably to be disappointed again that the developer will 1, leave them out of design discussions 2, throw bad SQL over the wall, 3, not use bind variables, ever and 4, well, all the other bad things we developers do.

The type that I have least encountered are the DBAs who actively seek out and engage developers. Hey, can I help you with that? Hey, you aren't using bind variables, here are the reasons you should. They know that in the long run, the better the developer, the easier their job will be.

I love that aspect of my job. I like helping others learn to do something better or more efficiently. I like to encourage others to download and install the database. But that's not the point of this post...

I needed a name for those DBAs who are 1, less qualified than I am to be a DBA (short list, I am under no illusions), 2, are wholly incompetent and 3, are jerks.

After a recent incident, as relayed by others, the &DBA was born.

What does the & mean?

In SQL*Plus, the & is a special character. If you run scripts you can parameterize them with &COLUMN_VALUE or something, right?

That's great for scripts.

It can go horribly wrong with code. At least the first time you try to load code via SQL*Plus. Here's the original

CREATE OR REPLACE
FUNCTION foo RETURN VARCHAR2
AS
  l_retval VARCHAR2(10);
  l_value VARCHAR2(30) := 'Barnes & Noble';
BEGIN
  SELECT 'IT WORKS!'
  INTO l_retval
  FROM dual
  WHERE 'Barnes & Noble' = l_value;
  
  RETURN l_retval;
  
END foo;
/
show errors

and here's what happens when I run it:

CJUSTICE@NO_CLUE>CREATE OR REPLACE
FUNCTION foo RETURN VARCHAR2
  2  AS
  3    l_retval VARCHAR2(10);
  4    l_value VARCHAR2(30) := 'Barnes & Noble';
  5  BEGIN
  6    SELECT 'IT WORKS!'
  7    INTO l_retval
  8    FROM dual
  9    WHERE 'Barnes & Noble' = l_value;
 10
 11    RETURN l_retval;
 12
 13  END foo;
 14  /
Enter value for noble: show errors
old   3:   l_value VARCHAR2(30) := 'Barnes & Noble';
new   3:   l_value VARCHAR2(30) := 'Barnes show errors';
Enter value for noble: testing
old   8:   WHERE 'Barnes & Noble' = l_value;
new   8:   WHERE 'Barnes testing' = l_value;

Warning: Function created with compilation errors.

This is how it is stored in the database:

FUNCTION foo RETURN VARCHAR2
AS
  l_retval VARCHAR2(10);
  l_value VARCHAR2(30) := 'Barnes show errors';
BEGIN
  SELECT 'IT WORKS!'
  INTO l_retval
  FROM dual
  WHERE 'Barnes testing' = l_value;

  RETURN l_retval;

END foo;

12 rows selected.

Yeah, that won't work.

To fix this, you simply set an attribute/directive? in your SQL*Plus session, SET DEFINE OFF (I believe there is another, but this is the one I use). Like this:

CJUSTICE@NO_CLUE>SET DEFINE OFF
CJUSTICE@NO_CLUE>
CJUSTICE@NO_CLUE>CREATE OR REPLACE
  2  FUNCTION foo RETURN VARCHAR2
  3  AS
  4    l_retval VARCHAR2(10);
  5    l_value VARCHAR2(30) := 'Barnes & Noble';
  6  BEGIN
  7    SELECT 'IT WORKS!'
  8    INTO l_retval
  9    FROM dual
 10    WHERE 'Barnes & Noble' = l_value;
 11
 12    RETURN l_retval;
 13
 14  END foo;
 15  /

Function created.

Elapsed: 00:00:00.22
CJUSTICE@NO_CLUE>show errors
No errors.
CJUSTICE@NO_CLUE>
CJUSTICE@NO_CLUE>SET DEFINE ON

All is now well in the world.

Now this might be vexing on your first couple of weeks on the job, but you quickly pick it up.

This is where the name comes from...

SET DEFINE OFF
&DBA
SET DEFINE ON

said that this was a known Oracle bug.

Really.

The Nerd Defense

Really couldn't pass this one up. Friend and co-worker, Erica Baer [@skibaer] who has a Ph.D. in some sort of crazy thing (and she's not even 30!) sent me a link to Eyeglasses and Mock Juror Decisions...I'm pretty sure her Ph.D. is in something related to psychology (which is good for me, free counseling) and the selection of jurors. Wait, it's Forensic Psychology, LinkedIn told me so. Sadly, much of my charm is wasted on her, she sees right through it. Oh well.

So the link...had a great picture:



There are like 4 elements that apply to me there:
1. Nerd
2. Justice
3. Bald
4. Rollie Fingers-esque mustache
5. Glasses

Ok, 5, I can count, but you knew that.

Random Thought: #19

Nickelodeon
I am lucky enough to get to watch The Fresh Beat Band every single day on Nickelodeon. It's one of katezilla's faves, after Dora the Explorer and Diego.

Anyway, one of the actors, Yvette Gonzalez-Nacer, is obviously very talented. She has an operatic (sp -10?) voice which overpowers her colleagues on the show. Doubtful it is intentional, she's just that much better.

Using the Google Machine, she's had a fairly impressive career. What I can't figure out, is how she ended up on this show. Nick does a good job at putting together shows with talented people (whether you agree with their...umm...whatever) who seem to be fairly normal (save Britney Spears sister, but that should be obvious now (and holy schnikeys, I know that...OMG...WTF???)).

Is it a good career move for her? Does she do it because she cares about the kids? Sadly, I ponder these things.

Percentages
How would you store a percentage? Would you store it as 40, 50 or 60? Or .4, .5 and .6? I opt for the latter. Why? Well, it's a decimal, it's a fraction of 1, right? If you store it as a whole number, what happens in 3 years after you are gone and the new person doesn't know? It's not obvious, is it?

OBIEE
I ran into an OBIEE project for a single business model that consisted of 2 separate design patterns, horizontal, and vertical fragmentation. That was fun. It was supposed to be easy, but it was not. I'd like to re-live that one soon. Maybe.

Fun with SQL - Part 578

by Enrique Aviles [twitter]
The first guest post in awhile. I met Mr. Aviles via twitter (yeah for Twitter!) some time back. We got to meet in real life at our local SOUG meeting with Tanel Põder came to town. He's also graced the front page for awhile wearing his oraclenerd t-shirt (which is now his avatar on Twitter).

Part of my job consists of tuning SQL queries. Opportunities abound and present themselves in many different scenarios. A few weeks ago I was assigned a trouble ticket. I was the lucky winner because, after some investigation by the developers, the culprit was identified as an SQL issue that was causing a stack overflow in Hibernate. For those unfamiliar with it, Hibernate is an Object Relational Mapping library for Java (more info here). The stack overflow occurred during string manipulations probably during the code generation phase. I didn’t have to do much investigation of my own because the developers indicated the offending query was composed of 60 IN clauses, each clause containing 1000 elements. The query resembled something like this:

SELECT COL1, COL2, COL3
FROM
WHERE (UNIQUE_ID IN (1, 2, 3,..., 1000)
  OR (UNIQUE_ID IN (1001, 1002, 1003,..., 2000)
  OR (UNIQUE_ID IN (2001, 2002, 2003,..., 3000)
  ...
  OR (UNIQUE_ID IN ( 59001, 59002, 59003, ..., 60000)

I was happy to see the query generation failed before it reached the database. It is usually better to have the application fail with a glaring error during unit tests rather than allowing a potential failure slip into production. Fortunately we test against a database that’s a slightly older copy of the production database so we execute all our queries using real data volumes, not just against artificial tiny development databases. Had we only tested on a development database, this issue would have made it to production with two possible outcomes:
- The application fails with the Hibernate stack overflow error that triggered the trouble ticket.
- Given a smaller set of unique ids, Hibernate successfully generates the query and sends it to the database.

Since the query never reached the database, it is hard to tell how well it would have performed.

For a handful of IN clauses, it is safe to assume the query would have run in a reasonable amount of time but 60 IN clauses could have possibly taken an unacceptable amount of time. Even if the database is able to process 60 IN clauses with no impact to the user, generating such statements as

UNIQUE_ID IN (1001, 1002,..., 2000)
UNIQUE_ID IN (2001, 2002,..., 3000)
UNIQUE_ID IN (59001, 59002, 59003,..., 60000));

in a query is not a good idea for various reasons. In the absence of bind variables, a lot of CPU will be consumed hard parsing SQL statements since potentially every generated query becomes a brand new query sent to the database even if the number of IN clauses remain static. Using bind variables lessens the load but doesn’t eliminate hard parsing as much as it should because queries with a different number of IN clauses become unique, thus unshareable. Both cases fill the shared pool with unshareable SQL, although the absence of bind variables would fill it faster. This is a waste of CPU cycles and memory. We can significantly reduce hard parsing and eliminate the risk of generating a huge SQL statement by implementing a temporary table.

Instead of selecting and sending to the application server all values that eventually ended in a series of IN clauses, we could simply insert those values in a temporary table and modify the original query to join the temporary table. The following is a sample temporary table:

CREATE GLOBAL TEMPORARY TABLE IDS_TMP 
(
  ID NUMBER
) 
  ON COMMIT PRESERVE ROWS;

The table was loaded with the same INSERT that returned all IDs that were used to build the IN clauses. Once the temporary table contained all desired IDs the application generated the following query:

SELECT COL1, COL2, COL3
FROM TABLE AS TAB
WHERE EXISTS
(SELECT TMP.ID
FROM IDS_TMP AS TMP
WHERE TMP.ID = TAB.ID);

Of course, we could have also generated a query with an INNER JOIN or with a single IN clause but EXISTS worked fine for our case. This is obviously a much shorter SQL text. It remains static regardless of how many IDs are selected since they are hidden in the temporary table. Additionally, there is no need to send all IDs to the application server, spend time building a long character sting, and send them back as part of a SQL query. This translates in one hard parse and many executions so the risk of wasting shared pool and CPU usage is eliminated.

The “magic” of temporary tables makes this a safe solution since data inserted is only available per session. User A cannot select data inserted by user B and vice versa. Temporary data disappears automatically after the session disconnects so there is no need to explicitly DELETE or TRUNCATE the table. At the end of the day, this proved to be a suitable solution.

The fact that IN clauses only support 1000 elements was seen as a limitation. If the database imposes limits chances are defeating them with apparently clever solutions will result in slow performing queries and will increase the risk of wasting system resources. When faced with similar limitations we should ask ourselves if there are other means of achieving the same result that don’t require circumventing what was considered a database shortcoming.

Follow Friday

I know this is a Twitter tradition and I know it is just a tad early, but here is my version of Follow Friday.

First up, Enrique Aviles. I met Mr. Aviles at Tanel Põder's visit back in January in Tampa. We had corresponded a bit prior via Twitter. For the longest time, I read his Twitter handle as "Evil Les 94," I hope that's not me projecting. Super nice guy and apparently has some fun horror stories he promises to share.



Next up, Don Seiler. Don and I have been verbal sparring partners on Twitter for a couple of years now. We appear to have a similar sense of humor, smart-ass. That might be a company-wide trait at Pythian, I seem to encounter a lot of smart-asses from that particular organization. :)



Finally, Lisa Dobson. My first introduction to Mrs. Dobson was through Tom Kyte's blog a few years ago. Since I can't seem to find the exact post that I think I remember, I link to this one where Mr. Kyte talks about introducing her at her presentation for Newbie DBAs.

Now, I'm not one to spread rumors, so don't mention this to anyone, but rumor has it, she "bitch slapped" Tom Kyte at some past event. This is wholly unsubstantiated, so don't hold me to it.



You may see a trend in my Follow Friday suggestions, you may not. If you do, go here, all proceeds go to katezilla (well, technically, to us, her parents, to offset her costs. Though she is a girl and girls are supposed to cost more...). :p