ORA-01873:领先的精度 [英] ORA-01873: the leading precision
问题描述
我正在尝试查询视图，但收到ORA-01873:间隔的前导精度太小"错误消息.以下是查询.
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.
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 或 select * from table where Col2 = 'xyz' order by Col1 但上面的查询不是为任何上校工作.请提出建议.
Other queries are running fine like select * from table where Col1 = 123 or select * from table where Col2 = 'xyz' order by Col1 but above query is not working for any col. Please suggest.
推荐答案
对于 numtodsinterval() 函数而言，您的一个数字纪元"数字似乎太大(或太小).您可以传递的最大秒数为 2^31-1:
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:
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 年的问题.
As an epoch that highest allowed number of second represents 2038-01-19 03:14:07. This is the year 2038 problem, essentially.
你也可以用负数到达那里:
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
使用 -power(2, 31) 包装为正值，但任何低于该值的错误:
Using -power(2, 31) wraps to a positive value, but anything lower than that errors:
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).
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.
当你有一个像 where Col1 = 123 这样的过滤器时它不会出错的原因是你的过滤器(谓词)被推入视图查询和具有值的行太高不评价.也许你只有一个这样的值，它的 col1 值是 not 123 而它的 col2 值是 not'xy'.如果您识别出问题行并使用它的实际 col1 值进行过滤，它仍然会出错.在没有过滤器的情况下，对所有行进行评估.
The reason it doesn't error when you have a filter like where Col1 = 123 is 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 col1 value is not 123 and its col2 value is not 'xyz'. If you identify a problem row and filter using it's actual col1 value it will still error. With no filters the evaluation is done for all rows.
您所拥有的特定负数似乎是一个神奇的数字:
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,
你必须修改视图定义而不是你的查询，除非 colh 包含在选择列表中(它似乎不是)，即使是你只能排除它 - 这仍然可能无法始终避免错误，具体取决于优化器如何处理查询.
You'll have to modify the view definition rather than your query though, unless colh is 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.
这篇关于ORA-01873:领先的精度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
