时间戳

首页 > 解决方案 > Wrong cardinality on "over partition by" query

sql - Wrong cardinality on "over partition by" query

问题描述

I have a table with code, send_date, and send_id as pk, on oracle 12.1.0.2

I need to fetch some values for the last code send of each value.

select  * from (
select code, some_column,
row_number() over (partition by code order by send_date desc, send_id desc) n
from my_table
) where n=1

Oracle made a cardinality estimation of 1 row due "where n=1" so choose a very bad execution plan on complex querys that use the previous SQL as subquery.

I assume that is a oracle bug https://support.oracle.com/knowledge/Oracle%20Database%20Products/2118138_1.html

There is any workarround to bypass this bug?.

标签： sqloracleoracle12cwindow-functions

解决方案

这可能是也可能不是错误。更有可能只是基于可用统计数据的优化器失败。

对于您的查询和下面的查询，我建议在(code, send_date desc, send_id desc, some_column).

以下是编写查询的两种替代方法，因此您可以比较性能：

select code, 
       max(some_column) keep (dense rank first order by send_date desc, send_id desc) as somecolumn
from my_table
group by code;

和：

select t.code, t.some_column
from my_table t
where (t.send_date, t.send_id) = (select t2.send_date, t2.send_id
                                  from my_table t2
                                  where t2.code = t.code
                                 )

推荐阅读