OraFAQ Forum: Performance Tuning » Materialized view not getting used

Home » RDBMS Server » Performance Tuning » Materialized view not getting used (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized view not getting used [message #332107]

Mon, 07 July 2008 09:25

vkumar
Messages: 1
Registered: July 2008

Junior Member

Hi,

I have created materialized view as below-
CREATE MATERIALIZED VIEW mv_trk_ctlg
build IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT product_info_key,TERRITORY_KEY,partner_key,
COUNT (metadata_available) metadata_available,
COUNT (BA_CUSTOM_TONE) BA_CUSTOM_TONE,
COUNT (ba_streaming) ba_streaming,
COUNT (ba_download) ba_download,
COUNT (BA_FULL_ALBUM) BA_FULL_ALBUM,
COUNT (COPYRIGHT_CUSTOM_TONE) COPYRIGHT_CUSTOM_TONE,
COUNT (COPYRIGHT_STREAMING) COPYRIGHT_STREAMING,
COUNT (COPYRIGHT_DOWNLOAD) COPYRIGHT_DOWNLOAD,
COUNT (COPYRIGHT_FULL_ALBUM) COPYRIGHT_FULL_ALBUM,
COUNT (TR_DELVERBL_DOWNLD) TR_DELVERBL_DOWNLD,
COUNT (TR_DELVERBL_STRMNG) TR_DELVERBL_STRMNG
FROM (
SELECT product_info_key,TERRITORY_KEY,partner_key,
DECODE(metadata_available, 'Y', 'Y') metadata_available,
DECODE(BA_CUSTOM_TONE, 'Y ', 'Y ') BA_CUSTOM_TONE,
DECODE(ba_streaming, 'Y ', 'Y ') ba_streaming,
DECODE(ba_download, 'Y ', 'Y ') ba_download,
DECODE(BA_FULL_ALBUM, 'Y ', 'Y ') BA_FULL_ALBUM,
DECODE(COPYRIGHT_CUSTOM_TONE, 'Y ', 'Y ') COPYRIGHT_CUSTOM_TONE,
DECODE(COPYRIGHT_STREAMING, 'Y ', 'Y') COPYRIGHT_STREAMING,
DECODE(COPYRIGHT_DOWNLOAD, 'Y ', 'Y') COPYRIGHT_DOWNLOAD,
DECODE(COPYRIGHT_FULL_ALBUM, 'Y ', 'Y') COPYRIGHT_FULL_ALBUM,
DECODE(TR_DELVERBL_DOWNLD, 'Y ', 'Y') TR_DELVERBL_DOWNLD,
DECODE(TR_DELVERBL_STRMNG, 'Y ', 'Y') TR_DELVERBL_STRMNG
FROM TRACK_CATALOG)
GROUP BY product_info_key,TERRITORY_KEY,partner_key;

and have set the rquired parameters like-
optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced

It is getting used only when the select query is similar to the one that is used in creating the MV. In all other cases base table is used. Any idea why MV is not used when only few qggregates are requested?

Report message to a moderator