oracle 闭合区间,开区间和闭区间哪个开销大？

最新推荐文章于 2025-03-04 19:57:43 发布

转载最新推荐文章于 2025-03-04 19:57:43 发布 · 512 阅读

文章标签：

#oracle 闭合区间

本文通过实际案例对比了数据库中使用开区间与闭区间查询的执行计划及性能差异。通过对谓词条件、执行计划成本及处理行数等关键指标的分析，探讨了不同区间查询方式对性能的影响。

测了一个3w行的表：

谓词：>=2116529915

Execution Plan

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

Plan hash value: 2412484994

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

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

| 0 | SELECT STATEMENT | | 4760 | 1357K| 5056 (1)| 00:01:01 | | |

| 1 | PARTITION RANGE ALL | | 4760 | 1357K| 5056 (1)| 00:01:01 | 1 | 13 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TPA_STS_CELL_AGG | 4760 | 1357K| 5056 (1)| 00:01:01 | 1 | 13 |

|* 3 | INDEX SKIP SCAN | IDX_TPA_STS_CELL_AGG | 4760 | | 4763 (1)| 00:00:58 | 1 | 13 |

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

Predicate Information (identified by operation id):

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

3 - access("INT_ID">=2116529915)

filter("INT_ID">=2116529915)

Statistics

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

8606 recursive calls

0 db block gets

1923 consistent gets

0 physical reads

0 redo size

76115 bytes sent via SQL*Net to client

539 bytes received via SQL*Net from client

16 SQL*Net roundtrips to/from client

182 sorts (memory)

0 sorts (disk)

220 rows processed

谓词：>2116529914

Execution Plan

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

Plan hash value: 2412484994

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

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

| 0 | SELECT STATEMENT | | 4737 | 1350K| 5031 (1)| 00:01:01 | | |

| 1 | PARTITION RANGE ALL | | 4737 | 1350K| 5031 (1)| 00:01:01 | 1 | 13 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TPA_STS_CELL_AGG | 4737 | 1350K| 5031 (1)| 00:01:01 | 1 | 13 |

|* 3 | INDEX SKIP SCAN | IDX_TPA_STS_CELL_AGG | 4737 | | 4741 (1)| 00:00:57 | 1 | 13 |

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

Predicate Information (identified by operation id):

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

3 - access("INT_ID">2116529914)

filter("INT_ID">2116529914)

Statistics

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

10030 recursive calls

0 db block gets

2251 consistent gets

0 physical reads

0 redo size

76115 bytes sent via SQL*Net to client

539 bytes received via SQL*Net from client

16 SQL*Net roundtrips to/from client

219 sorts (memory)

0 sorts (disk)

220 rows processed

看起来开区间高效点，cbo fundamental里说，计算闭区间card的时候加了一个修正值1/num_distinct，那么是不是可以得到结论开区间效率高？

我的int_id分布是不均匀的，是不是要参考直方图？请大家指教~~