oracle完全存在性判断逻辑

40阅读　0评论2025-04-26　dingjun123
分类：Oracle

 对于完全存在性判断，子查询非相关的。。。三大数据库的CBO都是差不多的处理逻辑：
 非相关的希望单独执行一次即可，然后结果缓存下来，对于存在性判断的非相关子查询，希望只执行一次只查询一条。
 
 pgsql的select * from a where exists(select 1 from b where a.id=b.id);这种是判断a.id is not null+子表b有数据则执行的逻辑，
 pg这种判断比oracle,mysql要弱点，它可能走nested loops semi+物化，循环驱动物化表多次，虽然物化表{BANNED}最佳多只存一行（因为存在性判断），
 oracle走FILTER没有问题，MySQL走hash join semi也没有问题，子表b只执行一次只查一行，也{BANNED}最佳多被驱动一次。
 
 ==================Oracle
 这是完全存在性判断，子查询只要有一条，就执行主表条件，子查询没有结果就不执行。这种完全存在性判断，就算子查询走全表也没有关系，
 union all的2个分支，只要找到一条即可，而且子查询只执行一次。
 
 select * from a1 where exists(select 1 from b1 union all select 1 from b2);
 
 Elapsed: 00:00:00.00


执行计划走FILTER,按照FILTER逻辑是主表的每一行驱动子查询（可能有CACHE减少驱动次数），如果这样理解，那么主表有99行，子查询应该驱动执行99次，而且
全表扫描，那么效率必然很低，实际上呢？


见ID=1的FILTER谓词：   1 - filter( EXISTS ( (SELECT 1 FROM "B1" "B1") UNION ALL  (SELECT 1  FROM "B2" "B2")))
这里不像一般的FILTER是有系统绑定变量:B1的，所以，这个FILTER实际上是个存在性判断，O这个计划显示的容易误导人，应该是
子查询找1条，驱动主表，这样就容易理解了




点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 1160945571

  5. ----------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. ----------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 99 | 10494 | 9 (0)| 00:00:01 |
  9. |* 1 | FILTER | | | | | |####实际上是假的FILTER
  10. | 2 | TABLE ACCESS FULL | A1 | 99 | 10494 | 3 (0)| 00:00:01 |
  11. | 3 | UNION-ALL | | | | | |
  12. | 4 | TABLE ACCESS FULL| B1 | 99 | | 3 (0)| 00:00:01 |
  13. | 5 | TABLE ACCESS FULL| B2 | 99 | | 3 (0)| 00:00:01 |
  14. ----------------------------------------------------------------------------

  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------

  19.    1 - filter( EXISTS ( (SELECT 1 FROM "B1" "B1") UNION ALL (SELECT 1
  20.               FROM "B2" "B2")))


  23. Statistics
  24. ----------------------------------------------------------
  25.           0 recursive calls
  26.           0 db block gets
  27.          14 consistent gets
  28.           0 physical reads
  29.           0 redo size
  30.        8764 bytes sent via SQL*Net to client
  31.         503 bytes received via SQL*Net from client
  32.           8 SQL*Net roundtrips to/from client
  33.           0 sorts (memory)
  34.           0 sorts (disk)
  35.          99 rows processed



可以通过真实执行计划看到，子查询Starts{BANNED}最佳多是1，而且B1,B2只找了B1，而且只找到一条，则直接查询A1。所以虽然是FILTER,但是实际执行逻辑
是完全存在性判断，是先执行子查询判断，再执行主表的。

点击(此处)折叠或打开

  1. select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
  2.  
  3. Plan hash value: 1160945571

  5. --------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  7. --------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.01 | 14 |
  9. |* 1 | FILTER | | 1 | | 99 |00:00:00.01 | 14 |
  10. | 2 | TABLE ACCESS FULL | A1 | 1 | 99 | 99 |00:00:00.01 | 11 |
  11. | 3 | UNION-ALL | | 1 | | 1 |00:00:00.01 | 3 |
  12. | 4 | TABLE ACCESS FULL| B1 | 1 | 99 | 1 |00:00:00.01 | 3 |
  13. | 5 | TABLE ACCESS FULL| B2 | 0 | 99 | 0 |00:00:00.01 | 0 |
  14. --------------------------------------------------------------------------------------

  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------

  19.    1 - filter( IS NOT NULL)

实际的逻辑应该是这样的,B2的starts=0,因为B1找到了1条：  

