Hi all

I have created a materialized view. One concept of materialized view is
that when you run a program using the tables, it should use materialized
view for the aggregates. But I feel it this is not correct. Any
comments on it will be really appreciated.

The views are used to craete this materialized view . I also attached
the file with the view


Below I am putting the script for Materialized view.
CREATE MATERIALIZED VIEW TRSADM.TRS_MV_DMS_ISSUE
TABLESPACE DATA_DIMENSION
NOCACHE
NOLOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT
week_no,
trs_day_no,
station_no,
station,
multi_drop_address,
machine_id,
ORIGIN_STATION_NO,
ORIGIN_STATION_NAME,
DESTINATION_STATION_NO,
DESTINATION_STATION_NAME,
device_type,
location_id,
ticket_type_id,
aimi_ticket_type_id,
source_system,
payment_type,
sum(group_size) GROUP_SIZE,
sum(FARE_CITYRAIL_PORTION_INCL_GST) FARE_CITYRAIL_PORTION_INCL_GST,
sum( FARE_CITYRAIL_PORTION_EXCL_GST) FARE_CITYRAIL_PORTION_EXCL_GST,
sum(ticket_value) ticket_value,
sum(GST_PAID) GST_PAID,
sum(0) Pay_Away,
sum(0) ALC_RTF_PAYMENT,
sum(0) net_revenue
FROM
TRS_vw_issue_dms_view GROUP By (week_no,
Trs_day_no,
station_no,
station,
multi_drop_address,
machine_id,
ORIGIN_STATION_NO,
ORIGIN_STATION_NAME,
DESTINATION_STATION_NO,
DESTINATION_STATION_NAME,
device_type,
location_id,
ticket_type_id,
aimi_ticket_type_id,
source_system,
payment_type) union all SELECT week_no,
trs_day_no,
station_no,
station,
multi_drop_address,
machine_id,
ORIGIN_STATION_NO,
ORIGIN_STATION_NAME,
DESTINATION_STATION_NO,
DESTINATION_STATION_NAME,
device_type,
location_id,
ticket_type_id,
aimi_ticket_type_id,
source_system,
payment_type,
sum(group_size) GROUP_SIZE,
sum(FARE_CITYRAIL_PORTION_INCL_GST) FARE_CITYRAIL_PORTION_INCL_GST,
sum( FARE_CITYRAIL_PORTION_EXCL_GST) FARE_CITYRAIL_PORTION_EXCL_GST,
sum(ticket_value) ticket_value,
sum(GST_PAID) GST_PAID,
sum(PAY_AWAY) PAY_AWAY,
sum(ALC_RTF_PAYMENT) ALC_RTF_PAYMENT,
sum(NET_REVENUE) net_revenue FROM TRS_VW_REVENUE_AFC_DMS_VIEW GROUP
By (week_no,
Trs_day_no,
station_no,
station,
multi_drop_address,
machine_id,
ORIGIN_STATION_NO,
ORIGIN_STATION_NAME,
DESTINATION_STATION_NO,
DESTINATION_STATION_NAME,
device_type,
location_id,
ticket_type_id,
aimi_ticket_type_id,
source_system,
payment_type)






This e-mail and any attachments may contain confidential information that is intended solely for the use of the intended recipient and may be subject to copyright. If you receive this e-mail in error, please notify the sender immediately and delete the e-mail and its attachments from your system. You must not disclose, copy or use any part of this e-mail if you are not the intended recipient. Any opinion expressed in this e-mail and any attachments is not an opinion of RailCorp unless stated or apparent from its content. RailCorp is not responsible for any unauthorised alterations to this e-mail or any attachments. RailCorp will not incur any liability resulting directly or indirectly as a result of the recipient accessing any of the attached files that may contain a virus.

Oracle LazyDBA home page