SQL Performance (MOSC)

MOSC Banner

Slow performance on a view joining to a materialized view with indexed columns.

edited May 8, 2016 5:00AM in SQL Performance (MOSC) 5 commentsAnswered

Hi, I have a performance issue with a view joining to a materialized view. 

Oracle RDBSM 12.1.0.2 

Oracle Linux Server release 6.6

When I query on the view by itself, the performance and explain plan look good.  The view pic_meter_reading_v2_cme  joins a second view pic_condition_due_v, to some look-up and data tables. 

Here is the view:

create or replace view pic_meter_reading_v2_cme as

        (select

            pcd.permit_application_id,

            pcd.wup_permit_nbr,

            pcd.wup_revision_nbr,

            pcd.wup_application_id,

            pcd.withdrawal_nbr,

            pcd.owner_identifier_txt,

            pcd.sequence_nbr,

            pcd.wup_condition_desc,

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center