Wednesday, March 28, 2007

Would you like to tell...

Would you like to tell Oracle what you think about partitioning?  You can choose to participate in a partitioning survey if you want to...

Tuesday, March 27, 2007

This is really bad...

Anyone that reads this blog knows that I'm an avid reader/fan of Kathy Sierra.  She has, as far as I can tell, done nothing but good.  Her work is always positive, never negative.  It is creative, well thought out, interesting, useful, helpful.  It is something to aspire to.

But some people cannot have that.  They have to tear down, ruin things, destroy for whatever reason.  And it happened to her in a really bad way.  I was stunned when I first read it - disgusted better describes it now.  Here is someone trying to be helpful to the technical community at large - with really good ideas and the passion to spread it and something like that happens. 

I've written about anonymity in the past.  I have not entirely changed my mind on that topic - but I have to admit that something like this makes me think about it.  I believe there are classes of people that need to be anonymous - people being persecuted by oppressive governments for example.  Perhaps people with something to reveal but fear for their job/family.  But anonymity in this case - just makes it harder to prosecute the people that need to be.

As Seth Godin wrote this morning - "Isn't it sad that misogyny is so common that there's even a word for it?" 

It makes me sad and very mad.  I hope she comes back...

Friday, March 23, 2007

Stringing them up...

Suppose someone asks you:

I need to take the results of a query and pivot a value. That is, I would like output from the EMP table to look like this:

DEPTNO  ENAMES
------- ------------
10      clark king miller
20      adams ford …

can this be done in just SQL?

With the addition of analytic functions in Oracle 8i Release 2 and the SYS_CONNECT_BY_PATH() function in Oracle 9i Release 1 – this became something we can in fact do rather easily in SQL. The approach we will take is:

  1. Partition the data by DEPTNO and within each DEPTNO sort the data by ENAME and assign a sequential number using the ROW_NUMBER() analytic function
  2. So, we end up with a record eventually that is the result of connecting 1 to 2 to 3 to 4 and so on for each DEPTNO
  3.  The SYS_CONNECT_BY_PATH() function will return the list of ENAMES concatenated together for us.

The query would look like this:

SQL> select deptno,
2 max(sys_connect_by_path(ename, ' ' )) scbp
3 from (select deptno, ename,
row_number() over
(partition by deptno order by ename) rn
4 from emp
5 )
6 start with rn = 1
7 connect by prior rn = rn-1 and prior deptno = deptno
8 group by deptno
9 order by deptno
10 /

DEPTNO SCBP
---------- ----------------------------------------
10 CLARK KING MILLER
20 ADAMS FORD JONES SCOTT SMITH
30 ALLEN BLAKE JAMES MARTIN TURNER WARD

 


I used to use STRAGG for this (prior to sys_connect_by_path and analytics) - but now find this approach preferable.

Tuesday, March 20, 2007

Recovery is...

Recovery is the only thing you really really need to be able to do.  As a DBA.  You can mess lots of other stuff up - but get to where you need recovery and then discover that - you cannot - that is a really bad feeling.

Funny how small the world is.  I was just reading this article about a disaster in Alaska with some data.  I was going to write about that - but got interrupted.  When I came back - there was a new article in my blog list so I glanced at it.  It was from Howard Rogers - and it was about... recovery.  And it referenced basically the same article (hosted on a  different server).  And it was from as far away from me as you can get and still be on this planet...

Read about Howard's nightmare here.

We've all had those nightmares happen to us at one point or another.

It is all about recovery.

Too bad Howard wasn't running 10g - flashback table T to before drop;

Too bad I didn't have a longer undo_retention (I do now...).

Too bad the Alaska Department of Revenue didn't test their backups.

Just hoping some people are "scared" now and - well - test their recovery capabilities.  Because it is all about recovery.  We can fix anything else anyone does wrong - but if you cannot recover - that is money, money down the drain...

The reddit effect...

Yesterday was a 'busy' day here:

graph_summary_areachart.php

