[20190612]NULL的資料型別.txt

[20190612]NULL的資料型別.txt

--//前幾天聽別人講了NULL的資料型別導致產生大量子游標的案例，java的程式，我檢索看到類似連結：
--//http://www.usn-it.de/index.php/2010/08/04/oracle112-mutex-s-too-many-child-cursors/

. setNUMBER(2)  => Bind Var. is number
. setNULL(2, java.sql.Types.INTEGER)  => Bind Var. is NUMBER

but setNUMBER(2)  => Bind Var. is varchar2.

But simply using setNull by default means setNull.NULL, and in both cases the setter method will not automatically use
the data type of the field in the table. Instead, the default is VARCHAR2, and exactly this makes my datatype different
to previous executions! So in theory, setting over 30 numeric values to null and non-null in an alternating matter,
creates 2^30 child cursors. Nice.

--//我不熟悉java程式設計，似乎講使用setNull定義的預設型別是VARCHAR2,應該寫成類似setNULL(2, java.sql.Types.INTEGER)的形式，才
--//能返回整形的NULL。
--//我記得以前yangtingkun講過關於NULL的資料型別的一些例子，自己拿sqlplus驗證看看：

1.環境：
SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0

SCOTT@test01p> create table t as select rownum id1 ,rownum id2 from dual connect by level<=10;
Table created.

SCOTT@test01p> variable v_id2 number ;
SCOTT@test01p> exec :v_id2 := null ;
PL/SQL procedure successfully completed.

SCOTT@test01p> update /*+ find_me */ t set id2 =:v_id2  where id1=3;
1 row updated.

SCOTT@test01p> rollback ;
Rollback complete.

SCOTT@test01p> variable v_id2 varchar2(10) ;
SCOTT@test01p> exec :v_id2 := null ;
PL/SQL procedure successfully completed.

SCOTT@test01p> update /*+ find_me */ t set id2 =:v_id2  where id1=3;
1 row updated.

SCOTT@test01p> rollback ;
Rollback complete.

--//確定:sql_id=3mwwz3vay4zn7.

2.看看是否產生子游標：

SCOTT@test01p> select sql_id,sql_text,child_number,executions from v$sql where sql_id='3mwwz3vay4zn7';
SQL_ID        SQL_TEXT                                                     CHILD_NUMBER EXECUTIONS
------------- ------------------------------------------------------------ ------------ ----------
3mwwz3vay4zn7 update /*+ find_me */ t set id2 =:v_id2  where id1=3                    0          1
3mwwz3vay4zn7 update /*+ find_me */ t set id2 =:v_id2  where id1=3                    1          1

SCOTT@test01p> @ share 3mwwz3vay4zn7
SQL_TEXT                       = update /*+ find_me */ t set id2 =:v_id2  where id1=3
SQL_ID                         = 3mwwz3vay4zn7
ADDRESS                        = 000007FEFFB24E10
CHILD_ADDRESS                  = 000007FF1184DB48
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind
mismatch(8)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>3</original_oacflg><original_oacdty>2</original_oacdty><new_oacdty>1</new_oacdty></ChildNode>
--------------------------------------------------
SQL_TEXT                       = update /*+ find_me */ t set id2 =:v_id2  where id1=3
SQL_ID                         = 3mwwz3vay4zn7
ADDRESS                        = 000007FEFFB24E10
CHILD_ADDRESS                  = 000007FF11C97D58
CHILD_NUMBER                   = 1
REASON                         = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind
mismatch(8)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>3</original_oacflg><original_oacdty>1</original_oacdty><new_oacdty>2</new_oacdty></ChildNode>
--------------------------------------------------
PL/SQL procedure successfully completed.

--//不能共享的原因是Bind mismatch.可以發現如果update欄位許多型別是number,按照上面的程式設計設定NULL(指使用setnull(N))會出現大量的子游標.
--//可以看出即使是NULL也是有資料型別的,不一致也會導致產生子游標.繼續測試:

SCOTT@test01p> variable v_id2 number ;
SCOTT@test01p> exec :v_id2 := 2 ;
PL/SQL procedure successfully completed.

SCOTT@test01p> update /*+ find_me */ t set id2 =:v_id2  where id1=3;
1 row updated.

SCOTT@test01p> rollback ;
Rollback complete.

SCOTT@test01p> select sql_id,sql_text,child_number,executions from v$sql where sql_id='3mwwz3vay4zn7';
SQL_ID        SQL_TEXT                                             CHILD_NUMBER EXECUTIONS
------------- ---------------------------------------------------- ------------ ----------
3mwwz3vay4zn7 update /*+ find_me */ t set id2 =:v_id2  where id1=3            0          2
3mwwz3vay4zn7 update /*+ find_me */ t set id2 =:v_id2  where id1=3            1          1