使用绑定变量进行 Oracle 分区修剪
我有一个大（150m+行）表，使用 DATE 分区键将其分区为四分之一。
当我使用诸如...之类的东西查询表时
SELECT *
FROM LARGE_TABLE
WHERE THE_PARTITION_DATE >= TO_DATE('1/1/2009', 'DD/MM/YYYY')
AND THE_PARTITION_DATE < TO_DATE('1/4/2009', 'DD/MM/YYYY');
...分区修剪工作正常...优化器能够意识到它只需要查看单个分区（在本例中为 2009 年第一季度）。 EXPLAIN PLAN 显示“PARTITION RANGE SINGLE”
但是，当我将此查询移至 PL/SQL 并传入相同的日期作为变量时，该计划显示为“PARTITION RANGE (ITERATOR)”...优化器无法理解这一点它只需要查看单个分区（大概是因为它在评估计划时没有实际值）。
到目前为止，我发现的唯一解决方法是编写一个 EXECUTE IMMEDIATE 代码，其中包含 SQL 字符串中的日期，以便分区修剪正常工作。
有更好的办法吗？
I have a large (150m+ row) table, which is partitioned into quarters using a DATE partition key.
When I query the table using something like...
SELECT *
FROM LARGE_TABLE
WHERE THE_PARTITION_DATE >= TO_DATE('1/1/2009', 'DD/MM/YYYY')
AND THE_PARTITION_DATE < TO_DATE('1/4/2009', 'DD/MM/YYYY');
... partition pruning works correctly... the optomiser is able to realise that it only needs to look at a single partition (in this case Q1 2009). EXPLAIN PLAN shows "PARTITION RANGE SINGLE"
However, when I move this query to PL/SQL and pass in the same dates as variables, the plan is showing as "PARTITION RANGE (ITERATOR)"... the optomiser is unable to understand that it only needs to look at the single partiiton (presumably because it doesn't have the actual values when it's evaluating the plan).
The only workaround I've found round so far is to code an EXECUTE IMMEDIATE including the dates in the SQL string so that the partition pruning works correctly.
Is there a better way?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为您不应该看到绑定变量的实际性能差异 - 您应该看到“PARTITION RANGE ITERATOR PARTITION: KEY KEY...”的执行计划步骤，这意味着 Oracle 将在执行时确定启动和停止分区。
I don't think you should see an actual performance difference with the bind variables - you should see an execution plan step of "PARTITION RANGE ITERATOR PARTITION: KEY KEY..." which means Oracle will determine the start and stop partitions at execution time.