sql - ORA-01873 : the leading precision

Question

我正在尝试查询 View ，但收到“ORA-01873:间隔的前导精度太小”错误消息。下面是查询。

Select * from table order by Col1.

下面是 View 结构:

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

下面是 View 定义:

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或 select * from table where Col2 = 'xyz' order by Col1但上述查询不适用于任何 col。请建议。

Answer 1

对于 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 的纪元。 This is the 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

使用 -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。这应该很容易找到，您可能需要考虑向这些列添加检查约束，这样它们就不能被设置了高 - 检查列值是否小于或等于 1000 * (power(2, 31) - 1) .并且要么大于零，要么大于- 1000 * (power(2, 31)也。

当您有像 where Col1 = 123 这样的过滤器时它不会出错的原因是您的过滤器(谓词)被推送到 View 查询中，并且不会评估具有过高值的行。也许你只有一个这样的值，它的 col1值不是 123 及其 col2值不是“xyz”。如果您确定问题行并使用它进行过滤，则是实际 col1值它仍然会出错。在没有过滤器的情况下，对所有行进行评估。

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

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

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

如果您想排除它，那么您将修改 View 定义以添加过滤器，例如:

...
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,

您必须修改 View 定义而不是您的查询，除非 colh包含在选择列表中(它似乎不是)，即使是，您也只能排除它 - 并且仍然可能无法始终避免错误，具体取决于优化器如何处理查询。