I really need professionals help now after wasting a week of time to solve my problem and searching on Stack Overflow. I have a view created on top of two different tables. This view has 500 million records.
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 ) )
Now, I'm querying for the distinct values from this view
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;
This query operation takes forever to run. Also, I'm assuming that the indexes on the underlying tables doesn't work when querying on the views. I'm looking for other alternative to speed up my query performance when dealing with views having 500+ million records.
Note:- Materialized view is not an option for me.
Indexes on table
SNO,MSG_ID,ORIG_SYS_NO, compound index(ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO)
Indexe on table