生産中有一個sql語句，做了union-all操作，對于時間的要求是極其嚴格的，目前已經從2秒的改進調整到了1秒以内，在此基礎上還想做進一步的調整，因為極其頻繁的查詢，如果一丁點的改進都會在時間上的飛躍，以下的sql語句目前時間控制在不到半秒的樣子。
因為表SMALL_OFFER_PARAM 是一個資料字典表，查詢的字段上沒有相關的索引。目前采用了exisits來做關聯。
SELECT DISTINCT 'K',
AR.RESOURCE_VALUE,
AR.RESOURCE_TYPE,
GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'),
TO_CHAR(SB.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')),
LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
'47001231000000'),
NVL(TO_CHAR(SB.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
'47001231000000')),
AR.AGREEMENT_NO,
SB.CUSTOMER_ID,
SB.SUB_STATUS,
SB.BUSINESS_ENTITY_ID,
SB.LANGUAGE,
SB.ROUTING_POLICY_ID,
SB.L9_PORT_IND,
SB.L9_SPLIT_PERIOD
FROM HUGE_RESOURCE AR,
SUBSCRIBER SB,
CUSTOMER CS
WHERE AR.AGREEMENT_NO = 1056851
AND AR.AGREEMENT_KEY = MOD(1056851, 100)
AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL)
AND AR.RANGE_IND = 'N'
AND SB.SUBSCRIBER_NO = AR.AGREEMENT_NO
AND EXISTS
(select /*+ INDEX(OP SMALL_OFFER_PARAM_1IX) */
1
from SMALL_OFFER_PARAM OP
where OP.PARAM_NAME in (AR.RESOURCE_PRM_CD, AR.BASE_PARAM_NAME) AND GUIDING_IND = 'Y' )
AND SB.CUSTOMER_ID = CS.CUSTOMER_ID
AND (SB.EXPIRATION_DATE IS NULL OR SB.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
AND (AR.EFFECTIVE_DATE
SB.EXPIRATION_DATE IS NULL)
AND (AR.EXPIRATION_DATE IS NULL OR
AR.EXPIRATION_DATE > SB.EFFECTIVE_DATE)
AND SB.SUB_STATUS != 'T'
UNION ALL
TO_CHAR(SH.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')),
NVL(TO_CHAR(SH.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
SH.CUSTOMER_ID,
SH.SUB_STATUS,
SH.BUSINESS_ENTITY_ID,
SH.LANGUAGE,
SH.ROUTING_POLICY_ID,
SH.L9_PORT_IND,
SH.L9_SPLIT_PERIOD
SUBSCRIBER_HISTORY SH,
AND SH.SUBSCRIBER_NO = AR.AGREEMENT_NO
where OP.PARAM_NAME in (AR.RESOURCE_PRM_CD, AR.BASE_PARAM_NAME) AND GUIDING_IND = 'Y')
AND SH.CUSTOMER_ID = CS.CUSTOMER_ID
AND (SH.EXPIRATION_DATE IS NULL OR SH.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
SH.EXPIRATION_DATE IS NULL)
AR.EXPIRATION_DATE > SH.EFFECTIVE_DATE)
AND SH.SUB_STATUS NOT IN ('C', 'T')
查詢穩定下來，資源情況如下：
Elapsed: 00:00:00.12
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5724 consistent gets
0 physical reads
0 redo size
1900 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
在此基礎上做了進一步的改進，采用了result_cache來做資料的緩存，使得後面的查詢能夠複用之前的結果集。
邏輯度有了一定的提高。時間從0.12降低到了0.08s,看似沒有多大的改進。如果細細算一下。
如果0.12秒，8萬條連續的查詢将會持續約2.67個小時。
如果0.08秒，8萬條連續的查詢将會持續約1.78個小時。
(select /*+ RESULT_CACHE */
DISTINCT PARAM_NAME as PARAM_NAME
from SMALL_OFFER_PARAM
where GUIDING_IND = 'Y') OP,
AND (OP.PARAM_NAME = AR.RESOURCE_PRM_CD or OP.PARAM_NAME = AR.BASE_PARAM_NAME)
GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'),
SH.CUSTOMER_ID,
使用資源情況如下：
Elapsed: 00:00:00.08
5156 consistent gets
當然了，再次基礎上更近一步。使用with子句
with op as (
select /*+ RESULT_CACHE */
where GUIDING_IND = 'Y'
)
OP,
SUBSCRIBER_HISTORY SH,
OP,
查詢穩定以後，資源使用情況如下，響應時間一下子降低到了0.03，相比0.12s提高了4倍。邏輯讀降低從5000多降低到了1122左右。
不過每次會有一個實體讀。
Elapsed: 00:00:00.03
2 recursive calls
4 db block gets
1122 consistent gets
1 physical reads
在這種情況下，使用result-cache的hint，就沒有什麼主要的提升了。
select
測試結果和加了hint沒有什麼差别了。
![Oracle的基本操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)