避免SQL中的函式呼叫

LuiseDalian發表於2014-01-17
SQL函式

在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;

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

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

  21. --查詢people表的資訊,同時通過sex表,獲取人員的性別資訊
  22. SET autotrace traceonly
  23. SET timing ON
  24. SELECT sex_id, first_name || \' \' || last_name AS full_name, get_sex_name(sex_id) AS gender FROM people;

  26. 75559 rows selected.
  27. Elapsed: 00:00:04.68

  29. Execution Plan
  30. ----------------------------------------------------------
  31. 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;

  4. 75559 rows selected.

  6. Elapsed: 00:00:00.49

  8. Execution Plan
  9. ----------------------------------------------------------
  10. 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;

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

  15. 75559 rows selected.

  17. Elapsed: 00:00:05.62

  19. Execution Plan
  20. ----------------------------------------------------------
  21. 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章