The traffic was more than double the prior 'record' of 6,700 hit way back in September 2005 (Oracle Open World 2005). 

All because I got picked up on reddit.com.  It is still having an effect today as I already have as many views at 7am as I do on a normal day.

I've written about reddit before - it is a resource I use pretty much every day I get on the computer (which frankly is most days).  It was interesting to watch my own article go up and down the reddit ranks - and read the comments from an entirely different group of people.

Saturday, March 17, 2007

Geeks need apply...

I thought this article got most of it right...

But, I also thought they missed the most important item.

  • Give them useful, meaningful, important things to do

I hated writing shelf-ware in a former life.  Nothing was worse then working on a project you knew no one would probably use.  I would rather work on a small, no fame/no glory project - that would get used; way before I worked on something big, huge, but useless.

Give me something meaningful - that is perhaps the most important thing.

More than their 8, but their 8 are important too mostly.....

A challenge!

I just got a question on asktom recently. To paraphrase, the question is basically:

We copy some data from another table. After the copy, some of the data is missing. When we check the source table, the data is there. What can be the reason for this????

Well, here is the code - after you read the code, I want you to GUESS as fast as possible what the problem PROBABLY IS. If you are a long time reader, I hope you get it "fast" :) My reply is here, but take the challenge first. Please, ignore the really bad "transaction processing" - yes it is *part* of the problem, but try to guess really fast why this code is most likely BROKEN without any visible errors.

Comment away..........

CREATE OR REPLACE PROCEDURE "SIEBEL_CUST"
IS
CURSOR siebel_cust_cur IS
SELECT *
FROM customerdata_vw
WHERE account_id NOT IN (SELECT cust_account_id
FROM nidapps.CUSTOMERS);

TYPE icust_tabarr IS TABLE OF customerdata_vw%ROWTYPE INDEX BY BINARY_INTEGER;

icustarr icust_tabarr;
insert_count NUMBER := 1;
i NUMBER := 1;
file_id UTL_FILE.FILE_TYPE;
BEGIN
OPEN siebel_cust_cur;
file_id := utl_file.FOPEN( 'h:\nidbatchprog', 'ston-c.log', 'w' );
LOOP
EXIT WHEN siebel_cust_cur%NOTFOUND ;
FETCH siebel_cust_cur INTO icustarr(i);
utl_file.PUT_LINE( file_id,i);
INSERT INTO CUSTOMERS (customer_id,cust_account_id,customer_name)
VALUES (customers_seq.NEXTVAL,icustarr(i).Account_ID,icustarr(i).Account_name);
i := i + 1;
COMMIT;
utl_file.PUT_LINE( file_id,icustarr(i).Account_name);
insert_count :=insert_count + 1;
IF insert_count = 1000 THEN
COMMIT;
insert_count := 1;
ELSE
insert_count := insert_count + 1;
END IF;
END LOOP;
dbms_output.put_line(i);
CLOSE siebel_cust_cur;
utl_file.PUT_LINE( file_id,'Customer migration completed');
dbms_output.put_line('for cursor');
utl_file.fclose(file_id);
EXCEPTION
WHEN OTHERS THEN NULL;
END;

Crossing the border...

I was on travel this week - spent most of it in Canada.  Crossing the border was an interesting experience.

When I fill out the forms used for immigration, I always tell the truth.  Yes, I'm here on business (check that box).  No, I haven't been on a farm.  No, I don't have any business material with me.  That sort of stuff.

Well, this time, honesty sort of backfired.  Why:

  • Yes, I'm here on business
  • No, I don't have work materials
  • Yes, I have a laptop

It was that last bit.  The customs agent wanted to know "is that your employers laptop" - nope, it is mine.  "Do you do work on it, business work?".  Well, I read email, browse the web, have all of my presentations on it, use it to present, run Oracle on it, demonstrate with it.  "So, it is your companies laptop then?".  Nope, it is mine.

