Re: Code in the database or middle tier (the CLR controversy)

Erland Sommarskog wrote:
> DA Morgan (damorgan_at_psoug.org) writes:
>

>>Erland Sommarskog wrote:
>>
>>>Actually, as long as we are into inner joins, both syntaxes are
>>>ANSI-compatible. It is when it comes to outer joins it matter.
>>>Here both SQL Server and Oracle have their own propritary operators.
>>
>>Not true. Oracle has for multiple versions now allowed either ANSI or
>>ISO syntax.

I misunderstood. You are correct in your assumption.

SQL> SELECT DISTINCT i.srvr_id

   2 FROM serv_inst i, servers s
   3 WHERE i.srvr_id = s.srvr_id(+);

11 rows selected.

Execution Plan

    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=11 Bytes=88)     1 0 SORT (UNIQUE) (Cost=6 Card=11 Bytes=88)

    2    1     NESTED LOOPS (OUTER) (Cost=5 Card=999 Bytes=7992)
    3    2       TABLE ACCESS (FULL) OF 'SERV_INST' (TABLE) (Cost=4
                 Card=999 Bytes=3996)
    4    2       INDEX (UNIQUE SCAN) OF 'PK_SERVERS' (INDEX (UNIQUE))
                 (Cost=0 Card=1 Bytes=4)

           0  recursive calls
           0  db block gets
          11  consistent gets
           0  physical reads
           0  redo size
         532  bytes sent via SQL*Net to client
         508  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           1  sorts (memory)
           0  sorts (disk)
          11  rows processed

SQL> SELECT DISTINCT i.srvr_id

   2 FROM serv_inst i LEFT OUTER JOIN servers s    3 ON i.srvr_id = s.srvr_id
   4 /

11 rows selected.

Execution Plan

    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=11 Bytes=88)     1 0 SORT (UNIQUE) (Cost=6 Card=11 Bytes=88)

    2    1     NESTED LOOPS (OUTER) (Cost=5 Card=999 Bytes=7992)
    3    2       TABLE ACCESS (FULL) OF 'SERV_INST' (TABLE) (Cost=4
                 Card=999 Bytes=3996)
    4    2       INDEX (UNIQUE SCAN) OF 'PK_SERVERS' (INDEX (UNIQUE))
                 (Cost=0 Card=1 Bytes=4)

           0  recursive calls
           0  db block gets
          11  consistent gets
           0  physical reads
           0  redo size
         532  bytes sent via SQL*Net to client
         508  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           1  sorts (memory)
           0  sorts (disk)
          11  rows processed

[Quoted] As you can see: Identical.

> But judging from Serge Rideau's post, there are problems with (+)= as well.
> Although the *= of SQL Server could still be worse. As a matter of fact,
> in SQL 2000, *= will only be available in compatibility mode only.

Serge is incorrect in that these limitations have always been there and Oracle developers have been using in-line views to work around them for so long that they don't even pay any attention: It just comes naturally.

[Quoted] I can't speak for Oracle but I expect the ISO operators will be there for the rest of my professional life. Oracle has always been far more committed to backward compatibility than some other companies. Often to my dismay in that some of the antiquities encourage bad practices.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)