sql 優化過程之union 替換 or

在網上看了一些sql優化的文章，其中有一條就是使用union 替換 or。原因是在索引列上使用or，會使索引失效，從而走全表掃描。說的比較籠統，我今天特意試驗了一下：在什麼情況下可以用union 替換 or才會使索引生效。

create table t as select * from all_objects;

然後又在t上新增了一列id，使用object_id填充的。

create index ind_t_id on t(id);只在id上建立索引。

set autotrace traceonly

select * from t where object_id=10 or id=20;

SQL> select * from t where object_id=10 or id=20;

未選定行

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1539 | 229 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 9 | 1539 | 229 (1)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=10 OR "ID"=20)

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
815 consistent gets
0 physical reads
0 redo size
1176 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
這種情況下索引失效。

select * from t where object_id=10

union

select * from id=20;

SQL> select * from t where object_id=10
2 union
3 select * from t where id=20;

未選定行

執行計劃
----------------------------------------------------------
Plan hash value: 298853808

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

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

| 0 | SELECT STATEMENT | | 10 | 1710 | 231 (2)|
00:00:03 |

| 1 | SORT UNIQUE | | 10 | 1710 | 231 (2)|
00:00:03 |

| 2 | UNION-ALL | | | | |
|

|* 3 | TABLE ACCESS FULL | T | 9 | 1539 | 228 (1)|
00:00:03 |

| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 171 | 1 (0)|
00:00:01 |

|* 5 | INDEX RANGE SCAN | IND_T_ID | 1 | | 1 (0)|
00:00:01 |

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

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

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

3 - filter("OBJECT_ID"=10)
5 - access("ID"=20)

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
13 recursive calls
0 db block gets
958 consistent gets
0 physical reads
0 redo size
1176 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

可以發現union替換or之後，索引生效。

然後我在object_id上建立索引,再看看情況是什麼樣的：

create index ind_t on t(object_id);

select * from t where object_id=10 or id=20;

SQL> select * from t where object_id=10 or id=20;

未選定行

執行計劃
----------------------------------------------------------
Plan hash value: 3010370457

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |

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

| 0 | SELECT STATEMENT | | 9 | 1539 | 148 (0
)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID | T | 9 | 1539 | 148 (0
)| 00:00:02 |

| 2 | BITMAP CONVERSION TO ROWIDS | | | |
| |

| 3 | BITMAP OR | | | |
| |

| 4 | BITMAP CONVERSION FROM ROWIDS| | | |
| |

|* 5 | INDEX RANGE SCAN | IND_T | | | 1 (0
)| 00:00:01 |

| 6 | BITMAP CONVERSION FROM ROWIDS| | | |
| |

|* 7 | INDEX RANGE SCAN | IND_T_ID | | | 1 (0
)| 00:00:01 |

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

可以發現這種情況下，即使使用了or，索引還是有效地。

當然這種情況使用union也是索引有效地。

結論：

有索引和無索引的一列 進行or連線時。使用union替換or會使索引生效。而兩列都有索引時。or和union都是可以使用索引的。

[@more@]

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