They scribbled someone on the immigration form, handed it to me and said "have a nice trip".  I head out of baggage claim - but instead of being told to go right (to freedom), I'm directed to the left - to additional scrutiny.  No worries - nothing to be found, no problem.

So, I wait a bit and finally someone comes along and says "ok, over here".  They read the form and we go through the whole "business or personal, who owns that computer" stuff again.  Same answers.  He asks to see my computer (a small Sony Vaio).  I oblige.  He spent a couple of minutes looking at it, rotating it, studying it, turning it around, flipping it over, looking it all up and down.  I finally asked "are you looking for something?". He said "the latch, to open it".  There is no latch, just lift the lid.  "Ahhhh, got it".

So, we have the laptop open now - he wants to turn it on.  Before doing that - he asks "is it password protected?".  I said "some of it is".  This perplexed him - I don't think he's ever heard that before.  So we turn it on.  We are presented with a logon screen - but with three choices:

  • Administrator
  • Family
  • Tkyte

He asks me how to proceed.  I was a bit dumbfounded at that point - what do you mean?  "Well, what should I click on to log in".  I tried to explain what the three accounts were- Administrator used to, well, administer the machine.  Family - used by, well, my kids to play games.  Tkyte - used by, well "me".  He asked me to pick one.  So I said let's use tkyte, I know that one best.

He clicks on it and it asks for a password.  He looks surprised and says "it needs a password".  I was like - that is OK, I have it, here you go... Now he is logged in.  But - my desktop looks a tad different from most - there is no IE on the desktop, just the recycle bin and a folder called programs - nothing else.

He really doesn't know what to do now.  No special searching software, nothing.  He looks at me and says "you know what we are doing here right?".  I said - not really (I knew what we were doing, I read the news and all, but just said "no").  "Well" he says "we are looking for pornography".  Ahh I say... Ok, no problem. 

But he is stuck.  There is nothing familiar.  So he clicks on the start menu and finds "My Pictures".  You know, if I was into that - that is precisely where I would stick all of my porn - right there in "My Pictures".  He goes into it - and sees all of my folders.  And al of my pictures, which we looked at.  He said "wow, you travel a lot", I said "yup". 

Now, after about 15 minutes of looking at my pictures (I have to resist the urge to point him to my favorites :) he shuts down my computer and says "Ok sir, thank you very much, have a nice trip".

What an utter and complete waste of time.  His, mine, all of it.

I have three accounts, tens of thousands of files.  Each account sees a different set of files.  I seriously doubt I would keep in "My Pictures" a folder full of "not good stuff".  Heck, I might encrypt the data, I might hide the file name, I might put it somewhere not obvious, I might use an account not visible on the logon window by default.  I might do a lot of stuff. 

The person doing the search - they were afraid of the computer.  They did not use one.  They did not know what they were looking for.  They did not know how to look for it.  I felt like giving him POINTERS as we were going through this.  I had to bite my tongue and refrain from giving him tips.  The reason - the last person on the planet you want to annoy - the customs people at a border crossing.  They can really ruin your day if they want to.

So, I just smiled and acted all patient.  Moved on when it was time to move one.

But, what an utter and total waste of time, money, everything.

And - it makes them look "not smart".

Just a good thing they didn't look in the recycle bin ;)

Thursday, March 08, 2007

The dreaded "when others then null" strikes again...

People think sometimes that I must be able to read the mind of "software" because they'll say "I'm observing X" and I'll respond with "you probably did Y" - and many times that guess is right.

I cannot read the mind of software - I can however recognize patterns, especially when I see the same bad pattern over and over and over and over again.

Another "when others then null" guess.  I hate "when others".  Wish it did not exist for all of the problems it causes.

Things I don't like

  • generic implementations that are not necessary
  • triggers
  • when others (not followed by raise!!)
  • triggers
  • not using bind variables
  • triggers

Wednesday, March 07, 2007

FizzBuzz busted...

Just recently I wrote about the "FizzBuzz" test.

Well, it popped up again.  Someone wrote "Why Can't Programmers... read".   The author notes that of course, programmers hopped right on and provided their own solutions to the FizzBuzz test.

