【新炬網路名師大講堂】CBO中”與NULL在cardinality計算上的差別

shsnchyw發表於2014-12-18
Null

在ORACLE裡，一般認為”與NULL是等價的，除了一些特別的語法，比如有id is null,但是沒有id is ”，以及”作為字元型別的特殊性，在decode等函式要求型別匹配的時候，與NULL可能不等價。本篇文章主要說明下”與NULL在SQL最佳化器中也有很大的區別，如果不瞭解這個區別，很可能SQL語句的效能，將要陷入災難境地。此問題來源於：http://www.itpub.net/thread-1838241-1-1.html 討論
 
  1.示例分析

create table t1 as select * from dba_objects ;
create table t2 as select * from dba_objects ;
create table t3 as select * from dba_objects ;
/
begin
  for xx in 1 .. 5 loop
    insert into t1 select * from t1;
    insert into t2 select * from t2;
    insert into t3 select * from t3;
    commit;
  end loop;
end;
/
create index i1 on  t1(object_name);
create index i2 on  t2(object_name);
create index i3 on  t3(object_name);
create index i4 on  t1(subobject_name);
create index i5 on  t2(subobject_name);
create index i6 on  t3(subobject_name);


–收集統計資訊省略

    針對下列語句，ORACLE對t2,t3走了FULL TABLE SCAN:

dingjun123@ORADB> show rel
release 1102000100


dingjun123@ORADB> set autotrace traceonly exp
dingjun123@ORADB> select *
  2    from (select t1.subobject_name, t1.object_name, t1.object_type
  3            from t1
  4          union all
  5          select ”, t2.object_name, t2.object_type
  6            from t2
  7          union all
  8          select t3.subobject_name, ”, t3.object_type from t3) t
  9  where (t.subobject_name = ‘T’ or t.object_name = ‘T’);
Elapsed: 00:00:00.05

