Saturday, May 02, 2009

Advice for dealing with ridiculous interview questions

I received an email last week with this question: "What is the maximum number of triggers that can be defined on a single table?"

My answer was: "Heck if I know. Check the Oracle documentation."

To which he replied: "Actually Oracle documentation does not mention anything about this. But this a favorite question asked during interviews for PL/SQL jobs in India."

A favorite interview question? How absurd! When I interview a person for a job as a PL/SQL developer (which I admit I have not done all that often). I am much more interested in:

Problem solving skills (MOST IMPORTANT): Can you think logically? If you are not well versed in symbolic logic (whether or not you know it as such), then it will be very difficult to solve your programming puzzles, debug your code, and understand other people's code.

Language familiarity (SECOND MOST IMPORTANT): Do you know about and have experience with critical, non-beginner features of the PL/SQL language, such as collections, FORALL, BULK COLLECT, AUTHID, autonomous transactions, etc.?

Awareness of limitations like the maximum number of triggers doesn't play any role in writing high quality code. In fact, one might argue that a brain filled with such arbitrary and irrelevant data is less likely to have a solid grasp of fundamentals and principles.

But if you really want to know the answer, the best way to check/prove the limit on the number of triggers you can define on a table is to test it with code (documentation can always be wrong, but the code is...the code...the "reality" within this particular chunk of cyberspace). Here's a script you can use to do just that:

SET SERVEROUTPUT ON

CREATE TABLE limits_table (n NUMBER)
/

DECLARE
n PLS_INTEGER := 1;
BEGIN
LOOP
EXECUTE IMMEDIATE 'create or replace trigger limits_table'
|| n ||
' before insert on limits_table for each row begin null; end;';
DBMS_OUTPUT.put_line ('Created ' || n || ' triggers!');
n := n + 1;
END LOOP;
END;
/

SELECT COUNT ( * )
FROM user_triggers
WHERE trigger_name LIKE 'LIMITS_TABLE%'
/

BEGIN
FOR rec IN (SELECT *
FROM user_triggers
WHERE trigger_name LIKE 'LIMITS_TABLE%')
LOOP
EXECUTE IMMEDIATE 'drop trigger ' || rec.trigger_name;
END LOOP;
END;
/

The result on Oracle Database 11g Release 1 was interesting: I ran this script while flying from Chicago down to Florida for Collaborate09. I started this script and many minutes later, I had to turn off the laptop since we were landing. By that time, over 5000 triggers had been created on the same table. Maybe I will run this later and let it run overnight, but really what's the point? No one is ever going to create that many triggers on a single table. Perhaps there is a lower, hard limit like 12 on Oracle Database 10g or earlier versions. Why don't you run this script on your database and let me know what you get?

To conclude, I suggest that if your interviewer asks you silly questions like this one, you should answer as follows:

"Why do you think it matters if I know this information? How will it help me be more successful on your team? And how do you know you've even got the right answer? Let's build a script to prove that the answer is right!"

Then whip out your laptop, throw together a script and show what a good problem solver you are. I guarantee you will have a much better shot at your job with this kind of answer.

5 comments:

Aman Sharma said...

Steve,

Thanks alot for this very nice read. I do agree to all what you said. About interviews, I believe , most of the interviewers are happy asking that only what they know. Tell them some thing which they neverheard of, one gets a raised eyebrow. Best person for any tech job is who knows where to look, what to look at a given problem, not some one who knows some keywords, syntaxes but unfortunately, that's not the mind set at many places.

"Why do you think it matters if I know this information? How will it help me be more successful on your team? And how do you know you've even got the right answer? Let's build a script to prove that the answer is right!"

Then whip out your laptop, throw together a script and show what a good problem solver you are. I guarantee you will have a much better shot at your job with this kind of answer.
LOL, at times it can back fire too. I was asked one time in an "instant" interview call (which I had to take while coming back to my home in a bus only because lady was not having time afterwards) that tell me the techniques of the join clause. Well, I started with all the nitty gritty details of Nested Loop, Sort Merge and so on. Immediately, that lady stopped me and said, hey don't you know about Equi Join, Outer Join etc? I couldn't stop my smile and said, I believe you wanted to say, "join types" not "techniques". Guess what, interview got finished right away :-) .

Regards
Aman....

Belly said...

If there is no documentation at all on the subject, how can the interviewer expect someone else to know the answer?
It looks like the question isn't even intended to check your knowledge, but rather your bullshit-level (will he start talking rubbish when we ask him a question we know he doesn't know the answer to) and your ability to admit there is something you don't know.

Like in the old days, in the student home I used to live in (yes, the stone age) whenever a room was available and somebody wanted to be the next tenant, the person was invited for an evening to find out if he/she would fit in in the existing group (usually they did). One of the questions that was frequently asked in one form or another, was 'what do you think of the domestic politics of Angola'.
The answer itself was irrelevant. But the way people reacted to the stupid question learned us a lot about the person giving the answer.

I think it's pointless to run your script for any length of time. Steven.
The only limit that I am aware of (this does not mean a thing) is the number of unique names that can be created within the limitations of the identifier rules (30 characters, not starting with a number, limited use of the character set).
But if there is another limit that I am (and as it seems you are) not aware of, then obviously we do not know what this limit is based upon.
Such a limit could be a maximum of x triggers.
But it could also be, for example, a maximum number of bytes for the combined code of the triggers on the table.
So you would have to at least run the script twice with different lengths for the triggers code.

I agree that (correct) code should give you the truth for any specific situation.
But only if you know and understand all aspects of the specific situation.

Girl Next Door (gnd) said...

Maybe the actual question is "how many different *types* of triggers can you have on a table?"
Insert/Update/Delete - 3
Before/After - 2
Row Level/Statement Level - 2

So, 3*2*2?

Not that this is a great interview question either...!

I've had the pleasure to tell off an interviewer once...!
I had just recd a job offer, but had already scheduled this phone interview. So, decided to take it.
After 15-20 minutes into the interview, talking about the actual work i'd done, the id10t had the nerve to ask me if i knew what a cursor was!!!
I told him he had to be joking! and that I wasn't gonna answer that question!!
best interview of my life :))

Fletch said...

As usual Steven, your logic again holds the truth. I dont care if you can tell me how many internation characters can fit into a CLOB. What an interviewer should care about is does the person have good programming skills. Does the person play well with others. If the person runs into a problem, do they have problem solving techniques that will get them through.. One of my favorite computer professors in college said at the beginning of his class "I by the end of my class you know where to find the answer, then I have done my job". Programming is about looking at problems and designing solutions not knowing off the top of your head minimums and maximums or even being able to regurge difficult syntax.

Hope life is treating you well and keep up the excellant work you do.
-- Gods Peace

marudos said...
This comment has been removed by a blog administrator.