我得到的响应时间约为200毫秒。
我想对其进行更多优化。
我该如何实现?
CREATE OR REPLACE
PROCEDURE GETSTORES
(
LISTOFOFFERIDS IN VARCHAR2,
REF_OFFERS OUT TYPES.OFFER_RECORD_CURSOR
)
AS
BEGIN
OPEN REF_OFFERS FOR
SELECT
/*+ PARALLEL(STORES 5) PARALLEL(MERCHANTOFFERS 5)*/
MOFF.OFFERID,
s.STOREID,
S.LAT,
s.LNG
FROM
MERCHANTOFFERS MOFF
INNER JOIN STORES s ON MOFF.STOREID =S.STOREID
WHERE
MOFF.OFFERID IN
(
SELECT
REGEXP_SUBSTR(LISTOFOFFERIDS,'[^,]+', 1, LEVEL)
FROM
DUAL CONNECT BY REGEXP_SUBSTR(LISTOFOFFERIDS, '[^,]+', 1, LEVEL) IS NOT NULL
)
;
END
GETSTORES;
我正在使用regex_substr从LISTOFOFFERIDS中用逗号分隔的字符串中获取OfferID的列表。
我已经在Stores表的STOREID上创建了索引,但无济于事。
一种新的方法也可以做到这一点,如果它更快。
类型声明相同:
create or replace
PACKAGE TYPES
AS
TYPE OFFER_RECORD
IS
RECORD(
OFFER_ID MERCHANTOFFERS.OFFERID%TYPE,
STORE_ID STORES.STOREID%TYPE,
LAT STORES.LAT%TYPE,
LNG STORES.LNG%TYPE
);
TYPE OFFER_RECORD_CURSOR
IS
REF
CURSOR
RETURN OFFER_RECORD;
END
TYPES;
选择计划揭示了以下信息:
Plan hash value: 1501040938
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 276 | 67620 | 17 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 276 | 67620 | 17 (12)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 276 | 61272 | 3 (34)| 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 1 | 202 | 3 (34)| 00:00:01 |
| 5 | HASH UNIQUE | | 1 | | 3 (34)| 00:00:01 |
|* 6 | CONNECT BY WITHOUT FILTERING (UNIQUE)| | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | OFFERID_INDEX | 276 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | MERCHANTOFFERS | 276 | 5520 | 0 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | STORES | 9947 | 223K| 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MERCHANTOFFERS"."STOREID"="STORES"."STOREID")
6 - filter( REGEXP_SUBSTR ('M1-Off2,M2-Off5,M2-Off9,M5-Off4,M10-Off1,M1-Off3,M2-Off4,M3-Off2,M4-Of
f6,M5-Off1,M6-Off1,M8-Off1,M7-Off3,M1-Off1,M2-Off1,M3-Off1,M3-Off4,M3-Off5,M3-Off6,M4-Off1,M4-Off7,M2
-Off2,M3-Off3,M5-Off2,M7-Off1,M7-Off2,M1-Off7,M2-Off3,M3-Off7,M5-Off5,M4-Off2,M4-Off3,M4-Off5,M8-Off2
,M6-Off2,M1-Off5,M1-Off6,M1-Off9,M1-Off8,M2-Off6,M2-Off7,M4-Off4,M9-Off1,M6-Off4,M1-Off4,M1-Off10,M2-
Off8,M3-Off8,M6-Off3,M5-Off3','[^,]+',1,LEVEL) IS NOT NULL)
8 - access("MERCHANTOFFERS"."OFFERID"="$kkqu_col_1")
最佳答案
/*+ PARALLEL(S 8) PARALLEL(MOFF 8)*/
。当您有别名时,必须在提示中使用别名。 STORES(STOREID, LAT, LNG)
)select count(distinct storeid) from (your_query)
),以及多少个商店? (Select count(*) from Stores
)? dbms_stats.gather_table_stats
分析了表? connect by
查询不是问题。它在0.02秒内运行。