Slow performance on a view joining to a materialized view with indexed columns.
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,