不等號影響執行計劃的相關實驗

論壇上的一個兄弟提出了一個對<>執行計劃不解的一個問題，原帖如下：

http://www.itpub.net/thread-1325933-1-2.html

做一個測試,可能具體稍稍有些不一樣只看看原理：

SQL> create table t_test_notequ1 as select * from dba_objects;

表已建立。

SQL> create index idx_test_notequ1 on t_test_notequ1 (object_id);

索引已建立。

SQL> select count(*) from t_test_notequ1;

COUNT(*)

----------

59623

執行計劃

----------------------------------------------------------

Plan hash value: 360092435

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 183 (2)| 00:00:03 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 60125 | 183 (2)| 00:00:03 |

-----------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

統計資訊

----------------------------------------------------------

28 recursive calls

0 db block gets

894 consistent gets

473 physical reads

0 redo size

410 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

SQL>

這個很好理解，優化器選擇全表掃描。因為並沒有謂詞限定資料選取條件。

注意到上面的rows是60125，建立表後並沒有做統計資訊的分析，可以看到：

SQL> select a.num_rows,a.blocks from dba_tables a where a.table_name = upper ('

t_test_notequ1');

NUM_ROWS BLOCKS

---------- ----------

SQL>

調優的一個習慣，很多情況下在每句執行前清空快取：

SQL> alter system flush buffer_cache;

系統已更改。

SQL> alter system flush shared_pool;

系統已更改。

SQL> select count(*) from t_test_notequ1 a where a.object_id <> 1;

COUNT(*)

----------

59622

執行計劃

----------------------------------------------------------

Plan hash value: 2434651730

--------------------------------------------------------------------------------

----------

Time |

--------------------------------------------------------------------------------

----------

| 0 | SELECT STATEMENT | | 1 | 13 | 33 (7)|

00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | |

|

|* 2 | INDEX FAST FULL SCAN| IDX_TEST_NOTEQU1 | 60125 | 763K| 33 (7)|

00:00:01 |

--------------------------------------------------------------------------------

----------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"."OBJECT_ID"<>1)

Note

-----

- dynamic sampling used for this statement

統計資訊

----------------------------------------------------------

305 recursive calls

0 db block gets

250 consistent gets

541 physical reads

0 redo size

410 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

依然沒有統計資訊，但是在條件中有object_id出現，且object_id上是有索引的，這裡並不是像很多人說的<>就一定不使用索引，也就是說CBO不一定會將<>作為禁用索引的必然條件。當然如果強制使用rule的hint的話，則是可以的：

SQL> select /*+ rule*/count(*) from t_test_notequ1 a where a.object_id <> 1;

COUNT(*)

----------

59622

執行計劃

----------------------------------------------------------

Plan hash value: 360092435

---------------------------------------------

| Id | Operation | Name |

---------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

|* 2 | TABLE ACCESS FULL| T_TEST_NOTEQU1 |

---------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"."OBJECT_ID"<>1)

Note

-----

- rule based optimizer used (consider using cbo)

統計資訊

----------------------------------------------------------

616 recursive calls

0 db block gets

921 consistent gets

833 physical reads

0 redo size

410 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

上面的2個查詢的效能先不說，可以大致看看物理讀，table full scan和index full scan還是有一些差別的，這裡先不看。

將count(*) 改成count(object_id)看看情況：

SQL> select count(object_id) from t_test_notequ1 a where a.object_id <> 1;

COUNT(OBJECT_ID)

----------------

59622

執行計劃

----------------------------------------------------------

Plan hash value: 2434651730

--------------------------------------------------------------------------------

----------

Time |

--------------------------------------------------------------------------------

----------

| 0 | SELECT STATEMENT | | 1 | 13 | 33 (7)|

00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | |

|

|* 2 | INDEX FAST FULL SCAN| IDX_TEST_NOTEQU1 | 60125 | 763K| 33 (7)|

00:00:01 |

--------------------------------------------------------------------------------

----------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"."OBJECT_ID"<>1)

Note

-----

- dynamic sampling used for this statement

統計資訊

----------------------------------------------------------

233 recursive calls

0 db block gets

238 consistent gets

537 physical reads

0 redo size

418 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

區別不大，比起count(*)來，更少的遞迴呼叫，更少的一致性讀，物理讀。*號會被解析成所有列，這個會增加遞迴呼叫，具體先不看。

將count(*)改成count(owner)，這時候可以看到為tfs了。

SQL> alter system flush shared_pool;

系統已更改。

SQL> alter system flush buffer_cache;

系統已更改。

SQL> select count(owner) from t_test_notequ1 a where a.object_id <> 1;

COUNT(OWNER)

------------

59622

執行計劃

----------------------------------------------------------

Plan hash value: 360092435

--------------------------------------------------------------------------------

-----

|

--------------------------------------------------------------------------------

-----

| 0 | SELECT STATEMENT | | 1 | 9 | 184 (3)| 00:00

:03 |

| 1 | SORT AGGREGATE | | 1 | 9 | |

|

|* 2 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59621 | 524K| 184 (3)| 00:00

:03 |

--------------------------------------------------------------------------------

-----

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"."OBJECT_ID"<>1)

統計資訊

----------------------------------------------------------

271 recursive calls

0 db block gets

869 consistent gets

826 physical reads

0 redo size

414 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

分析一下這個表：

SQL> analyze table t_test_notequ1 compute statistics;

表已分析。

SQL>

可以看到：

SQL> select a.num_rows,a.blocks from dba_tables a where a.table_name = upper ('t_test_notequ1');

NUM_ROWS BLOCKS

---------- ----------

59623 816

SQL>

再來執行前面兩個sql，發現並無實質區別。

再收集一下直方圖資訊：

SQL> analyze table t_test_notequ1 compute statistics for columns object_id size

100;

表已分析。

SQL> alter system flush shared_pool;

系統已更改。

SQL> alter system flush buffer_cache;

系統已更改。

SQL> select count(*) from t_test_notequ1 a where a.object_id <> 1;

COUNT(*)

----------

59622

執行計劃

----------------------------------------------------------

Plan hash value: 2434651730

--------------------------------------------------------------------------------

----------

Time |

--------------------------------------------------------------------------------

----------

| 0 | SELECT STATEMENT | | 1 | 4 | 33 (7)|

00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 4 | |

|

|* 2 | INDEX FAST FULL SCAN| IDX_TEST_NOTEQU1 | 59621 | 232K| 33 (7)|

00:00:01 |

--------------------------------------------------------------------------------

----------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"."OBJECT_ID"<>1)

統計資訊

----------------------------------------------------------

262 recursive calls

0 db block gets

187 consistent gets

143 physical reads

0 redo size

410 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

進一步下降了一致性讀和物理讀，但是對執行計劃沒有影響。

再來看看update和update以後的情況：

SQL> update t_test_notequ1 a set a.object_id = 2 where object_id <>1;

已更新59622行。

執行計劃

----------------------------------------------------------

Plan hash value: 932495790

--------------------------------------------------------------------------------

-----

|

--------------------------------------------------------------------------------

-----

| 0 | UPDATE STATEMENT | | 59621 | 232K| 136 (3)| 00:00

:02 |

|

|* 2 | INDEX FULL SCAN| IDX_TEST_NOTEQU1 | 59621 | 232K| 136 (3)| 00:00

:02 |

--------------------------------------------------------------------------------

-----

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OBJECT_ID"<>1)

統計資訊

----------------------------------------------------------

445 recursive calls

66976 db block gets

1206 consistent gets

144 physical reads

21305940 redo size

680 bytes sent via SQL*Net to client

599 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

59622 rows processed

SQL>

SQL> commit;

提交完成。

SQL>

同樣的也可以理解到這裡是index fs

不過未重新分析統計資訊，可以看到：

SQL> select a.distinct_keys, a.leaf_blocks

2 from dba_indexes a

3 where a.index_name = upper('idx_test_notequ1');

DISTINCT_KEYS LEAF_BLOCKS

------------- -----------

59622 132

SQL> select count(*) from t_test_notequ1 a where a.object_id <> 1;

COUNT(*)

----------

59622

執行計劃

----------------------------------------------------------

Plan hash value: 2434651730

--------------------------------------------------------------------------------

----------

Time |

--------------------------------------------------------------------------------

----------

| 0 | SELECT STATEMENT | | 1 | 4 | 33 (7)|

00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 4 | |

|

|* 2 | INDEX FAST FULL SCAN| IDX_TEST_NOTEQU1 | 59621 | 232K| 33 (7)|

00:00:01 |

--------------------------------------------------------------------------------

----------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"."OBJECT_ID"<>1)

統計資訊

----------------------------------------------------------

262 recursive calls

0 db block gets

600 consistent gets

351 physical reads

16516 redo size

410 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> commit;

提交完成。

這裡前面遺漏的提交所以寫了不少redo。

繼續更新其中一行：update t_test_notequ1 a set a.object_id = null where rownum = 1;

然後分析table和index

這些測試也類似，大致給出測試的結果：

對於count(*)和count(object_id)：

當使用<> (number value) 時總是ffs

<>null 時是ffs

= 1時是 index range scan

= 2 時是ffs

= 一個大於2的值時是 range scan

=null時是table full scan

Is null時是table full scan

Is not null 時是 ffs

>1是ffs

>2 以上是range scan

上面的結果可以看出，統計資訊，直方圖，選取列和規則都對執行計劃產生影響，但如果資訊足夠完全的話，cbo優化器很多時候都可以選擇一個相對較為合適的執行計劃，也有一些例外的情況，這些例外應該具體問題具體分析。

具體將會在後續逐一剖析詳解。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-668257/，如需轉載，請註明出處，否則將追究法律責任。

不等號影響執行計劃的相關實驗

相關文章