## Question and Answer

## You Asked

Hi, Tom.

Both on this site and in Expert One-On-One you alluded to some of the procedures you expect your own team of developers to follow. Could you expand on that a little bit? For example, what do you tell a new developer that joins your team? Do you have specific requirements when it comes to unit, application and system testing? Benchmarking? Ongoing performance testing?

I realize the question is more than a little broad. Any insight you can give is very much appreciated.

Thanks

Chris

Both on this site and in Expert One-On-One you alluded to some of the procedures you expect your own team of developers to follow. Could you expand on that a little bit? For example, what do you tell a new developer that joins your team? Do you have specific requirements when it comes to unit, application and system testing? Benchmarking? Ongoing performance testing?

I realize the question is more than a little broad. Any insight you can give is very much appreciated.

Thanks

Chris

## and Tom said...

I expect them to learn fully the tools at their disposal.

Not just a programming language, but everything. The database, the OS, whatever they are using.

Sometimes the answer is grep, sed and awk.

Other times, it is something deep in the database.

Unless you know fully what is in your toolbox, you'll never be able to use it -- all you'll have is a hammer.

I only ask them to keep learning something new -- every day. Find new ways to do the same things.

Then, benchmark them.

Scientifically find out which is best, and realize that best only applies in the case they tested for. (thats why I hate questions that start with "which is the best ....." -- for example "whats the best type of table.". That is a not "good" question. If there were a universal best -- we would have only created one kind. No need to wasted time on other types)

I expect them to build to perform (not tune) from day 1 -- in the design. Tune is a 4 letter word.

I expect them to have complete instrumentation everything -- so when we do need to tune, we have what we need.

defensive programming -- 1/2 of my code is probably what others would call "overhead".

do it in SQL. think in sets. slow by slow (whoops - meant row by row) is so 1980's.

thats a good start.

Not just a programming language, but everything. The database, the OS, whatever they are using.

Sometimes the answer is grep, sed and awk.

Other times, it is something deep in the database.

Unless you know fully what is in your toolbox, you'll never be able to use it -- all you'll have is a hammer.

I only ask them to keep learning something new -- every day. Find new ways to do the same things.

Then, benchmark them.

Scientifically find out which is best, and realize that best only applies in the case they tested for. (thats why I hate questions that start with "which is the best ....." -- for example "whats the best type of table.". That is a not "good" question. If there were a universal best -- we would have only created one kind. No need to wasted time on other types)

I expect them to build to perform (not tune) from day 1 -- in the design. Tune is a 4 letter word.

I expect them to have complete instrumentation everything -- so when we do need to tune, we have what we need.

defensive programming -- 1/2 of my code is probably what others would call "overhead".

do it in SQL. think in sets. slow by slow (whoops - meant row by row) is so 1980's.

thats a good start.

## Rating

Is this answer out of date? If it is, please let us know via a Comment

# Comments

Can you expand and explain a bit more about " sql, thinking in sets" i've seen you mention this before. I understand what sets are but I would just like you to explain (maybe with an example or two) of how thinking in sets rather than row by row can help you write sql.

I believe you have said it helps with very complicated queries.

Many thanks

I believe you have said it helps with very complicated queries.

Many thanks

instead of:

for x in ( select * from t1 )

loop

update t2 set ..... where key = t1.key;

if ( sql%rowcount = 0 )

then

insert into t2 values X;

end if;

end loop;

think "merge"

Instead of coding a join yourself (you would not BELIEVE how many times I see this) think "single query"

Anytime you see a cursor for loop ask yourself "why -- maybe this is just a single DML statement"

Anytime you see someone taking a flat file and reading it row by row (slow by slow) -- doing some lookup -- and then inserting the data -- ask yourself "why isn't that a single sql statement with an external table and maybe a pipelined function"

see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:13972936942324 <code>

for example.

Consider what this query does:

with players as

( select 'P'||rownum username

from all_objects

where rownum <= 8),

weeks as

( select rownum week

from all_objects

where rownum <= 7 )

select week,

max(decode(rn,1,username,null)) u1,

max(decode(rn,2,username,null)) u2,

max(decode(rn,3,username,null)) u3,

max(decode(rn,4,username,null)) u4,

max(decode(rn,5,username,null)) u5,

max(decode(rn,6,username,null)) u6,

max(decode(rn,7,username,null)) u7,

max(decode(rn,8,username,null)) u8

from ( select username,

week,

row_number() over (partition by week order by rnd) rn

from ( select username, week, dbms_random.random rnd

from players, weeks

)

)

group by week

/

It is a "program" to randomly assign 8 player to one of two sets of 4 players for 7 weeks.

In short, it creates a golf roster for a tournament.

Think in sets, not procedurally.

Look at my analytic SQL answers -- think in "sets".

for x in ( select * from t1 )

loop

update t2 set ..... where key = t1.key;

if ( sql%rowcount = 0 )

then

insert into t2 values X;

end if;

end loop;

think "merge"

Instead of coding a join yourself (you would not BELIEVE how many times I see this) think "single query"

Anytime you see a cursor for loop ask yourself "why -- maybe this is just a single DML statement"

Anytime you see someone taking a flat file and reading it row by row (slow by slow) -- doing some lookup -- and then inserting the data -- ask yourself "why isn't that a single sql statement with an external table and maybe a pipelined function"

see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:13972936942324 <code>

for example.

Consider what this query does:

with players as

( select 'P'||rownum username

from all_objects

where rownum <= 8),

weeks as

