程序中使用绑定变量可以降低硬解析的次数，节约shared pool的空间，降低CPU的负荷。但是绑定变量时会让执行计划略了直方图，导到执行计划有偏差。另外一点是用第三方工具使用绑定变量时，oracle允许一个无效的NUMBER类型可以插入到表里. 这个无效NUMBER类型导致的数据的不正确。使用EXP/IMP, CTAS(create table as select * from table)并不能消除这个错误。
======================================================
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 64-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> desc temp_test
Name Null? Type
----------------------------------------- --------
----------------------------
WAFER_NO NOT NULL VARCHAR2(10)
PROC_DATA_POINT NOT NULL VARCHAR2(20)
ROW_NO NOT NULL NUMBER
DEPARTMENT_ID NOT NULL VARCHAR2(5)
SQL> select * from system.temp_test;
WAFER_NO PROC_DATA_POINT ROW_NO DEPAR
---------- -------------------- ---------- -----
829331A ROW_RH -5.200E+27 MP
829331A ROW_RH -4.800E+27 MP
829331A ROW_RH -4.400E+27 MP
829331A ROW_RH -5.000E+25 MP
829331A ROW_RH -4.400E+25 MP
829331A ROW_RH -~ MP
829331A ROW_RH -5.000E+23 MP
829331A ROW_RH -4.400E+23 MP
829331A ROW_RH -5.000E+21 MP
829331A ROW_RH -4.600E+21 MP
829331A ROW_RH -5.200E+19 MP
======================================================
可以看出程序向NUMBER列插入了"-~"的内容.
关于Bug 979657的描述
=========================
Bug 979657 - Oracle allows insert of -0 (negative zero) or other corrupt numbers from OCI/Pro
Oracle allows an invalid Oracle numbers to be inserted into tables
when inserted using a bind variable in a ProC/OCI program or
by using some third party tool to populate tables.
Such invalid numbers can lead to wrong results or dumps in Oracle.
Note: This fix is enabled by setting at level 1
in 9.2. When enabled all IN numbers are checked for validity
and -0 is coerced to 0.
An invalid number will cause an ORA-1722 to be reported.
The fix is not enabled by default as it can lead to a performance
overhead in checking the number is valid.
Note2:
There is a problem with this fix when using NULL binds.
See bug 3448711.
Note3:
For 10g onwards this fix is protected by event 10890
instead of 10843. If EVENT_10890 is set in the client
environment then number checking is enabled.
=========================
关于绑定变量的一个实验参考
阅读(1299) | 评论(0) | 转发(0) |
