- 浏览: 1364089 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (551)
- 计划 (4)
- java (115)
- oracle (60)
- ajax (3)
- javascript (64)
- 计算机操作技巧集 (11)
- 近期关注话题 (10)
- 随想 (13)
- html (6)
- struts (15)
- hibernate (16)
- spring (2)
- game (0)
- Eglish (10)
- DisplayTag (6)
- jsp (18)
- css (3)
- eclipse (3)
- 其他知识 (8)
- 备用1 (12)
- 备用2 (1)
- 笑话-放松心情 (9)
- 设计 (1)
- 设计模式 (1)
- 数据结构 (0)
- office办公软件 (5)
- webwork (0)
- tomcat (2)
- MySql (1)
- 我的链接资源 (5)
- xml (2)
- servlet (0)
- PHP (13)
- DOM (0)
- 网页画图vml,canvas (1)
- 协议 (2)
- 健康 (3)
- 书籍下载 (1)
- jbpm (1)
- EXT (1)
- 自考 (2)
- 报表 (4)
- 生活 (64)
- 操作系统基础知识 (2)
- 测试 (2)
- guice (1)
- google学习 (2)
- Erlang (1)
- LOG4J (2)
- wicket (1)
- 考研 (1)
- 法律 (1)
- 地震 (1)
- 易学-等等相关 (1)
- 音乐 (1)
- 建站 (4)
- 分享说 (3)
- 购物省钱 (0)
- linux (1)
最新评论
-
zenmshuo:
如果使用SpreadJS这一类的表格工具,应该能更好的实现这些 ...
js中excel的用法 -
hjhj2991708:
第一个已经使用不了
jar包查询网站 非常好用! -
jiangmeiwei:
...
中文乱码 我的总结 不断更新 -
gary_bu:
...
response.sendRedirect 中文乱码问题解决 -
hnez:
多谢指点,怎么调试也不通,原来我在<body>&l ...
ExtJs IE ownerDocument.createRange() 错误解决方案
N Vs Exist in SQL
原文如下:
http://oracle-online-help.blogspot.com/2006/11/in-vs-exist-in-sql.html
IN Vs EXISTS
The two are processed quite differently.
IN Clause
Select * from T1 where x in ( select y from T2 )
is typically processed as:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
The sub query is evaluated, distinct, indexed and then
joined to the original table -- typically.
As opposed to "EXIST" clause
select * from t1 where exists ( select null from t2 where y = x )
That is processed more like:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop
It always results in a full scan of T1 whereas the first query can make use of
an index on T1(x).
So, when is exists appropriate and in appropriate?
Lets say the result of the subquery
( select y from T2 )
is "huge" and takes a long time. But the table T1 is relatively small and
executing ( select null from t2 where y = x.x ) is fast (nice index on
t2(y)). Then the exists will be faster as the time to full scan T1 and do the
index probe into T2 could be less then the time to simply full scan T2 to build
the subquery we need to distinct on.
Lets say the result of the subquery is small -- then IN is typicaly more
appropriate.
If both the subquery and the outer table are huge -- either might work as well
as the other -- depends on the indexes and other factors.
原文如下:
http://oracle-online-help.blogspot.com/2006/11/in-vs-exist-in-sql.html
IN Vs EXISTS
The two are processed quite differently.
IN Clause
Select * from T1 where x in ( select y from T2 )
is typically processed as:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
The sub query is evaluated, distinct, indexed and then
joined to the original table -- typically.
As opposed to "EXIST" clause
select * from t1 where exists ( select null from t2 where y = x )
That is processed more like:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop
It always results in a full scan of T1 whereas the first query can make use of
an index on T1(x).
So, when is exists appropriate and in appropriate?
Lets say the result of the subquery
( select y from T2 )
is "huge" and takes a long time. But the table T1 is relatively small and
executing ( select null from t2 where y = x.x ) is fast (nice index on
t2(y)). Then the exists will be faster as the time to full scan T1 and do the
index probe into T2 could be less then the time to simply full scan T2 to build
the subquery we need to distinct on.
Lets say the result of the subquery is small -- then IN is typicaly more
appropriate.
If both the subquery and the outer table are huge -- either might work as well
as the other -- depends on the indexes and other factors.
发表评论
-
oracle删除重复记录
2009-07-16 11:16 1215有困难,找猪八戒 Q:要删除一张表中的重复记录,但是要保留一条 ... -
db2 express-c 安装后检查及安装例子数据库
2009-02-27 17:07 3393摘自http://publib.boulder.ibm.com ... -
问:如何得到与WEB-INF同级目录下的配置文件
2008-09-23 08:35 2133Q: 有如下需求:需要从WEB-INF同级的目录下读取配 ... -
讨论如何优化这条sql
2008-09-11 16:33 1596SELECT * FROM ( ... -
SQL 指南
2008-05-27 11:45 1046http://www.sql-tutorial.com/ -
orace 分析函数
2008-05-26 09:08 1211select x.num, sum(x.num) over ( ... -
oralce tutoial 指南
2008-03-22 14:21 1027http://www.exforsys.com/tutoria ... -
查找部分字段重复的记录 ORACLE Identifying duplicate rows
2008-03-13 08:49 1964http://www.jlcomp.demon.co.uk/f ... -
oracle 资源网站
2008-01-12 11:42 1876oracle alter table table_ ... -
Top 5 Oracle Reference Books 前5本 oracle 参考书
2008-01-12 11:24 1717http://databases.about.com/od/o ... -
Oracle与DB2、MySQL取前10条记录的对比<转>
2008-01-11 16:46 2221原文:http://tech.ccidnet.com/art/ ... -
expert on e on one oracle - Thomas Kyte 读书笔记
2008-01-11 10:17 2109=============================== ... -
oracle 资源 整体理解oralce 比较好 英文网
2008-01-09 16:59 1190http://www.adp-gmbh.ch/ora/admi ... -
oracle java 插入 clob insert clob hibernate
2007-12-21 15:48 7230用jdbc 或者 hibernate http://www.w ... -
pl/sql 应用之一
2007-12-12 17:21 1135declare begin insert into x ... -
init.ora文件所在目录
2007-12-12 15:58 2166Oracle安装盘:\oracle\admin\DB名称\pf ... -
[Oracle] 如何解决ORA-04031 错误
2007-12-12 15:53 3253[Oracle] 如何解决ORA-04031 ... -
oracle faq 常见问题解答 http://www.orafaq.com/
2007-12-12 13:34 1498The Oracle FAQ http://www.oraf ... -
oracle 快速参考
2007-12-12 09:58 1094http://www.psoug.org/library.ht ... -
oracle like
2007-12-12 09:18 3414http://www.adp-gmbh.ch/ora/sql/ ...
相关推荐
- **SQL语句**:`SELECT relname, n.amname AS index_type FROM sys_class m, sys_am n WHERE m.relam = n.oid AND m.oid IN (SELECT b.indexrelid FROM sys_class a, sys_index b WHERE a.oid = b.indrelid AND a....
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'database_name.schema_name.StoredProcedureName') AND type in (N'P', N'PC')) BEGIN PRINT '存在' END ELSE BEGIN PRINT '不存在' ...
, @sql = @sql + ',' + '[' + a.name + ']' + CASE WHEN b.name IN ('char', 'nchar', 'varchar', 'nvarchar') THEN 'text(' + CAST(CASE WHEN a.length > 255 THEN 255 ELSE a.length END AS varchar) + ')' ...
WHERE b.name NOT IN ('image', 'text', 'uniqueidentifier', 'sql_variant', 'ntext', 'varbinary', 'binary', 'timestamp') AND object_id(@tbname) = id ``` 这段代码构建了一个 SQL 查询语句,用于获取指定表的...
SQL Server needs to lock data that does not exist! If no rows satisfy the WHERE condition the first time the range is scanned, no rows should be returned on any subsequent scans. Key range locks are ...
Currently OLE DB providers exist for SQL Server, Oracle, Access, ODBC, Active Directory Services and the Index Server. Key features: High speed - fast, native access to data without BDE. ...
Using PHP and Oracle to manage your distributed trace files--------------------------------------------------------------------------------------Deployment InstructionsOracle Layer The Oracle JServer must be installed and exist in a valid state. Ensure that the directories specified in the parameters for USER_DUMP_DEST and BACKGROUND_DUMP_DEST are set up as utl_file_dir directives in the init.ora,...
- 相关子查询和 `exist` 引导的查询:一般情况下, **D.in引导的查询都可以用exist引导的查询实现**。`exist` 子查询通常比 `in` 子查询更高效,尤其是在子查询返回大量数据时。 9. **视图更新**: **A.行列子集...
exist 存在, 发生 [ig'zist] '(SQL关键字 exists) extends (关键字) 继承、扩展 [ik'stend] false (关键字) final (关键字) finally (关键字) fragments 段落; 代码块 ['frægmәnt] FrameWork [java] 结构,...
CREATE SQL VIEW 视图名 AS SELECT ... ``` Creates a view within the current database. If no database is open, it creates an independent view that cannot be stored. **Delete a View:** ```plaintext ...
ErrorCode: 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_...
* TDataSetProvider generates incorrect SQL in response to a RefreshRecord request in some cases and fails to update all fields properly (Quality Central 4014). * When TDataSetProvider obtains the DB ...
```sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255) NOT NULL UNIQUE ); CREATE TABLE resources ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, ...
4. **数据源问题**:数据库连接或查询语句(`SQL: select * from person`)可能存在问题,导致无法正确执行查询。 ### 解决步骤 1. **检查构造函数**:确保`Person`类有一个公共的无参构造函数。如果之前为了某些...
- **IN与EXISTS**(B.in引导的查询都可以用exist引导的查询实现): IN和EXISTS都可以用来实现子查询,但是IN和EXISTS在性能和使用场景上有所不同。 #### 二十一、数据库恢复技术 - **恢复技术**(D.缓冲区技术): ...
#### exist 存在, 发生 [ig'zist] ('SQL关键字 exists') - **中文释义**:存在 - **用途**:在编程语言中,尤其是SQL中,`exists`关键字用于检查子查询是否返回行。 #### extends (关键字) 继承、扩展 [ik'stend] ...
- **EXIST/IN子句**:正确使用这些子句可以优化查询性能。 - **Reducer个数**:合理设定Reducer数量可以提高查询速度。 - **合并MapReduce操作**:通过组合多个操作来减少MapReduce作业的次数。 - **Bucket与...
java.lang.IllegalArgumentException: Document base D:\Program Files\Apache Software Foundation\Tomcat5.0\webapps\tomcat-docs does not exist or is not a readable directory ``` **问题描述:** 该错误表明...