Thursday, May 12, 2011

Bullet proofing entire record inserts/updates

There's a handy feature in PL/SQL that lets you insert an entire record into a table without specifying each individual field in the record. Here's an example.


create table wide_test
  (id number, 
   col_a varchar2(10), col_b varchar2(10), col_c varchar2(10));

declare
  cursor c_wide is 
   select *
   from wide_test;
  v_rec c_wide%rowtype;
begin
  v_rec.id := 1;
  v_rec.col_a := 'A';
  v_rec.col_b := 'B';
  v_rec.col_c := 'C';
  --
  insert into wide_test
  values v_rec;
end;
/


select * from wide_test;

But if you give that to a code reviewer, they may tell you that using SELECT * is bad practice. What happens when a new column is added to the table ? So he makes you recode it.

declare
  cursor c_wide is 
   select id, col_a, col_b, col_c
   from wide_test;
  v_rec c_wide%rowtype;
begin
  v_rec.id := 2;
  v_rec.col_a := 'A';
  v_rec.col_b := 'B';
  v_rec.col_c := 'C';
  --
  insert into wide_test
  values v_rec;
end;
/

select * from wide_test;

Everyone is happy....until a new column is added to the table.

ALTER TABLE wide_test ADD col_d VARCHAR2(10) DEFAULT 'D' NOT NULL;

declare
  cursor c_wide is 
   select id, col_a, col_b, col_c
   from wide_test;
  v_rec c_wide%rowtype;
begin
  v_rec.id := 3;
  v_rec.col_a := 'A';
  v_rec.col_b := 'B';
  v_rec.col_c := 'C';
  --
  insert into wide_test
  values v_rec;
end;
/

insert into wide_test
            *
ERROR at line 12:
ORA-06550: line 12, column 15:
PL/SQL: ORA-00947: not enough values

That didn't quire go as planned. You get the same error if you try
  insert into wide_test (id, col_a, col_b, col_c)
  values v_rec;

Perhaps the SELECT * was the right way to go ?

declare
  cursor c_wide is 
   select *
   from wide_test;
  v_rec c_wide%rowtype;
begin
  v_rec.id := 3;
  v_rec.col_a := 'A';
  v_rec.col_b := 'B';
  v_rec.col_c := 'C';
  --
  insert into wide_test
  values v_rec;
end;
/

declare
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("PERFORM"."WIDE_TEST"."COL_D")


Nope ! The "col_b" in v_rec isn't set so remains NULL, but the database column is NOT NULL. Because a value is specified in the INSERT the default doesn't kick in.

Happily there is a solution:

declare
  cursor c_wide is 
   select id, col_a, col_b, col_c
   from wide_test;
  v_rec c_wide%rowtype;
begin
  v_rec.id := 3;
  v_rec.col_a := 'A';
  v_rec.col_b := 'B';
  v_rec.col_c := 'C';
  --
  insert into
    (select id, col_a, col_b, col_c
    from wide_test)
  values v_rec;
end;
/

select * from wide_test;

We use an inline view as the target of the insert. Since the order of the columns is specified in the inline view, this will continue to work even if someone re-arranges the columns in the underlying table. A regular, permanent, view in the database works just as well.

This is called the Insert Statement Extension and there's a similar facility for UPDATE

DECLARE
  cursor c_wide is 
   select col_a, col_b, col_c
   from wide_test;
  v_rec c_wide%rowtype;
BEGIN
  v_rec.col_a := 'A1';
  v_rec.col_b := 'B1';
  v_rec.col_c := 'C1';
  UPDATE 
   (SELECT col_a, col_b, col_c 
    FROM wide_test)
  SET ROW = v_rec;
END;
/


Tuesday, May 10, 2011

Encouraging women in information technology

I participated in a twitter dialogue with @datachick (Karen Lopez) last week on the #WIT (women in technology or women in information technology ?) hash tag.

I don't think there'd be much argument that women are under-represented in IT, and that a more balanced split would benefit the whole industry.

My question was what can us blokes do to encourage more women into the industry (or to stay in the industry). There were a couple of helpful suggestions. Karen's first point was to encourage girls into STEM courses (Science, Technology, Engineering and Maths). No problem there. We could also do with a few more teachers specializing in those areas, but that's a separate topic.

Another point was that women may not push their opinion into a discussion, and it could be helpful to actually ask for their input. Or ask for their help if you are stuck. They know stuff. I follow several women tweeters/bloggers, including DBAKevlar, datachick, Erin Stellato, Lisa Dobson, Gwen Shapira, Mary McNeely, Boneist.... Not because they are women, but because they contribute interesting content.

Karen then recommended challenging any ant-diversity practices and @zippy1981 raised the matter of introversion. There are a lot of introverts in IT. Sitting in a corner, coding away. I suspect there will be fewer in years to come, at least in development. The increasing pace of change, and a closer dependence of the business on IT, means less scope for solitary activity. Again, another topic.

