Zauważyłem różnicę przy wykonywaniu zapytania w 8i (9i) oraz w 10g.
W 8i:
SQL> select version from v$instance
2 /

VERSION
-----------------
8.1.7.4.0

SQL> select rownum, a.*, b.*
2 from (select NULL id from dual) a
3 , (select 1 a_id from dual) b
4 where a.id = b.a_id (+)
5 /

ROWNUM I A_ID
--------- - ---------
1

SQL> select rownum, a.*, b.*
2 from (select NULL id from dual) a
3 , (select 1 a_id from dual where 1 = 0) b
4 where a.id = b.a_id (+)
5 /

ROWNUM I A_ID
--------- - ---------
1


W 10g:
SQL> select version from v$instance
2 /

VERSION
-----------------
10.2.0.2.0

SQL> select rownum, a.*, b.*
2 from (select NULL id from dual) a
3 , (select 1 a_id from dual) b
4 where a.id = b.a_id (+)
5 /

no rows selected

SQL> select rownum, a.*, b.*
2 from (select NULL id from dual) a
3 , (select 1 a_id from dual where 1 = 0) b
4 where a.id = b.a_id (+)
5 /

ROWNUM I A_ID
--------- - ---------
1


Czy ktoś wie, skąd ta różnica przy zapytaniu:
select rownum, a.*, b.*
from (select NULL id from dual) a
, (select 1 a_id from dual) b
where a.id = b.a_id (+)

i jak to się ma do ANSI SQL? Wg mnie w 8i jest poprawnie, bo w tabeli (widoku)
a mam 1 wiersz, a (+) przy warunku oznacza, że chcę ten jeden wiersz zwrócić,
nawet jeśli w b nie uda się znaleźć odpowiadającego wiersza ( swoją drogą -
nie uda się go nigdy znaleźć bo NULL nie jest równy niczemu ).

Marcin Wróblewski wrote:
[..]
> Czy ktoś wie, skąd ta różnica przy zapytaniu:
> select rownum, a.*, b.*
> from (select NULL id from dual) a
> , (select 1 a_id from dual) b
> where a.id = b.a_id (+)
>
> i jak to się ma do ANSI SQL? Wg mnie w 8i jest poprawnie, bo w tabeli (widoku)
> a mam 1 wiersz, a (+) przy warunku oznacza, że chcę ten jeden wiersz zwrócić,
> nawet jeśli w b nie uda się znaleźć odpowiadającego wiersza ( swoją drogą -
> nie uda się go nigdy znaleźć bo NULL nie jest równy niczemu ).

Generalnie jest to pytanie do Oracle Support, ale możesz włączyć w obu
wypadkach trace (lub przynajmniej explain plan) i zobaczyć jak to
wewnętrznie serwer wykonuje. Warto zobaczyć czy zmiana parametru
komatybilności może coś zmienić.

..... troche posprawdzałem (9i, 10gR1 działają tak jak poprzednio)
W 10gR2 Explan pokazuje ze te zapytania nie są równoważne - inny jest
warunek where...
10gR2
SQL> select rownum, a.*, b.*
from (select NULL id from dual) a
, (select 1 a_id from dual) b
where a.id = b.a_id (+) ;

select rownum, a.*, b.*
from (select NULL id from dual) a
, (select 1 a_id from dual where 1 = 0) b
where a.id = b.a_id (+) ; 2 3 4
no rows selected


Execution Plan
----------------------------------------------------------

Plan hash value: 2310253664

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 0 (0)|
|
| 1 | COUNT | | | | |
|
|* 2 | FILTER | | | | |
|
| 3 | NESTED LOOPS OUTER| | 1 | 2 | 4 (0)|
00:00:01 |
| 4 | FAST DUAL | | 1 | | 2 (0)|
00:00:01 |
| 5 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)|
00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(NULL IS NOT NULL)


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

SQL> SQL> 2 3 4
ROWNUM I A_ID
---------- - ----------
1


Execution Plan
----------------------------------------------------------
Plan hash value: 760961888

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
| 2 | NESTED LOOPS OUTER| | 1 | 3 | 2 (0)| 00:00:01 |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 3 | | |
|* 5 | FILTER | | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - filter(NULL IS NOT NULL AND NULL IS NOT NULL)


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






9i
11:04:00 SQL> select rownum, a.*, b.*
from (select NULL id from dual) a
, (select 1 a_id from dual) b
where a.id = b.a_id (+) ;

select rownum, a.*, b.*
from (select NULL id from dual) a
, (select 1 a_id from dual where 1 = 0) b
where a.id = b.a_id (+) ;11:04:02 2 11:04:02 3 11:04:02 4
ROWNUM I A_ID
---------- - ----------
1

Elapsed: 00:00:00.32

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT
2 1 NESTED LOOPS (OUTER)
3 2 TABLE ACCESS (FULL) OF 'DUAL'
4 2 TABLE ACCESS (FULL) OF 'DUAL'




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

11:04:03 SQL> 11:04:03 SQL> 11:04:03 2 11:04:03 3 11:04:03 4

ROWNUM I A_ID
---------- - ----------
1

Elapsed: 00:00:00.30

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT
2 1 NESTED LOOPS (OUTER)
3 2 TABLE ACCESS (FULL) OF 'DUAL'
4 2 VIEW
5 4 FILTER
6 5 TABLE ACCESS (FULL) OF 'DUAL'




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

11:04:04 SQL>


10gR1
SQL> select rownum, a.*, b.*
from (select NULL id from dual) a
, (select 1 a_id from dual) b
where a.id = b.a_id (+) ;

select rownum, a.*, b.*
from (select NULL id from dual) a
, (select 1 a_id from dual where 1 = 0) b
where a.id = b.a_id (+) ; 2 3 4
ROWNUM I A_ID
---------- - ----------
1


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=2)
1 0 COUNT
2 1 MERGE JOIN (OUTER) (Cost=4 Card=1 Bytes=2)
3 2 FAST DUAL (Cost=2 Card=1)
4 2 TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 B
ytes=2)





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

SQL> SQL> 2 3 4

ROWNUM I A_ID
---------- - ----------
1


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=3)
1 0 COUNT
2 1 MERGE JOIN (OUTER) (Cost=4 Card=1 Bytes=3)
3 2 FAST DUAL (Cost=2 Card=1)
4 2 VIEW (Cost=2 Card=1 Bytes=3)
5 4 FILTER
6 5 FAST DUAL (Cost=2 Card=1)




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



dap