感觉920版本上的AJ执行计划有bug，当子查询的结果可以通过索引完全获取时，AJ执行计划似乎就失效了。

看看下面这个例子：

SQL> CREATE TABLE T1 AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

Table created.

SQL> CREATE TABLE T2 AS SELECT ROWNUM ID1, ROWNUM ID2, A.* FROM DBA_SYNONYMS A;

Table created.

SQL> ALTER TABLE T1 ADD PRIMARY KEY (ID);

Table altered.

SQL> ALTER TABLE T2 MODIFY (ID1 NOT NULL, ID2 NOT NULL);

Table altered.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2')

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T1 WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE ID = ID1);

32008 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=160 Card=32008 Bytes=3328832)
1 0 HASH JOIN (ANTI) (Cost=160 Card=32008 Bytes=3328832)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=65 Card=45166 Bytes=4471434)
3 1 TABLE ACCESS (FULL) OF 'T2' (Cost=15 Card=13158 Bytes=65790)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
804 consistent gets
20 physical reads
0 redo size
3157285 bytes sent via SQL*Net to client
23966 bytes received via SQL*Net from client
2135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32008 rows processed

SQL> SELECT * FROM T1 WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE ID = ID2);

32008 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=160 Card=32008 Bytes=3328832)
1 0 HASH JOIN (ANTI) (Cost=160 Card=32008 Bytes=3328832)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=65 Card=45166 Bytes=4471434)
3 1 TABLE ACCESS (FULL) OF 'T2' (Cost=15 Card=13158 Bytes=65790)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
804 consistent gets
20 physical reads
0 redo size
3157285 bytes sent via SQL*Net to client
23966 bytes received via SQL*Net from client
2135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32008 rows processed

由于ID1和ID2无论从结构还是数据分布上都是一样的，所以上面两个SQL的执行计划是完全一样的。

下面在ID1上建立一个普通索引：

SQL> CREATE INDEX IND_T2_ID1 ON T2(ID1);

Index created.

SQL> SELECT * FROM T1 WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE ID = ID1);

32008 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2323 Card=2258 Bytes=223542)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=65 Card=2258 Bytes=223542)
3 1 INDEX (RANGE SCAN) OF 'IND_T2_ID1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
93084 consistent gets
79 physical reads
0 redo size
2345718 bytes sent via SQL*Net to client
23966 bytes received via SQL*Net from client
2135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32008 rows processed

SQL> SELECT * FROM T1 WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE ID = ID2);

32008 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=160 Card=32008 Bytes=3328832)
1 0 HASH JOIN (ANTI) (Cost=160 Card=32008 Bytes=3328832)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=65 Card=45166 Bytes=4471434)
3 1 TABLE ACCESS (FULL) OF 'T2' (Cost=15 Card=13158 Bytes=65790)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
804 consistent gets
50 physical reads
0 redo size
3157285 bytes sent via SQL*Net to client
23966 bytes received via SQL*Net from client
2135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32008 rows processed

第一个SQL的执行计划变成了FILTER，但是无论从执行计划的代价看还是从执行的实际效果看，这种执行计划的改变都是会降低性能的。

而且，更加关键的问题时，这个时候已经不可能让Oracle再以AJ的执行计划来执行了，即使添加HINT也不行。

SQL> SELECT * FROM T1 WHERE NOT EXISTS (SELECT /*+ HASH_AJ */ 1 FROM T2 WHERE ID = ID1);

32008 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2323 Card=2258 Bytes=223542)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=65 Card=2258 Bytes=223542)
3 1 INDEX (RANGE SCAN) OF 'IND_T2_ID1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
93084 consistent gets
50 physical reads
0 redo size
2345718 bytes sent via SQL*Net to client
23966 bytes received via SQL*Net from client
2135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32008 rows processed

SQL> SELECT * FROM T1 WHERE NOT EXISTS (SELECT /*+ NL_AJ */ 1 FROM T2 WHERE ID = ID1);

32008 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2323 Card=2258 Bytes=223542)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=65 Card=2258 Bytes=223542)
3 1 INDEX (RANGE SCAN) OF 'IND_T2_ID1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
93084 consistent gets
47 physical reads
0 redo size
2345718 bytes sent via SQL*Net to client
23966 bytes received via SQL*Net from client
2135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32008 rows processed

根据上面的结果，怀疑是Oracle在处理AJ查询时存在bug。上面的测试是在9204上进行的。而在10201上测试发现Oracle可以正确的选择执行计划。

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> CREATE TABLE T1 AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创建。

SQL> CREATE TABLE T2 AS SELECT ROWNUM ID1, ROWNUM ID2, A.* FROM DBA_SYNONYMS A;

表已创建。

SQL> ALTER TABLE T1 ADD PRIMARY KEY (ID);

表已更改。

SQL> ALTER TABLE T2 MODIFY (ID1 NOT NULL, ID2 NOT NULL);

表已更改。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2')

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE
SQL> SELECT * FROM T1 WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE ID = ID1);

已选择30263行。

执行计划
----------------------------------------------------------
Plan hash value: 629543484

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30264 | 3044K| 60 (7)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI| | 30264 | 3044K| 60 (7)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T2 | 20107 | 98K| 14 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 50370 | 4820K| 44 (5)| 00:00:01 |
-----------------------------------------------------------------------------

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

1 - access("ID"="ID1")

统计信息
----------------------------------------------------------
100 recursive calls
0 db block gets
2506 consistent gets
0 physical reads
0 redo size
1626407 bytes sent via SQL*Net to client
22572 bytes received via SQL*Net from client
2019 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
30263 rows processed

SQL> CREATE INDEX IND_T2_ID1 ON T2(ID1);

索引已创建。

SQL> SELECT * FROM T1 WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE ID = ID1);

已选择30263行。

执行计划
----------------------------------------------------------
Plan hash value: 1180113856

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30264 | 3044K| 47 (9)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 30264 | 3044K| 47 (9)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 50370 | 4820K| 44 (5)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T2_ID1 | 8026 | 40130 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

3 - access("ID"="ID1")

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
54754 consistent gets
22 physical reads
0 redo size
1626407 bytes sent via SQL*Net to client
22572 bytes received via SQL*Net from client
2019 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30263 rows processed

将9204上的执行计划和10201上的进行对比，更可以确认9204在AJ执行计划上存在着bug。