Oracle
如何創建索引以提高在 oracle 中創建表的聚合函式的性能
我正在使用 APEX_JSON 創建一個 Oracle ORDS API。我最近開始使用綁定變數而不是使用
||. 我正在嘗試in在我的情況下使用一個子句where。問題從這裡開始。我需要在左側的欄位
in是 anumber並且我的儲存過程的參數需要是varchar2因為它是逗號分隔的數字列表。範例（為簡潔而編輯）
CREATE OR REPLACE PROCEDURE GET_CATEGORYPRODS ( PCATEGORYID IN NUMBER, COMMASEPPRODUCTIDS IN VARCHAR2 ) AS l_cursor SYS_REFCURSOR; v_stmt_str STRING(5000); v_name NUMBER; --PRODUCT.NAME%TYPE; v_displayorder NUMBER; --PRODUCTCATEGORY%TYPE; BEGIN v_stmt_str := 'SELECT P.NAME, PC.DISPLAYORDER FROM PRODUCT P INNER JOIN PRODUCTCATEGORY PC ON P.PRODUCTID = PC.PRODUCTID WHERE PC.CATEGORYID := :CATEGORYID AND (P.PRODUCTID IN (SELECT * FROM TABLE(STRING_TO_TABLE_NUM(:COMMASEPPRODUCTIDS))) -- PREVIOUSLY WHERE || OCCURRED OR (:COMMASEPPRODUCTIDS IS NULL))'; s_counter := 0; OPEN l_cursor FOR v_stmt_str USING pcategoryid, commasepproductids, commasepproductids; FETCH l_cursor INTO v_productid, v_displayorder; APEX_JSON.OPEN_ARRAY; LOOP EXIT WHEN l_cursor%notfound; apex_json.open_object; apex_json.write('ProductID', v_productid); apex_json.write('DisplayOrder', v_displayorder); apex_json.close_object; END LOOP; apex_json.close_all; END GET_CATEGORYPRODS;參數範例
'97187,142555,142568,48418,43957,44060,45160,45171,333889,333898'為了解決這個問題，我創建了一個聚合函式，它接收一個字元串，用逗號分割，並將行通過管道傳遞給自定義類型。
自定義類型
create or replace type tab_number is table of number;聚合函式
create or replace FUNCTION string_to_table_num ( p VARCHAR2 ) RETURN tab_number PIPELINED IS BEGIN FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res FROM (SELECT p || ',' str FROM dual) CONNECT BY level <= length(str) - length(replace(str, ',', ''))) LOOP PIPE ROW(lower(cc.res)); END LOOP; END;查詢速度明顯變慢。我認為需要進行一些優化，但我以前從未做過任何類型的優化。經過一番研究，我發現
EXPLAIN PLAN並在原始查詢上執行了它。由於綁定變數，我無法得到一個好的答案，所以我決定在聚合函式上執行它。解釋計劃查詢
explain plan for select * from TABLE(string_to_table_num('97187,142555,142568,48418,43957,44060,45160,45171,333889,333898')); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);當我執行
EXPLAIN PLAN聚合函式時，結果是：Plan hash value: 127161297 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| STRING_TO_TABLE_NUM | 8168 | 16336 | 29 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------正如我之前所說，我是分析和優化查詢的菜鳥，但是對於這樣一個簡單的函式來說，8168 行和 16336 字節似乎很多。我查看了一下，發現問題可能是流水線表缺少索引。我試圖為該類型添加一個索引，
tab_number但它把它變成了一個需要在查詢中聲明的 PL/SQL 對象，而不是一個函式。我對這個很迷茫。如果您對我提到的任何場景有任何建議，我會全力以赴。提前致謝。
編輯 按照下面 Balazs 的回答中的步驟進行操作後，我執行
EXPLAIN PLAN了聚合函式和selectfrom 我的過程。聚合函式
Plan hash value: 229973419 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| F_CONVERT2 | 8168 | 16336 | 29 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------選擇功能
Plan hash value: 1690769838 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 2790 | 972 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | NESTED LOOPS OUTER | | 65 | 30225 | 972 (0)| 00:00:01 | | 3 | NESTED LOOPS OUTER | | 65 | 27950 | 842 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 65 | 27365 | 777 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTCATEGORY | 65 | 845 | 712 (0)| 00:00:01 | |* 6 | INDEX SKIP SCAN | SYS_C0012982 | 65 | | 709 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID | PRODUCTNEW | 1 | 408 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C0013161 | 1 | | 0 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | SYS_C0012993 | 1 | 9 | 1 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | IDX_URLMAPPER_PRODUCTID_FRIENDLYURL | 1 | 35 | 2 (0)| 00:00:01 | |* 11 | COLLECTION ITERATOR PICKLER FETCH | F_CONVERT2 | 1 | 2 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:COMMASEPPRODUCTIDS IS NULL OR EXISTS (SELECT 0 FROM TABLE() "KOKBF$0" WHERE VALUE(KOKBF$)=:B1)) 6 - access("pc"."CATEGORYID"=TO_NUMBER(:PCATEGORYID)) filter("pc"."CATEGORYID"=TO_NUMBER(:PCATEGORYID)) 7 - filter("p"."PUBLISHED"=1 AND "p"."DELETED"=0) 8 - access("p"."PRODUCTID"="pc"."PRODUCTID") 9 - access("p"."PRODUCTID"="pm"."PRODUCTID"(+)) 10 - access("p"."PRODUCTID"="um"."PRODUCTID"(+)) 11 - filter(VALUE(KOKBF$)=:B1)這些數字似乎在合理範圍內嗎？
編輯 2
create or replace PROCEDURE GET_CATEGORYPRODS2 ( COMMASEPPRODUCTIDS IN VARCHAR2 , COMMASEPPRODUCTSKUS IN VARCHAR2 , PCATEGORYID IN NUMBER , SORTBY IN VARCHAR2 ) .... IF sortby IS NULL OR sortby = 'null' OR sortby = '' THEN v_sortby := '"p".Discontinued, "pc".DisplayOrder '; ELSIF sortby = 'PriceAscending' THEN v_sortby := '"p".discontinued, "p".price '; ELSIF sortby = 'PriceDescending' THEN v_sortby := '"p".discontinued, "p".price DESC '; ELSIF sortby = 'Name' THEN v_sortby := '"p".discontinued, "p".name '; ELSE v_sortby := '"p".discontinued, "pc".displayorder '; END IF; ....我還使用綁定變數將其添加到查詢的末尾。
試試這樣：
create or replace type tab_number is table of number; create or replace FUNCTION string_to_table_num (p VARCHAR2) RETURN tab_number res tab_nubmer; BEGIN select regexp_substr(p,'[^,]+', 1, level) bulk collect into res from dual connect by regexp_substr(p, '[^,]+', 1, level) is not null; return res; END;並像這樣使用它：
ids tab_nubmer := string_to_table_num(COMMASEPPRODUCTIDS); begin OPEN l_cursor FOR SELECT P.NAME, PC.DISPLAYORDER FROM PRODUCT P INNER JOIN PRODUCTCATEGORY P ON P.PRODUCTID = PC.PRODUCTID WHERE PC.CATEGORYID = CATEGORYID AND P.PRODUCTID MEMBER of ids;您不需要動態 SQL。
可以
ORDER BY這樣做：ORDER BY "p".discontinued, CASE sortby WHEN 'PriceAscending' THEN "p".price WHEN 'PriceDescending' THEN "p".price DESC WHEN 'Name' THEN "p".name ELSE "pc".DisplayOrder -- includes 'null' or '' END