0

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.

view:-

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.

Edit:-

Indexes on table t_vz_service_utilization are SNO,MSG_ID,ORIG_SYS_NO, compound index(ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO)

Indexe on table t_vz_lookup is SNO

Explain Plan:-

enter image description here

2
  • What is the execution plan? What is the reason for FULL(su1)? – Wernfried Domscheit Mar 28 '20 at 10:39
  • I need to get all the rows from the view matching my condition. hence it requires a full scan. Correct me if my understanding is wrong. – Madhu Stv Mar 29 '20 at 13:32
1
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
;

There. Now you can use the lookup table on this, something like this.

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 )
;

Of course this could be far from optimal, but you gave us nothing else than the query. We do not know your indexes (which columns, b-tree/bitmap?), constraints (FK/UK/PK), table structure (width, row migration/chained rows), init parameters (star transformation enabled?), table/index statistics, not even a plan...

3
  • I have edited the question with table indexes and the Explain Plan. – Madhu Stv Mar 29 '20 at 14:11
  • 1
    @MadhuStv Not the plan format we generally use, but at least we now know something: you use Exadata, your parallel hint is not working, indexes are not utilized (not necesarily a problem), the join order is what I expected but not what I would use for this type of query. The problem with this approach is that it produces the result of all those joins and finally performs the DISTINCT (HASH UNIQUE) on the whole. Did you try the above? – Balazs Papp Mar 29 '20 at 14:18
  • Your query performs better( which is executed on the tables) than the query executed on the view. In this case, I don't see any reason to use the view at all. – Madhu Stv Mar 30 '20 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.