我觉得 这个 问题 也 很好解决,这样就 ok 了select * from emp where empno = 12345 union select * from emp where empno = 4568 union select * from emp where empno = 7896
SQL> select substr(data, 2 instr(data,',',1,rn)+1, 3 instr(data,',',1,rn+1)-instr(data,',',1,rn)-1) id 4 from (select data,rn from 5 (select ','||'12345,4568,7896'||',' as data from dual) a, 6 (select rownum rn from dual connect by rownum <10) t 7 where t.rn<=length(a.data)-length(replace(a.data,','))-1);ID ---------- 12345 4568 7896
SQL> set autotrace on explain SQL> alter session set optimizer_mode='rule';会话已更改。SQL> select * from scott.emp where empno in (12345,4568,7896);未选定行 执行计划 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | ----------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | CONCATENATION | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | | 3 | INDEX UNIQUE SCAN | PK_EMP | | 4 | TABLE ACCESS BY INDEX ROWID| EMP | | 5 | INDEX UNIQUE SCAN | PK_EMP | | 6 | TABLE ACCESS BY INDEX ROWID| EMP | | 7 | INDEX UNIQUE SCAN | PK_EMP | -----------------------------------------------Note ----- - 'PLAN_TABLE' is old version - rule based optimizer used (consider using cbo)SQL> alter session set optimizer_mode='choose';会话已更改。SQL> select * from scott.emp where empno in (12345,4568,7896);未选定行 执行计划 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 74 | 1 (0)| | 1 | INLIST ITERATOR | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 74 | 1 (0)| | 3 | INDEX UNIQUE SCAN | PK_EMP | 2 | | 1 (0)| ----------------------------------------------------------------------------SQL> alter session set optimizer_mode='all_rows';会话已更改。SQL> select * from scott.emp where empno in (12345,4568,7896);未选定行 执行计划 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 74 | 1 (0)| | 1 | INLIST ITERATOR | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 74 | 1 (0)| | 3 | INDEX UNIQUE SCAN | PK_EMP | 2 | | 1 (0)| ----------------------------------------------------------------------------SQL> alter session set optimizer_mode='first_rows';会话已更改。SQL> select * from scott.emp where empno in (12345,4568,7896);未选定行 执行计划 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 74 | 1 (0)| | 1 | INLIST ITERATOR | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 74 | 1 (0)| | 3 | INDEX UNIQUE SCAN | PK_EMP | 2 | | 1 (0)| ----------------------------------------------------------------------------SQL> SQL> SQL>
我觉得 这个 问题 也 很好解决,这样就 ok 了select *
from emp
where empno = 12345
union
select *
from emp
where empno = 4568
union
select * from emp where empno = 7896
2 instr(data,',',1,rn)+1,
3 instr(data,',',1,rn+1)-instr(data,',',1,rn)-1) id
4 from (select data,rn from
5 (select ','||'12345,4568,7896'||',' as data from dual) a,
6 (select rownum rn from dual connect by rownum <10) t
7 where t.rn<=length(a.data)-length(replace(a.data,','))-1);ID
----------
12345
4568
7896
SQL> set autotrace on explain
SQL> alter session set optimizer_mode='rule';会话已更改。SQL> select * from scott.emp where empno in (12345,4568,7896);未选定行
执行计划
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP |
| 3 | INDEX UNIQUE SCAN | PK_EMP |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP |
| 5 | INDEX UNIQUE SCAN | PK_EMP |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP |
| 7 | INDEX UNIQUE SCAN | PK_EMP |
-----------------------------------------------Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)SQL> alter session set optimizer_mode='choose';会话已更改。SQL> select * from scott.emp where empno in (12345,4568,7896);未选定行
执行计划
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 1 (0)|
| 1 | INLIST ITERATOR | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 74 | 1 (0)|
| 3 | INDEX UNIQUE SCAN | PK_EMP | 2 | | 1 (0)|
----------------------------------------------------------------------------SQL> alter session set optimizer_mode='all_rows';会话已更改。SQL> select * from scott.emp where empno in (12345,4568,7896);未选定行
执行计划
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 1 (0)|
| 1 | INLIST ITERATOR | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 74 | 1 (0)|
| 3 | INDEX UNIQUE SCAN | PK_EMP | 2 | | 1 (0)|
----------------------------------------------------------------------------SQL> alter session set optimizer_mode='first_rows';会话已更改。SQL> select * from scott.emp where empno in (12345,4568,7896);未选定行
执行计划
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 1 (0)|
| 1 | INLIST ITERATOR | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 74 | 1 (0)|
| 3 | INDEX UNIQUE SCAN | PK_EMP | 2 | | 1 (0)|
----------------------------------------------------------------------------SQL>
SQL>
SQL>