ITPub博客

首页 > 数据库 > Oracle > 避免SQL中的函数调用

避免SQL中的函数调用

原创 Oracle 作者:luisedalian 时间:2014-01-17 11:16:54 0 删除 编辑

SQL编码中,尽量避免在SQL中进行函数调用,否则会产生大量的递归调用而影响性能。

如果用表连接来代替函数调用,往往会更高效。

点击(此处)折叠或打开

  1. DROP TABLE people;
  2. DROP TABLE sex;
  3. CREATE TABLE people (first_name VARCHAR2(200), last_name VARCHAR2(200), sex_id NUMBER);
  4. CREATE TABLE sex(NAME VARCHAR2(20), sex_id NUMBER);
  5. INSERT INTO people(first_name, last_name, sex_id) SELECT object_name, object_type, 1 FROM dba_objects;

  6. INSERT INTO sex(name, sex_id) VALUES(\'男\' ,1);
  7. INSERT INTO sex(name, sex_id) VALUES(\'女\' ,2);
  8. INSERT INTO sex(name, sex_id) VALUES(\'不详\' ,3);
  9. COMMIT;

  10. CREATE OR REPLACE FUNCTION get_sex_name(p_id sex.sex_id%TYPE) RETURN sex.name%TYPE
  11. IS
  12.   v_name sex.name%TYPE;
  13. BEGIN
  14.   SELECT NAME INTO v_name FROM sex WHERE sex_id = p_id;
  15.   RETURN v_name;
  16. END;
  17. /

  18. --查询people表的信息,同时通过sex表,获取人员的性别信息
  19. SET autotrace traceonly
  20. SET timing ON
  21. SELECT sex_id, first_name || \' \' || last_name AS full_name, get_sex_name(sex_id) AS gender FROM people;

  22. 75559 rows selected.
  23. Elapsed: 00:00:04.68

  24. Execution Plan
  25. ----------------------------------------------------------
  26. Plan hash value: 2528372185


点击(此处)折叠或打开

  1. SELECT p.sex_id, p.first_name || \' \' || p.last_name AS full_name, sex.name
  2. from people p, sex where sex.sex_id = p.sex_id;

  3. 75559 rows selected.

  4. Elapsed: 00:00:00.49

  5. Execution Plan
  6. ----------------------------------------------------------
  7. Plan hash value: 1973058250

 

函数调用

表连接

执行时间

4.68

0.49

递归调用

75592

9

逻辑读

534500

5564


点击(此处)折叠或打开

  1. --验证函数调用的次数,就是执行计划中递归调用的次数
  2. --在函数中增加 dbms_application_info.set_client_info(userenv(\'client_info\')+1)。
  3. CREATE OR REPLACE FUNCTION get_sex_name(p_id sex.sex_id%TYPE) RETURN sex.name%TYPE IS
  4.   v_name sex.name%TYPE;
  5. BEGIN
  6.   SELECT NAME INTO v_name FROM sex WHERE sex_id = p_id;
  7.   dbms_application_info.set_client_info(userenv(\'client_info\') + 1);
  8.   RETURN v_name;
  9. END;

  10. exec dbms_application_info.set_client_info(\'0\');
  11. SET autotrace traceonly
  12. SELECT sex_id, first_name || \' \' || last_name AS full_name, get_sex_name(sex_id) AS gender FROM people;

  13. 75559 rows selected.

  14. Elapsed: 00:00:05.62

  15. Execution Plan
  16. ----------------------------------------------------------
  17. Plan hash value: 2528372185



点击(此处)折叠或打开

  1. --查看函数调用次数
  2. SELECT userenv(\'client_info\') FROM dual;
  3. USERENV(\'CLIENT_INFO\')
  4. ----------------------------------------------------------------
  5. 75559


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1072037/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    1986
  • 访问量
    5641821