9i奇怪的AJ執行計劃

感覺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。