where type in(1,2,3,4,5,6)这里的条件就是一个字符串了 exists要怎么使用?
where type in(1,2,3,4,5,6)这里的1,2,3,4,5,6是参数带入的,有可能1,4,5,6,7,8,9,12,14......参数很多,这样呢?会不会很慢? type是索引
select * from emp where empno in(7369,7788); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------------- ---------- ---------- --------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00.00000 800 900 20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00.00000 3000 900 20 执行计划 ---------------------------------------------------------- Plan hash value: 2441457562---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 76 | 2 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 76 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | BIN$btQk2mJNTzWasNJXoOSNnw==$2 | 2 | | 1 (0)| 00:00 ----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("EMPNO"=7369 OR "EMPNO"=7788)
...用动态execute immediate 'select * from tb where id in'||参数;
这个就更不懂了,我只是组装sql语句到数据库里执行的
exists的用法是在这样子的: select a1 from A where exists (select 1 from B where ....) 就是说,要想查询a1,必须先满足exists后面的子查询条件。 其实楼主的这个,直接用in就可以了,如果楼主的(1,2,3...)这些参数是从表中抽出来的话,楼主可以用exists来替换in
where type in(1,2,3,4,5,6)啥要求?
exists要怎么使用?
type是索引
select * from emp where empno in(7369,7788); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------------- ---------- ---------- ---------
7369 SMITH CLERK 7902 1980-12-17 00:00:00.00000 800 900 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00.00000 3000 900 20
执行计划
----------------------------------------------------------
Plan hash value: 2441457562----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 76 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | BIN$btQk2mJNTzWasNJXoOSNnw==$2 | 2 | | 1 (0)| 00:00
----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 3 - access("EMPNO"=7369 OR "EMPNO"=7788)
select a1 from A where exists (select 1 from B where ....)
就是说,要想查询a1,必须先满足exists后面的子查询条件。
其实楼主的这个,直接用in就可以了,如果楼主的(1,2,3...)这些参数是从表中抽出来的话,楼主可以用exists来替换in