1.問題SQL

原SQL：

SELECT OID, MNUM, ORDERID, DELEGATEID, TO_CHAR(GROUPID) GROUPID, SUBRECDEFID
FROM JUDGE_TASK
WHERE ORDERID = '171231208465729706'
   OR MNUM = '171231208465729706'
  AND CITY = 171;

執行計劃走了 BITMAP CONVERSION TO ROWIDS，Oracle對 OR 條件做了位圖轉換，效率不是很高。

BITMAP CONVERSION FROM ROWIDS：對于普通 B*樹索引，Oracle 也可以將數據記錄的 ROWID 映射成一個位圖，然后進行位圖操作。

BITMAP CONVERSION TO ROWIDS：將位圖映射為ROWID。在一個位圖鍵值中，包含了一批數據記錄的起始地址和結束地址，且這批記錄是連續的，因此位圖中的每一個位就按序對應了一條數據記錄。

SQL> SELECT OID,MNUM, ORDERID, DELEGATEID ,TO_CHAR(GROUPID) GROUPID,
  2  SUBRECDEFID FROM JUDGE_TASK WHERE ORDERID = '314231016343245850'  OR MNUM = '314231016343245850'  AND
  3  CITY = 314;

1 row selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 534055191

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                       |     1 |    57 |   729   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL                       |                       |     1 |    57 |   729   (0)| 00:00:01 |     1 |   364 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| JUDGE_TASK             |     1 |    57 |   729   (0)| 00:00:01 |     1 |   364 |
|   3 |    BITMAP CONVERSION TO ROWIDS             |                       |       |       |            |          |       |       |
|   4 |     BITMAP OR                              |                       |       |       |            |          |       |       |
|   5 |      BITMAP CONVERSION FROM ROWIDS         |                       |       |       |            |          |       |       |
|*  6 |       INDEX RANGE SCAN                     | IDX_JUDGE_TASK_ORDERID |       |       |   292   (0)| 00:00:01 |     1 |   364 |
|   7 |      BITMAP CONVERSION FROM ROWIDS         |                       |       |       |            |          |       |       |
|*  8 |       INDEX RANGE SCAN                     | IDX_JUDGE_TASK_MNUM |       |       |   437   (0)| 00:00:01 |     1 |   364 |
------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("ORDERID"='314231016343245850' OR "MNUM"='314231016343245850' AND "CITY"=314)	<<<<
   6 - access("ORDERID"='314231016343245850')
   8 - access("MNUM"='314231016343245850')


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

2.優化措施

核心思路：用 UNION ALL 拆分 OR 條件，讓每個子查詢單獨走索引，提高訪問效率。

2.1 錯誤改寫SQL

讀SQL的邏輯可能是想從JUDGE_TASK表中取出ORDERID = '314231016343245850' OR MNUM = '314231016343245850'并且CITY=171的數據，該寫如下：

SELECT OID, MNUM, ORDERID, DELEGATEID, TO_CHAR(GROUPID) GROUPID, SUBRECDEFID
FROM JUDGE_TASK
WHERE ORDERID = '171231208465729706'
  AND CITY = 171
UNION ALL
SELECT OID, MNUM, ORDERID, DELEGATEID, TO_CHAR(GROUPID) GROUPID, SUBRECDEFID
FROM JUDGE_TASK
WHERE MNUM = '171231208465729706'
  AND CITY = 171;


Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3419299962

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                       |     2 |   114 |    60   (0)| 00:00:01 |       |       |
|   1 |  UNION-ALL                                  |                       |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR                  |                       |     1 |    57 |    24   (0)| 00:00:01 |   113 |   141 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| JUDGE_TASK             |     1 |    57 |    24   (0)| 00:00:01 |   113 |   141 |
|*  4 |     INDEX RANGE SCAN                        | IDX_JUDGE_TASK_ORDERID |     1 |       |    24   (0)| 00:00:01 |   113 |   141 |
|   5 |   PARTITION RANGE ITERATOR                  |                       |     1 |    57 |    36   (0)| 00:00:01 |   113 |   141 |
|*  6 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| JUDGE_TASK             |     1 |    57 |    36   (0)| 00:00:01 |   113 |   141 |
|*  7 |     INDEX RANGE SCAN                        | IDX_JUDGE_TASK_MNUM |     1 |       |    35   (0)| 00:00:01 |   113 |   141 |
-------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("CITY"=314)
   4 - access("ORDERID"='314231016343245850') <<<<<<
   6 - filter("CITY"=314)
   7 - access("MNUM"='314231016343245850') <<<<<


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

該寫完和原來SQL一對結果一致，邏輯讀從1000降到了158，效果很好了。我在統計時還在計算優化效率

邏輯讀 由 1017 -> 158  

平均每10h 35億個邏輯讀，預計降低 (1017-158)/1017=84.4%

一天預計可從80億減少到12.4億
 80 * （1-84.4%）= 12.4億

附：and 和 or 的優先級比較

過了一段時間這個SQL也沒有實施，在OB上碰到一個查詢轉換的hint想拿來試試發現了問題，sql中and優先級好像比or高，證明一下

-- and 和 or的優先級比較
-- 反證法
-- 假設or的優先級比and高
a.
select 1 from dual where 1=1 or 1=1 and 1=2;-- 有結果

