[Change] ORACLE IN and EXISTS in comparison

2010-03-31  来源:本站原创  分类:Database  人气:203 

Address: http://www.cnblogs.com/yf520gn/archive/2009/01/12/1374359.html

Implementation process EXISTS
select * from t1 where exists (select null from t2 where y = x)
Can be understood as:
for x in (select * from t1)
if (exists (select null from t2 where y = xx)
end if
end loop
In and exists for the performance difference:
If the sub-query result set record low, the main query table is larger and another index should be used in, the other hand, if the outer main query record low, sub-query in Congress, another index use exists.
In fact, we distinguish between in and exists mainly caused by the drive sequence change (this is the key to performance changes), if it exists, then the outside layer of table-driven form, is first visit, if it is IN, then the first implementation of the sub-queries, so We will drive the rapid return to the table as the goal, then they would take into account the relationship between the index and the result set

When not NULL IN other processing such as:
select 1 from dual where null in (0,1,2, null)


NOT EXISTS in the implementation process
select .....
from rollup R
where not exists (select 'Found' from title T
where R.source_id = T. Title_ID);
Can be understood as:
for x in (select * from rollup)
if (not exists (that query)) then
end if;

NOTE: NOT EXISTS and NOT IN can not completely replace each other to see specific needs. If you choose a column can be empty, it can not be replaced.

For example, the following statements and see their differences:
select x, y from t;
------ ------
select * from t where x not in (select y from t t2)
no rows

select * from t where not exists (select null from t t2
where t2.y = tx)
------ ------
Therefore, to determine specific needs

Not in and not exists for the performance difference:
not in only when the sub-query, select keywords fields are not null constraint, or to be implied when using not in, another big if the main query table, sub-query in the form of small, but records and more, you should use not in, and use anti hash join.
If the main query table, record low recorded in many sub-query table, and have an index, you can use not exists, also not in the best can also use the / * + HASH_AJ * / or external connection + is null
NOT IN in a better cost-based applications

For example:
select .....
from rollup R
where not exists (select 'Found' from title T
where R.source_id = T. Title_ID);

Change the (good)

select ......
from title T, rollup R
where R.source_id = T. Title_id (+)
and T. Title_id is null;

Or (good)
sql> select / * + HASH_AJ * / ...
from rollup R
where ource_id NOT IN (select ource_id
from title T
where ource_id IS NOT NULL)

Note: The above is just some suggestions from the theory, the best principle is the basis of all the above can be used to analyze the implementation plan, the wording of the statement obtained the best hope to challenge

Address: http://www.cnblogs.com/diction/archive/2008/01/18/1043844.html

and exists in the sql in the efficiency analysis, and then quote a simple example:

declare @ t table (id int identity (1, 1), v varchar (10))
insert @ t select 'a'
union all select 'b'
union all select 'c'
union all select 'd'
union all select 'e'
union all select 'b'
union all select 'c'
- A written statement in the sql
select * from @ t where v in (select v from @ t group by v having count (*)> 1)
- B exists in the sql statement is written
select * from @ ta where exists (select 1 from @ t where id! = a.id and v = av)

2 statement functions are found in the table variable @ t, v records containing duplicate values.

The first sql statement to use in, but the sub-query is not connected with the outside.

The second sql statement to use exists, but the sub-query has links with the outside.

We see SQL query plan, it is clear.

selec v from @ t group by v having count (*)> 1

This Sql statement, its execution does not depend on the main query the main sentence (I do not know how to describe in the outside and inside, for the time being so call it, we understand that on-line)

Well, SQL in the query will be optimized to be its cached result set

The cache





Follow-up operation, the main query processing in each step, the equivalent processing where v in ('b', 'c'), of course, the statement would not be so converted, only to illustrate the point, that is the main inquiry line for each treatment (note To currentROW, the sub-query will not scan the table, the results will match with the cache


select 1 from @ t where id! = a.id and v = av

This one, its implementation depended on the main query in each row.

When dealing with the main query that the first row currentROW (id = 1), the sub-query is executed again select 1 from @ t where id! = 1 and v = 'a' scan the whole table, from the first line in mind currentSubROW (id = 1 ) start scanning, id the same filter, subquery rows down, currentSubROW (id = 2) continue, id different, but the v values do not match, subquery rows continue down ... until currentSubROW (id = 7) did not find matching, sub-query processing over the first line currentROW (id = 1) were filtered rows down the main query

With the second row, currentROW (id = 2), sub-query select 1 from @ t where id! = 2 and v = 'b', the first line currentSubROW (id = 1) v values do not match the subquery downward The second line, id the same filter, the third line, ... to the sixth row, id different, v values match, find matches, returned to no longer deal with record down. main query down.

With the third row, and so on ...

sql optimization, the use of in and exist? Mainly the conditions of your screen or on the main query sub-query.

  • [Change] ORACLE IN and EXISTS in comparison 2010-03-31

    Address: http://www.cnblogs.com/yf520gn/archive/2009/01/12/1374359.html Implementation process EXISTS select * from t1 where exists (select null from t2 where y = x) Can be understood as: for x in (select * from t1) loop if (exists (select null from

  • ORACLE IN and EXISTS in comparison 2010-07-23

    Two simple examples to illustrate the "exists" and "in" the efficiency 1) select * from T1 where exists (select 1 from T2 where T1.a = T2.a); T1 and T2 small amount of data very large amount of data, T1 2) select * from T1 where T1.a i

  • Oracle in the spool command Comparison of two methods to achieve 2011-02-13

    Oracle in the spool command Comparison of two methods to achieve http://database.51cto.com 2009-04-21 11:23 Anonymous 51CTO Forum I Comments (0) Abstract: The Central Standing Committee met in the production need to be relatively large number of tabl

  • [Change] Oracle database design - the definition of primary key constraint 2010-03-30

    Declare the primary key constraint (PRIMARY KEY) A table is not necessarily the primary key, but most tables are created the primary key, primary key must be unique and form the primary key columns can not be empty. Imagine a table of information sto

  • [Change] oracle partition table 2010-06-07

    Copyright: original works, reproductions, please contact the author. Otherwise held liable. Transfer from: http://tianzt.blog.51cto.com/459544/171759 the actual author of the article thanks, I do not provide second reproduced, thank you Article from

  • oracle and sql server feature comparison 2011-01-05

    Oracle Database 10g compared the competitive advantage of SQL Server 2005 Microsoft officially released on 2005.11.7 SQL Server 2005. By comparing Oracle Database 10g and SQL Server 2005 features and performance, we give the competitive advantage of

  • change Oracle 8080 2010-03-29

    Use Oracle Enterprise Manager Console, log on to dba select the database, XML Database, Configuration. Change the http-port for other ports can be.

  • [Change] Oracle For update of 2010-07-25

    http://www.cnblogs.com/zping/archive/2009/01/06/1370245.html Wrote, 1 select * from hjh for update [of hjh.a]; lock the table all the rows can select, insert, can not update, Delete Commit submitted, unlock 2 SELECT * FROM hjh WHERE a = 1 FOR UPDATE

  • [Change] ORACLE's SQLPLUS commonly used commands 2010-08-07

    The SQLPLUS command used ORACLE http://blog.chinaunix.net/u2/70049/showart_1969396.html 1, ORACLE startup and shut down one, single environment in order to activate or deactivate the ORACLE system must first switch to the ORACLE user, as su - oracle

  • [Change] Oracle Exception Handling 2010-08-23

    -- Exception Handling ---------------------------------------------------------// -- Predefined Exception --NO_DATA_FOUND No data satisfy the query requirements --ZERO_DIVIDE Arithmetic error, such as trying to use 0 Removal of a number --INVALID_NUM

  • Oracle's Replace function and comparison with the translate function Detailed 2011-08-02

    A brief comparison: level instead of replace string Such as: SELECT REPLACE ('accd', 'cd', 'ef') from dual; -> aefd translate the character level instead of Such as: select translate ('acdd', 'cd', 'ef') from dual; -> aeff Detailed respectively. rep

  • [Change] Oracle Tablespace created 2010-02-23

    [] Oracle Tablespace created transfer 2009 - Tablespace ORACLE, the table space is the basic method of data management, all users of the object to be stored in the table space, that is, users have the right to use space to create a user object. Other

  • [Change] Java in the way of comparison of four operating xml 2010-04-17

    1. Introduction 1) DOM (JAXP Crimson parser) DOM is platform-and language-independent manner, said the official W3C standard XML document. DOM is a hierarchical organization of nodes or pieces of information collection. This hierarchy allows the deve

  • Oracle to_date date on the comparison interval 2010-05-13

    where a.id = b.godowinid and to_char (a.audittime, 'yyyy-mm-dd')> = istartdate and to_char (a.audittime, 'yyyy-mm-dd') <= ienddate Over time for comparison, if istartdate = '2010-05-13 ', ienddate = '2010-05-13' In fact, if the above time frame does

  • [Change] ORACLE function Daquan 2010-06-11

    Articles Category: Database SQL in single-record function 1.ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii ('A') A, ascii ('a') a, ascii ('0 ') zero, ascii (' ') space from dual; AA ZERO SPACE --------- -

  • [Change] Oracle Exception Handling Summary 2010-08-23

    To deal with PL / SQL application of various errors , Developers can use various types of abnormal . Oracle Provides 1: Predefined Exception Used to handle common Oracle errors 2: Non-predefined exception Predefined exception to handle the error can

  • Oracle SID already exists on this machine, specify a different SID "solution 2010-11-11

    windows system: 1, the start - "Set -" Control Panel - "Administrative Tools -" Service to stop all Oracle services. 2, the start -> programs -> Oracle - OraHome92-> Oracle Installation Products-> Universal Installer uninst

  • Oracle In和exists not in和not exists的比较分析 2013-12-09

    一个是问in exist的区别,一个是not in和not exists的区别 把这两个很普遍性的网友比较关心的问题总结回答一下. in和exist的区别 从sql编程角度来说,in直观,exists不直观多一个select, in可以用于各种子查询,而exists好像只用于关联子查询 从性能上来看 exists是用loop的方式,循环的次数影响大,外表要记录数少,内表就无所谓了 in用的是hash join,所以内表如果小,整个查询的范围都会很小,如果内表很大,外表如果也很大就很慢了,这时候e

  • [Change] Oracle Insert the record ID from the growing realization 2010-04-03

    create table users ( userid integer primary key, username varchar2 (10), address varchar2 (20), phone varchar2 (13), loginname varchar2 (20), loginpwd varchar2 (20) ) create sequence user_sq; - Trigger id from the growth create or replace trigger tri

  • [Change] oracle advanced queue application in communications design 2010-07-23