查询中的Oracle和SQLServer函数评估
发布时间:2020-12-12 16:26:08 所属栏目:百科 来源:网络整理
导读:假设我在Oracle中的select或where子句上有一个函数调用,如下所示: select a,b,c,dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) from my_table 可以为MS SQLServer构建类似的示例. 每种情况下的预期行为是什么? HASH函数是否会为表中的每一行调用一次,
假设我在Oracle中的select或where子句上有一个函数调用,如下所示:
select a,b,c,dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) from my_table 可以为MS SQLServer构建类似的示例. 每种情况下的预期行为是什么? HASH函数是否会为表中的每一行调用一次,或者DBMS将足够智能只调用一次函数,因为它是一个具有常量参数且没有副作用的函数? 非常感谢. Oracle的答案取决于它.将为每个选定的行调用该函数,除非函数标记为“确定性”,在这种情况下,它只会被调用一次.CREATE OR REPLACE PACKAGE TestCallCount AS FUNCTION StringLen(SrcStr VARCHAR) RETURN INTEGER; FUNCTION StringLen2(SrcStr VARCHAR) RETURN INTEGER DETERMINISTIC; FUNCTION GetCallCount RETURN INTEGER; FUNCTION GetCallCount2 RETURN INTEGER; END TestCallCount; CREATE OR REPLACE PACKAGE BODY TestCallCount AS TotalFunctionCalls INTEGER := 0; TotalFunctionCalls2 INTEGER := 0; FUNCTION StringLen(SrcStr VARCHAR) RETURN INTEGER AS BEGIN TotalFunctionCalls := TotalFunctionCalls + 1; RETURN Length(SrcStr); END; FUNCTION GetCallCount RETURN INTEGER AS BEGIN RETURN TotalFunctionCalls; END; FUNCTION StringLen2(SrcStr VARCHAR) RETURN INTEGER DETERMINISTIC AS BEGIN TotalFunctionCalls2 := TotalFunctionCalls2 + 1; RETURN Length(SrcStr); END; FUNCTION GetCallCount2 RETURN INTEGER AS BEGIN RETURN TotalFunctionCalls2; END; END TestCallCount; SELECT a,TestCallCount.StringLen('foo') FROM( SELECT 0 as a FROM dual UNION SELECT 1 as a FROM dual UNION SELECT 2 as a FROM dual ); SELECT TestCallCount.GetCallCount() AS TotalFunctionCalls FROM dual; 输出: A TESTCALLCOUNT.STRINGLEN('FOO') ---------------------- ------------------------------ 0 3 1 3 2 3 3 rows selected TOTALFUNCTIONCALLS ---------------------- 3 1 rows selected 因此,在第一种情况下,StringLen()函数被调用了三次.现在用StringLen2()执行时表示确定性: SELECT a,TestCallCount.StringLen2('foo') from( select 0 as a from dual union select 1 as a from dual union select 2 as a from dual ); SELECT TestCallCount.GetCallCount2() AS TotalFunctionCalls FROM dual; 结果: A TESTCALLCOUNT.STRINGLEN2('FOO') ---------------------- ------------------------------- 0 3 1 3 2 3 3 rows selected TOTALFUNCTIONCALLS ---------------------- 1 1 rows selected 所以StringLen2()函数只被调用一次,因为它被标记为确定性. 对于未标记为确定性的函数,您可以通过修改查询来解决此问题: select a,hashed from my_table cross join ( select dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) as hashed from dual ); (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |