bianxq
- 浏览: 94566 次
- 性别:
- 来自: 福州
-
最新评论
-
JonHans：
...
ORALCE /*+NO_EXPAND*/ 含义 -
sangli：
Alter table table parallel 4;
...
oracle parallel execution example -
Ivan.t：
不会这么巧吧？你是银钦？http://ivanstudy.bl ...
Oracle Raw，number，varchar2...转换 -
Christ：
那么，如何使用Hibernate存取RAW？显然 我无法使用u ...
Oracle Raw，number，varchar2...转换 -
bianxq：
执行计划没有变化，说明你的并行提示被忽略掉了。检查你的书写和系 ...
oracle parallel execution example
sql 语句中or条件之种种情况
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
分享到：
发表评论
-
[译] PL/SQL 格式化指南 (PL/SQL Formatting Guide)
2009-11-30 17:03 1715最近一直在修改以前同事写的Oracle存储过程，由于编码不规范 ... -
Oracle Raw，number，varchar2...转换
2009-09-17 11:24 7851Oracle Raw，number，varchar2... ... -
Oracle's Query Transformer
2009-09-09 17:56 1138Oracle's Query Transformer O ... -
观察analyze table compute statistics 都对什么对象统计了信息
2009-09-08 12:52 10454观察analyze table compute statis ... -
/*+ precompute_subquery */子查询中的提示
2009-09-08 10:51 1508QUOTE:------------------------- ... -
关于绑定变量的一点心得
2009-09-08 10:03 1289我们一直在告诉开发人员一定要使用绑定变量，而你是否真正了解 ... -
push_subq提示
2009-09-06 12:17 1304PUSH_SUBQ 可以用来控制子查询的执行 这个是PUSH_ ... -
查询--驱动表
2009-08-24 10:58 1242查询中何为驱动表阿？ ... -
SQL连接驱动表帖子
2009-08-24 10:08 2462http://www.itpub.net/v ... -
Oracle中巧用CTAS快速建立表格
2009-08-21 15:55 1417CTAS是通过查询，然后根 ... -
优化SQL语句的一些规则
2009-08-20 21:01 1443大家都在讨论关于数据 ... -
oracle中关于in和exists，not in 和 not exists、关联子查询、非关联子查询
2009-08-20 15:01 5315oracle中关于in和e ... -
Oracle的大表,小表与全表扫描
2009-08-20 11:11 1347通常对于小表，Oracle建议通过全表扫描进行数据访问，对于大 ... -
reverse函数与like % 的使用
2009-08-19 18:03 1243oracle 提供一个reverse函数,可以实现将一个对象反 ... -
Oracle语句优化规则汇总(二)
2009-08-19 15:14 7231. 用UNION替换OR (适用于 ... -
Oracle语句优化规则汇总(一)
2009-08-19 15:07 876Oracle sql 性能优化调整 1. 选用适合的OR ... -
几种索引扫描方式
2009-08-19 14:47 15571）索引唯一扫描 如果查询时是通过unique或primary ... -
oracle不使用索引原因定位
2009-08-19 11:36 1681较典型的问题有：有时，表明明建有索引，但查询过程显然没有 ... -
列定义是否为空对COUNT（*）操作索引选择的影响
2009-08-19 11:28 1256SQL> desc test; Name ... -
ORALCE /*+NO_EXPAND*/ 含义
2009-08-18 11:02 3047求教 ORALCE /*+NO_EXPAND*/ 含义 是什么 ...
相关推荐
SQL语句是数据库操作的核心，它用于查询、插入、更新和删除数据，是任何数据库管理系统中的基础工具。在IT行业中，编写SQL语句是一项必备技能，但手动编写和调试SQL语句可能会耗费大量时间和精力，尤其在处理复杂...
VisualC 实效编程 85 SQL语句中设置时段检索条件VisualC 实效编程 85 SQL语句中设置时段检索条件VisualC 实效编程 85 SQL语句中设置时段检索条件VisualC 实效编程 85 SQL语句中设置时段检索条件VisualC 实效编程 85 ...
PB脚本中SQL语句写法与SQL中语句写法对照 PB脚本中SQL语句写法与SQL中语句写法对照是非常重要的...PB脚本中SQL语句写法与SQL中语句写法对照是非常重要的知识点，它们之间的差异和相似之处都需要我们认真研究和学习。
sql语句sql语句sql语句sql语句sql语句
OracleSqlConvert4MysqlSqlTool.java这个源码工具，根据描述，应该是实现了自动读取Oracle SQL语句，分析其结构，并根据MySQL的语法规则进行转换，然后将转换后的SQL语句保存到指定的目标文件中。这个工具简化了手动...
在上面的代码中，我们首先构建了一个基本的 SQL 语句，然后根据条件添加过滤条件。 执行 SQL 语句 执行 SQL 语句可以使用 Python 的 MySQL 连接器模块实现。例如： ``` res = query(sql) ``` 在上面的代码中，我们...
sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明...
根据提供的文件信息，我们可以深入探讨如何通过条件拼接来构造SQL查询语句，这在实际开发中极为常见。 ### SQL语句拼接的基础概念 SQL（Structured Query Language）是一种用于管理关系型数据库的标准语言，其主要...
在Java编程中，调试SQL语句是开发过程中的常见任务，尤其当面对复杂且冗长的查询时。为了提高效率并使SQL语句更易于理解和分析，格式化SQL语句显得尤为重要。标题提及的"Java打印漂亮的SQL语句(被格式化的SQL语句)...
在某些情况下，我们需要在 Hibernate DAO 中执行原生 SQL 语句，这时我们可以使用 `HibernateCallback` 接口来实现该功能。 例如，我们可以使用以下代码来执行一个原生 SQL 语句： ```java public Object ...
### ArcGIS中的SQL语句详解 #### 一、SQL在ArcGIS中的角色与应用 SQL，全称为Structured Query Language，即结构化查询语言，是专为数据库设计的一种标准语言，用于管理和操作数据库中的数据。在ArcGIS环境中，SQL...
动态SQL是指在运行时才能确定其具体内容的SQL语句，它允许用户根据不同的条件构造不同的查询或更新操作。 #### 描述分析 描述部分提到“动态的实现表名的动态的配置及动态的配置所对应的列名”，这进一步明确了本文...
1. 动态SQL：在C#中，你可以使用字符串操作生成动态SQL语句，这在需要根据条件生成不同结构的SQL时非常有用。例如，你可以根据用户的选择决定是否在WHERE子句中包含某个条件。 2. 存储过程：另一种方法是使用SQL ...
在SQL的SELECT语句中，可以使用各种运算符和通配符来过滤结果，如`=`, `, `>`, `LIKE`, `%`, `_` 等。例如，`LIKE '%find this%'` 查找包含"find this"的字符串，而`LIKE '[a-zA-Z]%'` 查找以字母开头的字符串。同时...
尽管如此，我将基于标题和描述中提供的关键词“Effective MySQL之SQL语句最优化”来构建知识点。 1. SQL语句最优化的概念：在数据库管理中，对SQL语句进行优化是提高数据库性能的关键环节。最优化的SQL语句能够在...
DISTINCT 控制语句是 SQL 中最常用的控制语句之一，它允许我们从数据库中读取一个或多个栏位的所有资料，並且去掉重复的值。DISTINCT 控制语句的语法如下： SELECT DISTINCT "栏位名" FROM "表格名" 例如，要在 ...
例如，要在Store_Information表格中找出所有不同的店名，可以使用以下SQL语句： SELECT DISTINCT store_name FROM Store_Information ### WHERE指令 WHERE指令让我们能够选择性地抓取资料。WHERE指令的语法结构...
比如，我们可以使用`<if>`、`<choose>`、`<when>`、`<otherwise>`、`<where>`等标签来根据条件动态地插入、删除或修改SQL语句中的部分。 2. **MappedStatement**：每个SQL语句在MyBatis中都被封装成一个...
SQLTracker是一款专为数据库操作监控设计的工具，它在IT领域中主要用于跟踪和记录SQL语句的执行情况。SQL（Structured Query Language）是用于管理关系数据库的编程语言，包括查询、更新、插入和删除数据等操作。SQL...
在易语言中，编写SQL语句与在其他编程语言中的方式有所不同，因为它提供了专门的数据库模块来支持SQL操作。 二、动态拼接原理 动态拼接SQL语句的主要目的是为了提高程序的灵活性，使得可以根据不同的条件或参数...