b.
select 1 from dual where 1=1 and 1=2 union ALL
select 1 from dual where 1=1 and 1=2; -- 無結果

c.
select 1 from dual where 1=1 union ALL
select 1 from dual where 1=1 and 1=2; -- 有結果

d.
select 1 from dual where (1=1 or 1=1) and 1=2; -- 無結果

e.
select 1 from dual where 1=1 or (1=1 and 1=2);-- 有結果

假設or的優先級比and高， 也就是說在沒有括號的情況下，Oracle 會先計算 OR，再計算 AND。

那么a成立那么d一定成立， 但實際返回結果，與假設矛盾，推到,得證 Oracle 中 AND 優先于 OR。

這個好像很冷門問了很多同事都沒了解過。

2.2 正確改寫SQL

SQL因為可讀性很差被誤導了，實際上and優先級高于or，應該將ORDERID = '171231208465729706' AND CITY = 171 UNION ALL ... MNUM = '171231208465729706' AND CITY = 171;改成ORDERID = '171231208465729706' UNION ALL ... MNUM = '171231208465729706' AND CITY = 171;

SQL> SQL> SELECT OID, MNUM, ORDERID, DELEGATEID, TO_CHAR(GROUPID) AS GROUPID, SUBRECDEFID
  2  FROM TBCS.JUDGE_TASK
  3  WHERE ORDERID = '171231208465729706'
  4  UNION ALL
  5  SELECT OID, MNUM, ORDERID, DELEGATEID, TO_CHAR(GROUPID) AS GROUPID, SUBRECDEFID
  6  FROM TBCS.JUDGE_TASK
  7  WHERE MNUM = '171231208465729706' AND CITY = 171
  8    AND ORDERID <> '171231208465729706'; 
Elapsed: 00:00:00.00

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

Plan hash value: 1123629201

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                       |     2 |   110 |   328   (0)| 00:00:01 |       |       |
|   1 |  UNION-ALL                                  |                       |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ALL                       |                       |     1 |    53 |   292   (0)| 00:00:01 |     1 |   364 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| JUDGE_TASK             |     1 |    53 |   292   (0)| 00:00:01 |     1 |   364 |
|*  4 |     INDEX RANGE SCAN                        | IDX_JUDGE_TASK_ORDERID |     1 |       |   292   (0)| 00:00:01 |     1 |   364 |
|   5 |   PARTITION RANGE ITERATOR                  |                       |     1 |    57 |    36   (0)| 00:00:01 |   169 |   197 |
|*  6 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| JUDGE_TASK             |     1 |    57 |    36   (0)| 00:00:01 |   169 |   197 |
|*  7 |     INDEX RANGE SCAN                        | IDX_JUDGE_TASK_MNUM |     1 |       |    35   (0)| 00:00:01 |   169 |   197 |
-------------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("ORDERID"='171231208465729706')
   6 - filter("CITY"=171 AND "ORDERID"<>'171231208465729706')
   7 - access("MNUM"='171231208465729706')


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

邏輯讀 由 1017 -> 609  
平均每10h 35億個邏輯讀，預計降低 (1017-609)/1017=40.1%

一天預計可從80億減少到47.92億
 80 * （1-40.1%）= 47.92億

2.3 HINT 改寫 - USE_CONCAT

其實可以不用改寫SQL，HINT中提供了一個/*+ USE_CONCAT */，強制優化器使用 UNION ALL 運算符將 OR 條件轉換為復合查詢。

SQL> SELECT/*+ USE_CONCAT */ OID,
  2         MNUM,
  3         ORDERID,
  4         DELEGATEID,
  5         TO_CHAR(GROUPID) GROUPID,
  6         SUBRECDEFID
  7    FROM JUDGE_TASK
  8   WHERE ORDERID = '171231208465729706'
  9      OR MNUM = '171231208465729706'
 10     AND CITY = 171;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 46570055

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                       |     2 |   114 |   328   (0)| 00:00:01 |       |       |
|   1 |  CONCATENATION                              |                       |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR                  |                       |     1 |    57 |    36   (0)| 00:00:01 |   169 |   197 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| JUDGE_TASK             |     1 |    57 |    36   (0)| 00:00:01 |   169 |   197 |
|*  4 |     INDEX RANGE SCAN                        | IDX_JUDGE_TASK_MNUM |     1 |       |    35   (0)| 00:00:01 |   169 |   197 |
|   5 |   PARTITION RANGE ALL                       |                       |     1 |    57 |   292   (0)| 00:00:01 |     1 |   364 |
|*  6 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| JUDGE_TASK             |     1 |    57 |   292   (0)| 00:00:01 |     1 |   364 |
|*  7 |     INDEX RANGE SCAN                        | IDX_JUDGE_TASK_ORDERID |     1 |       |   292   (0)| 00:00:01 |     1 |   364 |
-------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("CITY"=171)
   4 - access("MNUM"='171231208465729706')
   6 - filter(LNNVL("MNUM"='171231208465729706') OR LNNVL("CITY"=171))
   7 - access("ORDERID"='171231208465729706')


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

3.優化效果對比

原邏輯讀：80億/天，優化后（40%降低）：80 * (1-0.401) ≈ 47.92億。