Execution Plan
———————————————————-
Plan hash value: 3458763867
——————————————————————————————-
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————-
|   0 | SELECT STATEMENT                   |      | 62890 |  5773K| 18934   (1)| 00:03:48 |
|   1 |  VIEW                              |      | 62890 |  5773K| 18934   (1)| 00:03:48 |
|   2 |   UNION-ALL                        |      |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID     | T1   | 12678 |   433K|    38   (0)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS    |      |       |       |            |          |
|   5 |      BITMAP OR                     |      |       |       |            |          |
|   6 |       BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|*  7 |        INDEX RANGE SCAN            | I4   |       |       |     1   (0)| 00:00:01 |
|   8 |       BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|*  9 |        INDEX RANGE SCAN            | I1   |       |       |     3   (0)| 00:00:01 |
|* 10 |    TABLE ACCESS FULL               | T2   | 24167 |   778K|  9429   (1)| 00:01:54 |
|* 11 |    TABLE ACCESS FULL               | T3   | 26045 |   279K|  9466   (1)| 00:01:54 |
——————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   7 – access(“T1″.”SUBOBJECT_NAME”=’T')
   9 – access(“T1″.”OBJECT_NAME”=’T')
  10 – filter(”=’T’ OR “T2″.”OBJECT_NAME”=’T')
  11 – filter(”=’T’ OR “T3″.”SUBOBJECT_NAME”=’T')

    看錶t2、t3對應謂詞的實際基數情況:

dingjun123@ORADB>    SELECT COUNT(*) FROM t2 WHERE ”=’T’ OR “T2″.”OBJECT_NAME”=’T';
  COUNT(*)
———-
       128
1 row selected.
Elapsed: 00:00:11.50


dingjun123@ORADB>    SELECT COUNT(*) FROM t3 WHERE ”=’T’ OR “T3″.”SUBOBJECT_NAME”=’T';
  COUNT(*)
———-
         0
1 row selected.
Elapsed: 00:00:14.85

    CBO最佳化器估算的對應謂詞的基數與實際的技術差別如下：

表名 估算基數 實際基數 差別倍數
T2 24167 128 188.80
T3 26045 0 26045

    很顯然，實際基數與估算的基數差別太大，從而最佳化器選擇了錯誤的執行路徑，正確的執行路徑應該是走索引的。究其原因，可以分析下，

dingjun123@ORADB>    SELECT COUNT(*) FROM t2 WHERE  ”T2″.”OBJECT_NAME”=’T';
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 2583336616
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |    24 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |    24 |            |          |
|*  2 |   INDEX RANGE SCAN| I2   |    52 |  1248 |     3   (0)| 00:00:01 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
   2 – access(“T2″.”OBJECT_NAME”=’T')


dingjun123@ORADB>         SELECT COUNT(*) FROM t2 WHERE ”=’T';
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 402395414
——————————————————————–
| Id  | Operation           | Name | Rows  | Cost (%CPU)| Time     |
——————————————————————–
|   0 | SELECT STATEMENT    |      |     1 |  9411   (1)| 00:01:53 |
|   1 |  SORT AGGREGATE     |      |     1 |            |          |
|*  2 |   FILTER            |      |       |            |          |
|   3 |    TABLE ACCESS FULL| T2   |  2411K|  9411   (1)| 00:01:53 |
——————————————————————–
Predicate Information (identified by operation id):
—————————————————
   2 – filter(”=’T')

  很顯然，是由於謂詞”=’T'造成了最佳化器的估算錯誤，”=’T'最佳化器估算100%的選擇性，透過OR一合併，針對表T2的最終基數是24167，因此，最佳化器選擇全表掃描。這很顯然是錯誤的，”=’T',走FILTER應該轉為NULL IS NOT NULL的形式，最終此分支根本無需計算才對。下面換成NULL測試：

dingjun123@ORADB>   SELECT COUNT(*) FROM t2 WHERE NULL=’T';
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 402395414
——————————————————————–
| Id  | Operation           | Name | Rows  | Cost (%CPU)| Time     |
——————————————————————–
|   0 | SELECT STATEMENT    |      |     1 |     0   (0)|          |
|   1 |  SORT AGGREGATE     |      |     1 |            |          |
|*  2 |   FILTER            |      |       |            |          |
|   3 |    TABLE ACCESS FULL| T2   |  2411K|  9411   (1)| 00:01:53 |
——————————————————————–
Predicate Information (identified by operation id):
—————————————————
   2 – filter(NULL IS NOT NULL)

  換成NULL=’T'後，FILTER自動轉為NULL IS NOT NULL，顯然，這是個永遠不成立的條件，所以，根本無須執行ID=3的操作，最終ID=0的結果COST=0，針對這種FILTER單分支的執行計劃注意，雖然子步驟3COST=9411，但是實際可能沒有執行，詳細的計劃可以用DBMS_XPLAN.DISPLAY_CURSOR檢視：

dingjun123@ORADB> alter session set statistics_level=all;
Session altered.
Elapsed: 00:00:00.04


dingjun123@ORADB> SELECT COUNT(*) FROM t2 WHERE NULL=’T';
  COUNT(*)
———-
         0
1 row selected.
Elapsed: 00:00:00.05

dingjun123@ORADB> @display_cursor
PLAN_TABLE_OUTPUT
————————————————————————————————–
SQL_ID  f33mr91wyuq03, child number 0
————————————-
SELECT COUNT(*) FROM t2 WHERE NULL=’T’
Plan hash value: 402395414
—————————————————————————-
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   |
—————————————————————————-
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |
|*  2 |   FILTER            |      |      1 |        |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| T2   |      0 |   2411K|      0 |00:00:00.01 |
—————————————————————————-
Predicate Information (identified by operation id):
————————————————–
   2 – filter(NULL IS NOT NULL)

20 rows selected.
Elapsed: 00:00:00.74

   透過ID=3步驟的Starts=0,很清晰地看出，最終未執行ID=3的步驟。從這點上說，最佳化器針對”與NULL的相關計算方式還是有很大區別。

 2.問題解決
     透過以上分析，將”改為NULL，問題得到有效解決或者把謂詞寫到每個子SQL裡面。當然，只要是非”的都是可以的，比如’ ‘(空格）。

dingjun123@ORADB> SELECT *
  2  FROM (SELECT t1.subobject_name, t1.object_name, t1.object_type
  3         FROM t1
  4         UNION ALL
  5         SELECT NULL, t2.object_name, t2.object_type
  6         FROM t2
  7         UNION ALL
  8         SELECT t3.subobject_name, NULL, t3.object_type FROM t3) t
  9  WHERE (t.subobject_name = ‘T’ OR t.object_name = ‘T’);


256 rows selected.
Elapsed: 00:00:00.72

Execution Plan
———————————————————-
Plan hash value: 3324794093
——————————————————————————————-
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————-
|   0 | SELECT STATEMENT                   |      | 12746 |  1170K|    92   (0)| 00:00:02 |
|   1 |  VIEW                              |      | 12746 |  1170K|    92   (0)| 00:00:02 |
|   2 |   UNION-ALL                        |      |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID     | T1   | 12678 |   433K|    38   (0)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS    |      |       |       |            |          |
|   5 |      BITMAP OR                     |      |       |       |            |          |
|   6 |       BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|*  7 |        INDEX RANGE SCAN            | I4   |       |       |     1   (0)| 00:00:01 |
|   8 |       BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|*  9 |        INDEX RANGE SCAN            | I1   |       |       |     3   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID     | T2   |    52 |  1716 |    41   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN               | I2   |    52 |       |     3   (0)| 00:00:01 |
|  12 |    TABLE ACCESS BY INDEX ROWID     | T3   |    16 |   176 |    13   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN               | I6   |    16 |       |     1   (0)| 00:00:01 |
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
   7 – access(“T1″.”SUBOBJECT_NAME”=’T')
   9 – access(“T1″.”OBJECT_NAME”=’T')
  11 – access(“T2″.”OBJECT_NAME”=’T')
  13 – access(“T3″.”SUBOBJECT_NAME”=’T')

Statistics
———————————————————-
          1  recursive calls
          0  db block gets
        241  consistent gets
         99  physical reads
          0  redo size
       4006  bytes sent via SQL*Net to client
        602  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        256  rows processed

    ORACLE最佳化器雖然強大，但是最佳化器要判斷的東西實在太多太複雜，導致ORACLE強大的最佳化器也不一定是很完善的，有各種各樣的BUG，或未完善的地方，值得我們注意，但是，相信各種新的版本上，會有更多驚喜的特性。
   注：以上問題，在RBO中同樣存在。

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

相關文章