在 Oracle 視圖中查詢 5 億條記錄需要很長時間

在浪費了一周的時間來解決我的問題並在 Stack Overflow 上搜尋之後，我現在真的需要專業人士的幫助。我在兩個不同的表上創建了一個視圖。此視圖有 5 億條記錄。

看法：-

CREATE VIEW VZ.SERVICE_UTILIZATION
AS (
select T0base.SNO as SNO,
   T0base.SERVICE_CALL_DATE as SERVICE_CALL_DATE,
   T1.LOOKUP_VALUE as ORIG_SYS,
   T2.LOOKUP_VALUE as DEST_SYS,
   T0base.MSG_ID as MSG_ID,
   T7.LOOKUP_VALUE as SERVICE_NAMESPACE,
   T4.LOOKUP_VALUE as OPERATION_ACCESSED,
   T0base.RESPONSE_TIME as SERVICE_PROVIDER_TIME,
   T5.LOOKUP_VALUE as SERVICE_END_POINT,
   T0base.SLA_TIME as SLA_TIME,
   T0base.SUCCESS as SUCCESS,
   T8.LOOKUP_VALUE as PERSPECTIVE,
   T0base.CREATION_DTM as CREATION_DTM,
   T3.LOOKUP_VALUE as SERVICE_TYPE,
   T0base.PROCESS_ID as PROCESS_ID,
   T0base.ACTOR as ACTOR,
   T6.LOOKUP_VALUE as CLIENT_END_POINT,
   T0base.SERVICE_PROVIDER_TIME_TYPE as SERVICE_PROVIDER_TIME_TYPE,
   T0base.PARENT_PROCESS_ID as PARENT_PROCESS_ID,
   T0base.ORIG_SYS_NO as ORIG_SYS_NO,
   T0base.DEST_SYS_NO as DEST_SYS_NO,
   T0base.SERVICE_TYPE_NO as SERVICE_TYPE_NO,
   T0base.OPERATION_NO as OPERATION_ACCESSED_NO,
   T0base.SERVICE_END_POINT_NO as SERVICE_END_POINT_NO,
   T0base.CLIENT_END_POINT_NO as CLIENT_END_POINT_NO,
   T0base.NAMESPACE_NO as NAMESPACE_NO,
   T0base.SERVICEBUSPROCESSID as SERVICEBUSPROCESSID,
   T0base.TRANSACTIONID as TRANSACTIONID,
   T0base.PARTNERMACHINE as PARTNERMACHINE,
   T0base.PARTNERCONNECTIVITYTIME as PARTNERCONNECTIVITYTIME,
   T0base.DATASIZE as DATASIZE
from t_vz_service_utilization T0base
 left join t_vz_lookup T1
   on (T0base.ORIG_SYS_NO = T1.SNO )
 left join t_vz_lookup T2
   on (T0base.DEST_SYS_NO = T2.SNO )
 left join t_vz_lookup T3
   on (T0base.SERVICE_TYPE_NO = T3.SNO )
 left join t_vz_lookup T4
   on (T0base.OPERATION_NO = T4.SNO )
 left join t_vz_lookup T5
   on (T0base.SERVICE_END_POINT_NO = T5.SNO )
 left join t_vz_lookup T6
   on (T0base.CLIENT_END_POINT_NO = T6.SNO )
 left join t_vz_lookup T7
   on (T0base.NAMESPACE_NO = T7.SNO )
 left join t_vz_lookup T8
   on (T0base.PERSPECTIVE_NO = T8.SNO )
)

現在，我正在從這個視圖中查詢不同的值

SELECT /*+ FULL(su1) PARALLEL(a, 8) */ 
              DISTINCT su1.orig_sys AS orig_sys, 
                       su1.dest_sys AS dest_sys, 
                       su1.operation_accessed AS operation, 
                       su1.service_namespace AS namespace 
         FROM service_utilization su1;

此查詢操作需要永遠執行。另外，我假設在查詢視圖時基礎表上的索引不起作用。在處理具有 500+ 百萬條記錄的視圖時，我正在尋找其他替代方法來提高查詢性能。

注意：-物化視圖對我來說不是一個選項。

編輯：-

表上的索引t_vz_service_utilization是SNO,MSG_ID,ORIG_SYS_NO, compound index(ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO)

表上的索引t_vz_lookup是SNO

解釋計劃：-

select /*+ full(su) no_index(su) parallel(su 8) */
 distinct ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO
from
 t_vz_service_utilization su
;

那裡。現在您可以在此使用查找表，就像這樣。

select /*+ no_merge(T0base) leading(T0base) */
 T1.LOOKUP_VALUE as ORIG_SYS,
 T2.LOOKUP_VALUE as DEST_SYS,
 T4.LOOKUP_VALUE as OPERATION_ACCESSED,
 T7.LOOKUP_VALUE as SERVICE_NAMESPACE
from
 (select /*+ full(su) no_index(su) parallel(su 8) */
     distinct ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO
   from
     t_vz_service_utilization su) T0base
 left join t_vz_lookup T1
   on (T0base.ORIG_SYS_NO = T1.SNO )
 left join t_vz_lookup T2
   on (T0base.DEST_SYS_NO = T2.SNO )
 left join t_vz_lookup T4
   on (T0base.OPERATION_NO = T4.SNO )
 left join t_vz_lookup T7
   on (T0base.NAMESPACE_NO = T7.SNO )
;

當然，這可能遠非最佳，但您只給了我們查詢。我們不知道您的索引（哪些列、b-tree/點陣圖？）、約束（FK/UK/PK）、表結構（寬度、行遷移/鍊式行）、初始化參數（啟用星形轉換？）、表/索引統計，甚至沒有計劃…

引用自：https://dba.stackexchange.com/questions/262837