Oracle Development Series (iii) Exists&not exists usage (10g)

Note: The following content is suitable for beginners of Oracle Development or Java developers, the master skipped

A exists&in

The following three statement functions are from the IODSO.QOS_HISENTRY_SHEET_JTEXT_TD inside find sheet_no in the IODSO.QOS_HISENTRY_SHEET_TD table Arch_time 1 days inside the list.

IODSO.QOS_HISENTRY_SHEET_JTEXT_TD has a common federated index.

IODSO.QOS_HISENTRY_SHEET_TD has a normal index.

Data volume conditions for two tables

Select COUNT (1) from iodso.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.shee T_NO);

Execution Plan Comparison

The execution plan is generated by the F5 key of PL/SQL Dev, which generally looks at the execution plan to suggest from Sqlplus explain plan for look but developers may be more accustomed to using PL, SQL tools

And the tool can be positioned to the first place of execution and the corresponding operation description at the bottom there is a string of English such as Sort_unique's interpretation at the bottom of the red circle where sort a result set and eliminate duplicates meaning is the result Set sort and go heavy

Plan for SQL 1:

Plan for SQL 3:

Plan for SQL 2:

Three SQL is exactly the same from the above execution plan and order.

Execution results

Results of SQL 1 execution:

Results of SQL2 Execution:

Results of SQL3 Execution:

From the above, the fastest sql2 executed by SQL1 is the slowest

The above is from the case of small watch and then look at the following statement (check the case of large tables):

1

Select a.* from  iodso.qos_hisentry_sheet_td a where a.arch_time between trunc (sysdate-1, ' DD ') and       trunc (sysdat E, ' 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);

So many of the online said exists than in fast or retrieve big table when exists than in fast and so is not necessarily accurate, now many things Baidu may be copied to copy, there are some previous 8i 9i old version of the rules are now basically 10g above not necessarily applicable. The conclusions on the Internet should be carefully tested with the best of their own.

The efficiency of exists and in is usually similar, and depends on the execution plan and the actual execution time.

PS: Most enterprise-level developers may prefer to use in easy-to-understand thinking

Two not exists&not in
1

Select T.occur_area_id-1,  COUNT (1) all_num,   SUM (case when             (DECODE (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 (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              S.userid = T6.userid              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 above execution plan you can see that the cost difference is very large, not exists is much smaller than not. Not exists uses a hash join anti instead of a filter. The execution time does not exists a few minutes without in execution for 30 minutes has not been completed.

Small Summary: (this content turn)
Semi-join
Usually occurs in SQL that uses exists or in, and the so-called Semi-join is the record of the first table when there is one or more matching records in the second table when two tables are associated;
Unlike normal joins, when Semi-join, records in the first table return at most one time

Anti-join
The second table does not find the matching record, it will return the first table of records;
When to choose Anti-join1
Use not in and the corresponding column has a NOT NULL constraint
Not exists, it is not guaranteed to use anti-join every time.
When Anti-join is not available, Oracle often uses the filter substitution

Filter
For each line of the appearance, we perform a full table scan of the internal table, which is really like the neested loop we are familiar with, but it is unique in that it maintains a hash table

32 Tables update 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); com Mit

The statement may seem simple, but when AP BP itself is a complex query, it might be difficult to think of.

Oracle Development Series (iii) Exists&not exists usage (10g)