But, they did not read the specification!  So, they got it wrong.  Entirely wrong - but make comments like "I think the above solution is not elegant.".  Whoops.

Here is my second take on it, in my 3rd favorite language - C:

[tkyte@desktop ~]$ cat test.c
#include "stdio.h"
int main()
{
int i;
char buff[5];

for( i = 1; i<=100; i++ )
printf( "%s%s%s\n",
(i%3) ? "":"Fizz",
(i%5) ? "":"Buzz",
((i%3)&&(i%5)) ?
(snprintf(buff,sizeof(buff),"%d", i), buff):"" );
}
[tkyte@desktop ~]$ make test
cc test.c -o test
[tkyte@desktop ~]$ ./test
1
2
Fizz
4
Buzz
Fizz
7
8
Fizz
Buzz
11
Fizz
13
14
FizzBuzz
...
Buzz
Fizz
97
98
Fizz
Buzz
[tkyte@desktop ~]$

Man, it has been a while since I programmed C, I was a bit rusty - it did not compile the first time (second time is a charm).


I have to admit - more than once I've fallen into the "sorry, I read that too fast, you are right - that won't work" trap myself on asktom.  That is one reason peer review is really important - no one lets a wrong answer get through :)

Tuesday, March 06, 2007

Old Pictures...

Stumbled upon a really neat site - if you like old pictures.  One of my favorite "coffee table" books is Washington Then & Now - mainly because

  • it is full of pictures, not too many words :)
  • it is full of old pictures

So, to find this site if just - well - old pictures was quite a pleasure.

Three that caught my attention - just to whet your interest were:

  1. The Bonus division where the many clerks figure the amount of the bonus each veteran is entitled to.  Notice the sign hanging there "Computing Division, Computing Section".  We have come a small way in the last 100 years.  You will be looking at the original computers - human beings.
  2. View of the US Capitol during the first.  I found the composition to be very striking.  The haze of the fire, the long exposure (note the ghost image in the lower right) - made a really interesting shot.
  3. Exterior of People's Drug Store.  I like the old cars, buildings and people - some of the interior shots are really cool as well - you get to see what was on the shelves and all.

Anyway, if you like that stuff - be prepared to have some amount of time.  There are a lot of them there.

Monday, March 05, 2007

Order by redux...

Always good to beat something totally to death.

Thanks Jonathan.

Sunday, March 04, 2007

Things to say when you are losing...

70 things to say when you are losing a technical argument.  I believe I've heard most of them at one point or another - well, except #8 - I've never heard of that guy before.

My favorites:

  • #1: That won't scale. I hear that one all of the time.  It is easy to win back the argument though - just say "Why?". 
  • #6: That can't be generalized to a cross-platform build.  But the way I hear it is "that would not be database independent..."
  • #11: Yes, well, that's just not the way things work in the real world.  Ugh, I hate that one.  What they really mean is "hey, our standard operating procedures - drafted in the year 1985 - don't permit intelligent decisions".
  • #12: I like your idea.  Why don't you write up a white paper and we'll review it at the next staff meeting?  You only fall for that trap once or twice :)
  • #20: Have you LOOKED at the number of I/O requests that will create?  Always respond with - so I presume you have, save us the time - how many are we talking about and why?
  • #25: No, no, no.  We're really working on an N-TIER architecture here.  No comment
  • #48: Let's table this for now, and we'll talk about it one-on-one off-line.  Now you know what they really mean....

Friday, March 02, 2007

This could be seriously cool...

In the year 2000, I bought 2- 21" (1600x1200) monitors with my advance from the book Expert One on One Oracle.  I have one at home and one at work that I use still.  They cost me - as much as this monster does today.

That is so my next monitor.  Can  you imagine?  They have a little box that plugs into the PCMCIA slot on your laptop (I only use a laptop) and with that and the video out of the laptop itself, you can actually drive a beast like that.

I might never leave my office again :)