技巧2：union 代替or的情况
当SQL语句中，or 条件上面有一个为子查询，并且子查询上的表与源表不同，这个时候就可以用union代替or或者你发现执行计划中的 filter 有 or 并且 or 后面跟上子查询
(EXISTS...)的时候就要注意，比如：
2 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' OR
"T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e' OR EXISTS (SELECT 0 FROM "KHGL_KHYWDLXX" "Y" WHERE "Y"."KH_ID"=:B1 AND
"Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e'))
当然了，当你看到operation中的filter也应该要注意这些
示例如下(请自己动手实验)：
create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects;
create index idx1 on test1(object_id);
create index idx2 on test1(owner);
create index idx3 on test2(object_id);
create index idx4 on test2(owner);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST1',
estimate_percent => 100,
method_opt => 'for columns owner size 200',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST2',
estimate_percent => 100,
method_opt => 'for columns owner size 200',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/
比如这个SQL：
select * from test1 where owner='SCOTT' or object_id in(select object_id from test2 where owner='SCOTT');
执行计划如下：
select * from test1 where owner='SCOTT' or object_id in(select object_id from test2 where owner='SCOTT');
1859 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4136318878
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5493 | 520K| 296 (1)| 00:00:04 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TEST1 | 74533 | 7060K| 296 (1)| 00:00:04 |
| 3 | BITMAP CONVERSION TO ROWIDS | | 1 | 19 | 2 (0)| 00:00:01 |
| 4 | BITMAP AND | | | | | |
| 5 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 6 | INDEX RANGE SCAN | IDX3 | 1860 | | 1 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 8 | INDEX RANGE SCAN | IDX4 | 1860 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SCOTT' OR EXISTS (SELECT 0 FROM "TEST2" "TEST2" WHERE
"OBJECT_ID"=:B1 AND "OWNER"='SCOTT'))
6 - access("OBJECT_ID"=:B1)
8 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
475201 consistent gets
0 physical reads
0 redo size
70860 bytes sent via SQL*Net to client
1772 bytes received via SQL*Net from client
125 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1859 rows processed
将SQL改写成UNION形式：
SQL> select * from test1 where owner='SCOTT'
union
select * from test1 where object_id in(select object_id from test2 where owner='SCOTT'); 2 3
1859 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1667050602
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3719 | 386K| | 502 (81)| 00:00:07 |
| 1 | SORT UNIQUE | | 3719 | 386K| 553K| 502 (81)| 00:00:07 |
| 2 | UNION-ALL | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | TEST1 | 1859 | 176K| | 55 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX2 | 1859 | | | 5 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 1860 | 210K| | 352 (1)| 00:00:05 |
| 6 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1860 | 35340 | | 55 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX4 | 1860 | | | 5 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TEST1 | 74533 | 7060K| | 296 (1)| 00:00:04 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OWNER"='SCOTT')
5 - access("OBJECT_ID"="OBJECT_ID")
7 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1131 consistent gets
0 physical reads
0 redo size
79068 bytes sent via SQL*Net to client
1772 bytes received via SQL*Net from client
125 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1859 rows processed
逻辑读从475201降到1131，性能提升非常明显。
