oracle ORA-01873：领先的精度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37772124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01873: the leading precision
提问by ankitpandey
I am trying to query view but getting "ORA-01873: the leading precision of the interval is too small" error message. Below is the query.
我正在尝试查询视图，但收到“ ORA-01873：间隔的前导精度太小”错误消息。下面是查询。
Select * from table order by Col1.
Below is view structure:
下面是视图结构：
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
Below is View Definition:
以下是视图定义：
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 ....
Other queries are running fine like select * from table where Col1 = 123or select * from table where Col2 = 'xyz' order by Col1but above query is not working for any col. Please suggest.
其他查询运行良好，如select * from table where Col1 = 123或select * from table where Col2 = 'xyz' order by Col1但上述查询不适用于任何列。请建议。
回答by Alex Poole
One of your numeric 'epoch' numbers appears to be too large (or too small) for the numtodsinterval()function to handle. The biggest value you can pass as the number of seconds is 2^31-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.
As an epoch that highest allowed number of second represents 2038-01-19 03:14:07. This is the year 2038 problem, essentially.
作为最高允许秒数代表 2038-01-19 03:14:07 的纪元。这本质上是 2038 年的问题。
You can get there with a negative number too:
您也可以使用负数到达那里：
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)wraps to a positive value, but anything lower than that errors:
使用-power(2, 31)wraps 为正值，但任何低于该错误的错误：
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
You are dividing by 1000, so one of your columns F to K has a value that exceeds 2147483647000. That should be fairly easy to find, and you might want to consider adding a check constraint to those columns so they can't be set too high - check that the column value is less than or equal to 1000 * (power(2, 31) - 1). And either greater than zero, or greater than-1000 * (power(2, 31)too.
您除以 1000，因此 F 到 K 列之一的值超过 2147483647000。这应该很容易找到，您可能需要考虑向这些列添加检查约束，这样它们就不能被设置了高 - 检查列值是否小于或等于1000 * (power(2, 31) - 1)。要么大于零，要么大于- 1000 * (power(2, 31)。
The reason it doesn't error when you have a filter like where Col1 = 123is that your filter (predicate) is pushed up into the view query and the row(s) with values that are too high are not evaluated. Perhaps you only have a single such value, and its col1value is not123 and its col2value is not'xyz'. If you identify a problem row and filter using it's actual col1value it will still error. With no filters the evaluation is done for all rows.
当你有一个过滤器时它不会出错的原因where Col1 = 123是你的过滤器（谓词）被推到了视图查询中，并且没有评估值太高的行。也许你只有一个这样的值，它的col1值不是123，它的col2值不是'xyz'。如果您确定问题行并使用它的实际col1值进行过滤，它仍然会出错。在没有过滤器的情况下，对所有行进行评估。
The specific negative number you have seems to be a magic number:
您拥有的特定负数似乎是一个神奇的数字：
SQL> select date '1970-01-01' - 2208988800/86400 from dual;
DATE'1970-01-01'-2208988800/86400
---------------------------------
1900-01-01 00:00:00
If you want to exclude that then you would have modify the view definition to either add a filter, e.g:
如果您想排除它，那么您将修改视图定义以添加过滤器，例如：
...
AND tab2.colh > 0
or change the column expression to handle it, eithe rignoring it and leaving it null, or probably more usefully returning that magic date:
或更改列表达式以处理它，要么忽略它并将其保留为空，或者可能更有用地返回该神奇日期：
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,
You could also change from using an interval to using date arithmetic:
您还可以从使用间隔更改为使用日期算法：
TO_CHAR(DATE'1970-01-01' + ( tab2.colh / 86400000 ), 'YYYY/MM/DD HH24:MI:SS') AS Col13,
You'll have to modify the view definition rather than your query though, unless colhis included in the select list (which it doesn't seem to be), and even if it was you could only exclude it - and that still might not always avoid the error, depending on how the optimiser handled the query.
你必须修改视图定义而不是你的查询，除非colh它包含在选择列表中（它似乎不是），即使它是你也只能排除它 - 这仍然可能并不总是避免错误，这取决于优化器如何处理查询。
