union 代替or的情况

来源：互联网发布：mac 安装复杂编辑：程序博客网时间：2024/04/28 12:26
技巧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 client125  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)       1859  rows processed将SQL改写成UNION形式：SQL> select * from test1 where owner='SCOTT' unionselect * 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 client125  SQL*Net roundtrips to/from client  1  sorts (memory)  0  sorts (disk)       1859  rows processed逻辑读从475201降到1131，性能提升非常明显。
0 0