解决方案 »
- EXP-00056: 遇到 ORACLE 错误 12170导出数据库遇到错误,新手,希望遇到大神指点。
- load data infile方式数据无法正确导入的问题导入数据中有0开头数据不对
- select * from dept ,dept e各位老大帮帮忙吧
- oracle em 无法连上
- 查询最新发布的四条记录,一SQL语句?
- 在过程中:如何在table1中插入table2的数据?
- oracle 10g的em不能用!!!高手救命!!!!
- 请问在一个已经有10000多条记录的表里可不可以再增加或删除一个字段?修改一个字段的属性?要注意些什么?
- 请教:ORA00439,对象模式未启动,咋办(ORA.8.1.7)
- 十分法,如何取加和为一定值的N条记录
- myeclipse database explorer链接oracle10g出现异常
- 存储过程 行列转置
我觉得 这个 问题 也 很好解决,这样就 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>