点击(此处)折叠或打开

  1. select a1.* from a1,(select 1 from (select 1 from b1 union all select 1 from b2) where rownum=1);
  2.     

  4. 99 rows selected.

  6. Elapsed: 00:00:00.00

  8. Execution Plan
  9. ----------------------------------------------------------
  10. Plan hash value: 822935806

  12. -------------------------------------------------------------------------------
  13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  14. -------------------------------------------------------------------------------
  15. | 0 | SELECT STATEMENT | | 99 | 10494 | 9 (0)| 00:00:01 |
  16. | 1 | MERGE JOIN CARTESIAN | | 99 | 10494 | 9 (0)| 00:00:01 |
  17. | 2 | VIEW | | 1 | | 6 (0)| 00:00:01 |
  18. |* 3 | COUNT STOPKEY | | | | | |
  19. | 4 | VIEW | | 198 | | 6 (0)| 00:00:01 |
  20. | 5 | UNION-ALL | | | | | |
  21. | 6 | TABLE ACCESS FULL| B1 | 99 | | 3 (0)| 00:00:01 |
  22. | 7 | TABLE ACCESS FULL| B2 | 99 | | 3 (0)| 00:00:01 |
  23. | 8 | BUFFER SORT | | 99 | 10494 | 9 (0)| 00:00:01 |
  24. | 9 | TABLE ACCESS FULL | A1 | 99 | 10494 | 3 (0)| 00:00:01 |
  25. -------------------------------------------------------------------------------

  27. Predicate Information (identified by operation id):
  28. ---------------------------------------------------

  30.    3 - filter(ROWNUM=1)



  34. PLAN_TABLE_OUTPUT
  35. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  36. SQL_ID gqacy9u017x5g, child number 0
  37. -------------------------------------
  38.       select a1.* from a1,(select 1 from (select 1 from b1 union all
  39. select 1 from b2) where rownum=1)

  41. Plan hash value: 822935806

  43. --------------------------------------------------------------------------------------------------------------------
  44. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
  45. --------------------------------------------------------------------------------------------------------------------
  46. | 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.01 | 7 | | | |
  47. | 1 | MERGE JOIN CARTESIAN | | 1 | 99 | 99 |00:00:00.01 | 7 | | | |
  48. | 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
  49. |* 3 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 3 | | | |
  50. | 4 | VIEW | | 1 | 198 | 1 |00:00:00.01 | 3 | | | |
  51. | 5 | UNION-ALL | | 1 | | 1 |00:00:00.01 | 3 | | | |
  52. | 6 | TABLE ACCESS FULL| B1 | 1 | 99 | 1 |00:00:00.01 | 3 | | | |
  53. | 7 | TABLE ACCESS FULL| B2 | 0 | 99 | 0 |00:00:00.01 | 0 | | | |
  54. | 8 | BUFFER SORT | | 1 | 99 | 99 |00:00:00.01 | 4 | 20480 | 20480 |18432 (0)|
  55. | 9 | TABLE ACCESS FULL | A1 | 1 | 99 | 99 |00:00:00.01 | 4 | | | |
  56. --------------------------------------------------------------------------------------------------------------------

  58. Predicate Information (identified by operation id):
  59. ---------------------------------------------------

  61.    3 - filter(ROWNUM=1)

这种存在性判断，子查询条件where a1.object_id=a1.object_id （一样的），只找a1的object_id  is not null + 表b1有数据即可，
使用FILTER存在性检查，同样这种a1和b1没有关联的，对于子查询b1只找1次，1条即可。


oracle这种还是比较智能的，后面可以看看pg,mysql的，会转为nested loops semi,会执行多次b1的扫描，不够优化，当然他们会使用materilization技术提高效率。


点击(此处)折叠或打开

  1. select * from a1 where exists (select 1 from b1 where a1.object_id=a1.object_id);
  2. select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

  4. PLAN_TABLE_OUTPUT
  5. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  6. SQL_ID 4snt03yzrpmcq, child number 0
  7. -------------------------------------
  8. select * from a1 where exists (select 1 from b1 where
  9. a1.object_id=a1.object_id)

  11. Plan hash value: 1803926644

  13. -------------------------------------------------------------------------------------
  14. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  15. -------------------------------------------------------------------------------------
  16. | 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.01 | 13 |
  17. |* 1 | FILTER | | 1 | | 99 |00:00:00.01 | 13 |
  18. |* 2 | TABLE ACCESS FULL| A1 | 1 | 99 | 99 |00:00:00.01 | 11 |
  19. | 3 | TABLE ACCESS FULL| B1 | 1 | 1 | 1 |00:00:00.01 | 2 |
  20. -------------------------------------------------------------------------------------

  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------

  25.    1 - filter( IS NOT NULL)
  26.    2 - filter("A1"."OBJECT_ID" IS NOT NULL

等价于这种写法，执行计划与上面的完全一样，像pg改成这种才能扫描b1一次。   


点击(此处)折叠或打开

  1. select * from a1
  2. where exists (select 1 from b1)
  3. and a1.object_id is not null;

  5. PLAN_TABLE_OUTPUT
  6. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  7. SQL_ID cwt341hjrrppu, child number 0
  8. -------------------------------------
  9. select * from a1 where exists (select 1 from b1) and a1.object_id is
  10. not null

  12. Plan hash value: 1803926644

  14. -------------------------------------------------------------------------------------
  15. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  16. -------------------------------------------------------------------------------------
  17. | 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.01 | 13 |
  18. |* 1 | FILTER | | 1 | | 99 |00:00:00.01 | 13 |
  19. |* 2 | TABLE ACCESS FULL| A1 | 1 | 99 | 99 |00:00:00.01 | 11 |
  20. | 3 | TABLE ACCESS FULL| B1 | 1 | 1 | 1 |00:00:00.01 | 2 |
  21. -------------------------------------------------------------------------------------

  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------

  26.    1 - filter( IS NOT NULL)
  27.    2 - filter("A1"."OBJECT_ID" IS NOT NULL)






上一篇：oracle,mysql组合索引前导列范围条件且包含等值条件，可能效率只有范围的更高
下一篇：oracle使用扩展列统计信息解决复杂谓词cardinality估算不准的问题