请教in和exists的区别

anlinew · 发表于 2008-4-14 15:42

原帖由 mihawk 于 2008-4-14 15:26 发表
这个用全表扫描试试

Select /*+full(f)*/ID
From tb_role f
where e.role_id = f.id
Connect By Prior Id = parent_id
Start With Id = 1
/

本来准备建议这样的，但如果这里id=?会变的话，hint也就不一定合适了

paulyibinyi · 发表于 2008-4-14 15:45

原帖由 mihawk 于 2008-4-14 15:35 发表
整个语句试试这个(语法等效的, 但是强制使用了hash和full table)

select /*+ordered use_hash(e x)*/
e.id, e.role_id, e.login
from tb_user e ,
(Select /*+full(f)*/distinct ID
From tb_role f
Connect By Prior Id = parent_id
Start With Id = 1) x
where e.role_id = x.id
/

恩 tks
SQL> select /*+ordered use_hash(e x)*/
  2       e.id, e.role_id, e.login
  3  from tb_user e ,
  4  (Select /*+full(f)*/distinct ID
  5  From tb_role f
  6  Connect By Prior Id = parent_id
  7  Start With Id = 1) x
  8  where e.role_id = x.id
  9  ;

8369 rows selected.

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=8556 Bytes=2
      99460)

1 0 HASH JOIN (Cost=16 Card=8556 Bytes=299460)
2 1    TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=8556 Byt
      es=188232)

3 1    VIEW (Cost=5 Card=8974 Bytes=116662)
4 3    CONNECT BY (WITH FILTERING)
5 4       NESTED LOOPS
6 5          INDEX (UNIQUE SCAN) OF 'PK_tb_role' (UNIQUE)
         (Cost=1 Card=1 Bytes=5)

7 5          TABLE ACCESS (BY USER ROWID) OF 'tb_role'
8 4       HASH JOIN
9 8          CONNECT BY PUMP
  10 8          TABLE ACCESS (FULL) OF 'tb_role' (Cost=5 Car
      d=8974 Bytes=80766)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
       923  consistent gets       0  physical reads
      0  redo size
   224163  bytes sent via SQL*Net to client
   6553  bytes received via SQL*Net from client
      559  SQL*Net roundtrips to/from client
      9  sorts (memory)
      0  sorts (disk)
   8369  rows processed

SQL>

paulyibinyi · 发表于 2008-4-14 15:47

原帖由 anlinew 于 2008-4-14 15:42 发表

本来准备建议这样的，但如果这里id=?会变的话，hint也就不一定合适了

这个会变化的

zhp6489 · 发表于 2008-4-14 15:47

Subject:  Upgrading from 8.1.X to 9.X - Subquery Issues - Diagnosing and Resolving
  Doc ID:  Note:258945.1 Type:  TROUBLESHOOTING
  Last Revision Date:  13-JUL-2006 Status:  PUBLISHED

PURPOSE

The Oracle Cost Based Optimizer is continually been enhanced. These enhancements were designed to improve performance but in some cases can cause a minority of queries to perform no better or worse than before. This article discusses some of the potential problems you may encounter when moving SQL Queries from Oracle 8i to Oracle 9i. It mainly centres on changes within the Cost Based Optimizer.
SCOPE & APPLICATION

DBAs and Application Designers
Upgrading from 8.1.X to 9.X - Subquery Issues - Diagnosing and Resolving

Subquery Unnesting Changes

Oracle 9i enabled subquery unnesting by default, controlled by the <Parameter:OPTIMIZER_FEATURES_ENABLE>.
If this was set to >= 9.0.0. then subquery unnesting would be enabled.
This means that on Oracle9i, queries containing subqueries are likely to be unnested when they were not in Oracle8i.

In most cases unnesting subqueries provides performance enhancements, but in some cases unnesting can produce a sub-optimal plan (or other factors, such as missing or innaccurate statistics, can produce a poor plan making the unnesting seem a bad option).

If an application has 8i to 9i upgrade related query tuning problem, please consider if subqueries are involved
Enhanced subquery unnesting

