oracle exist慢,谁能举例用了exists会很快，以及用了exists会很慢的例子，谢谢

newkid 发表于 2013-2-24 09:57

在10G以前，CBO还没那么聪明的时候，

慢：

9i也不明显

SQL> with 大表 as(select OBJECT_ID 列 from all_objects),

2  小表 as(select USER_ID 列 from all_users)

3  SELECT * FROM 大表

4  WHERE 大表.列 IN (SELECT 小表.列 FROM 小表);

已选择31行。

已用时间:  00: 00: 00.00

Execution Plan

----------------------------------------------------------

ERROR:

ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错

Statistics

----------------------------------------------------------

14  recursive calls

0  db block gets

911  consistent gets

0  physical reads

0  redo size

822  bytes sent via SQL*Net to client

525  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

31  rows processed

SQL> with 大表 as(select OBJECT_ID 列 from all_objects),

2  小表 as(select USER_ID 列 from all_users)

3  SELECT A.* FROM 大表 A JOIN (SELECT DISTINCT 小表.列 FROM 小表) B ON A.列=B.列;

已选择31行。

已用时间:  00: 00: 00.00

Execution Plan

----------------------------------------------------------

ERROR:

ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错

Statistics

----------------------------------------------------------

14  recursive calls

0  db block gets

911  consistent gets

0  physical reads

0  redo size

822  bytes sent via SQL*Net to client

525  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

31  rows processed

SQL> with 大表 as(select OBJECT_ID 列 from all_objects),

2  小表 as(select USER_ID 列 from all_users)

3  SELECT * FROM 大表

4  WHERE EXISTS (SELECT 1 FROM 小表 WHERE 大表.列=小表.列);

已选择31行。

已用时间:  00: 00: 00.01

Execution Plan

----------------------------------------------------------

ERROR:

ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

52433  consistent gets

0  physical reads

0  redo size

822  bytes sent via SQL*Net to client

525  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

31  rows processed

SQL> with 小表 as(select OBJECT_ID 列 from all_objects),

2  大表 as(select USER_ID 列 from all_users)

3  SELECT * FROM 大表

4  WHERE EXISTS (SELECT 1 FROM 小表 WHERE 大表.列=小表.列);

已选择31行。

已用时间:  00: 00: 00.00

Execution Plan

----------------------------------------------------------

ERROR:

ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错

Statistics

----------------------------------------------------------

14  recursive calls

0  db block gets

974  consistent gets

0  physical reads

0  redo size

822  bytes sent via SQL*Net to client

525  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

31  rows processed

SQL> with 小表 as(select OBJECT_ID 列 from all_objects),

2  大表 as(select USER_ID 列 from all_users)

3  SELECT * FROM 大表

4  WHERE 大表.列 IN (SELECT 小表.列 FROM 小表);

已选择31行。

已用时间:  00: 00: 00.00

Execution Plan

----------------------------------------------------------

ERROR:

ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错

Statistics

----------------------------------------------------------

14  recursive calls

0  db block gets

935  consistent gets

0  physical reads

0  redo size

822  bytes sent via SQL*Net to client

525  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

31  rows processed

SQL> with 小表 as(select OBJECT_ID 列 from all_objects),

2  大表 as(select USER_ID 列 from all_users)

3  SELECT A.* FROM 大表 A JOIN (SELECT DISTINCT 小表.列 FROM 小表) B ON A.列=B.列;

已选择31行。

已用时间:  00: 00: 00.01

Execution Plan

----------------------------------------------------------

ERROR:

ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错

Statistics

----------------------------------------------------------

14  recursive calls

0  db block gets

21415  consistent gets

0  physical reads

0  redo size

822  bytes sent via SQL*Net to client

525  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

31  rows processed