But in the meantime, introverts are not going to be the ones giving talks at schools on career day. Or challenging the alpha-males in the executive positions who might skimp on diversity-friendly practices.

True Story: One place I worked had the ladies toilet larger on each floor than the gents (because cubicles take up more room than urinals), except on one floor. The large skew in the IT section meant that the gents got extra space. This sometimes confused women visiting the floor....but never more than once. There's a lot of gray area between recognising an existing imbalance, reinforcing it and falling back to the "it's an industry norm" excuse.

I don't have any solutions. It is just something to think about.
 
Oh, and the recent XKCD is sort of appropriate.





This is a reconstructed / edited extract of the twitter conversation:

syd_oracle: How can we (men) encourage without being patronising ? #WIT
datachick: Best? Talk to young people about your career and the opportunities it provides. Encourange them to tak STEM courses.

MelikaNoKaOi: I see a lot of women in IT who are afraid to offer their ideas, so ask them for their ideas directly :) #WIT
retracement: Simple. Actually talking to women in IT (at minimum) as our equals, which quite frankly shouldn't be that hard to do. #WIT
retracement:Men are generally useless even talking to other men, never mind women so that's probably the real issue. #WIT

datachick: People can help by challenging immediately anyone who contributes to anti-diversity practices.
zippy1981: So your asking a bunch of introverts to challenge the alphas, who became successful by choosing not to be the alphas
datachick: Are you saying that Women are introverts? Not my experience...
datachick: I want to solve the problem of girls tinking that IT is a solitary job. None of my work has been.
datachick: Or that the only IT job is programming. I still fight that one, though, with adults.
zippy1981:Well for some it is an ideal. Don't get me wrong I appreciate good PMs, despise the mediocore ones, and respect great admins 1/2
zippy1981:so head dwn pgrming is not the only path, but if its your ideal path your not gonna push your daughter to be a sales engineer 2/2
datachick: Generally young girls are attracted to more collaborative jobs, which is why I want them to know IT has those.
zippy1981:I'm all for them taking those jobs so I can sit in the corner. Give me a fast Computer and a bug report to steer her by.
zippy1981:I'm saying that MIT that would appreciate more #WIT would encourage them to follow the same path that got them there.
zippy1981:I guess what I'm saying is I'm going to encourage a person who wants to be in my field to follow what worked for me.
datachick:That makes sense. I would do the same (and I spent only a trivial amount of time as a dev.)

Crossover

When should I retweet someting ? 

I follow a lot of Oracle peeps, including the big names such as Cary Milsap. I've got a few followers too, and the vast majority of them are also Oracle peeps. If Cary tweets something then there's not much point in me re-tweeting it because very few of my followers wouldn't have seen Cary's original.

I also follow a few antipodean tweeters, and a couple in the security area and some SQL Server ones. The same pattern applies to blogs I follow. I've got Google Reader set up so that if I share something there, it comes out as a tweet.

I'm more likely to share/tweet/retweet something from the 'non Oracle' field as I think it will reach a different audience. Crossover is important. Mary McNeely went to talk Oracle to some Unix people.

I can't see myself as a world-class Oracle guru. But I am one of a small number of Australian Oracle bloggers. It is the crossover aspects which allows us to contribute into the community

Saturday, May 07, 2011

From CRUD to AAAR - Pirates of Tahiti

When I wore a younger man's clothes, everything was cruddy. CREATE, READ, UPDATE and DELETE. Which equated to 'INSERT, SELECT, UPDATE and DELETE' in SQL terms. We didn't have MERGE in them days.

DELETE was the first to go. It was too hard to get back something you'd deleted, and something ALWAYS got deleted by mistake. We first moved to a 'soft delete', which meant setting a flag on the record indicating it should be (generally) ignored. After we ended up with tables that were 90% 'deleted' records, we went a step further. That's my first 'A' ; Archiving - moving the record into a separate table where it was easier to ignore.

UPDATE also gives problems. When your system was used by three men and a dog, you didn't run into too many concurrency conflicts. They generally worked on different records and only very occasionally had to wait on another user's locks. As you start to scale up, you run into more conflicts. Everyone would be trying to update the daily balance at the same time. You've all seen the effect at traffic lights. You reach the load point where more people are being added to the traffic queue than are being let through on each green light. It only sorts itself out when the load drops, and try telling sales that you need fewer customers.

So rather than UPDATE, you do INSERT or CREATE or, as I'm calling it to fit the pirate theme, APPEND. My second 'A'. Say you have 100 iPads in stock, and have them on a single stock record. Every sale you make, you need to update that record to reduce the available quantity. Only one person can update it at a time and everyone else has to wait. So rather than update the record, you append a transaction record saying how many you sold. I can insert a record and you can insert a record and neither of us has to wait for the other to finish. [Oracle can actually be smart so that when two people are inserting records at the same time, they actually go into different database blocks.]

