sql 语句中or条件之种种情况--
sql 语句中or条件之种种情况
create table test_or as select * from dba_objects
create index i_test_or_1 on test_or (object_id)
create index i_test_or_2 on test_or (object_name)
-----------------------------------------------------------------------------------------------------------------------
性能优化群79801530
1、相同字段or条件，转换为inlist 走index range scan
SQL> select * from test_or a
2 where a.object_id=20 or a.object_id=21;
执行计划
----------------------------------------------------------
Plan hash value: 114014695
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 172 | 4 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST_OR_1 | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=20 OR "A"."OBJECT_ID"=21)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
2、不同字段or条件，可分别index scan
SQL> select * from test_or a
2 where a.object_id=20 or a.object_name='ICOL$';
执行计划
----------------------------------------------------------
Plan hash value: 3681382264
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 258 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_OR | 3 | 258 | 3 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_2 | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | I_TEST_OR_1 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."OBJECT_NAME"='ICOL$')
7 - access("A"."OBJECT_ID"=20)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
由于这里的行数很少，oracle 选择了bitmap conversion，事实上相比concatenation也是比较高效的
SQL> select /*+use_concat*/* from test_or a
2 where a.object_id=20 or a.object_name='ICOL$';
执行计划
----------------------------------------------------------
Plan hash value: 1192526883
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 258 | 5 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_2 | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=20)
4 - filter(LNNVL("A"."OBJECT_ID"=20))
5 - access("A"."OBJECT_NAME"='ICOL$')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
3、不同表上or条件，可以看到任然能够正确选择index range scan
SQL> select * from test_or a ,test_or b
2 where a.object_id=b.object_id
3 and (a.object_name='ICOL$'
4 or b.object_name='ICOL$');
执行计划
----------------------------------------------------------
Plan hash value: 2828610916
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 688 | 14 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 2 | 344 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_2 | 2 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 2 | 344 | 7 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | I_TEST_OR_2 | 2 | | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."OBJECT_NAME"='ICOL$')
6 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
7 - filter(LNNVL("B"."OBJECT_NAME"='ICOL$'))
10 - access("A"."OBJECT_NAME"='ICOL$')
11 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
4、or条件中有一个为子查询的情况，无法index scan
SQL> select * from test_or a
2 where a.object_id in (select object_id from test_or b where b.object_id=20)
3 or a.object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 4077212359
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1602 | 134K| 96 (3)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST_OR | 32012 | 2688K| 96 (3)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX RANGE SCAN| I_TEST_OR_1 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."OBJECT_ID"=20 OR EXISTS (SELECT /*+ */ 0 FROM
"TEST_OR" "B" WHERE :B1=20 AND "OBJECT_ID"=:B2))
3 - filter(:B1=20)
4 - access("OBJECT_ID"=:B1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
411 consistent gets
使用use_concat也无效
SQL> select /*+use_concat*/* from test_or a
2 where a.object_id in (select object_id from test_or b where b.object_id=20)
3 or a.object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 4077212359
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1602 | 134K| 96 (3)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST_OR | 32012 | 2688K| 96 (3)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX RANGE SCAN| I_TEST_OR_1 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
5、修改成union可应用index
SQL> select * from test_or a
2 where a.object_id in (select object_id from test_or b where b.object_id=20)
3 union
4 select * from test_or a
5 where a.object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 614230733
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 177 | 7 (58)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 177 | 7 (58)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 91 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | 5 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."OBJECT_ID"=20)
6 - access("A"."OBJECT_ID"=20)
8 - access("A"."OBJECT_ID"=20)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
6、利用precompute_subquery也可利用上索引
SQL> select * from test_or a
2 where a.object_id in (select /*+ precompute_subquery */ object_id from test_or b where b.object_id=20)
3 or a.object_name='ICOL$';
执行计划
----------------------------------------------------------
Plan hash value: 3681382264
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 258 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_OR | 3 | 258 | 3 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_2 | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | I_TEST_OR_1 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."OBJECT_NAME"='ICOL$')
7 - access("A"."OBJECT_ID"=20)
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
7 consistent gets
7、
SQL> select * from test_or a
2 where a.object_id in (select /*+precompute_subquery */ object_id from test_or b where b.object_id=20)
3 or a.object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 2170966137
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=20)
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
6 consistent gets
SQL> select * from test_or a
2 where a.object_id in (select /*+precompute_subquery */ object_id from test_or b where b.object_id=20)
3 or a.object_id=21;
执行计划
----------------------------------------------------------
Plan hash value: 114014695
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 172 | 4 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST_OR_1 | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=20 OR "A"."OBJECT_ID"=21)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
9 consistent gets
可以看到 /*+precompute_subquery */后子查询被转换成了or条件，从而可以使用index
但这将导致的一个问题是recursive calls会随着子查询的结果集增加，如果子查询结果集很大，可能会带来额外的开销
如：
SQL> select * from test_or a
2 where a.object_id in (select /*+precompute_subquery */ object_id from test_or b where b.object_id<2
3 or a.object_id=21;
已选择19行。
执行计划
----------------------------------------------------------
Plan hash value: 114014695
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 1634 | 17 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 19 | 1634 | 17 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST_OR_1 | 19 | | 16 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=2 OR "A"."OBJECT_ID"=3 OR "A"."OBJECT_ID"=4 OR
"A"."OBJECT_ID"=5 OR "A"."OBJECT_ID"=6 OR "A"."OBJECT_ID"=7 OR "A"."OBJECT_ID"=8 OR
"A"."OBJECT_ID"=9 OR "A"."OBJECT_ID"=10 OR "A"."OBJECT_ID"=11 OR "A"."OBJECT_ID"=12
OR "A"."OBJECT_ID"=13 OR "A"."OBJECT_ID"=14 OR "A"."OBJECT_ID"=15 OR
"A"."OBJECT_ID"=16 OR "A"."OBJECT_ID"=17 OR "A"."OBJECT_ID"=18 OR
"A"."OBJECT_ID"=19 OR "A"."OBJECT_ID"=21)
统计信息
----------------------------------------------------------
22 recursive calls
0 db block gets
78 consistent gets
----------------------------------------------------------------------------------------------------------------------SQL> select * from test_or a
2 where a.object_id=20 or a.object_id=21;
执行计划
----------------------------------------------------------
Plan hash value: 114014695
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 172 | 4 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST_OR_1 | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=20 OR "A"."OBJECT_ID"=21)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
2、不同字段or条件，可分别index scan
SQL> select * from test_or a
2 where a.object_id=20 or a.object_name='ICOL$';
执行计划
----------------------------------------------------------
Plan hash value: 3681382264
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 258 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_OR | 3 | 258 | 3 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_2 | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | I_TEST_OR_1 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."OBJECT_NAME"='ICOL$')
7 - access("A"."OBJECT_ID"=20)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
由于这里的行数很少，oracle 选择了bitmap conversion，事实上相比concatenation也是比较高效的
SQL> select /*+use_concat*/* from test_or a
2 where a.object_id=20 or a.object_name='ICOL$';
执行计划
----------------------------------------------------------
Plan hash value: 1192526883
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 258 | 5 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_2 | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=20)
4 - filter(LNNVL("A"."OBJECT_ID"=20))
5 - access("A"."OBJECT_NAME"='ICOL$')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
3、不同表上or条件，可以看到任然能够正确选择index range scan
SQL> select * from test_or a ,test_or b
2 where a.object_id=b.object_id
3 and (a.object_name='ICOL$'
4 or b.object_name='ICOL$');
执行计划
----------------------------------------------------------
Plan hash value: 2828610916
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 688 | 14 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 2 | 344 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_2 | 2 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 2 | 344 | 7 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | I_TEST_OR_2 | 2 | | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."OBJECT_NAME"='ICOL$')
6 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
7 - filter(LNNVL("B"."OBJECT_NAME"='ICOL$'))
10 - access("A"."OBJECT_NAME"='ICOL$')
11 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
4、or条件中有一个为子查询的情况，无法index scan
SQL> select * from test_or a
2 where a.object_id in (select object_id from test_or b where b.object_id=20)
3 or a.object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 4077212359
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1602 | 134K| 96 (3)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST_OR | 32012 | 2688K| 96 (3)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX RANGE SCAN| I_TEST_OR_1 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."OBJECT_ID"=20 OR EXISTS (SELECT /*+ */ 0 FROM
"TEST_OR" "B" WHERE :B1=20 AND "OBJECT_ID"=:B2))
3 - filter(:B1=20)
4 - access("OBJECT_ID"=:B1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
411 consistent gets
使用use_concat也无效
SQL> select /*+use_concat*/* from test_or a
2 where a.object_id in (select object_id from test_or b where b.object_id=20)
3 or a.object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 4077212359
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1602 | 134K| 96 (3)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST_OR | 32012 | 2688K| 96 (3)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX RANGE SCAN| I_TEST_OR_1 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
5、修改成union可应用index
SQL> select * from test_or a
2 where a.object_id in (select object_id from test_or b where b.object_id=20)
3 union
4 select * from test_or a
5 where a.object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 614230733
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 177 | 7 (58)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 177 | 7 (58)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 91 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | 5 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."OBJECT_ID"=20)
6 - access("A"."OBJECT_ID"=20)
8 - access("A"."OBJECT_ID"=20)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
6、利用precompute_subquery也可利用上索引
SQL> select * from test_or a
2 where a.object_id in (select /*+ precompute_subquery */ object_id from test_or b where b.object_id=20)
3 or a.object_name='ICOL$';
执行计划
----------------------------------------------------------
Plan hash value: 3681382264
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 258 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_OR | 3 | 258 | 3 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | I_TEST_OR_2 | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | I_TEST_OR_1 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."OBJECT_NAME"='ICOL$')
7 - access("A"."OBJECT_ID"=20)
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
7 consistent gets
7、
SQL> select * from test_or a
2 where a.object_id in (select /*+precompute_subquery */ object_id from test_or b where b.object_id=20)
3 or a.object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 2170966137
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 1 | 86 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_TEST_OR_1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=20)
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
6 consistent gets
SQL> select * from test_or a
2 where a.object_id in (select /*+precompute_subquery */ object_id from test_or b where b.object_id=20)
3 or a.object_id=21;
执行计划
----------------------------------------------------------
Plan hash value: 114014695
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 172 | 4 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 2 | 172 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST_OR_1 | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=20 OR "A"."OBJECT_ID"=21)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
9 consistent gets
可以看到 /*+precompute_subquery */后子查询被转换成了or条件，从而可以使用index
但这将导致的一个问题是recursive calls会随着子查询的结果集增加，如果子查询结果集很大，可能会带来额外的开销
如：
SQL> select * from test_or a
2 where a.object_id in (select /*+precompute_subquery */ object_id from test_or b where b.object_id<2
3 or a.object_id=21;
已选择19行。
执行计划
----------------------------------------------------------
Plan hash value: 114014695
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 1634 | 17 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OR | 19 | 1634 | 17 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST_OR_1 | 19 | | 16 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=2 OR "A"."OBJECT_ID"=3 OR "A"."OBJECT_ID"=4 OR
"A"."OBJECT_ID"=5 OR "A"."OBJECT_ID"=6 OR "A"."OBJECT_ID"=7 OR "A"."OBJECT_ID"=8 OR
"A"."OBJECT_ID"=9 OR "A"."OBJECT_ID"=10 OR "A"."OBJECT_ID"=11 OR "A"."OBJECT_ID"=12
OR "A"."OBJECT_ID"=13 OR "A"."OBJECT_ID"=14 OR "A"."OBJECT_ID"=15 OR
"A"."OBJECT_ID"=16 OR "A"."OBJECT_ID"=17 OR "A"."OBJECT_ID"=18 OR
"A"."OBJECT_ID"=19 OR "A"."OBJECT_ID"=21)
统计信息
----------------------------------------------------------
22 recursive calls
0 db block gets
78 consistent gets
create table test_or as select * from dba_objects
create index i_test_or_1 on test_or (object_id)
create index i_test_or_2 on test_or (object_name)
-----------------------------------------------------------------------------------------------------------------------
性能优化群79801530
