create table t1 as select * from dba_objects where object_id<=10000;
create table t2 as select * from t1;
create index idx_t1_id on t1(object_id);
create index idx_t1_name on t1(object_name);
create index idx_t2_id on t2(object_id);
create index idx_t2_name on t2(object_name);
exec dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'T1', estimate_percent => 100, cascade=>true,method_opt =>'FOR ALL COLUMNS SIZE AUTO',no_invalidate=>false);
exec dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'T2', estimate_percent => 100, cascade=>true,method_opt =>'FOR ALL COLUMNS SIZE AUTO',no_invalidate=>false);
set autotrace on
set linesize 200
set pagesize 300
or的两边都是直接的条件时,可以使用CONCATENATION
select object_type from t1 where
t1. object_id=2
or
t1. object_name='KOKED1';
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 29 | 2 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | IDX_T1_ID | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 29 | 2 (0)| 00:00:01 |
| 5 | INDEX RANGE SCAN | IDX_T1_NAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
这时如果不走CONCATENATION,效率会差很多
select /*+ no_expand */object_type from t1 where
t1. object_id=2
or
t1. object_name='KOKED1';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 58 | 26 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 58 | 26 (0)| 00:00:01 |
--------------------------------------------------------------------------
但or两边都改成子查询,就没有CONCATENATION出现了,即使加了Hint
select /*+ use_concat */object_type from t1 where
t1. object_id in(select object_id from t2 where object_id=2)
or
t1. object_name in(select object_name from t2 where object_name='KOKED1');
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 883 | 25607 | 26 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 9057 | 256K| 26 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX RANGE SCAN | IDX_T2_ID | 1 | 4 | 1 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | INDEX RANGE SCAN| IDX_T2_NAME | 1 | 18 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------