ORA:01467排序键过长 [英] ORA:01467 Sort key too long

问题描述

我正在尝试通过结合学生历史记录和学生表来查找行的原始值. 我必须创建动态查询，以便在运行时从具有不同模式的学生表中选择列. 当我在下面的查询中执行超过90列的查询时，出现错误"ORA-01467"

I am trying to find originl value for row by combining studenthistory and student table. I have to create dynamic query which select column at runtime from student table with different schemas. When I am executing below query with more than 90 columns it is giving the error "ORA-01467"

select sh.id,
       coalesce(sh.name,
                lag(sh.name ignore nulls) over (partition by sh.id order by sh.DatetimeCreated),
                s.name
               ) as name,
       coalesce(sh.city,
                lag(sh.city ignore nulls) over (partition by sh.id order by sh.DatetimeCreated),
                s.city
               ) as city,
       coalesce(sh.address,
                lag(sh.address ignore nulls) over (partition by sh.id order by sh.DatetimeCreated),
                s.address
               ) as address,
        s.createdDateTime,
        sh.createdDateTime as updatedDateTime,
Coalesce(sh.column1, lag(sh.column1)over(partition by sh.id order by sh.DatetimeCreated desc), s.column1) as column1,
from studenthistory sh join
     student s
     on s.id = sh.id
union all
select s.id, s.name, s.city, s.address, s.createdDateTime, s.updatedDateTime
from student s;

推荐答案

这是Oracle中的已知限制.

This is the known limitation in Oracle.

根据Oracle文档，ORA-01467的原因和解决方法是

According to the Oracle docs, cause and resolution for ORA-01467 is

原因:DISTINCT，GROUP BY，ORDER BY或SET操作需要 排序键长于Oracle支持的排序键.列太多 或在SELECT语句中指定的组功能过多.

Cause: A DISTINCT, GROUP BY, ORDER BY, or SET operation requires a sort key longer than that supported by Oracle. Either too many columns or too many group functions were specified in the SELECT statement.

操作:减少涉及的列或组功能的数量 操作.

Action: Reduce the number of columns or group functions involved in the operation.

查询中有90列可能超出数据库的块大小.

There are 90 columns in your query which might be exceeding the block size of your DB.

除了减少列数以使这些列的数据必须适合单个块之外，没有其他解决方法.

There is no workaround for this issue except to reduce the number of columns so that data of those columns must fit into the single block.

干杯！

这篇关于ORA:01467排序键过长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！