Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Chris.

Asked: December 14, 2003 - 6:02 pm UTC

Last updated: July 02, 2005 - 7:14 pm UTC

Version: 9.2

Viewed 1000+ times

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

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.

Rating

  (7 ratings)

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

Comments

A reader, December 14, 2003 - 10:21 pm UTC


Sets

DXL, December 15, 2003 - 6:16 am UTC

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

Tom Kyte
December 15, 2003 - 8:15 am UTC

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".


A reader, December 15, 2003 - 9:02 am UTC


A reader, December 15, 2003 - 9:03 am UTC

I love this school of Tom. :-)

Can a single SQL do this

Steve, July 02, 2005 - 4:08 pm UTC

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!

Tom Kyte
July 02, 2005 - 4:17 pm UTC

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.

n Queens

Darren L, July 02, 2005 - 6:37 pm UTC

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> 

 

Tom Kyte
July 02, 2005 - 7:14 pm UTC

very nice ;)

you don't know what you're missing

Viswanathan Anand, August 27, 2007 - 3:35 pm UTC

you *really* don't play chess ?
you don't know what you're missing ...

it can even be more fun than SQL

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library