ORA-01873: 领先精度

我正在尝试查询视图，但得到“ORA-01873: 区间的前导精度太小” 错误消息。以下是查询。

Select * from table order by Col1.

下面是视图结构：

Col1    NOT NULL NUMBER
Col2    NOT NULL NVARCHAR2(80)
Col3    NOT NULL NUMBER
Col4    NOT NULL VARCHAR2(10)
Col5        NVARCHAR2(80)
Col6        NVARCHAR2(255)
Col7        NUMBER
Col8    NOT NULL NVARCHAR2(255)
Col9    NOT NULL NVARCHAR2(1)
Col10   NOT NULL NUMBER
Col11       VARCHAR2(19)
Col12       VARCHAR2(19)
Col13       VARCHAR2(19)
Col14       VARCHAR2(19)
Col15       VARCHAR2(19)
Col16       VARCHAR2(19)
Col17       NUMBER
Col18       NVARCHAR2(255)
Col19       NVARCHAR2(80)
Col20   NOT NULL NUMBER

下面是视图定义：

SELECT tab2.cola AS Col1,
    tab1.col AS COl2,    
    tab2.colb AS Col3,
    tab7.col AS Col4,
    DECODE(tab3.col, NULL, tab4.col) AS COl5,
    tab8.col AS Col6,
    tab6.col AS COl7,
    tab2.Colc AS Col8,
    tab2.cold AS Col9,
    tab2.cole AS Col10,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colf / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS COl11,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colg / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS Col12,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colh / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS Col13,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.coli / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS Col14,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colj / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS COl15,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colk / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS COl16,
    tab2.coll AS Col17,
    tab9.col AS Col18,
    tab10.col AS Col19,
    tab2.colm AS Col20
FROM tab1 ,
    tab2 ,
    tab3,
    tab4,
    tab5,
    tab6,
    tab7,
    tab8,
    tab9,
    tab10
WHERE ....

其他查询运行良好，例如select * from table where Col1 = 123 or select * from table where Col2 = 'xyz' order by Col1但上述查询不适用于任何列。请建议。

您的数字“纪元”数字之一对于numtodsinterval()函数来处理。您可以传递的最大秒数为 2^31-1：

SQL> select numtodsinterval(power(2,31) - 1, 'SECOND') as interval from dual; 

INTERVAL     
--------------
24855 3:14:7.0

SQL> select numtodsinterval(power(2,31), 'SECOND') as interval from dual; 

SQL Error: ORA-01873: the leading precision of the interval is too small
01873. 00000 -  "the leading precision of the interval is too small"
*Cause:    The leading precision of the interval is too small to store the
           specified interval.
*Action:   Increase the leading precision of the interval or specify an
           interval with a smaller leading precision.

作为纪元，允许的最高秒数代表 2038-01-19 03:14:07。这是2038年的问题 https://en.wikipedia.org/wiki/Year_2038_problem， 本质上。

你也可以用负数到达那里：

SQL> select numtodsinterval(-2208988800, 'SECOND') as interval from dual;

SQL Error: ORA-01873: the leading precision of the interval is too small

Using -power(2, 31)换行为正值，但任何低于该值的值都会出错：

SQL> select numtodsinterval(power(2,31) - 1, 'SECOND') as interval from dual;

INTERVAL     
--------------
24855 3:14:7.0

SQL> select numtodsinterval(-power(2,31), 'SECOND') as interval from dual;

INTERVAL     
--------------
24855 3:14:8.0

SQL> select numtodsinterval(-power(2,31) - 1, 'SECOND') as interval from dual;

SQL Error: ORA-01873: the leading precision of the interval is too small

您要除以 1000，因此 F 到 K 列之一的值超过 2147483647000。这应该很容易找到，您可能需要考虑向这些列添加检查约束，这样它们就无法被设置high - 检查列值是否小于或等于1000 * (power(2, 31) - 1)。并且大于零，或大于-1000 * (power(2, 31) too.

当你有像这样的过滤器时它不会出错的原因where Col1 = 123是您的过滤器（谓词）被推入视图查询中，并且不评估值太高的行。也许你只有一个这样的值，并且它col1值为not123 及其col2值为not'xyz'。如果您识别出问题行并使用它的实际值进行过滤col1值它仍然会出错。如果没有过滤器，将对所有行进行评估。

您拥有的特定负数似乎是一个神奇的数字：

SQL> select date '1970-01-01' - 2208988800/86400 from dual;

DATE'1970-01-01'-2208988800/86400
---------------------------------
1900-01-01 00:00:00              

如果您想排除它，那么您必须修改视图定义以添加过滤器，例如：

...
AND tab2.colh > 0

或者更改列表达式来处理它，要么忽略它并将其保留为空，或者可能更有用地返回那个神奇的日期：

    TO_CHAR(CASE WHEN tab2.colh = -2208988800000 THEN DATE'1900-01-01'
      ELSE DATE'1970-01-01' + NUMTODSINTERVAL( tab2.colh / 1000,'SECOND')
      END, 'YYYY/MM/DD HH24:MI:SS') AS Col13,

您还可以从使用间隔更改为使用日期算术：

    TO_CHAR(DATE'1970-01-01' + ( tab2.colh / 86400000 ), 'YYYY/MM/DD HH24:MI:SS') AS Col13,

不过，您必须修改视图定义而不是查询，除非colh包含在选择列表中（似乎没有），即使它是，你也只能排除它 - 并且仍然可能无法总是避免错误，具体取决于优化器处理查询的方式。