Oracle Development Series (3) exists & amp; not exists usage and comparison with in & amp; not in (10g), oracleexists

Oracle Development Series (iii) exists & not exists usage and comparison with in & not in (10g), oracleexists

Note: The following content is suitable for beginners of oracle development, java, and other developers.

One exists & in

The following three statements show the table sheet_no in iodso. qos_hisentry_sheet_jtext_td in the iodso. qos_hisentry_sheet_td table arch_time within one day.

Iodso. qos_hisentry_sheet_jtext_td has a common Union index.

Iodso. qos_hisentry_sheet_td has a common index.

Data Volume of two tables

Select count (1) from IOs. qos_hisentry_sheet_td -- 29843027

Select count (1) from iodso. qos_hisentry_sheet_jtext_td -- 29973242

1

select *  from iodso.qos_hisentry_sheet_jtext_td t where t.sheet_no in (select a.sheet_no                        from iodso.qos_hisentry_sheet_td a                       where a.arch_time between trunc(sysdate - 1, 'dd') and                             trunc(sysdate, 'dd'));

2

select *  from iodso.qos_hisentry_sheet_jtext_td t where t.sheet_no in (select a.sheet_no                        from iodso.qos_hisentry_sheet_td a                       where a.arch_time between trunc(sysdate - 1, 'dd') and                             trunc(sysdate, 'dd')                         and t.sheet_no = a.sheet_no);

3

select *  from iodso.qos_hisentry_sheet_jtext_td t where exists (select a.sheet_no          from iodso.qos_hisentry_sheet_td a         where a.arch_time between trunc(sysdate - 1, 'dd') and               trunc(sysdate, 'dd')           and t.sheet_no = a.sheet_no);

Comparison of execution plans

The execution plan is generated by the F5 key of pl/SQL Dev. Generally, we recommend that you use sqlplus explain plan for the execution plan, but developers may prefer pl and SQL tools.

The tool can locate the first execution and the corresponding operation description has a string of English explanations such as sort_unique at the bottom of the Red Circle. sort a result set and eliminate duplicates means sort and deduplicate the result set

SQL 1 plan:

SQL 3 plan:

SQL 2 plan:

From the above execution plan and sequence, the three SQL statements are exactly the same.

Execution result

SQL 1 execution result:

SQL 2 execution result:

SQL 3 execution result:

From the above, the fastest execution of sql1 is the slowest execution of sql2.

The above is to check the small table and then look at the following statement (check the big table ):

1

select a.*  from iodso.qos_hisentry_sheet_td a where a.arch_time between trunc(sysdate - 1, 'dd') and       trunc(sysdate, 'dd')   and sheet_no in       (select sheet_no from iodso.qos_hisentry_sheet_jtext_td t);

2

select a.*          from (select *            from iodso.qos_hisentry_sheet_td a           where a.arch_time between trunc(sysdate - 1, 'dd') and                 trunc(sysdate, 'dd')) a         where exists (select t.sheet_no                  from iodso.qos_hisentry_sheet_jtext_td t                 where t.sheet_no = a.sheet_no);

Therefore, it is not always accurate to say that exists is faster than in on the Internet or that exists is faster than in When retrieving large tables. Many of Baidu's items may be copied, in addition, some old 8i version rules are generally 10 Gb or above and are not necessarily applicable. The conclusion on the Internet should be used with caution. It is best to test it on your own.

The efficiency of exists and in is usually similar. The execution plan and actual execution time should prevail ,.

Ps: most enterprise-level developers may prefer in for easy understanding.

2 not exists & not in
1

select t.occur_area_id-1,  COUNT(1) ALL_NUM,   SUM(CASE             WHEN (DECODE(SIGN(T.FLOW_TIME - t.fact_flow_time), -1, 0, 1) = 0) THEN              1             ELSE              0           END) CS_NUM  from  QOS_NET_CONTROL_GD_sb Twhere t.sheet_no not in(SELECT  t1.sheet_no  FROM QOS_NET_CONTROL_GD_sb T1,IODSO.QOS_EOSORG_T_EMPLOYEE     T2,          IODSO.QOS_EOSORG_T_ORGANIZATION T3,       iodso.qos_eosoperator t6 WHERE T1.USERID = T6.userid   and t6.operatorid = t2.operatorid   and t2.orgid=t3.orgid and T1.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')   AND T1.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')  )group by t.occur_area_id;

2

select t.occur_area_id - 1,         COUNT(1) ALL_NUM,         SUM(CASE               WHEN (DECODE(SIGN(T.FLOW_TIME - t.fact_flow_time), -1, 0, 1) = 0) THEN                1               ELSE                0             END) CS_NUM    from QOS_NET_CONTROL_GD_sb T   where not exists   (select 1            from QOS_NET_CONTROL_GD_sb           s,                 IODSO.QOS_EOSORG_T_EMPLOYEE     T2,                 IODSO.QOS_EOSORG_T_ORGANIZATION T3,                 iodso.qos_eosoperator           t6           where T.Sheet_No = s.sheet_no             and s.USERID = T6.userid             and t6.operatorid = t2.operatorid             and t2.orgid = t3.orgid)             and T.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')   group by t.occur_area_id

From the execution plan above, we can see that cost is very different, not exists is much smaller than not in. Not exists uses hash join anti and not in uses filter. The execution time shows that not exists has been executed for several minutes and not in has not been completed for 30 minutes.

Summary: (transfer this content)
Semi-join
In SQL statements that use exists or in, the so-called semi-join is used to associate two tables. When the second table has one or more matching records, returns the record of the first table;
The difference with normal join is that when semi-join is performed, the records in the first table are returned only once at most.

Anti-join
If no matching record is found in the second table, the record in the first table is returned;
When to select anti-join1
Use not in and the corresponding column has the not null Constraint
Not exists. it is not guaranteed that anti-join is used every time.
When anti-join cannot be selected, oracle usually uses filter instead.

Filter
A full table scan is performed on each row of the External table. It is actually like the neested loop we are familiar with, but it is unique in that it maintains a hash table

Three or two tables are updated based on a field Association

update ap   set ap.t =       (select bp.t from bp where ap.s = bp.s) where exists (select 1 from bp where ap.s = bp.s);commit;

The statement seems very simple, but it may be difficult to think about it when the ap bp itself is a very complex query.