oracle函数调用在大容量插入期间被高速缓存

Question

当插入批量行并使用函数调用作为其中一个列值时，我从函数中每隔10-11行获得完全相同的值。该函数实际生成UUID值并返回唯一的结果。如果我用函数的实际代码替换insert语句中的函数调用，它就不会重复。

因此，我得出的结论是，oracle实际上缓存了函数的结果，并且每插入10-11行就只调用一次。如何更改此行为？

我调用的函数是从http://www.oracle-base.com/articles/9i/UUID9i.php获取的：

create or replace
FUNCTION        new_uuid RETURN VARCHAR2 AS
  l_seed        BINARY_INTEGER;
  l_random_num  NUMBER(5);
  l_date        VARCHAR2(25);
  l_random      VARCHAR2(4);
  l_ip_address  VARCHAR2(12);
BEGIN
  l_seed := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS'));
  DBMS_RANDOM.initialize (val => l_seed);
  l_random_num := TRUNC(DBMS_RANDOM.value(low => 1, high => 65535));
  DBMS_RANDOM.terminate;

  l_date       := conversion_api.to_hex(TO_NUMBER(TO_CHAR(SYSTIMESTAMP,'FFSSMIHH24DDMMYYYY')));
  l_random     := RPAD(conversion_api.to_hex(l_random_num), 4, '0');
  l_ip_address := conversion_api.to_hex(TO_NUMBER(REPLACE(NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), '123.123.123.123'), '.', '')));

  RETURN SUBSTR(l_date, 1, 8)                     || '-' ||
         SUBSTR(l_date, 9, 4)                     || '-' ||
         SUBSTR(l_date, 13, 4)                    || '-' ||
         RPAD(SUBSTR(l_date, 17), 4, '0')         || '-' ||
         RPAD(L_RANDOM || L_IP_ADDRESS, 12, '0');
END;

下面是我使用的insert语句：

INSERT INTO My_TABLE(ID, NAME,)
SELECT NEW_UUID(), NAME
FROM MY_TABLE2;
COMMIT;

此语句中的select会生成大量重复的UUID。虽然这条语句会产生唯一的结果：

SELECT RPAD(RPAD(my_schema.conversion_api.to_hex(TRUNC(DBMS_RANDOM.VALUE( 1, 65535))), 4, '0') || my_schema.conversion_api.to_hex(TO_NUMBER(REPLACE(NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), '123.123.123.123'), '.', ''))), 12, '0') sss
FROM my_schema.MY_TABLE

Answer 1

问题是，“随机”实际上不是随机的。给定DBMS_RANDOM.INITIALISE()的相同种子，后续对DBMS_RANDOM.VALUE()的调用将返回相同的结果。看看这个：

SQL> exec DBMS_RANDOM.initialize (val => 1)

PL/SQL procedure successfully completed.

SQL> select TRUNC(DBMS_RANDOM.value(low => 1, high => 65535)) from dual
  2  /

TRUNC(DBMS_RANDOM.VALUE(LOW=>1,HIGH=>65535))
--------------------------------------------
                                       49214

SQL> r
  1* select TRUNC(DBMS_RANDOM.value(low => 1, high => 65535)) from dual

TRUNC(DBMS_RANDOM.VALUE(LOW=>1,HIGH=>65535))
--------------------------------------------
                                       56385

SQL> r
  1* select TRUNC(DBMS_RANDOM.value(low => 1, high => 65535)) from dual

TRUNC(DBMS_RANDOM.VALUE(LOW=>1,HIGH=>65535))
--------------------------------------------
                                       23941

SQL> exec DBMS_RANDOM.initialize (val => 1)

PL/SQL procedure successfully completed.

SQL> select TRUNC(DBMS_RANDOM.value(low => 1, high => 65535)) from dual;

TRUNC(DBMS_RANDOM.VALUE(LOW=>1,HIGH=>65535))
--------------------------------------------
                                       49214