( select rownum week

from all_objects

where rownum <= 7 )

select week,

max(decode(rn,1,username,null)) u1,

max(decode(rn,2,username,null)) u2,

max(decode(rn,3,username,null)) u3,

max(decode(rn,4,username,null)) u4,

max(decode(rn,5,username,null)) u5,

max(decode(rn,6,username,null)) u6,

max(decode(rn,7,username,null)) u7,

max(decode(rn,8,username,null)) u8

from ( select username,

week,

row_number() over (partition by week order by rnd) rn

from ( select username, week, dbms_random.random rnd

from players, weeks

)

)

group by week

/

It is a "program" to randomly assign 8 player to one of two sets of 4 players for 7 weeks.

In short, it creates a golf roster for a tournament.

Think in sets, not procedurally.

Look at my analytic SQL answers -- think in "sets".

I love this school of Tom. :-)

Your sql for golf roster for a tournament made me

think of the classic 8 queens puzzle(the problem of putting eight chess queens on an 8×8 chessboard such that none of them is able to capture any other using the standard chess queen's moves.)

Can a single SQL do that?

Thanks!

think of the classic 8 queens puzzle(the problem of putting eight chess queens on an 8×8 chessboard such that none of them is able to capture any other using the standard chess queen's moves.)

Can a single SQL do that?

Thanks!

with a cartesian product, we could do anything :)

I'll have to leave this one as an exercise for the reader, I don't play chess.

I'll have to leave this one as an exercise for the reader, I don't play chess.

Steve, this should do it.. set :n to the number of queens. (n is valid at 1 and >= 4 the answer for 2 and 3 is rubbish as there is no solution) select rownum col#, pos row# from (select pos from (select rownum pos from dual connect by level <= :n) where mod(pos, 2) = 0 and not (pos = 2 and mod( :n, 12 ) in (3, 9)) union all /* Move 2 to the end of the set, if the remainder was 3 or 9 */ select 2 from dual where mod( :n, 12 ) in (3, 9) /* /EVENS */ union all /* ODDS */ select r from (select r, mod(rownum-1, 2) ord, trunc((rownum-1)/2) set# from (select rownum r from dual connect by level <= :n) where mod(r+1, 2) = 0 and not (r = 5 and mod( :n, 12 ) = 2) and not (r in (1,3) and mod( :n, 12 ) in (3, 9)) order by set#, case /* Flip pairs if remainder was 8 */ when mod( :n, 12 ) = 8 then -ord /* only flip first pair if the remainder was 2 */ when mod( :n, 12 ) = 2 and set# = 0 then -ord else ord end) union all /* Move 5 to the end of the set if the remainder was 2 */ select 5 from dual where mod( :n, 12 ) = 2 union all /* Move one and 3 to the end of the set if the remainder was 3 or 9 */ select (2 * rownum) - 1 from dual where mod( :n, 12 ) in (3, 9) connect by level <= 2) / SQL> var n number SQL> exec :n:=4; PL/SQL procedure successfully completed. SQL> select rownum col#, pos row# 2 from (select pos 3 from (select rownum pos 4 from dual 5 connect by level <= :n) 6 where mod(pos, 2) = 0 7 and not (pos = 2 and mod( :n, 12 ) in (3, 9)) 8 union all 9 /* Move 2 to the end of the set, if the remainder was 3 or 9 */ 10 select 2 11 from dual 12 where mod( :n, 12 ) in (3, 9) 13 /* /EVENS */ 14 union all 15 /* ODDS */ 16 select r 17 from (select r, mod(rownum-1, 2) ord, trunc((rownum-1)/2) set# 18 from (select rownum r 19 from dual 20 connect by level <= :n) 21 where mod(r+1, 2) = 0 22 and not (r = 5 and mod( :n, 12 ) = 2) 23 and not (r in (1,3) and mod( :n, 12 ) in (3, 9)) 24 order by set#, 25 case 26 /* Flip pairs if remainder was 8 */ 27 when mod( :n, 12 ) = 8 28 then 29 -ord 30 /* only flip first pair if the remainder was 2 */ 31 when mod( :n, 12 ) = 2 32 and set# = 0 33 then 34 -ord 35 else 36 ord 37 end) 38 union all 39 /* Move 5 to the end of the set if the remainder was 2 */ 40 select 5 41 from dual 42 where mod( :n, 12 ) = 2 43 union all 44 /* Move one and 3 to the end of the set if the remainder was 3 or 9 */ 45 select (2 * rownum) - 1 46 from dual 47 where mod( :n, 12 ) in (3, 9) 48 connect by level <= 2) 49 / COL# ROW# ---------- ---------- 1 2 2 4 3 1 4 3 SQL> exec :n:=8; PL/SQL procedure successfully completed. SQL> / COL# ROW# ---------- ---------- 1 2 2 4 3 6 4 8 5 3 6 1 7 7 8 5 8 rows selected. SQL> exec :n:= 15; PL/SQL procedure successfully completed. SQL> / COL# ROW# ---------- ---------- 1 4 2 6 3 8 4 10 5 12 6 14 7 2 8 5 9 7 10 9 11 11 COL# ROW# ---------- ---------- 12 13 13 15 14 1 15 3 15 rows selected. formula credit to:http://en.wikipedia.org/wiki/Eight_queens_puzzle#Constructing_a_solution

-- Daz. SQL>

very nice ;)

you *really* don't play chess ?

you don't know what you're missing ...

it can even be more fun than SQL

you don't know what you're missing ...

it can even be more fun than SQL