Some of the NoSQL databases go further. Say I've got a profile record giving my account id, name and email address and I want to update the email address. Rather than do an update of the record, I actually append a new record with a later timestamp or version number and the new email address. This gives me a better audit history, and an easier replication mechanism between distributed copies of the data (because the timestamp settles arguments about the order in which things happened).

The third 'A' is for Aggregation. I take my set of sales transaction and total them up to see how many I sold. I have another set of data indicating how many I've bought. Eventually they'll balance out, even if I have to append some '-1' records to my sales and give refunds to a disappointed fanboi or two. Or with my profile, I take the details from the record with the highest version. Eventually the older ones may be archived.

At the end we will always have 'R' for reading. There's not much point in collecting data that no-one ever reads.

Anyway, that's why there are so many pirates involved in databases these days. I don't have any explanation for any cowboys in the industry.

PS. tahiti.oracle.com is Oracle's Document Search engine. I don't think it was very big on pirates, but if I had mentioned the Caribbean I probably would have ended up with Disney's lawyers.

Thursday, May 05, 2011

Why we learn maths

I've got a parent table of movies and child table of ratings where people give the film a score on a scale of 1 to 10. In my display, I want to display an average rating for the film.

I don't want to calculate the average of scores of rows for each display. With a large ratio of 'child' to 'parent' that could be very expensive. I want to denormalise but only just enough.

Say "Thor" has a current average score of 7 and a new review adds a rating of 2, what does that do to my average ? You can't tell from those figures, because an average is a ratio. Whenever you derive one value from multiple values, you are going to lose some functionality.

What I should do is take a step back and store on my movie table a TOTAL of people's rating, plus a COUNT of the number of ratings. Now, rather than an average of 7, I've got 10 reviews with a total rating of 70. Add in my new row and I've got 11 reviews with a total of 72 (giving an average of a bit more than 6.5). I don't need to re-read all those individual rows to recalculate the average.

This is why we learn maths !

The next issue will be contention. Fifty people coming out of a screening, all adding their ratings at the same time. Scary ? INSERTs are good at concurrency. There might be a few brief latches/mutex's over sequences and index blocks, but no actual locking problems. Updates are a different matter, and if we get fifty people tring to update the total/count values at the same time it will get ugly.

This is where queues come in. Have those 50 inserted rows join a queue and a single process dequeue them to calculate and  update the movie's total and count.

This is why we learn computer science. Of course similar things happen in the real world too, and queuing theory is....a part of maths.

The queue / de-queue model may show inconsistencies while waiting for ratings to be processed off the queue. Is 'Eventually Consistent' good enough ? If you need transactional consistency then leave the ratings in a 'pending' state until after they've been de-queued. If you need immediacy AND transactional consistency, then you'll end up with a potential bottleneck. Recognising immovable objects is a vital lesson to learn too.

Inspired by a stackoverflow question.

Tuesday, May 03, 2011

Worried about gamification and gender bias

One fun part about blogging is that you don't need to be an expert in everything, you don't need to know all the answers, and sometimes you can throw out an opinion or questions and see what reaction you get. This is one of those posts.

I play Steven Feuerstein's PL/SQL Challenge pretty much every day. I see it as a fun way to keep my knowledge base exercised. I also follow theappslab which has covered gamification for a while, and I recognize the Challenge as applying gamification to learning, with its leaderboard and achievements (for both ranking and participation), plus prizes at the end of the Quarterly Quest.

Then I turn around and see that "Female IT Staff Are Still Getting Paid Less Then Males", also reported here.I think salary is too simplistic a measure. I had lunch with one Java programmer a couple of weeks back andwe did discuss general pay and conditions between her (public sector) job and my one as a consultant. I may get paid more, but I have a longer working week and less control about where I'm working (plus I've been in the business a bit longer - though there's a point where years don't usefully equate to experience).

I follow @datachick who often tweets about Women in technology (and NASA and barbie and a whole bunch of stuff). And I look around a lot of the places I work and see more blokes than women. I do think we, as an industry, need to do something to encourage more equal participation.

I wonder whether gamification offers any opportunities for that encouragement, or whether it may actually make things worse. I've dug around a bit but haven't really found much useful data on gender bias here. Nick Yee has looked at EverQuest and found that female players were more interested in the social aspects rather than goal-focussed. The vast majority of players are men which may indicate that the population was weighted towards a particular type of personality more common amongst men, but perhaps the same applies to the technology field.

I don't want to pick on the PL/SQL Challenge. I enjoy it. I also like the elements of gaming in StackOverflow with its achievements and "powers" as points levels are gained. But I'd like some insight into how these might shape a male-dominated profession. You can pretty much guarantee that when an employer or a recruiter looks through CVs, they will be checking that person on Google and LinkedIn. Advertiser funded sites will use gamification to favour frequent participants.