Diagnosing Subquery Unnesting issues:

Look for subqueries in the query text
Examine the explain plan and determine if the subquery has been unnested
Compare the current explain plan with the plan from the earlier version to see if the subquery is being handled differently
Disable subquery unnesting and see if that resolves the problem

To workaround subquery unnesting issues, you may disable unnesting in one of the following ways:

Set <Parameter:UNNEST_SUBQUERY> (underscore) = FALSE

alter session set "_UNNEST_SUBQUERY" = false;

If _unnest_subquery = false does not help then you may also need to set _always_semi_join = off (if the subquery is an IN or EXISTS) or _always_anti_join = off (if the subquery is a NOT IN or NOT EXISTS)

alter session set "_ALWAYS_SEMI_JOIN" = off;
alter session set "_ALWAYS_ANTI_JOIN" = off;

Set <Parameter:OPTIMIZER_FEATURES_ENABLE> to a value less than "9.0.0"
This is a static parameter and cannot be changed on the fly. It has to be set in one of the instance initialisation files (e.g. initSID.ora).
Set <Parameter:UNNEST_NOTEXISTS_SQ> (underscore) = OFF for not exists subqueries.
Use a /*+ NO_UNNEST */ hint in the subquery
Rewrite the query to modify or remove the subquery
Please note that the decision to unnest a subquery is not costed in Oracle9i. The decision to unnest a subquery is taken based on a set of heuristics (rules) before the query is optimized.
RELATED DOCUMENTS

Note 258167.1 Upgrading from 8.1.X to 9.X - Potential Query Tuning Related Issues
Note 144967.1 Manipulating the access path of queries involving subqueries
Note 199070.1 Optimizing statements that contain views or subqueries
@
@ INTERNAL Note 250646.1 SubQuery Unnesting - IN SubQuery
@ INTERNAL Note 258228.1 SubQuery Unnesting - NOT IN SubQuery
@ INTERNAL Note 258676.1 SubQuery Unnesting - EXISTS SubQuery .

shiri512003 · 发表于 2008-4-14 15:51

good job！
自己要学的还很多呀，努力！！！

zhp6489 · 发表于 2008-4-14 15:52

@ INTERNAL Note 250646.1 SubQuery Unnesting - IN SubQuery
@ INTERNAL Note 258228.1 SubQuery Unnesting - NOT IN SubQuery
@ INTERNAL Note 258676.1 SubQuery Unnesting - EXISTS SubQuery
已经找不到了

anlinew · 发表于 2008-4-14 15:53

原帖由 paulyibinyi 于 2008-4-14 15:47 发表

这个会变化的

那么可能大多数情况下nest loop join可能更好了
从sql字面猜测业务逻辑来看，具体如何，还需研究实际业务

mihawk · 发表于 2008-4-14 15:54

原帖由 anlinew 于 2008-4-14 15:42 发表

本来准备建议这样的，但如果这里id=?会变的话，hint也就不一定合适了

猜想:
1. id=?不会变, 因为这个语句应该是取整棵树的数据.
2. 觉得 select id from tb_role都可以解决问题, 而不需要进行递归查询.

不知道楼主的业务逻辑, 仅仅是猜想.

paulyibinyi · 发表于 2008-4-14 16:07

原帖由 mihawk 于 2008-4-14 15:54 发表

猜想:
1. id=?不会变, 因为这个语句应该是取整棵树的数据.
是的但id=经常会变化的有时从第五层或其他层开始查
2. 觉得 select id from tb_role都可以解决问题, 而不需要进行递归查询.
这是业务逻辑需要递归查询从某个代理下的所有子代理下的所有user

不知道楼主的业务逻辑, 仅仅是猜想.

paulyibinyi · 发表于 2008-4-14 16:10

原帖由 anlinew 于 2008-4-14 15:53 发表

那么可能大多数情况下nest loop join可能更好了
从sql字面猜测业务逻辑来看，具体如何，还需研究实际业务

恩目前生产sql语句没加任何提示,改成in方式查询速度还可以接受