SQL> r
  1* select TRUNC(DBMS_RANDOM.value(low => 1, high => 65535)) from dual

TRUNC(DBMS_RANDOM.VALUE(LOW=>1,HIGH=>65535))
--------------------------------------------
                                       56385

SQL> r
  1* select TRUNC(DBMS_RANDOM.value(low => 1, high => 65535)) from dual

TRUNC(DBMS_RANDOM.VALUE(LOW=>1,HIGH=>65535))
--------------------------------------------
                                       23941

SQL> 

如果我们看一下你从Tim的网站上得到的代码，我们会看到这一行：

l_seed := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS'));

由此我们可以推测您的进程每秒插入10-11行:)

如果您将SYSDATE替换为SYSTIMESTAMP，并将掩码更改为go毫秒(或更小)，那么每次都应该获得不同的种子，因此每次都会得到不同的值。请注意，您仍然需要强制重新计算函数，以确保每行得到不同的结果(请参阅下面的演示)。

我有说过“保证”吗。啊哦。这是随机性的本质，它可以产生相同的结果两次运行。因此，也许这应该是“将每行获得相同结果的机会降至最低”。

或者，在开始批量插入之前，从函数中删除初始化并调用它。这是否可行完全取决于您的业务逻辑。

演示

下面是一个生成“随机”数的函数：

create or replace function get_random_number 
    (p_seed in number := 0)
    return pls_integer
is
begin
    if p_seed = 0
    then
        DBMS_RANDOM.initialize (val => TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS')));
    else    
        DBMS_RANDOM.initialize (val => p_seed);
    end if;
    return TRUNC(DBMS_RANDOM.value(low => 1, high => 65535));
end;
/

如果我们使用默认参数调用它20次，它每次都返回相同的数字：

SQL> select rownum
       , get_random_number
from   dual
connect by level <= 20
/
  2    3    4    5  
    ROWNUM GET_RANDOM_NUMBER
---------- -----------------
         1             10239
         2             10239
         3             10239
         4             10239
         5             10239
         6             10239
         7             10239
         8             10239
         9             10239
        10             10239
        11             10239
        12             10239
        13             10239
        14             10239
        15             10239
        16             10239
        17             10239
        18             10239
        19             10239
        20             10239

20 rows selected.

SQL> 

然而，如果我们传递一个值，它每次都使用不同的种子，瞧！我们得到了一个不同的结果：

SQL> select rownum
       , get_random_number(rownum)
from   dual
connect by level <= 20
/
  2    3    4    5  
    ROWNUM GET_RANDOM_NUMBER(ROWNUM)
---------- -------------------------
         1                     49214
         2                      6476
         3                     42426
         4                      2370
         5                     48546
         6                     52483
         7                      6964
         8                     46764
         9                     27569
        10                      7673
        11                     52446
        12                     50229
        13                     27861
        14                     31413
        15                     11518
        16                     13471
        17                     38766
        18                      9949
        19                     61656
        20                     25797

20 rows selected.

SQL>

这之所以有效，是因为传入ROWNUM会强制对每一行的函数求值。您不应该在生产系统中使用ROWNUM作为种子:时间戳更好。或者将datetime与rownum连接起来，为每一行提供一个唯一的种子。

Answer 2

APC的诊断正确。你需要在你的随机生成器种子中有熵。

不过，Oracle已经有了一个惟一的id生成器，即SYS_GUID()。

SELECT sys_guid(), name FROM my_table2;

您可以尝试这样做，它会生成9个GUID：

SELECT sys_guid() from dual connect by level < 10;

​

​

不要试图在轮子已经存在的情况下重新发明轮子。

Answer 3

我还没有尝试过，但我相信Oracle只计算一次new_uuid()函数的值，并为每个返回的行输出(就像您选择了systimestamp一样，不管是从什么地方...它将为所有行输出相同的时间戳。

因此，您可以修改您的函数，以便从每一行获取一些输入(可能是种子？)，或者只使用序列。