这样一条sql应该怎么优化?
select * from sys_user
where user_code = 'zhangyong'
or user_code in
(select grp_code
from sys_grp
where sys_grp.user_code = 'zhangyong')

Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=RULE
1  0   FILTER
2  1     TABLE ACCESS (FULL) OF 'SYS_USER'
3  1     INDEX (UNIQUE SCAN) OF 'PK_SYS_GRP' (UNIQUE)

Statistics
----------------------------------------------------------
14  recursive calls
4  db block gets
30590 consistent gets
0  physical reads
0  redo size
1723  bytes sent via SQL*Net to client
425  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
3  rows processed

里面的查询返回的记录数一般只有一两条，但sys_user表的数据很多，怎么样才能让这条sql以sys_grp为驱动表？
表中记录情况如下:

SQL> select count(*) from sys_grp;
COUNT(*)----------25130
SQL> select count(*) from sys_user;
COUNT(*)
----------
15190

优化:
降低逻辑读是优化SQL的基本原则之一
我们尝试通过降低逻辑读来加快SQL的执行.
这里我们使用or展开来改写SQL查询:

select * from sys_user where user_code = 'zhangyong'
union all
select * from sys_user where user_code <> 'zhangyong'
and user_code in (select grp_code from sys_grp where sys_grp.user_code = 'zhangyong')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         130 consistent gets
          0  physical reads
          0  redo size
       1723  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   UNION-ALL
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SYS_USER'
   3    2       INDEX (UNIQUE SCAN) OF 'PK_SYS_USER' (UNIQUE)
   4    1     NESTED LOOPS
   5    4       VIEW OF 'VW_NSO_1'
   6    5         SORT (UNIQUE)
   7    6           TABLE ACCESS (BY INDEX ROWID) OF 'SYS_GRP'
   8    7             INDEX (RANGE SCAN) OF 'FK_SYS_USER_CODE' (NON-UNIQUE)
   9    4       TABLE ACCESS (BY INDEX ROWID) OF 'SYS_USER'
  10    9         INDEX (UNIQUE SCAN) OF 'PK_SYS_USER' (UNIQUE)  
   
我们注意到,通过改写,逻辑读减少到130，从30590到130这是一个巨大的提高,减少逻辑读最终会减少资源消耗,提高SQL的执行效率.
这个改写把Filter改为了Nest LOOP,索引得以充分利用.从而大大提高了性能.
我们同时注意到，这里引入了一个排序
排序来自于这一步:

-----------------------------------------------------------------------------------------
6  5  SORT (UNIQUE)
7  6    TABLE ACCESS (BY INDEX ROWID) OF 'SYS_GRP'
8  7       INDEX (RANGE SCAN) OF 'FK_SYS_USER_CODE' (NON-UNIQUE)
------------------------------------------------------------------------------------------
在'SYS_GRP'表中，user_code 是非唯一键值
在in值判断里，要做sort unique排序，去除重复值
这里的union all是不需要排序的

问题答案都在一起了 不好玩

前几天遇到一个问题。
一个存储过程 使用REF CURSOR作为返回值
把SQL单拿出来，很快 1秒左右 如果放在存储过程里调用 就要用26-32秒。

后来发现我使用其他方法 替换了OR 在存储过程中也能在一秒中得到结果。

个人分析：
由于OR会引起执行计划的混乱。所以剃掉Or 使其向效率高的执行计划上偏移。

碰到or的时候，如果执行计划有问题，可以考虑使用union all。
但是具体情况还是要具体分析。
分析执行计划还是基本功啊？

请问，非相关子查询Oracle会怎么处理？
是先将子查询作一遍得到结果，然后父查询中的每一行数据直接应用这结果？还是对于父查询中的每一行数据，都要重新进行一次子查询？

张，这个问题很复杂，细细看看我的帖子：6篇关于semi join和anti join。
这对我们的优化非常重要。

SQL> create table scott.tabcol as select owner,table_name,column_name,data_type,data_type_mod,data_type_owner from dba_tab_cols;

Table created.

SQL> create table scott.tabgrp as select column_name from dba_tab_cols group by column_name;

Table created.

SQL> alter table tabgrp add constraint pk_column primary key(column_name);
SQL> exec dbms_stats.gather_index_stats('SCOTT','PK_COLUMN');
SQL> exec dbms_stats.gather_table_stats('SCOTT','TABGRP');
SQL> create index idx_column on tabcol(column_name);
SQL> exec dbms_stats.gather_index_stats('SCOTT','IDX_COLUMN');
SQL> exec dbms_stats.gather_table_stats('SCOTT','TABCOL');

set autot trace exp stat;
select * from tabcol
where column_name = 'NAME'
or column_name in
(select column_name
from tabgrp
where column_name = 'NAME');

Execution Plan
----------------------------------------------------------
Plan hash value: 339360689

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |  2130 | 85200 |    74   (2)| 00:00:01 |
|*  1 |  FILTER             |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL | TABCOL    | 42311 |  1652K|    74   (2)| 00:00:01 |
|*  3 |   FILTER            |           |       |       |            |          |
|*  4 |    INDEX UNIQUE SCAN| PK_COLUMN |     1 |    12 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COLUMN_NAME"='NAME' OR  EXISTS (SELECT /*+ */ 0 FROM
              "TABGRP" "TABGRP" WHERE :B1='NAME' AND "COLUMN_NAME"=:B2))
   3 - filter(:B1='NAME')
   4 - access("COLUMN_NAME"=:B1)


select t1.* from tabcol t1,(select column_name from tabgrp where column_name = 'NAME') t2
where t1.column_name = t2.column_name and t1.column_name = 'NAME';

Execution Plan
----------------------------------------------------------
Plan hash value: 2228261001

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    52 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |            |     1 |    52 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN          | PK_COLUMN  |     1 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| TABCOL     |     1 |    40 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_COLUMN |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COLUMN_NAME"='NAME')
   4 - access("T1"."COLUMN_NAME"='NAME')
       filter("T1"."COLUMN_NAME"="COLUMN_NAME")

select * from sys_user
where user_code = 'zhangyong'
or user_code in
(select grp_code
from sys_grp
where sys_grp.user_code = 'zhangyong')

Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=RULE
1  0   FILTER
2  1     TABLE ACCESS (FULL) OF 'SYS_USER'
3  1     INDEX (UNIQUE SCAN) OF 'PK_SYS_GRP' (UNIQUE)

故意使用RBO?如果换成CBO呢？